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 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


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.

Retiring sp_who and sp_who2


Sp_who and sp_who2 have been some very popular T-SQL commands. What if I told you that you could get more and better information from DMVs that have been around since SQL Server 2005?

With the advent of SQL Server 2005 came along new objects called Dynamic Management Views, also known as DMVs. One of the great places where improvements were made by adding the DMVs is when investigating what is happening on a SQL Server at the moment production troubleshooting is required. When things go south and the DBA is contacted, the new DMV’s are invaluable. Despite this, there are still some folks out there using sp_who and sp_who2. With this post I want to demonstrate why you should stop using sp_who and sp_who2. Retire these commands and move on to something better.


retire sp_who and sp_who2
retire sp_who and sp_who2

Creating a Blocking Transaction

To demonstrate this, we need a blocking transaction. I connected to AdventureWorks2014 on my local instance and in one query window I have the first query and in a second window the second query. Execute these queries and blocking will occur.


      UPDATE [Person].[Person]
      SET FirstName = 'Ken'
      WHERE BusinessEntityID = 1

      UPDATE [Person].[Person]
      SET FirstName ='Jason'
      WHERE BusinessEntityID = 1

Now that you have a blocking transaction, open a third window in SQL Server Management Studio (hereafter SSMS) and run the below.

Comparing sp_who, sp_who2 and SQL Server DMV’s


  ES.session_id AS BlockedSession
, BlockedSQL.text AS BlockedSQL --Not in sp_who or sp_who2
, ES.login_name
, ES.program_name
, ES.host_name
, ER.blocking_session_id
, BlockingSQL.text AS BlockingSQLText
, ER.cpu_time
, ER.logical_reads  --Not in sp_who or sp_who2
, ER.reads --Not in sp_who or sp_who2
, ER.writes --Not in sp_who or sp_who2
, ER.lock_timeout  --Not in sp_who or sp_who2 
--Lock time-out period in milliseconds for this request
, ER.row_count --Not in sp_who or sp_who2 
/*Number of rows that have been returned to the client by this request.
, ER.total_elapsed_time --Not in sp_who or sp_who2
, CASE CAST(ER.transaction_isolation_level AS VARCHAR(20)) 
  WHEN '0' THEN 'Unspecified'
  WHEN '1' THEN 'ReadUncomitted'
  WHEN '2' THEN 'ReadCommitted'
  WHEN '3' THEN 'Repeatable'
  WHEN '4' THEN 'Serializable'
  WHEN '5' THEN 'Snapshot'
  END AS transaction_isolation_level --Not in sp_who or sp_who2
, ER.wait_type --if currently blocked this will have a value
, ER.wait_time  --Not in sp_who or sp_who2
, ER.last_wait_type --Not in sp_who or sp_who2 
/*If session was previously blocked, this will show what the wait type was.
, ER.wait_resource --Not in sp_who or sp_who2.  
/*Can use wait_resource and expand this query to use other DMVs to find what table and index is being waited on, for example.  
, ER.deadlock_priority --Not in sp_who or sp_who2
, DB_name(ER.database_id) AS DBName  
, ER.executing_managed_code  
/*Not in sp_who or sp_who2 --Indicates whether a specific request is currently executing common language runtime objects, such as routines, types, and triggers. It is set for the full time a common language runtime object is on the stack, even while running Transact-SQL from within common language runtime
, EC.client_net_address AS ClientIPAddress 
--Not in sp_who or sp_who2

FROM sys.dm_exec_sessions AS ES
INNER JOIN sys.dm_exec_requests AS ER ON ES.session_id = ER.session_id
INNER JOIN sys.dm_exec_connections AS EC ON ER.blocking_session_id = EC.session_id
CROSS APPLY sys.dm_exec_sql_text(ER.plan_handle) AS BlockedSQL
CROSS APPLY sys.dm_exec_sql_text(EC.most_recent_sql_handle) AS BlockingSQL

From sp_who and sp_who2 you should get an output similar to below where blocking information is provided related to SPIDs 52 and 53.

Click to enlarge.

sp_who and sp_who2

The output from the query of the DMVs looks like the below.  The results have far more columns than can be displayed in a screen capture.

Click to enlarge.

Blocked and Blocking SQL

Please note that there is very little difference between the data in sp_who and sp_who2. However, there is a big difference between those older object calls and the third script. Out of the myriad of columns returned by the third script, very few are returned by sp_who or sp_who2.

Be sure and go back to SSMS and commit the two queries involved in the blocking to clean up the demo.

Some items of interest in the third script that are not in the older sp_who or sp_who2 are:

  1. The SQL statement being ran by both the blocked and blocking process.
  2. Number of logical (in memory) reads
  3. Break down of reads versus writes
  4. Whether or not the requests are executing in CLR code
  5. Wait Resource: This shows the DBA things like whether there is a KEY or Page wait occurring and where.
  6. Isolation level of the statements involved
  7. IP Address of the client involved

By using the above third query, the SQL statements of both the blocked and blocking session can be retrieved without the extra step of running DBCC INPUTBUFFER as would be needed when using sp_who or sp_who2. This will save the DBA valuable time when troubleshooting a live issue.

The DMV’s are just so much more powerful than calls to sp_who and sp_who2 that are dependent on sys.processes. I would strongly encourage experimentation with the new DMV’s. They will be a valuable tool in the DBA toolbox.

Next Steps To Take

  1. Do some research on the Dynamic Management Objects. These include not only views but dynamic management functions that also return valuable information. Check out this article at MSSQLTips to get started.
  2. Check out Glenn Berry’s SQL Server diagnostic queries.
  3. Over on your dev environment, run some more tests with the DMV query above and it’s related views to explore what other information is available.
  4. When you’re comfortable with them, make using the DMVs part of your troubleshooting process.
  5. Consider using sp_whoisactive from Adam Machanic.
  6. If you would like help with anything in this post, or with something else related to SQL Server, then you can reach out to me on Twitter, and I’ll be glad to offer assistance.