Stored Procedure

Find blocking using sp_who in SQL Server

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

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

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,

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’

sp_who1

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s