Collections:
Test Script for INSERT with PRIMARY KEY
Where can I get a SQL script to do I/O performance test with INSERT statement using BIGINT with PRIMARY KEY?
✍: FYIcenter.com
Here is a SQL script to do I/O performance test
with INSERT statement using BIGINT with PRIMARY KEY.
-- fyi_insert_primary_key.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 TEMP_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 TEMP_DATA_PK PRIMARY KEY CLUSTERED (ID) ) select t.name, p.partition_id, p.rows, a.type_desc, a.data_space_id, a.data_pages, f.file_id into #TEMP_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 TEMP_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, #TEMP_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 #TEMP_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 '72057601516896256 100 IN_ROW_DATA 4 1 256 100 63 '72057601516896256 100 ROW_OVERFLOW_DATA 0 1
Â
⇒ Test Script for INSERT with MEMORY_OPTIMIZED
⇠Test Script for INSERT with BIGINT
⇑ I/O Tests on INSERT Statements
⇑⇑ SQL Server Storage Tutorials
2019-05-17, 1083👍, 0💬
Popular Posts:
Why I am getting gzip compressed HTTP response in SoapUI? If you run a HTTP request for some Website...
How to generate currency test values? Currency test values are frequently needed in testing date and...
In what order thread groups are executed in JMeter? Thread groups, including regular "Thread Groups"...
How to see my IP address Host Name? To help you to see your IP Address Host Name, FYIcenter.com has ...
How to set Content-Type to application/json? The server requires the Content-Type to be application/...