6/16/21

Do I have that backup somewhere...

Yesterday evening I just cooked up a simple backup recipe, reason was just a simple weekly full/differential backup over all non-system databases on one instance. It's far from perfect, but it works.

It consists of a database to store information about backups of databases in a table with information about the last backups, and a procedure to do the backup. The below script also adds all databases with an database_id higher than 4, i.e. excludes master, model, msdb, and tempdb. If you want specific databases only just change the content of the Managed_Databases table.

USE master;
GO
CREATE DATABASE Simple_Maintenance;
GO
USE Simple_Maintenance;
GO
CREATE TABLE Managed_Databases
 (
  [db_id] int NOT NULL,
  [db_name] sysname NOT NULL,
  Last_Full_Backup datetime2(0) NULL,
  Last_Full_Backup_Number int NULL,
  Last_Differential_Backup datetime2(0) NULL,
  Last_Differential_Backup_Number int NULL
 );
GO
INSERT INTO Managed_Databases([db_id], [db_name])
SELECT database_id, name FROM sys.databases WHERE database_id > 4;
GO
And then we have the procedure, it's some lines of code. Hopefully you can read through it.

USE Simple_Maintenance
GO
CREATE OR ALTER PROCEDURE Make_Backup
 @backup_destination varchar(max) = 'F:\Databases\MSSQL13.MSSQLSERVER\MSSQL\Backup',
 @full_backup_day int    = 7,
 @debug int       = 1
/*
 Day for full backup
 1 Monday
 2 Tuesday
 3 Wednesday
 4 Thursday
 5 Friday
 6 Saturday
 7 Sunday
*/
AS
BEGIN
SET DATEFIRST 1;
DECLARE
 @database varchar(max),
 @name_template varchar(max)  = '@database_@type_@number',
 @type varchar(max),
 @number varchar(max);
DECLARE
 @backup varchar(max) = '
BACKUP DATABASE [@database]
TO DISK = ''@backup_destination\@name.BAK''
WITH
 STATS = 10,
INIT, -- I FORGOT THIS IN THE ORIGINAL RECIPE
 COMPRESSION
',
 @exec varchar(max),
 @db_id int,
 @day int = DATEPART(weekday, getdate()),
 @name varchar(max);
DECLARE dbs CURSOR FOR
SELECT [DB_ID], [DB_NAME] FROM Managed_Databases;
OPEN dbs;
FETCH NEXT FROM dbs INTO @db_id, @database;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @day = @full_backup_day
BEGIN
 SET @type = 'FULL';
 SELECT @number = CAST((ISNULL(Last_Full_Backup_Number, 2) % 2) + 1 as varchar(max)) FROM Managed_Databases WHERE [DB_ID] = @db_id;
 SET @name = REPLACE(
    REPLACE(
    REPLACE(
     @name_template,  '@database',@database),
        '@type', @type),
        '@number', @number);
 SET @exec = REPLACE(
    REPLACE(
    REPLACE(
     @backup, '@database',@database),
        '@backup_destination', @backup_destination),
        '@name', @name);
END
ELSE
BEGIN
 SET  @type = 'DIFFERENTIAL';
 SELECT @number = CAST((ISNULL(Last_Differential_Backup_Number, 2) % 2) + 1 as varchar(max)) FROM Managed_Databases WHERE [DB_ID] = @db_id;
 SET @name = REPLACE(
    REPLACE(
    REPLACE(
     @name_template,  '@database',@database),
        '@type', @type),
        '@number', @number);
 SET @exec = REPLACE(
    REPLACE(
    REPLACE(
     @backup, '@database',@database),
        '@backup_destination', @backup_destination),
        '@name', @name) +
    ', ' + @type;
END;
IF @debug = 1
BEGIN
PRINT @exec;
END
ELSE
BEGIN
IF @day = @full_backup_day
BEGIN
EXEC (@exec);
UPDATE Managed_Databases
 SET
  Last_Full_Backup = getdate(),
  Last_Full_Backup_Number = CAST(@number as int)
 WHERE [DB_ID] = @db_id;
END
ELSE
BEGIN
IF EXISTS(SELECT 1 FROM Managed_Databases WHERE [DB_ID] = @db_id AND Last_Full_Backup IS NOT NULL)
BEGIN
EXEC (@exec);
UPDATE Managed_Databases
 SET
  Last_Differential_Backup = getdate(),
  Last_Differential_Backup_Number = CAST(@number as int)
 WHERE [DB_ID] = @db_id;
END;
END;
END;
FETCH NEXT FROM dbs INTO @db_id, @database;
END;
CLOSE dbs;
DEALLOCATE dbs;
END;
And finally you just need to run the procedure. If you execute it with default parameters it will run in debug mode and only print out the syntax for doing backups. The prinout do not care if you have a full backup or not when there is a differential backup day. It gives you the syntax.

The procedure do not have any error checking either, you should probably add try...catch logic, and also checks in the database exists, and are in a backupable state. As I said, I cooked this up for fixing a simple need.

6/15/21

Some In Memory OLTP analytical scripts

I will add descriptions about this in time.

