Category Archives: SQL Server Blocking

Identifying Blocking Processes


For a quick check, you can run the following little query:
select * from master..sysprocesses where spid in (select blocked from
master..sysprocesses)
or blocked != 0

  

The spid(s) with no value in the blocked column is the head blocker.

********************************************************************************************************

  

Blocking occurs when one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection. This causes the second connection to wait until the first connection releases its locks. By default, a connection will wait an unlimited amount of time for the blocking lock to go away. Blocking is not the same thing as a deadlock.

A certain amount of blocking is normal and unavoidable. Too much blocking can cause connections (representing applications and users) to wait extensive periods of time, hurting overall SQL Server performance. In the worst cases, blocking can escalate as more and more connections are waiting for locks to be released, creating extreme slowdowns. The goal should be to reduce blocking as much as possible.

Locks held by SELECT statements are only held as long as it takes to read the data, not the entire length of the transaction. On the other hand, locks held by INSERT, UPDATE, and DELETE statements are held until the entire transaction is complete. This is done in order to allow easy rollback of a transaction, if necessary.

Some causes of excessive blocking, and ways to help avoid blocking, include:

  • Long-running queries. Anytime a query of any type, whether it is a SELECT, INSERT, UPDATE, or DELETE, takes more than a few seconds to complete, blocking is likely. The obvious solution to this is to keep transactions as short as possible. There are many tips on this web site on how to help reduce transaction time, but some of them include optimize Transact-SQL code, optimize indexes, break long transactions into multiple, smaller transactions, avoiding cursors, etc.
  • Canceling queries, but not rolling them back. If your application’s code allows a running query to be cancelled, it is important that the code also roll back the transaction. If this does not happen, locks held by the query will not be released, which means blocking can occur.
  • Distributed client/server deadlock. No, this is not your typical deadlock that is handled automatically by SQL Server, but a very special situation that is not automatically resolved by SQL Server.Here is what can happen. Let’s say that an application opens two connections to SQL Server. The application then asynchronously starts a transaction and sends a query through the first connection to SQL Server, waiting for results. The application then starts a second transaction and sends a query through the second connection to SQL Server, waiting for results. At some point, one of the queries from one of the connections will begin to return results, and the application will then begin to process them.As the application processes the results, at some point what could happen is that the remainder of the results become blocked by the query running from the other connection. In other words, the first query can not complete because it is being blocked by the second query. So in essence, this connection is blocked and cannot continue until the second query completes. But what happens is that the second query tries to return its results, but because the application is blocked (from the first query), its results cannot be processed. So this means that this query cannot complete, which means the block on the first query can never end, and a deadlock situation occurs. Neither connection will give up, so neither connection ever ends, and the deadlock situation never ends.SQL Server is unable to resolve this type of deadlock, so unless you want to write applications that hang forever, you can take these steps to prevent this unusual situation: 1) Add a query time-out for each of the queries, or 2) Add a lock time-out for each of the queries, or 3) Use a bound connection for the application.

In many ways, the best way to avoid blocking is to write well-tuned applications that follow the tuning advice found on this website.

*******************************************************************************************************

One way to help identify blocking locks is to use Enterprise Manager.

 
If you do, you will see which SPID is blocking what other SPIDs. Unfortunately, this screen is not
dynamically updated, so you will want to refresh this screen often if you are looking for blocking locks.
To refresh the screen, right-click on “Current Activity,” not “Process Info,” and then select “Refresh.”
Most blocking locks go away soon. But if a blocking lock does not go away, and it is preventing one or more
users from performing necessary tasks, you can ask the user whose SPID is causing the blocking
to exit their program that is causing the block.

If you expand “Process Info” Under “Current Activity,” for the appropriate server,
and then scroll to the right of the screen, you will see if there are currently any blocking locks.

Or, you can KILL the blocking SPID from Enterprise Manager or Management Studio.

KILLing the blocking SPID will cause the current transaction to rollback and allow the blocked SPIDs to continue. 
*********************************************************************************************************

To help identify and correct queries that cause blocking locks, you need to find out what the query
looks like that is causing the blocking lock.
Before you can do this, you must first identity the SPID that is causing the blocking.
Once you have done that, there are two ways to view the query that is causing the blocking lock.
 First, from Enterprise Manager or Management Studio, in the “Process Info” window located under
“Current Activity,” right-click on the SPID causing the blocking and choose “Properties.”
This will display the query.

Second, from Query Analyzer or Management Studio, enter this code to reveal the query
causing the blocking lock:     DBCC INPUTBUFFER (<spid>)

 Once you have identified the query that is causing the blocking lock,
you can begin researching it to see if there is anything you can do to modify the query to
avoid blocking in the future.
********************************************************************************************************

To help identify the type of lock that a blocking lock is holding, you must first identify the SPID that is
causing the blocking, Once you have that, there are two ways to view the type of lock being held by the
blocking lock:

First, from Enterprise Manager or Management Studio, in the “Locks/Process ID” window located under
“Current Activity,” locate the SPID causing the blocking lock, and click on it.
The type of lock will be displayed in the right-hand window.

Second, run this command in Query Analyzer or Management Studio:    sp_lock

 You will then have to match the SPID of the block lock to the SPID listed in the results of this command.

Knowing the type of lock held by the blocking lock can help you figure out why the query in question is
causing a blocking lock. 

********************************************************************************************************
One way to help identify blocking locks is to use the SQL Server Profiler.
The Profiler is useful for capturing blocking locks because it can capture blocking locks over time,
unlike the Enterprise Manager or Management Studio, which only shows blocking locks as of the current instant.

In addition, the query that is available from the INPUTBUFFER may not be enough information to diagnose a
blocking problem. Sometimes, a query that runs just before the query that is causing the blocking is related
to the blocking problem. By performing a Profiler Trace, you can see all the queries and other activity that
precede a blocking lock.

In order to use the trace data, you will have to know the SPID that caused the
blocking lock, and then look up the data from the trace for this one particular SPID.

%d bloggers like this: