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’]

Arguments

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.

4. LoginName – It represents login associated with the session.
5. Hostname – It represents hostname with the session.
6. Blk – It represents session id for blocking process.
7. Dbname – It represents database name connected to a session.
8. Cmd – It represents type of command executing on the session.
9. 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,
PRIMARY KEY CLUSTERED 
(
	[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

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

BEGIN TRAN

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.

Regards,

Kapil Singh

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

Follow me on Twitter