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

Page Size Varies on SQL Server 2016 Database
What is the data page size on SQL Server 2016 Database? To find out the data page size on SQL Server 2016 Database, we can run the SQL script, fyi_page_insert.sql, presented in the last tutorial with different @Size on SQL Server 2016 Database. The following table shows the summary of the test resul... 2019-05-12, 1286👍, 0💬

fn_virtualfilestats() Not Useful on SQL Server 2016
Why is fn_virtualfilestats() not giving correct write counter values on SQL Server 2016? 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 w... 2019-05-12, 1118👍, 0💬

Test Script for INSERT with PRIMARY KEY
Where can I get a SQL script to do I/O performance test with INSERT statement using BIGINT with PRIMARY KEY? Here is a SQL script to do I/O performance test with INSERT statement using BIGINT with PRIMARY KEY. -- fyi_insert_primary_key.sql -- Copyright (c) FYIcenter.com DECLARE @Count INT SET @Count... 2019-05-17, 1083👍, 0💬

fn_virtualfilestats() Not Useful on Azure SQL
Why is fn_virtualfilestats() not giving correct write counter values on Azure SQL Database? 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 incor... 2019-05-12, 1042👍, 0💬

Test Script for INSERT with BIGINT
Where can I get a SQL script to do I/O performance test with INSERT statement using BIGINT without primary key? Here is a SQL script to do I/O performance test with INSERT statement using BIGINT without primary key. The test result can provide a baseline for INSERT statement with a primary key. -- f... 2019-05-18, 1002👍, 0💬

Use allocation_units for I/O Tests
How to Use allocation_units system view for I/O Tests? If want to use the allocation_units system view to calculate the I/O statistics while doing performance tests, you need to take a copy of the current counters from the allocation_units view before running a test, run the test, then compare count... 2019-05-12, 939👍, 0💬

Use fn_virtualfilestats() for I/O Tests
How to Use fn_virtualfilestats() for I/O Tests? If want to use the fn_virtualfilestats() function table to calculate the I/O statistics while doing performance tests, you need to take a copy of the current counters from the fn_virtualfilestats() function before running a test, run the test, then com... 2019-05-12, 914👍, 0💬

Test Script for SELECT with CURSOR
Where can I get a SQL script to do I/O performance test with SELECT statement using CURSOR to loop through records? Here is a SQL script to do I/O performance test with SELECT statement using CURSOR to loop through records. It uses the fn_virtualfilestats() function table to calculate the I/O statis... 2019-05-06, 878👍, 0💬

I/O Tests on SELECT Statements
Where to find tutorials on I/O tests on SELECT Statements on SQL Server Database? Here is a list of tutorials to answer many frequently asked questions compiled by FYIcenter.com team on I/O tests on SELECT Statements on SQL Server Database. Test Script for SELECT with CURSOR No Reads on SELECT with ... 2019-05-31, 869👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.