Every process, whether user-initiated or internal to SQL Server, is constantly acquiring and releasing locks. Most of the time, locks don't cause problems. In fact, locks prevent problems that would arise if processes were able to make changes to data that other processes are in the middle of modifying. Locking problems arise only when a process holds a lock longer than necessary, or longer than another user would like, and blocks other users' processes. To those users, their processes are blocked—unable to retrieve or modify data.
Sysprocesses is the system table that can reveal the most clues about why a process is blocking others. Although the syslockinfo system table, which I discussed last month in "Inside Sysprocesses," contains all the details about locks that a given process holds, I find sysprocesses more helpful because it can frequently tell you what's causing a process to hold the locks. Let's look at a few sysprocesses columns that are useful for troubleshooting, especially for solving locking and blocking problems.
Even before you know the nature of your system's performance problems, if you suspect that blocking is a cause, the sysprocesses table is the place to start looking. Whenever I start wondering whether a process hasn't returned results because another process is blocking it, I either run sp_who or look at the sysprocesses table. Sp_who returns a column called blk, and sysprocesses has a column called blocked; both names mean the same thing. When a process's server process ID (SPID) appears in the blk column, that process is blocking the process whose ID value is in the spid column.
For example, suppose sp_who returns a row like the one that Figure 1 shows. The 52 in the blk column means that process 52 is the blocking process; the 53 in the spid column identifies the process that's being blocked. You now need to determine why process 52 is a blocker. In almost all cases, you can track the block to a lock that the blocking process is holding.
Tracking the Culprit
I usually troubleshoot blocks by selecting useful columns from the sysprocesses table instead of running sp_who. By directly accessing sysprocesses, I can filter rows and look at other columns in the table to get the information I need. In particular, if I suspect that one process is waiting for another, I can look at the stalled process's waittime column, which contains the number of milliseconds the process has been waiting. This column will have a nonzero value only if the process is currently in a wait state. The following query returns the blocking process's SPID and the total current waittime for any processes that are in a wait state:
SELECT spid, blocked, waittime FROM sysprocesses WHERE waittime > 0
Another sysprocesses column, waittype, contains a hexadecimal representation of the type of lock a process is holding. But if a process currently holds a lock, the lastwaittype column will have a slightly more readable version of lock type, so you don't have to worry about how to translate the hex value. For example, when the waittype value is a shared lock, the lastwaittype value is LCK_M_S (lock mode shared), and when the waittype value is an update lock, the lastwaittype value is LCK_M_U (lock mode update). The lastwaittype and waitresource columns give you information about either the process's current or last wait state, but the waittime column always refers to the current wait state. If waittime is 0, the lastwaittype and waitresource columns are for a previous wait condition and won't help you figure out the current problem.
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:
USE pubs BEGIN TRAN UPDATE titles SET price = price * 1.1
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.
- 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:
- 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).
