There are a lot of great tools out there to help DBA’s monitor SQL Server perfromance. Windows Perfmon counters and use of the Windows Relog command, for example, can be used to build a database of performance metrics. Write a few queries against that database and a DBA can learn a lot about what’s going on inside the SQL Server. Profiler and Extended Events also provide windows into performance.
I recently ran across a question on SQLServerCentral.com about performance that intrigued me. 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 Reades/sec for sustained periods of time. The question gave me an opportunity to do some 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.
Also, keep in mind that, like many things in life, 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. However, I used 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 else you can get from them.