SQL Disk Write Latency VS Host Disk Device Write Latency (ms)

Hi all,

I'm having one SQL server with some peak on "SQL Disk Write Latency" in the "Disk" tab:

If I look in the "Disk Device" tab, I see no latency at all (for the same server at the same time):

I'm trying to figure out how that is possible, if there's a correlation between "SQL Disk Write Latency" and "Host Disk Device Write Latency (ms)" ?

Any ideas here?

Find more posts tagged with

disk io

sql server disk latency

Accepted answers

All comments

chenry

mattsbr

"SQL Disk Write Latency" is tied to what the latency is seen within SQL Server and "Host Disk Device Write Latency" is tied to the ESXi host's latency across all storage in a LUN. This could be caused by even one file having lots of activity and causing latency within SQL Server. I would suggest going to the "Write" tab within the "Storage I/O" to see your write performance pain points.

This two should correlate fairly closely. Note that I'm drilled down to an hour within "Storage I/O" tab.

4-18-2018 9-00-36 AM.png

4-18-2018 9-07-08 AM.png

mattsbr

Thanks for the explanation,it make sense, I don't think our full SSD 3par SAN is over used.

So I did what you said and TempDB is it hard. I have 8 CPU so 8 TempDB file.

Another thing that I don't get, the wait type is 'Other Wait' and not 'I/O wait' why is this in disk if it's not IO related?

What is Other Wait?

I'm just talking about general concept when troubleshooting with DPA for Disk/IO issues here, I know printscreens are not from the same time.

chenry

Wait types are in two buckets on that Storage I/O tab. This is so you can compare I/O waits to other waits (like PAGEIOLATCH memory wait) that are not impacting I/O. In your graph for example at 3:56PM you had 2 minutes of waits based on I/O and 7 minutes of some other wait not related to I/O.

I would suggest drilling into Trends for the 3:50PM to 4PM timeslice and look at the waits tab. You should see lots of waits not relating to I/O.