SELECT with PRIMARY KEY on Azure SQL Database

Q

What is the data read performance with the SELECT statement using PRIMARY KEY as the search criteria on Azure SQL Database?

✍: FYIcenter.com

A

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.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 Azure (RTM) - 12.0.2000.8 May 2 2019

 SIZE  COUNT  DURATION      BYTES  FILE_ID  READS  BYTES READ  Throughput MB/s
  256    100         3      25600        1      0           0             8.14
  256    500        10     128000        1      0           0            12.21
  256   1000        30     256000        1      0           0             8.14
  256   5000       140    1280000        1      0           0             8.72
  256  10000       210    2560000        1      0           0            11.63
  512  10000       237    5040000        1      0           0            20.28
 1024  10000       196   10160000        1      0           0            49.44
 2048  10000       230   20480000        1      0           0            84.92
 4096  10000       264   40960000        1      0           0           147.96
 8192  10000       380   81840000        1      0           0           205.39
16384  10000       410  163760000        1      0           0           380.91
32768  10000       447  327680000        1      0           0           699.11
32768  20000       953  327680000        1      0           0           655.82

As you can see, the peak throughput of 699.11 MB/s reached at @Size=32768 and @Count=10000.

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

? Test Script for SELECT with MEMORY_OPTIMIZED

? SELECT with PRIMARY KEY on SQL Server 2016

? I/O Tests on SELECT Statements

?? SQL Server Storage Tutorials

2019-04-21, ∼1814🔥, 0💬

Introduction to Selenium
Where to find tutorials on Introduction to Selenium? Here is a list of tutorials to answer many frequently asked questions compiled by FYIcenter.com team on Introduction to Selenium. What Is Selenium ⇒ What Is Selenium ⇐ Selenium Tutorials ⇑⇑ Selenium Tutorials 2019-09-14, ∼2541🔥, 1💬

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, ∼2150🔥, 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, ∼2058🔥, 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, ∼2035🔥, 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, ∼1908🔥, 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, ∼1896🔥, 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, ∼1876🔥, 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, ∼1830🔥, 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, ∼1824🔥, 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.