How to Minimize SQL Server Blocking

One way to help identify blocking locks is to use Enterprise Manager. 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. 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. 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. [7.0, 2000, 2005] Updated 4-17-2006

*****

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. [7.0, 2000] Updated 4-17-2006

*****

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. [7.0, 2000, 2005] Updated 4-17-2006

*****

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.

Below are two different Profiler configurations, one for SQL Server 7.0, and one for SQL Server 2000 and SQL Server 2005.

SQL Server 7.0 Profiler Configuration

Events

Error and Warnings: Exception
Misc: Attention
Misc: Execution Plan
Sessions: Connect
Sessions: Disconnect
Sessions: Exiting Connection
TSQL: RPC:Starting
TSQL: RPC:Completed
TSQL: SQL:BatchStarting
TSQL: SQL:BatchCompleted
Stored Procedures: SP:StmtStarting
Stored Procedures: SP:StmtCompleted
Transactions: SQLTransaction

Data Columns

Group By: SPID
Event Class
Text
Integer Data
Binary Data
Application Name
NT User Name
SQL User Name
Start Time
End Time
Connection ID

Filters

Trace Event Criteria: Severity (type 24 in the Maximum box)
Add other filters as desired to reduce a flood of too much data

SQL Server 2000 and 2005 Profiler Configuration

Events

Error and Warnings: Exception
Error and Warnings: Attention
Performance: Execution Plan
Sessions: Exiting Connection
Stored Procedures: RPC: Starting
Stored Procedures: RPC: Completed
Stored Procedures: SP: Starting
Stored Procedures: SP: Completed
Stored Procedures: SP: StmtStarting
Stored Procedures: SP: StmtCompleted
Transactions: SQLTransaction
TSQL: SQL:BatchStarting
TSQL: SQL:BatchCompleted

Data Columns

Group: SPID
EventClass
TextData
IntegerData
BinaryData
ApplicationName
NTUserName
LoginName
StartTime
EndTime

Filters

Trace Event Criteria: Severity (type 24 in "Less than or equal" box)
Add other filters as desired to reduce a flood of too much data

One of the best ways to use the Profiler is to begin a trace, duplicate the activity that causes the blocking lock, identify the SPID of the blocking lock in Enterprise Manger or Management Studio, and then stop the trace. Next, look up the SPID in the trace, viewing all of the activity that occurred up to the blocking lock occurring. [7.0, 2000] Updated 7-24-2006