We just got a new SAN and I was looking at sys.dm_io_virtual_file_stats to make sure we are seeing an improvement compared to our last SAN. I noticed that all the tempdb files are seeing avg write latencies of about 350ms. This is still an improvement as our last SAN had stats of 1000ms avg write for tempdb in sys.dm_io_virtual_file_stats but not as good as I hoped.

Looking at the win.disk.duration perfmon counter on our tempdb drive for the past three days (since boot) the avg disk latency is < 1ms. Sometimes there are spikes to 150ms for one interval (30 seconds).

Looking at our SAN's monitoring tools it says the LUN has an avg write latency of 0.5ms.

VMWares stats are pretty much inline with the win.disk.duration perfmon counters.

Any pointers to where I should look at next to work out what is causing the discrepancy between Sql Servers io stats and perfmon/vmware/san monitoring stats?

  • What's the connection from the server to the SAN? – Erik Darling Nov 7 '17 at 7:43
  • What statement are you issuing to determine the average write latency in ms? – hot2use Nov 7 '17 at 7:59
  • @sp_BlitzEric we are using 10gb iSCSI. Each esxi host has two dedicated iSCSI nics. – Michael Holzheimer Nov 7 '17 at 9:05
  • @hot2use I am using the query on this page. sqlskills.com/blogs/paul/… – Michael Holzheimer Nov 7 '17 at 9:06
  • Seen the note in the original post? The best way to use this data for analysis is to periodically capture snapshots of the sys.dm_db_io_virtual_file stats data and then examine the averages over that small time period. That way you’ll be able to see latency spikes. link to second script – hot2use Nov 7 '17 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.