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," InstantDoc ID 38674, 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.
Prev. page
[1]
2
next page 
