We have a SQL Server 2016 SP2 Enterprise with latest CU with the database files spread out over different disks.
So we have data,log, tempdb and system db's have each their own drive. Date and log only contain one file.
Those drives each has their own LUN on a All-Flash SAN.
To monitor the latency I capture sys.dm_io_virtual_file_stats
every 15 minutes and then calculate the latency using the previous snapshot.
For the write latency I'm using following calculation:
(io_stall_write_ms - lag (io_stall_write_ms,1,0) over (order by checkdate))/(num_of_writes - lag (num_of_writes,1,0) over (order by checkdate)) write_latency
I'm getting an average write latency of 10ms but when I'm starting perfmon (with duration set to 900 seconds) and monitor the avg. Disk sec/Write in the same period for the same drive I'm only getting an average write latency of 3 ms.
I'm also capturing the wait stats for the same period, when I look at PAGEIOLATCH_EX waits and calculates how long every wait took, I'm also getting a value of approx 3ms.
I thought io_stall_write_ms represented the same as avg. Disk sec/Write or am I missing something?
Can somebody explain this behavior?