Where can I get a SQL script to do I/O performance test with SELECT statement on a MEMORY_OPTIMIZED table?

Here is a SQL script to do I/O performance test with SELECT statement using BIGINT PRIMARY KEY on a MEMORY_OPTIMIZED table.

-- fyi_select_in_memory.sql
-- Copyright (c) FYIcenter.com 
-- 

DECLARE @Count INT
SET @Count = 100
DECLARE @Size INT
SET @Size = 256

DECLARE @I INT
DECLARE @Message VARCHAR(5760)
SET @Message = 'The quick brown fox jumps over the lazy dog. '
SET @I = 7
WHILE @I > 0 BEGIN
   SET @Message = @Message+@Message
   SET @I = @I - 1
END
SET @Message = SUBSTRING(@Message,1,(@Size-8)/8-2)

CREATE TABLE FYI_DATA (
   ID BIGINT,
   MSG_1 VARCHAR(4096),
   MSG_2 VARCHAR(4096), 
   MSG_3 VARCHAR(4096), 
   MSG_4 VARCHAR(4096), 
   MSG_5 VARCHAR(4096), 
   MSG_6 VARCHAR(4096), 
   MSG_7 VARCHAR(4096), 
   MSG_8 VARCHAR(4096),
   CONSTRAINT FYI_DATA_PK PRIMARY KEY NONCLUSTERED (ID)
) WITH (MEMORY_OPTIMIZED=ON) 

SET @I = 1
WHILE @I <= @Count BEGIN
   INSERT INTO FYI_DATA VALUES (@I,@Message,@Message,@Message,@Message,
      @Message,@Message,@Message,@Message)
   SET @I = @I+1
END

select t.name, p.partition_id, p.rows, a.type_desc, 
   a.data_space_id, a.data_pages, f.file_id
   into #FYI_STATS
   from sys.objects t, sys.partitions p, sys.allocation_units a, 
      sys.database_files f
   where t.name = 'FYI_DATA'
      and t.object_id = p.object_id 
      and p.hobt_id = a.container_id
      and a.data_space_id = f.data_space_id

DECLARE @Msg_1 VARCHAR(4096)
DECLARE @Msg_2 VARCHAR(4096) 
DECLARE @Msg_3 VARCHAR(4096) 
DECLARE @Msg_4 VARCHAR(4096) 
DECLARE @Msg_5 VARCHAR(4096) 
DECLARE @Msg_6 VARCHAR(4096) 
DECLARE @Msg_7 VARCHAR(4096) 
DECLARE @Msg_8 VARCHAR(4096)

DECLARE @Bytes BIGINT
SET @Bytes = 0

DECLARE @STime DATETIME
SET @STime = GETDATE()
SET @I = 1
WHILE @I <= @Count BEGIN
   SELECT @Msg_1=MSG_1, @Msg_2=MSG_2, @Msg_3=MSG_3, @Msg_4=MSG_4,
      @Msg_5=MSG_5, @Msg_6=MSG_6, @Msg_7=MSG_7, @Msg_8=MSG_8
    FROM FYI_DATA WHERE ID=@I 
   SET @Bytes = @Bytes + 8
   SET @Bytes = @Bytes + LEN(@Msg_1) + 2
   SET @Bytes = @Bytes + LEN(@Msg_2) + 2
   SET @Bytes = @Bytes + LEN(@Msg_3) + 2
   SET @Bytes = @Bytes + LEN(@Msg_4) + 2
   SET @Bytes = @Bytes + LEN(@Msg_5) + 2
   SET @Bytes = @Bytes + LEN(@Msg_6) + 2
   SET @Bytes = @Bytes + LEN(@Msg_7) + 2
   SET @Bytes = @Bytes + LEN(@Msg_8) + 2
   SET @I = @I+1    
END
DECLARE @ETime DATETIME
SET @ETime = GETDATE()

DECLARE @Duration INT
SET @Duration = DATEDIFF(millisecond,@STime,@ETime)
SELECT @Size AS Size, @Count AS Count, @Duration AS Duration, @Bytes as Bytes,
   ''''+CONVERT(VARCHAR,p.partition_id) AS Partition, p.rows-l.rows AS '+Rows', 
   a.type_desc, a.data_pages-l.data_pages as "+Pages", f.file_id  
   from sys.objects t, sys.partitions p, sys.allocation_units a, 
      sys.database_files f, #FYI_STATS l 
   where t.name = 'FYI_DATA'
      and t.object_id = p.object_id 
      and p.hobt_id = a.container_id
      and a.data_space_id = f.data_space_id
      and l.file_id = f.file_id
      and l.partition_id = p.partition_id
      and l.type_desc = a.type_desc

DROP TABLE #FYI_STATS
DROP TABLE FYI_DATA

This script uses a temporary table to keep a copy of different counters of about the target table before the test.

New values of those same counters are compared with old values to calculate the changes.

The fn_virtualfilestats() function view is not used to calculate the I/O statistics, because it has no counters on files that support MEMORY_OPTIMIZED tables.

Run the script with the default @Size and @Count values on SQL Server.

Size  Count  Duration  Bytes  Partition          +Rows  type_desc    +Pages  file_id
256   100    0         25600  72057601518993408  0      IN_ROW_DATA  0       65537
256   100    0         25600  72057601519058944  0      IN_ROW_DATA  0       65537

As you can see, reading data from an in-memory (MEMORY_OPTIMIZED) table is really fast. It took less than 1 ms to get 25 KB data.

There 2 partitions created for the in-memory (MEMORY_OPTIMIZED) table on the same file ID.

 

⇒ SELECT with MEMORY_OPTIMIZED Table on SQL Server 2016

⇐ SELECT with PRIMARY KEY on Azure SQL Database

⇑ I/O Tests on SELECT Statements

⇑⇑ SQL Server Storage Tutorials