sp_who – Day 1 – SQL Server System Stored Procedure

Hi Folks, I am starting a new series system stored procedures in SQL Server. In this series you will learn about many useful stored procedures which we use in our day-to-day work and also explore and learn new system stored procedure in SQL Server 2014.

So, in my first blog post we will start with most commonly used stored procedure sp_who.

Sp_who stored procedure returns information about current SQL server processes, users and sessions.

There is another stored procedure sp_who2 which is undocumented stored procedure. Sp_who2 provides more comprehensive information in comparison to sp_who.

Syntax of both stored procedure is as follows:

Sp_who [ [@loginame] = ‘login’ | session Id |’ ACTIVE’]
Sp_who2 [[@loginname] = ‘login’ | session Id |’ACTIVE’]


Login name identifies process belonged to a particular user login.

Session represents the session ID of the connection.

ACTIVE includes the current user login session.

Sp_who stored procedure return the following:

EXEC sp_who


Column Name Description

  1. Spid – Server process Id. It represents the session Id of the connection.
  2. Ecid – Execution Context Id. It represents what thread the process was executed on. 0 indicates that the process was executed on main thread.
  3. Status – It represents the status of the session. Possible status values are:

Running – It indicates session is performing some work.

Runnable – The session has performed some work but currently has no work to perform.

Sleeping – It indicates session is waiting to perform work.

Background – It indicates session is performing some background tasks.

Suspended – It indicates that session is waiting for an event to complete.

Dormant – It indicates session is being reset by server.

Rollback – It indicates session is currently rolling back a transaction.

Pending – It indicates that session is waiting on an available thread.


Spinloop – It indicates that session is waiting on a spinloop to become free.

  1. LoginName – It represents login associated with the session.
  2. Hostname – It represents hostname with the session.
  3. Blk – It represents session id for blocking process.
  4. Dbname – It represents database name connected to a session.
  5. Cmd – It represents type of command executing on the session.
  6. Request_Id – It represents Id of the request running in the session.

 Now let’s see how we can use sp_who stored procedure to identify blocking queries.

For this blocking demo, I have created a demo table in my database called ‘Customer’

CREATE TABLE [dbo].[Customer](
	[CustomerID] [int] IDENTITY(1,1) NOT NULL,
	[CustomerName] [varchar](50) NOT NULL,
	[CEO] [varchar](40) NULL,
	[Phone] [varchar](20) NOT NULL,
	[CustomerID] ASC

Now I will start a transaction and leave it open without committing or rollback.


INSERT INTO customer(CustomerName, ceo, phone)
VALUES('ABC', 'AA','111223')

Now I will execute this query in another window:

SELECT * FROM customer

Now, execute the stored procedure

EXEC sp_who ‘sa’


As, you can see in the picture that in column blk we have value other than 0 which tells that blocked id is  59 and blocked by spid 56.

Now to resolve this blocking either I can kill the connection, executes rollback or commit transaction and it will resolve.

That’s all folks for first part of system stored procedure series. Hope you will like it.


Kapil Singh

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook 

Follow me on Twitter


About Kapil Singh Kumawat

Kapil Singh Kumawat has been working with SQL Server since last 5 years. He is from Jaipur, Rajasthan, India and currently working with Cognizant Technology Solutions as SQL Server Developer. He has good experience in performance tuning, SSIS, data migration and data designing. Apart from database he has interest in travelling, watching football and listening music.

View all posts by Kapil Singh Kumawat →

2 Comments on “sp_who – Day 1 – SQL Server System Stored Procedure”

  1. great article…
    can you clarify sp_who2 ‘login’. I have tried that and it yields nothing. It shows the entire list. where as sp_who ‘login’ shows me the narrowed down list

    trying to run against sql2012

    1. Thanks Arsalan.
      EXEC sp_who2 ‘login’ tell you the login name associated with the session. Sp_who2 shows additional information which is not available in sp_who procedure like Command, CPUTime, DiskIO, LastBatch etc.
      When you run EXEC sp_who2 without any login name it will shows you information for all logins.
      Hope I have answered your query.

Leave a Reply

Your email address will not be published.