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