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
2019-04-23, 1344🔥, 0💬
Popular Posts:
How to turn off HTTP response compression in SoapUI? HTTP response compression reduces the data size...
How to convert a date and time to a standard format? Date and time can be displayed in a number of s...
How to valid IPv6 addresses? In order to help your programming or testing tasks, FYIcenter.com has d...
How to valid UUID/GUID strings? In order to help your programming or testing tasks, FYIcenter.com ha...
How to generate MAC addresses? To help you to obtain some MAC addresses for testing purpose, FYIcent...