What's the Holdup?
After you determine which process is blocking the others, you need to find out what the process is doing that's making it hold up other processes. The process might be blocked by another process, it might be in the middle of a large operation, or it might just be holding locks that it hasn't released yet.
If the blocker process is also blocked, you might have a chain of processes blocking each other. In this case, you need to find out which process is at the head of the chain. The code in Listing 1 creates a new system stored procedure, sp_leadblocker, which reports any processes that are blocking others but that aren't themselves blocked. You can create this procedure in the master database and execute it from anywhere by issuing the command EXEC sp_leadblocker.
You still need to figure out why the lead blocker is blocking other processes. Several methods of investigation are available to help you; I recommend the following steps:
- Run sp_lock, passing it the SPID of the lead blocker as the parameter. Sp_lock will show you what locks that process is holding. Sometimes, just a table name will be enough information for you to determine why the process is blocking others. For example, if you see that the lead blocker is holding locks on your customer_statements table, you might surmise that someone is running monthly reports.
- If knowing which objects are locked isn't helpful, look at the sysprocesses columns last_batch and open_tran, which the sp_leadblocker procedure returns. An open_tran value greater than 0 means that the process has an active transaction. If the last_batch value (a datetime data type) indicates that this process hasn't submitted a batch for execution in a long time, the problem might be that the process has an open transaction. The transaction might still be open because of an application error in which the client didn't send a COMMIT command to SQL Server. Or the problem could be an ad hoc transaction that a user submitted through a query tool such as Query Analyzer. For example, if you submit the following batch, locks will remain on the titles table until you get around to submitting a COMMIT TRAN or a ROLLBACK TRAN command:
USE pubs
BEGIN TRAN
UPDATE titles
SET price = price * 1.1
<execute>
If you can't discover why the transaction is open, you might need to find the user who executed the blocking process and have her help you figure out why a transaction is open. As a last resort, you might have to kill the blocking connection.
- If you can't track down the user responsible for the process, you might be able to discover the last operation the process completed. For example, the DBCC INPUTBUFFER command, which takes a SPID as a parameter, returns the first 255 characters of the last batch that the process submitted. This command might help, but it might not. For example, if the process that submitted the preceding batch had a SPID of 52, I could run DBCC INPUTBUFFER(52) and get the result that Figure 2 shows. This information is useful because it shows a BEGIN TRAN statement that has no corresponding COMMIT or ROLLBACK statement. However, if process 52 had issued a SELECT statement or called a system procedure such as sp_lock after updating the titles table, the information would show only that subsequent SELECT statement, which wouldn't help you figure out why process 52 is holding locks.
Alternatively, you can use Enterprise Manager to get the same information that DBCC INPUTBUFFER provides. Open the Management folder in SQL Server, then select Current Activity. In the right pane, you'll see for every active process a row that contains much of the same information as the sysprocesses row for that process. If you double-click a row for a connection (or right-click and choose Properties), you'll get a message box that contains the same information that DBCC INPUTBUFFER returns.
The Last Resort
I hope that these suggestions and the information in sysprocesses will give you a start in tracking down blocking problems. In some cases, though, the information you get might not be enough, and you'll have to kill either a known blocker or the blocked process itself. The only way to find everything that a blocking process is doing is to use SQL Server Profiler to track all activity on your server. However, you can't set up a trace as an afterthought; you must define the trace and have it running before the problem occurs. Next month, I'll finish exploring sysprocesses by looking at some new columns that Microsoft added to the system table in SQL Server 2000 Service Pack 3 (SP3).