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

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.

    BEGIN TRANSACTION
      UPDATE [Person].[Person]
      SET FirstName = 'Ken'
      WHERE BusinessEntityID = 1
    --COMMIT TRAN

      BEGIN TRAN
      UPDATE [Person].[Person]
      SET FirstName ='Jason'
      WHERE BusinessEntityID = 1
      --COMMIT TRAN

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

sp_who;
GO
sp_who2;
GO

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

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.

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.