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