Collections:
Test Script for INSERT with MEMORY_OPTIMIZED
Where can I get a SQL script to do I/O performance test with INSERT statement using BIGINT with MEMORY_OPTIMIZED?
✍: FYIcenter.com
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
2019-05-24, 1256🔥, 0💬
Popular Posts:
How to generate IPv6 Addresses? To help you to obtain some IPv6 addresses for testing purpose, FYIce...
How to Pass Windows Environment Variable to JMeter? I want to use Windows temporary folder environme...
How to generate IPv6 Addresses? To help you to obtain some IPv6 addresses for testing purpose, FYIce...
How to use HTTP protocol to post an XML message to a server? You can follow this tutorial to post an...
How to perform UUDecode (Unix-to-Unix Decode)? UUEncode is Unix-to-Unix encoding used on Unix system...