SELECT with PRIMARY KEY on SQL Server 2016

Q

What is the data read performance with the SELECT statement with a PRIMARY KEY field on SQL Server 2016?

✍: FYIcenter.com

A

To see the data read performance with the SELECT statement with a PRIMARY KEY field on SQL Server 2016, you can run the SQL script, fyi_select_in_memory.sql, presented in the last tutorial with different @Count and @Size.

The following table shows the summary of the test results. Some fields are removed to save space. additional fields are added with calculations on existing fields.

@@version = Microsoft SQL Server 2016 - 13.0.5292.0 (X64) 
  
 Size   Count  Duration        Bytes  Throughput MB/s 
  256     100         3        25600             8.14
  256     500        36       128000             3.39
  256    1000       103       256000             2.37
  256    5000        63      1280000            19.38
  256   10000       123      2560000            19.85
  512   10000       126      5040000            38.15
 1024   10000       140     10160000            69.21
 2048   10000       156     20480000           125.20
 4096   10000       156     40960000           250.40
 8192   10000       203     81840000           384.48
16384   10000       280    163760000           557.76
32768   10000       360    327680000           868.06
32768   20000       703    655360000           889.05
32768   50000      1653   1638400000           945.25
32768  100000      3516   3276800000           888.79
32768  500000     19656  16384000000           794.92

As you can see the peak throughput of reading data, 945.25 MB/s reached at @Size=32768 and @Count=50000.

 

? SELECT with PRIMARY KEY on Azure SQL Database

? Test Script for SELECT with PRIMARY KEY

? I/O Tests on SELECT Statements

?? SQL Server Storage Tutorials

2019-04-24, 174👍, 0💬