Collections:
Test Script for SELECT with MEMORY_OPTIMIZED
Where can I get a SQL script to do I/O performance test with SELECT statement on a MEMORY_OPTIMIZED table?
✍: FYIcenter.com
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
2019-04-23, 1044👍, 0💬
Popular Posts:
In what order thread groups are executed in JMeter? Thread groups, including regular "Thread Groups"...
How to validate email address format? In order to help your programming or testing tasks, FYIcenter....
How to generate currency test values? Currency test values are frequently needed in testing date and...
How to generate test fractional numbers? Test fractional numbers are numbers with fractions that are...
How to perform regular expression pattern match with multiple occurrences? To perform a regular expr...