Correlating IO Wait with Queries

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


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.

Happy querying!

Retiring sp_who and sp_who2

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.

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.



  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 van 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, which has been deprecated for some time now. I would strongly encourage experimentation with the new DMV’s. They will be a valuable tool in the DBA toolbox.