SELECT with BIGINT on SQL Server 2016

Q

What is the data read performance with the SELECT statement using BIGINT as the search criteria on SQL Server 2016?

✍: FYIcenter.com

A

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 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  FILE_ID  READS  BYTES READ  Throughput MB/s
  256    100        13      25600        1      0           0             1.88
  256    500       110     128000        1      0           0             1.11
  256   1000       453     256000        1      0           0             0.54
  256   5000     10323    1280000        1      0           0             0.12
  256  10000     13760    2560000        1      0           0             0.18
  512  10000     17293    5040000        1      0           0             0.28
 1024  10000     22213   10160000        1      0           0             0.44
 2048  10000     52806   20480000        1      0           0             0.37
 4096  10000     95976   40960000        1      0           0             0.41
 8192  10000     98683   81840000        1      0           0             0.79
16384  10000    108390  163760000        1      0           0             1.44
32768  10000    111546  327680000        1      0           0             2.80
32768  20000    468680  655360000        1      0           0             1.33

Comparing the result of SELECT statement with CURSOR, the SELECT statement with BIGINT as the search criteria is performing poorly.

The peak throughput using SELECT statement with CURSOR is 434.03 MB/s, while the peak throughput using SELECT statement with BIGINT as the search criteria is only 2.80 MB/s. This is expected, because the BIGINT column is used as a search criteria and it is not indexed.

Note that fn_virtualfilestats() view failed to report any changes on read counters.

Â

? SELECT with BIGINT on Azure SQL Database

? Test Script for SELECT with BIGINT

? I/O Tests on SELECT Statements

?? SQL Server Storage Tutorials

2019-04-29, 1061👍, 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, 1062👍, 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, 1051👍, 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, 1042👍, 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, 1007👍, 0💬

I/O Tests on SELECT Statements
Where to find tutorials on I/O tests on SELECT Statements on SQL Server Database? Here is a list of tutorials to answer many frequently asked questions compiled by FYIcenter.com team on I/O tests on SELECT Statements on SQL Server Database. Test Script for SELECT with CURSOR No Reads on SELECT with ... 2019-05-31, 1000👍, 0💬

Test Script for SELECT with CURSOR
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 statis... 2019-05-06, 998👍, 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, 988👍, 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, 977👍, 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-04-21, 957👍, 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.