fn_virtualfilestats() Not Useful on Azure SQL

Q

Why is fn_virtualfilestats() not giving correct write counter values on Azure SQL Database?

✍: FYIcenter.com

A

If you run the SQL script, fyi_insert_write.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 giving incorrect write counter values.

The following table shows the summary of the test results. Some fields are removed to save space. The EXPECTED field is calculated as SIZE * COUNT for the total number of bytes expected to be stored in the table.

@@version = Microsoft SQL Azure (RTM) - 12.0.2000.8 May 2 2019

 SIZE  COUNT  DURATION  LATENCY  +ROWS  +PAGES  FILE_ID  WRITES  BYTES WRITTEN   EXPECTED
  256    100       457        4    100       4        1       1           8192      25600
  256    500      2100        4    500      18        1       1           8192     128000
  256   1000      4244        4   1000      36        1       1           8192     256000
  256   5000     21447        4   5000     179        1       3          24576    1280000
  256  10000     42124        4  10000     358        1       4          32768    2560000
  512  10000     42187        4  10000     777        1       6          49152    5120000
 1024  10000     41804        4  10000    1436        1       5          40960   10240000 
 2048  10000     41180        4  10000    5004        1      41       13950976   20480000
 4096  10000     42820        4  10000   10000        1     107       79233024   40960000
 8192  10000     45240        4  10000   10000        1     119       57729024   81920000
16384  10000     49483        4  10000   10000        1     689      231718912  163840000
32768  10000     52757        5  10000   10000        1     461      219561984  327680000
32768  20000    102313        5  20000   20000        1     952      552665088  655360000

As you can see:

  • The Azure SQL Database gives us a consistent latency of about 4 ms to perform a single INSERT statement, no matter how small or big the record size is.
  • The number of pages seems to be proportional to the total data size of the table, when record size is lower than 1,024 bytes.
  • The page size seems to be a constant when record size is low. But when record size gets higher, the page size is changing to match the record size!
  • The number of writes and number of bytes written on the file can not be related to the amount of data inserted to the table at all!
  • In most cases, the number of bytes written on the file is less than the number of bytes expected to be stored in the table. This could be the result of server caching and delaying the write operations.
  • But in 2 cases, when @Size=4096/@Count=10000 and @Size=16384/@Count=10000, fn_virtualfilestats() reported more bytes written to the file than what we expected.

 

fn_virtualfilestats() Not Useful on SQL Server 2016

Use fn_virtualfilestats() for I/O Tests

I/O Tests on INSERT Statements

⇑⇑ SQL Server Storage Tutorials

2019-05-12, 277👍, 0💬