Where can I get a SQL script to do I/O performance test with SELECT statement using BIGINT PRIMARY KEY as the search criteria?

Here is a SQL script to do I/O performance test with SELECT statement using BIGINT PRIMARY KEY as the search criteria. It uses the fn_virtualfilestats() function table to calculate the I/O statistics while doing SELECT Statement performance tests.

-- fyi_select_primary_key.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 CLUSTERED (ID)
) 

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

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 @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()

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"
   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  +ROWS
256   100    16        25600  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.

⇒ SELECT with PRIMARY KEY on SQL Server 2016

⇐ SELECT with BIGINT on Azure SQL Database

⇑ I/O Tests on SELECT Statements

⇑⇑ SQL Server Storage Tutorials