Using exec sp_who2 to help with SQL Server troubleshooting

If you haven’t used sp_who2 before then it is a great utility to help in diagnosing a problem with your database application.

What is sp_who2 ?

It’s a stored procedure which is installed with SQL Server which when run, outputs a row for each “SPID”. SPID stands for Server Process ID and one is created every time an application needs to open a connection to SQL Server.

Each row contains a number of useful columns including information on things like the status of the SPID, what its resource usage is in terms of CPU/IO and what login is currently executing the command.

Permissions needed for sp_who2

A login can run sp_who2 and obtain information about its own connection. For a full list of SPID’s, either the login has to have sysadmin permission or VIEW SERVER STATE permission.

Using sp_who2 to help identify blocking queries

Lets say for example that the phone rings and everyone in the department using the sales system is complaining that their copy of the application is freezing when trying to access the customer sales order data.

So for this blocking demo, I have created a simple table in my database called “Orders” and I am going to start a transaction and leave it open without committing or rolling it back.
[sourcecode language=’sql’]BEGIN TRAN
INSERT INTO Orders(AccountID, DatePlaced, Amount, Currency)
VALUES(1, GETDATE(), 100, ‘$’);[/sourcecode]
Now I will try and read the orders table via another query
[sourcecode language=’sql’]SELECT * FROM Orders;[/sourcecode]
and again via another query…
[sourcecode language=’sql’]SELECT * FROM Orders;[/sourcecode]
Now I will run exec sp_who2 to check what is happening to these connections and it tells me that I have two blocked SPID’s (55 and 56) and that they are being blocked by SPID 54

The DBA now has to decide how best to proceed with resolving this problem based on the cause and effect of taking action.

They will try and work out what the lead blocker is doing and one way they might do this would be to use “DBCC INPUTBUFFER(SPID)” where SPID is the number of the connection to be analysed and the results returned tell the DBA what the command/query was executing as that SPID.

Instead of using sp_who2, another way would be to look at the dm_exec_requests DMV. I’m going to look at these in a future post.

In my demo it is easy to resolve this as I can kill the connection, force a commit or force a rollback and it will be instantly resolved.

It might not be as clear cut as this in another scenario. Lets say that a process has been running for some time with a large update and this causes the blocking problem described in the demo.

When the DBA views the results of sp_who2 and notices that the process has been running for many hours, they know that by killing the process, a rollback has to finish before the resource will become available again for other queries to be able to complete.

The rollback could also take many hours in this instance and so such decisions cannot be taken lightly. The solution will vary depending on the scenario.

With this tool, you can check a specific connection. It takes an optional parameter SPID, for example:

[sourcecode language=’sql’]

exec sp_who2 54

[/sourcecode]

Something I get asked is whether there is a way to check for active connections by running a filter – something like exec sp_who2 ‘active’. Sadly this doesn’t work but there is a simple way and that is to capture the results to a temporary table or temporary variable and then query that.

There is a nice piece on this over at Stack Overlow which is worth a read and demonstrates a few solutions to this.

Conclusion

sp_who2 should be part of every DBA’s troubleshooting toolbox.

It provides a great overview of what the connections are doing on the SQL Server and can quickly help the DBA find reasons for increases in application timeouts, high disk IO or high CPU pressure.