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 Activity – All 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.”