Correlating IO Wait with Queries

Have you ever looked at sys.dm_os_virtual_file_stats and saw horrible latency for your database files? Generally speaking, you want to see latency below 20 ms on your database files.  much above that and there are some problems somewhere that need some tuning.

But, how do you know what queries are causing your issue? What’s causing those IO waits?

How to Find Queries Associated with Pending IO Requests

I responded to a question on SQLServerCentral.com about performance that is related to these types of scenarios. The poster wanted to know if there was a way to correlate pending io requests with the queries that were experiencing the waits using the DMV sys.dm_io_pending_requests.

This was a well timed question that got my attention because I was working on my own scenario with a SQL Server and IO issues.  I had set up a custom data collector set in perfmon and analyzed the data.   The data showed very high Avg. Disk Queue Length, low Page Life Expectancy and high Page Reads/sec for sustained periods of time.  By correlating all this data together, it is easy for you to see that I had one or more queries hitting the disk to retrieve data to load into memory and causing pages to be flushed out. The forum question, and my own scenario, sent me off to do additional investigation and I arrived at the query below.

Reviewing SQL Server IO Latency

SELECT    
ipir.io_type, 
ipir.io_pending,
ipir.scheduler_address, 
ipir.io_handle,
os.scheduler_id, 
os.cpu_id, 
os.pending_disk_io_count,
ER.session_id, 
ER.command, 
ER.cpu_time, 
ST.text

FROM sys.dm_io_pending_io_requests ipir
INNER JOIN sys.dm_os_schedulers os ON ipir.scheduler_address = os.scheduler_address
INNER JOIN sys.dm_exec_requests AS ER ON os.scheduler_id = ER.Scheduler_id
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST

I began by looking at sys.dm_io_pending_requests in BOL.  I noticed that this DMV had Scheduler_Address as one of its outputs.  I quickly realized I could join to sys.dm_os_schedulers on the scheduler_address and then make an additional join to get to sys.dm_exec_requests.  From there I was only a CROSS APPLY away from getting the SQL statement associated with the IO waits.

Sys.dm_os_schedulers has its own column that keeps track of pending IO called “pending_disk_io_count.”  As a result, you could eliminate sys.dm_io_pending_requests from the above query if you were truly interested only in the pending io count.  However, using both DMV’s does provide a more detailed picture.

Performance Tuning IO Waits

When doing performance tuning, keep in mind that observed issues may only be symptoms.  In my case, after reviewing execution plans and data from SET STATISTICS IO ON, I could see that the high IO wasn’t due to poor performing storage, but queries that were performing millions of physical reads.  I examined the query plan and could see that an index was missing.  I applied the index and the physical reads dropped to almost zero.  I used the results from the above query to bolster my case to the application vendor that the index was needed because extremely high physical IO was killing the server.

Try out these DMV’s and see what other interesting gems you can get from them.

Next Steps to Take

  1. If you want to track queries causing IO Latency, you could create a table to hold the output of the query in this post and schedule this query to run in a SQL Agent job on an interval. Then examine your table over time.
  2. Be sure to review execution plans related to these queries to look for other performance tuning  opportunities.
  3. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.
>