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. Thee forum question, and my own scenario, sent me off to do additional investigation and I arrived at the query below.
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.
Later I realized that 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, 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.