Blocking in SQL Server

Blocking in SQL servers happens when a connection to SQL server blocks one or more query, and another connection to SQL server requires a conflicting lock type on query, or query locked by the primary connection. This leads to the another connection waiting until the primary connection releases its locks. One connection can block another connection, no matter whether or not they are from identical application or separate applications on different client computers.

A few seconds of blocking is normal and avoidable. But an excessive amount of blocking can cause connections (applications or user) to attend extensive periods of your time, foremost important information to get during a blocking condition is that blocking SPID (SQL process id), and what it is doing.

Gathering Blocking Information :

The query below identifies SPID (SQL process ID) at top of blocking chain and therefore SQL Statement –

Select * 
from sysprocesses 
where blocked <> 0 ;

Output : All columns like spid, kpid, blocked, waittime, waittype, waitresource, lastwaittype, dbid, uid, cpu, etc are empty.



After you have identified blocking SPID, issue subsequent command to urge the content –

dbcc inputbuffer (SPID)

To remove blocking, use the KILL command.

KILL SPID_ofBlockingQuery



Note : Once you KILL a process, you are ending it and this will cause unpredictable results. Using SQL KILL command quite once against an equivalent spid kills newer processing that is reusing this spid. Use KILL command just one occasion unless you verify that method has not changed with prior KILL command.

When blocking increase to purpose where there is an impact on system performance, it is usually due to one among below reasons –

  • A SPID holds locks on a group of resources for an extended period of your time before releasing them. This sort of blocking resolves itself over time but can cause performance degradation.
  • A SPID holds locks on a group of resources and never releases them. This sort of blocking does not resolve itself and prevents access to the affected resources indefinitely.

To Identify the head of blocking chain by using features that are provided through SQL Server Management Studio.

To try this, use the below steps –

Select server object, expand Reports, expand Standard Reports, then click ActivityAll Blocking Transactions.



Below report shows transactions at top of blocking chain if any –

Activity Report when there are not any Blocking

If you expand the transaction, report will show transactions that are blocked by top transaction. This report will also show “Blocking SQL Statement” and therefore the “Blocked SQL Statement.”

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.