Database - SELECT with PRIMARY KEY on SQL Server 2016

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-05-25, 143👍, 0💬

Test Script for SELECT with MEMORY_OPTIMIZED
Where can I get a SQL script to do I/O performance test with SELECT statement on a MEMORY_OPTIMIZED table? Here is a SQL script to do I/O performance test with SELECT statement using BIGINT PRIMARY KEY on a MEMORY_OPTIMIZED table. -- fyi_select_in_memory.sql -- Copyright (c) FYIcenter.com -- DECLARE... 2019-05-25, 144👍, 0💬

SELECT with PRIMARY KEY on SQL Server 2016
What is the data read performance with the SELECT statement with a PRIMARY KEY field on SQL Server 2016? 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 d... 2019-05-25, 144👍, 0💬

Test Script for SELECT with PRIMARY KEY
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 ca... 2019-06-01, 141👍, 0💬

SELECT with PRIMARY KEY on Azure SQL Database
What is the data read performance with the SELECT statement using PRIMARY KEY as the search criteria on Azure SQL Database? To see the data read performance with the SELECT statement using PRIMARY KEY as the search criteria on Azure SQL Database, you can run the SQL script, fyi_select_primary_key.sq... 2019-05-25, 135👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.