[SQL Server] Identifying SQL Server Disk Latency

When you troubleshoot a general performance issue in SQL Server, you're bound to wonder if disks and disk-related performance are the cause of the problem.

In this article, we will describe some ways to analyze disk read and write latencies.

By running the following query, you will be able to extract some useful metrics and information.

also :

The Avg Read Stall (ms) and Avg Write Stall (ms) columns are the two columns to focus on.

From my point of view, since data is written to datafiles asynchronously, you only need to worry about the avg read stall for .mdf files. Similarly the transaction log TLOG file is only used for writing operations (not reading), you only need to worry about the avg write stall for ldf files. Exception made for tempdb for which you must look at and analyze both metrics.

In terms of representative value:

• < 10 ms (very good)

• 10 - 20 ms (acceptable)

• > 20 ms (you need to focus on it, could be bad)

you can also use Perfmon (performance monitor) to capture real-time disk response times (choose Avg. Disk sec/Read and Avg. Disk sec/Write for a specific disk you want to monitor).