fn_virtualfilestats() Not Useful on SQL Server 2016

Q

Why is fn_virtualfilestats() not giving correct write counter values on SQL Server 2016?

✍: FYIcenter.com

A

If you run the SQL script, fyi_insert_write.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 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 Server 2016 - 13.0.5292.0 (X64) 

 SIZE  COUNT  DURATION  LATENCY  +ROWS  +PAGES  FILE_ID  WRITES  BYTES WRITTEN   EXPECTED
  256    100        80        0    100       4        1       0              0      25600
  256    500       316        0    500      18        1       0              0     128000
  256   1000       810        0   1000      35        1       0              0     256000
  256   5000      3603        0   5000     173        1      71        2572288    1280000
  256  10000      6986        0  10000     345        1      36        1515520    2560000
  512  10000      7590        0  10000     777        1      41        3137536    5120000
 1024  10000      7803        0  10000    1436        1      45        6660096   10240000
 2048  10000      6500        0  10000    5004        1      54       12091392   20480000
 4096  10000      7243        0  10000   10000        1     157       41779200   40960000
 8192  10000      9426        0  10000   10000        1     159       82337792   81920000
16384  10000      8970        0  10000   10000        1     190      102129664  163840000
32768  10000     12693        1  10000   10000        1     446      281780224  327680000
32768  20000     25440        1  20000   20000        1     952      660676608  655360000

As you can see:

  • The SQL Server 2016 gives us a consistent latency of about 1 ms or less 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 some cases, like @Size=256/@Count=5000, @Size=4096/@Count=10000 and @Size=8192/@Count=10000, fn_virtualfilestats() reported more bytes written to the file than what we expected.

 

Test Script for INSERT with BIGINT

fn_virtualfilestats() Not Useful on Azure SQL

I/O Tests on INSERT Statements

⇑⇑ SQL Server Storage Tutorials

2019-05-12, 1361🔥, 0💬