Where can I get a SQL script to do I/O performance test with INSERT statement using BIGINT with MEMORY_OPTIMIZED?

Here is a SQL script to do I/O performance test with INSERT statement using BIGINT with MEMORY_OPTIMIZED. In this case, the table content should be cached in memory as much as possible.

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

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

DECLARE @STime DATETIME
DECLARE @ETime DATETIME
DECLARE @Duration INT

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) 

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

SET @STime = GETDATE()
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
SET @ETime = GETDATE()

SET @Duration = DATEDIFF(millisecond,@STime,@ETime)
SELECT @Size AS Size, @Count AS Count, @Duration AS Duration, 
   ''''+CONVERT(VARCHAR,p.partition_id) AS Partition, p.rows-l.rows AS '+Rows', 
   a.type_desc AS Allocation, 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.

Run the script with the default @Size and @Count values on SQL Server. The output shows that the script is working correctly:

SIZE  COUNT  DURATION  PARTITION           +ROWS  ALLOCATION         +PAGES  FILE_ID
256   100    63        '72057601518206976  100    IN_ROW_DATA        0       20731
256   100    63        '72057601518272512  100    IN_ROW_DATA        0       20731

Note that 2 partitions are created for this table, with 0 data pages added. This sounds correct, because the data is cached in memory.

⇒ I/O Tests on SELECT Statements

⇐ Test Script for INSERT with PRIMARY KEY

⇑ I/O Tests on INSERT Statements

⇑⇑ SQL Server Storage Tutorials