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.