Collections:
Test Script for SELECT with CURSOR
Where can I get a SQL script to do I/O performance test with SELECT statement using CURSOR to loop through records?
✍: FYIcenter.com
Here is a SQL script to do I/O performance test with SELECT statement using CURSOR to loop through records. It uses the fn_virtualfilestats() function table to calculate the I/O statistics while doing SELECT Statement performance tests.
-- fyi_select_cursor.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-2) CREATE TABLE FYI_DATA ( 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) ) SET @I = 0 WHILE @I < @Count BEGIN INSERT INTO FYI_DATA VALUES (@Message,@Message,@Message,@Message, @Message,@Message,@Message,@Message) SET @I = @I+1 END WAITFOR DELAY '00:00:05' select t.name, p.partition_id, p.rows, a.type_desc, a.data_space_id, a.data_pages, f.file_id, s.NumberReads, s.BytesRead, s.NumberWrites, s.BytesWritten into #FYI_STATS from sys.objects t, sys.partitions p, sys.allocation_units a, sys.database_files f, fn_virtualfilestats(DB_ID(),NULL) s 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 f.file_id = s.fileid DECLARE Result CURSOR FOR SELECT * FROM FYI_DATA 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 @STime DATETIME SET @STime = GETDATE() OPEN Result FETCH NEXT FROM Result INTO @Msg_1, @Msg_2, @Msg_3, @Msg_4, @Msg_5, @Msg_6, @Msg_7, @Msg_8 DECLARE @Bytes BIGINT SET @Bytes = 0 WHILE @@FETCH_STATUS = 0 BEGIN 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 FETCH NEXT FROM Result INTO @Msg_1, @Msg_2, @Msg_3, @Msg_4, @Msg_5, @Msg_6, @Msg_7, @Msg_8 END DECLARE @ETime DATETIME SET @ETime = GETDATE() CLOSE Result; DEALLOCATE Result; WAITFOR DELAY '00:00:05' DECLARE @Duration INT SET @Duration = DATEDIFF(millisecond,@STime,@ETime) SELECT @Size AS Size, @Count AS Count, @Duration AS Duration, @Bytes as Bytes, p.rows-l.rows as '+Rows', a.type_desc, a.data_pages-l.data_pages as "+Pages", f.file_id, s.NumberReads-l.NumberReads as "Reads", s.BytesRead-l.BytesRead as "Bytes Read", s.NumberWrites-l.NumberWrites as "Writes", s.BytesWritten-l.BytesWritten as "Bytes Written", l.* from sys.objects t, sys.partitions p, sys.allocation_units a, sys.database_files f, fn_virtualfilestats(DB_ID(),NULL) s, #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 f.file_id = s.fileid 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 "WAITFOR DELAY '00:00:05'" function is called to wait for 5 seconds to give enough time for the system to flush out cached data.
Run the script with the default @Size and @Count values on SQL Server. The output may give you 2 records with many fields. But you can divide the record into 2 chunks:
SIZE COUNT DURATION Bytes THROUGHPUT LATENCY +ROWS 256 100 16 25600 6250 0 0 type_desc +Pages file_id Reads Bytes Read Writes Bytes Written IN_ROW_DATA 0 1 0 0 0 0 ROW_OVERFLOW_DATA 0 1 0 0 0 0
As you can see, the script reported that 25600 bytes of data was retrieved from the target table. But the fn_virtualfilestats view reported 0 reads from the physical file. This could happen in two possible situations, data in target table was cached in memory, and fn_virtualfilestats view was not updated yet.
⇒ No Reads on SELECT with CURSOR on SQL Server 2016
⇐ I/O Tests on SELECT Statements
2019-05-06, 1260🔥, 0💬
Popular Posts:
How to Open and Close Internet Explorer with UFT script? One way to open and close Internet Explorer...
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...
What are date and time test values? Date and time test values are frequently needed in testing date ...
How to generate currency test values? Currency test values are frequently needed in testing date and...