Identify blocking in SQL Server

Posted: October 26, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , ,

In all version of SQL Server we face Locking and Blocking problems. Now its very first thing to know blocking and blocked SPID. When a user or DBA itself come to know that particular process seems to hung or a process is not processing properly, only checking the applicable database blocking makes a great deal to winning the battle. Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock. These forces the second connection to be blocked until the first connection completes. With this being said, locking is a natural occurrence in SQL Server in order to maintain data integrity.

In SQL Server, there are many ways to findout the blocking and blocked SPID/Process. Some options are like:

  • sp_who2    System Stored Procedure
  • sys.dm_exec_requests  DMV
  • Sys.dm_os_waiting_tasks   DMV
  • SQL Server Management Studio Activity Monitor
  • SQL Server Management Studio Reports
  • SQL Server Profiler

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s