• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Using exec sp_who2 to help with SQL Server troubleshooting

May 20, 2012 by Andy Hayes 4 Comments

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
Using exec sp_who2 to identify blocked queries
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.

 

Filed Under: All Articles, SQL Server Administration Tagged With: performance, sql server, troubleshooting

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Comments

  1. Anil says

    January 28, 2014 at 7:28 am

    hi Andy,

    Thanks for sharing this article.

    As we know Sesion id 54 is the lead blocker which is bloacking SPID 55 & 56. But when i see in the blkby column, it looks like SPID 55 is blocking SPID 56. I believe SPID 56 is being blocked by SPID 54.

    Please correct me if i am wrong.

    Regards,
    Anil

    Reply
    • Andy Hayes says

      January 28, 2014 at 8:22 pm

      Hi Anil

      I think you are correct. From my understanding of what you have said, 54 is blocking 55, 55 is blocking 56 but ultimately, the cause of the block chain is 54. So you would look at this SPID first to see what it is doing using something like DBCC INPUTBUFFER(54)

      Thanks for your comment and good luck. Hope you get it fixed.

      Andy

      Reply
  2. Chris says

    April 8, 2019 at 12:07 am

    Hi,

    This is a good start for documenting the procedure, but there must be more uses for sp_who2?

    For example, I can see all of the following Statuses:

    * sleeping
    * BACKGROUND
    * RUNNABLE
    * SUSPENDED

    And the following sorts of Commands:

    * TASK MANAGER
    * BRKR TASK
    * UNKNOWN TOKEN
    * RESOURCE MONITOR
    * XE DISPATCHER
    * BRKR EVENT HANDLER
    * FT FULL PASS
    * FT CRAWL MON
    * AWAITING COMMAND
    * SELECT
    * SELECT INTO
    * LAZY WRITER
    * RECOVERY WRITER
    * SIGNAL HANDLER
    * LOG WRITER
    * LOCK MONITOR
    * XTP_CKPT_AGENT
    * TRACE QUEUE TASK
    * SYSTEM_HEALTH_MO
    * RECEIVE
    * XE TIMER

    I guess the MS docs will have the exhaustive information (maybe?) but it would be great to have an article that kind of says “this group of commands relates to xyz system processes – and normally you aren’t interested in them”, and “however this group of commands is important, it could suggest xyz and you should investigate by doing xyz”.

    Cheers.

    Reply
  3. Caroljean Shirley says

    January 28, 2020 at 2:36 pm

    Regarding:
    CHRIS says

    April 8, 2019 at 12:07 am

    Hi,

    This is a good start for documenting the procedure, but there must be more uses for sp_who2?

    For example, I can see all of the following Statuses:
    ”

    I agree with you. I’d like to see some information with regard to the Statuses as well as some insight on the “wait_type”.

    Cheers
    CJ

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAPTCHA
Refresh

*

Primary Sidebar

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©