Hi friends, in this blog I will tell you how can we find blocking using sp_who system stored procedure. Also, we will learn about sp_who stored procedure.
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_who2provides 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:
Column Name Description
- Spid – Server process Id. It represents the session Id of the connection.
- Ecid – Execution Context Id. It represents what thread the process was executed on. 0 indicates that the process was executed on main thread.
- 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.
- LoginName – It represents login associated with the session.
- Hostname – It represents hostname with the session.
- Blk – It represents session id for blocking process.
- Dbname – It represents database name connected to a session.
- Cmd – It represents type of command executing on the session.
- 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.
INSERT INTO customer(CustomerName, ceo, phone)
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.
Thats all folks in this post. Hope you will like it.