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, 1010👍, 0💬

Test Script for SELECT with BIGINT
Where can I get a SQL script to do I/O performance test with SELECT statement using BIGINT as the search criteria? Here is a SQL script to do I/O performance test with SELECT statement using BIGINT as the search criteria. It uses the fn_virtualfilestats() function table to calculate the I/O statisti... 2019-04-19, 1072👍, 0💬

SELECT with BIGINT on SQL Server 2016
What is the data read performance with the SELECT statement using BIGINT as the search criteria on SQL Server 2016? To see the data read performance with the SELECT statement using BIGINT as the search criteria on SQL Server 2016, you can run the SQL script, fyi_select_bigint.sql, presented in the l... 2019-04-29, 1070👍, 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-05-18, 1060👍, 0💬

SELECT Test Summary on SQL Server 2016
What are performance differences of running the SELECT statement with different criteria and table storage options on SQL Server 2016? Here is a summary of performance differences of running the SELECT statement with different criteria and table storage options on SQL Server 2016. Throughput is calc... 2019-05-15, 1052👍, 0💬

SELECT with MEMORY_OPTIMIZED Table on SQL Server 2016
What is the data read performance with the SELECT statement with a MEMORY_OPTIMIZED table on SQL Server 2016? To see the data read performance with the SELECT statement with a MEMORY_OPTIMIZED table on SQL Server 2016, you can run the SQL script, fyi_select_in_memory.sql, presented in the last tutor... 2019-04-17, 1049👍, 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-04-23, 1048👍, 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-04-24, 1011👍, 0💬

SELECT with BIGINT on Azure SQL Database
What is the data read performance with the SELECT statement using BIGINT as the search criteria on Azure SQL Database? To see the data read performance with the SELECT statement using BIGINT as the search criteria on Azure SQL Database, you can run the SQL script, fyi_select_bigint.sql, presented in... 2019-05-19, 994👍, 0💬

No Reads on SELECT with CURSOR on Azure SQL Database
Why is there no reads recorded by fn_virtualfilestats() on SELECT statements with CURSOR on Azure SQL Database? If you run the SQL script, fyi_select_cursor.sql, presented in the last tutorial with different @Count and @Size on Azure SQL Database, you will see that the fn_virtualfilestats() function... 2019-04-25, 982👍, 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.