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, 895👍, 0💬
Popular Posts:
Here is a list of top popular Software Test Management Commercial and Free Tools: Rank Name Vendor P...
How to perform UUDecode (Unix-to-Unix Decode)? UUEncode is Unix-to-Unix encoding used on Unix system...
How to generate test fractional numbers? Test fractional numbers are numbers with fractions that are...
How to validate and decode MAC (Media Access Control) addresses? In order to help your programming o...
How to generate ISBN numbers? To help you to obtain some ISBN numbers for testing purpose, FYIcenter...