Blocking in SQL server 2008

How to check who is blocking your SQL server

Basic Troubleshooting on  when you see a  transaction is running for long time and  how to find which is blocking your SQL server

Step 1:

USE Master

GO

EXEC sp_who2

GO

This is a System SP which will show the entire process running currently running in SQL server . It will  display the Users list, database name and CPU time of the current transaction running .

This show the current session is blocked by 54 SPID.

Step 2:

To find the current blocking session using DMV :

USE Master

GO

SELECT * 

FROM sys.dm_exec_requests

WHERE blocking_session_id <> 0;

GO

 Step 3:

To find out what are the waiting sessions due to this blocking session :

USE Master

GO

SELECT session_id, wait_duration_ms, wait_type, blocking_session_id 

FROM sys.dm_os_waiting_tasks 

WHERE blocking_session_id <> 0

GO

 Step 4:

 

GUI Based findings of blocking sessions::

1.        

1               1.Activity monitor :

Through Activity monitor you can check which is blocking your SQL server .

 After clicking this you will  get an window showing the active sessions and blocked SP ID.

    2.Reports:

Reports is one of the best tool to increase SQL server  performance by finding which query is running for long time and blocking sessions .

One of my favourite  is Top queries by Avg. CPU time

Through reports we can check which session is blocked and you can find the RCA(Root cause analysis)

 Based on this trouble shooting you can KILL the SPID which is blocking your SQL server.

KILL 54;

Be care full when you kill a  transaction .Since the transaction would have completed 90% and killing this session is highly useless.

Based on the % completed and prior approval from the user KILL the transaction.