Sql-server – find out current disk response time in MS SQL Server using DMOs

sql server

using sys.dm_io_virtual_file_stats DMV I get io_stall which return response time in ms, but it is an accumulated result. Using Activity Monitor Data File I/O Pane, in the Response Time(ms) column we get aggregated but instant results. Though this column points to a corresponding field in the DMV, the results widely differ. In Activity Monitor there are relatively small numbers. I want to know how can I get that same instant response time via script. Is it possible to get that information in SQL Server using tsql or should I use server monitoring tools?

Best Answer

What is Activity Monitor Doing

You're right, those values from sys.dm_io_virtual_file_stats accumulate the entire time the SQL Server service is running:

The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started.

Activity Monitor is just taking a snapshot of the results, waiting 10 seconds, taking another snapshot, and then showing you the difference. This is why the numbers are small.

It can be useful to view these diffs, or log them to a table on a schedule, in order to help you know what's "normal" and what's "slow" for your server. Some tools that can help you do this:

Paul Randal's script: Capturing IO latencies for a period of time
sp_BlitzFirst (which conveniently has parameters that will log the results to a table for your automatically): Logging sp_BlitzFirst to Tables

If your goal is to see the same numbers from Activity Monitor via T-SQL, I would go with one of these options.

Disk Response Time vs File Response Time

The sys.dm_io_virtual_file_stats dynamic management function returns these stalls (AKA latency or disk response time) on a per-file basis. If you're looking for a pattern of slow response times, or a way to get latency, at the disk / LUN level, you'll have to be careful to consider which disk each of these files is on.

To the direct point of your question, SQL Server is not a great source of information about disk-level I/O issues.

What is Slow?

You'll see varying rules of thumb online, here's a common one (from SQLPerformance.com):

If you ask SQLskills, we will tell you something along the lines of:

Excellent: < 1ms

Very good: < 5ms

Good: 5 – 10ms

Poor: 10 – 20ms

Bad: 20 – 100ms

Really bad: 100 – 500ms

OMG!: > 500ms

If you do a Bing search, you will find articles from Microsoft making the recommendations similar to:

Good: < 10ms

Okay: 10 – 20ms

Bad: 20 – 50ms

Take those values with a grain of salt though. What matters is that performance is good enough, not whether a number has a certain value. Make sure to treat the patient, not the monitor.

Outside of SQL Server

To get truly useful information about about disk I/O, you'll really want to go outside of SQL Server. Windows Performance Monitor (PerfMon) is a good way to see some of these stats in "real time." You can see them aggregated across the whole machine, or by disk. The counters you want are Avg. Disk sec/Read and Avg. Disk sec/Write, which are both part of the Physical disk object.

I've heard that xperf is also a good tool for investigating this. I haven't used it, but I'll refer you to an excellent Microsoft blog post that provides a lot of details on usage and analysis: Analyzing Storage Performance using the Windows Performance Analysis ToolKit (WPT)

Maybe It's Not the Disk

It's important to note that high disk response times are not necessarily due to a "slow disk." As the MS blog post above mentions, It could be SAN issues (you might have unplugged or low throughput cabling, downgraded to slower disks than expected on the SAN, the network might be overloaded - even by traffic from other servers and sources - causing slow response times, etc).

It could be that the workload is doing too much I/O, and the disk just can't keep up. This type of problem can potentially be solved with query and index tuning, rather than trying to improve or upgrade a disk.

It could even be high CPU or oversubscribed VMs, or many other things.

Related Solutions

Sql-server – Investigating/Troubleshooting I/O Spikes

I have experianced a similar issue a few of times in the last year, and each time it has been due to an external issue, normally anti-virus software with incorrectly configured exclusions. I would check this.

In the mean-time, the following query will let you examine the level of physical IOs comming from SQL Server:

SELECT a.io_stall, a.io_stall_read_ms, a.io_stall_write_ms, a.num_of_reads, 
a.num_of_writes, 
--a.sample_ms, a.num_of_bytes_read, a.num_of_bytes_written, a.io_stall_write_ms, 
( ( a.size_on_disk_bytes / 1024 ) / 1024.0 ) AS size_on_disk_mb, 
db_name(a.database_id) AS dbname, 
b.name, a.file_id, 
db_file_type = CASE 
                   WHEN a.file_id = 2 THEN 'Log' 
                   ELSE 'Data' 
                   END, 
UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location 
FROM sys.dm_io_virtual_file_stats (NULL, NULL) a 
JOIN sys.master_files b ON a.file_id = b.file_id 
AND a.database_id = b.database_id 
ORDER BY a.io_stall DESC

Pay particular attention to IO_Stalls, Reads and Writes. This query cam from http://blogs.msdn.com/b/dpless/archive/2010/12/01/leveraging-sys-dm-io-virtual-file-stats.aspx

Sql-server – Overwhelmed with Sensor Data – Excel to SQL Server fails

To help those who follow... I ended up packing the data into a comma separated string for insert into a varchar(max) column. Not what I wanted to do but it works and I'm on my way. Another gotcha with this method is that although varchar(max) can store 2GB the table read from an external connection in excel only returns the first 32765 bytes of the string. To get round that I needs to run a vba script to get the full string using GetChunk() method.

Best.