Where can I get a SQL script to do I/O performance test with SELECT statement using CURSOR to loop through records?

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

⇑ I/O Tests on SELECT Statements

⇑⇑ SQL Server Storage Tutorials