Please use this as an inspiration for your own In Memory OLTP analysis.
CREATE DATABASE InMemDemos
ON PRIMARY (NAME=InMemDemos_Data, FILENAME='D:\Data\InMemDemos_Data.mdf', SIZE=10MB, FILEGROWTH=10MB),
FILEGROUP InMemDemos_FG CONTAINS MEMORY_OPTIMIZED_DATA (NAME=InMemDemos_FG, FILENAME='D:\Data\InMemDemos_FG')
LOG ON (NAME=InMemDemos_Log, FILENAME='D:\Data\InMemDemos_Log.ldf', SIZE=10MB, FILEGROWTH=10MB)
First part creates the database, Second part creates three tables:
USE InMemDemos
GO
CREATE TABLE InMem_BC100
(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=100),
Data CHAR(100) COLLATE Finnish_Swedish_BIN2 NOT NULL
)
WITH
(
MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA
)
CREATE TABLE InMem_BC1000
(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000),
Data CHAR(100) COLLATE Finnish_Swedish_BIN2 NOT NULL
)
WITH
(
MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA
)
CREATE TABLE InMem_BC1000000
(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
Data CHAR(100) COLLATE Finnish_Swedish_BIN2 NOT NULL
)
WITH
(
MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA
)
Let us populate the tables :
WITH
a2(Col) AS (SELECT NULL UNION ALL SELECT NULL),
a4(Col) AS (SELECT NULL FROM a2 x CROSS JOIN a2 y),
a16(Col) AS (SELECT NULL FROM a4 x CROSS JOIN a4 y),
a256(Col) AS (SELECT NULL FROM a16 x CROSS JOIN a16 y),
a64k(Col) AS (SELECT NULL FROM a256 x CROSS JOIN a256 y),
a16m(Col) AS (SELECT NULL FROM a64k x CROSS JOIN a64k y),
tally(num) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM a16m)
INSERT INTO InMem_BC100(Data)
SELECT CAST(num as VARCHAR(100)) + REPLICATE('_', 100-(LEN(CAST(num as VARCHAR(100))) * 2)) + CAST(num as VARCHAR(100)) FROM tally WHERE num <= 500000
GO
WITH
a2(Col) AS (SELECT NULL UNION ALL SELECT NULL),
a4(Col) AS (SELECT NULL FROM a2 x CROSS JOIN a2 y),
a16(Col) AS (SELECT NULL FROM a4 x CROSS JOIN a4 y),
a256(Col) AS (SELECT NULL FROM a16 x CROSS JOIN a16 y),
a64k(Col) AS (SELECT NULL FROM a256 x CROSS JOIN a256 y),
a16m(Col) AS (SELECT NULL FROM a64k x CROSS JOIN a64k y),
tally(num) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM a16m)
INSERT INTO InMem_BC1000(Data)
SELECT CAST(num as VARCHAR(100)) + REPLICATE('_', 100-(LEN(CAST(num as VARCHAR(100))) * 2)) + CAST(num as VARCHAR(100)) FROM tally WHERE num <= 500000
GO
WITH
a2(Col) AS (SELECT NULL UNION ALL SELECT NULL),
a4(Col) AS (SELECT NULL FROM a2 x CROSS JOIN a2 y),
a16(Col) AS (SELECT NULL FROM a4 x CROSS JOIN a4 y),
a256(Col) AS (SELECT NULL FROM a16 x CROSS JOIN a16 y),
a64k(Col) AS (SELECT NULL FROM a256 x CROSS JOIN a256 y),
a16m(Col) AS (SELECT NULL FROM a64k x CROSS JOIN a64k y),
tally(num) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM a16m)
INSERT INTO InMem_BC1000000(Data)
SELECT CAST(num as VARCHAR(100)) + REPLICATE('_', 100-(LEN(CAST(num as VARCHAR(100))) * 2)) + CAST(num as VARCHAR(100)) FROM tally WHERE num <= 500000
How about some checkpoint statistics:
SELECT * FROM sys.dm_db_xtp_checkpoint_stats
 And status about the Data and Delta files:
SELECT
da.container_guid,
da.relative_file_path AS Data_File,
de.relative_file_path AS Delta_File,
da.internal_storage_slot,
da.state_desc,
da.file_size_in_bytes AS Data_File_Size,
da.file_size_used_in_bytes AS Data_File_Size_Used,
de.file_size_in_bytes AS Delta_File_Size,
de.file_size_used_in_bytes AS Delta_File_Size_Used,
da.inserted_row_count,
de.deleted_row_count
FROM sys.dm_db_xtp_checkpoint_files da
INNER JOIN sys.dm_db_xtp_checkpoint_files de
ON da.checkpoint_file_id = de.checkpoint_pair_file_id
WHERE da.file_type_desc = 'DATA' AND de.file_type_desc = 'DELTA'
And continue with some Table statistics:
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS Table_Name,
xtms.memory_allocated_for_table_kb,
xtms.memory_used_by_table_kb,
xtms.memory_allocated_for_indexes_kb,
xtms.memory_used_by_indexes_kb
FROM sys.schemas s
INNER JOIN sys.tables T
ON s.schema_id = t.schema_id
INNER JOIN sys.dm_db_xtp_table_memory_stats xtms
ON t.object_id = xtms.object_id
 All the way to specific indexes:
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS Table_Name,
i.name AS Index_Name,
i.type_desc AS Index_Type,
xhis.total_bucket_count,
xhis.empty_bucket_count,
xhis.avg_chain_length,
xhis.max_chain_length,
xmc.allocated_bytes,
xmc.used_bytes,
xis.*
FROM sys.schemas s
INNER JOIN sys.tables T
ON s.schema_id = t.schema_id
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
INNER JOIN sys.dm_db_xtp_index_stats xis
ON t.object_id = xis.object_id AND i.index_id = xis.index_id
INNER JOIN sys.dm_db_xtp_hash_index_stats xhis
ON xis.object_id = xhis.object_id AND xis.index_id = xhis.index_id
INNER JOIN sys.dm_db_xtp_memory_consumers xmc
ON xis.object_id = xmc.object_id AND xis.index_id = xmc.index_id
Remeber to throw some checkpoint from time to time:
CHECKPOINT
 Ok, I'm done now and I hope you can make use of the queries I shared here.