No Reads on SELECT with CURSOR on Azure SQL Database

Q

Why is there no reads recorded by fn_virtualfilestats() on SELECT statements with CURSOR on Azure SQL Database?

✍: FYIcenter.com

A

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 view is not reporting any reads at all.

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        10      25600        1      0           0             2.44
  256    500        37     128000        1      0           0             3.30
  256   1000        50     256000        1      0           0             4.88
  256   5000       360    1280000        1      0           0             3.39
  256  10000       754    2560000        1      0           0             3.24
  512  10000       754    5120000        1      0           0             6.48
 1024  10000       740   10240000        1      0           0            13.20
 2048  10000       780   20480000        1      0           0            25.04
 4096  10000       890   40960000        1      0           0            43.89
 8192  10000      1173   81920000        1      0           0            66.60
16384  10000      1173  163840000        1      0           0           133.21
32768  10000      1160  327680000        1      0           0           269.40
32768  20000      2690  655360000        1      0           0           232.34

As you can see:

  • Comparing the peak throughput of 269 40 MB/s, the Azure SQL Database with the default service tier is not performing as good as SQL Server 2016, which has a peak throughput of 434.03 MB/s.
  • But fn_virtualfilestats() view also failed to report any changes on read counters.

 

Test Script for SELECT with BIGINT

No Reads on SELECT with CURSOR on SQL Server 2016

I/O Tests on SELECT Statements

⇑⇑ SQL Server Storage Tutorials

2019-04-25, 233👍, 0💬