No Reads on SELECT with CURSOR on SQL Server 2016

Q

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

✍: FYIcenter.com

A

If you run the SQL script, fyi_select_cursor.sql, presented in the last tutorial with different @Count and @Size on SQL Server 2016, 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 Server 2016 - 13.0.5292.0 (X64) 

 SIZE  COUNT  DURATION      BYTES  FILE_ID  READS  BYTES READ  Throughput MB/s
  256    100        16      25600        1      0           0             1.53
  256    500        13     128000        1      0           0             9.39
  256   1000        33     256000        1      0           0             7.40
  256   5000       156    1280000        1      0           0             7.83
  256  10000       296    2560000        1      0           0             8.25
  512  10000       313    5120000        1      0           0            15.60
 1024  10000       343   10240000        1      0           0            28.47
 2048  10000       360   20480000        1      0           0            54.25
 4096  10000       470   40960000        1      0           0            83.11
 8192  10000       656   81920000        1      0           0           119.09
16384  10000       686  163840000        1      0           0           227.77
32768  10000       720  327680000        1      0           0           434.03
32768  20000      1470  655360000        1      0           0           425.17

As you can see:

  • The SQL Server 2016 gives us a very good performance on reading data using SELECT statements with CURSOR. It can read 327,680,000 within a second.
  • But fn_virtualfilestats() view failed to report any changes on read counters.

 

No Reads on SELECT with CURSOR on Azure SQL Database

Test Script for SELECT with CURSOR

I/O Tests on SELECT Statements

⇑⇑ SQL Server Storage Tutorials

2019-05-17, 157👍, 0💬