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.

Counting currency...

I was just stumbling over an problem on a forum. They wanted to count currency. Let us imagine you have an ATM that delivers every single coin and bill from a specific currency, and people will withdraw any specific amount, and you want to calculate how many of each to deliver.

Well, look no further.

DECLARE @Pengar int = 9888;
DECLARE
@Valörer TABLE (Värde int, Typ char(5), Benämning varchar(25))
INSERT INTO @Valörer (Värde, Typ, Benämning)
VALUES (1, 'Mynt', '1 kr')
, (2, 'Mynt', '2 kr')
, (5, 'Mynt', '5 kr')
, (10, 'Mynt', '10 kr')
, (20, 'Sedel', '20 kr')
, (50, 'Sedel', '50 kr')
, (100, 'Sedel', '100 kr')
, (200, 'Sedel', '200 kr')
, (500, 'Sedel', '500 kr')
, (1000, 'Sedel', '1000 kr');
WITH
cash AS (
SELECT
ROW_NUMBER() OVER(ORDER BY Värde DESC) AS Ordning,
Värde, Typ, Benämning
FROM @Valörer WHERE Värde <= @Pengar
),
b AS (
SELECT
Ordning, Värde, Typ, Benämning,
(@Pengar - (@Pengar % Värde))/Värde Antal,
@Pengar % Värde AS Rest
FROM cash a WHERE Ordning = 1
UNION ALL
SELECT
a.Ordning, a.Värde, a.Typ, a.Benämning,
(b.Rest - (b.Rest % a.Värde))/a.Värde,
b.Rest % a.Värde
FROM cash a INNER JOIN b ON a.Ordning = b.Ordning + 1
),
cashing as (
SELECT
Typ, Benämning, Antal,
Antal * Värde AS [Valör Värde],
SUM(Antal * Värde) OVER(ORDER BY Ordning ASC) AS [Totalt Värde]
FROM b WHERE Antal > 0
)
SELECT * FROM cashing;