aba_lockinfo

An SQL text by Erland Sommarskog, SQL Server MVP.

If you ever tried to track down a blocking situation by running sp_who and DBCC INPUTBUFFER while the users are standing screaming at you, aba_lockinfo is the thing for you. Note that if you are using SQL 2005 SP2 or later, you should try my beta_lockinfo instead.

aba_lockinfo is a stored procedure that provides information about processes and the locks they hold. aba_lockinfo is designed to gather as much situation about a blocking situation as possible, so that you can instantly find the culprit, and kill blocking process if the situation is desperate. Then you can sit back and analyse the output from aba_lockinfo to understand how the blocking situation arose, so that you can take precautions to prevent it from reoccurring. The output from aba_lockinfo shows all active process as well as passive processes with locks, which objects they lock and their last command. The most recent version even gives you the currently running stored procedure and the the current statement within that procedure. (Requires SQL 2000 SP3.)

You can also run aba_lockinfo periodically and run it, for instance, every five minutes, and detect blocking situations the users never alarmed you on. Such blocking situations may indicate that there is a procedure in need of trimming, but the cause also be a bug in client software. Two things two look for:

A client not fully consuming the result set.
A client that calls a stored procedure that starts a transaction. Later the client times out fails to rollback transaction – which is not rolled back automatically.

There are five versions of aba_lockinfo:

Download the version you want by clicking the links. If you are running SQL7 or SQL2000, you can put the procedure in any database you like. If you use SQL 6.5, there are special considerations, see the end of this page.

Note that the version for SQL 2005 is not the proper solution. The code is the same as for SQL 2000 SP3, except that I have accommodated the procedure for the longer output from DBCC INPUTBUFFER. Since SQL 2005 incurs many changes to how metadata and system data is exposed, I have developed a successor, named beta_lockinfo, described separately. Aba_lockinfo appears to work well on SQL 2005, as long as your application does not use new features in SQL 2005 to any greater extent. Here are two examples of known issues: 1) Tables that are in a schema of which the name that does not coincide with the owner will be displayed as "MISSING?". 2) If an application uses MARS ("Multiple Active Results Sets"), and issues multiple requests, this causes aba_lockinfo to fail with a primary-key error.

The way it works

The SP reads the dynamically constructed tables sysprocesses and syslocks (SQL6.5) or syslockinfo (SQL7/2000) into temp tables, and thus collects a snapshot of process and lock information. aba_lockinfo first reads the locks, and then the processes, so there is a small chance for some process information being out of sync. aba_lockinfo translates object ids are translated to names through the system tables in each database with active processes, to give you information that is easy to digest, aba_lockinfo uses DBCC INPUTBUFFER and the system function fn_get_sql() – the latter function is only available in SQL 2000 SP3 or later. Since DBCC INPUTBUFFER is accessed separately, there somewhat bigger probability for it to be out of sync with the process information. The information from fn_get_sql(), is simultaneous with the process snapshot (but may not agree exactly with the lock information).

Parameters

aba_lockinfo takes three parameters, @processes, @details and @fancy, detailed in the table below:

@processes

0 list only "interesting" processes. See below for a discussion on "interesting" processes. This is the default.

1 list all processes, but only perform DBCC INPUTBUFFER and fn_get_sql() on active processes.

2 list all processes, and perform DBCC INPUTBUFFER and fn_get_sql() on all of them.

@details

0 If the same processes holds more one lock of the same kind on the same object, the lock is only listed once along with a count value. This is the default.

1 Each lock is listed separately. Use this option with care. If you have 10000 locks on the system, aba_lockinfo will take a long time to complete with @details = 1.

@fancy

0 Output is passed directly from the working tables as-is. This output is usually sufficient when you use Query Analyzer in grid mode. This is the default on the versions on SQL7 and later.

1 aba_lockinfo puts the result into an extra table, and then produces a result set where the column widths are adapted to data. If you are using text mode in Query Analyzer, you get an extra blank line between each process. @fancy = 1 is good for text mode. In grid mode, you are not likely to notice any difference. The version for SQL Server 6.5 does not have this parameter; but always acts as @fancy = 1.

Interesting processes

An interesting processes is a processing which is doing something interesting:

Holding a lock (beyond a single lock on database level which about all processes in SQL7 or later hold).
Being blocked.
Executing and is not a system processes.
Has an open transaction.
Is not the process running aba_lockinfo.
Does not exist in sysprocesses.

The last thing may seem bewildering, but it could happen. There are two possible reasons for a processes being listed as lock owner, and yet not exist in sysprocesses:

The process has gone kaput, and the lock(s) are orphaned. This indicates a bug in SQL Server.
Since aba_lockinfo retrieves locking and process information separately, so a process that held a lock, may have gone when aba_lockinfo reads sysprocesses.

These two cases are easily distinguishable by the fact that the truly orphaned locks are very likely to reappear with each run of aba_lockinfo. (Orphaned locks typically require a restart of SQL Server.)

How to find the blocker and the blockee

The key column in the result set is the lvl column. If this column has double exclamation marks (!!) this process is blocking. If there is a number the process is blocked.

To see what a process is blocked by, use the lstatus column on SQL7 and SQL2000. This column is WAIT for resources that the process is waiting for. On SQL6.5 use blkby which indicates which process this process is blocked by. Then scan locktype for this process. The lock type will end in -blk when a processes is waiting for this resource.

How much resources does aba_lockinfo take?

I've tried to design aba_lockinfo to be as lean as possible when you run with the default values. But aba_lockinfo has to invent all locks on the server, and if the system is busy and there are million of locks, aba_lockinfo may take around five minutes to complete. If you specify any the parameters with a different value than the default, aba_lockinfo will take more resources. I recommend that you stick to @processes = 0 and @details = 0 in most situations. The cost for @fancy = 1 is more marginal, and it make you more efficient, if you are using text mode.

If your system is really hogged, and you just want to know who is creating all this mess, try this:

   SELECT req_spid, COUNT(*) FROM master.dbo.syslockinfo GROUP BY req_spid

Result set

The result set from aba_lockinfo isn't exactly normalised. That is, there is one row per lock, so all process information is repeated for every lock the process hold. (And if the process does not hold a lock, there is still a row.)

When nothing else is noted, contents is the on all versions of SQL Server.

To make the output easier to read NULLs and other values that means "nothing" are displayed with blank space. When all process data (login, status etc) is blank, this indicates a missing process.

These are the columns:

spid The process id.

ecid Thread number within the process. Only on SQL7/SQL2000. If there is more than one ecid per spid, this indicates that there is parallelism in progress.

cnt When @details = 0: The number of instances of this lock combination. 0 when the process does not hold a lock. When @details = 1: syslockinfo.req_refcnt (not on SQL 6.5.)

login Login name in SQL Server.

status/prcstatus Taken from sysprocesses. Same as in sp_who. Called status in SQL6.5, prcstatus in SQL7/2000.

command Taken from sysprocesses. Same as in sp_who

dbname Current database of the process.

host Host name of the client.

appl Name of program at the client.

opntrn Number of open transactions, taken from sysprocesses.open_trans. SQL7 and later.

lvl This column relates to blocking. When blank the process is not involved in blocking. Double exclamation marks (!!) indicates that the process is blocking without being blocked itself, and thus may be the culprit in a situation where many processes are blocked. A number indicates how far back in the locking queue the process is. 1 means that the process is blocked by the !!-process, 2 means that the process is blocked by a process with lvl = 1, etc.

blkby Which process the process is blocked by. Same as blk in sp_who.

locktype What kind of lock. Same as in sp_lock. For SQL7 and later see the documentation of syslockinfo.req_mode for an explanation. For 6.5 look for values ending in –blk, these are blocking locks.

ownertype Type of object associated with the lock. Transation (Xact), cursor, session or exsession. Take from syslockinfo.req_ownertype. SQL7/2000 only.

object Name of object the process is locking. In SQL7 and later the string may contain an index name. Blank if the process does not hold any locks. If the name includes the string MISSING?, aba_lockinfo was not able to translate the object id, presumably because the object no longer exists. For an application lock (SQL2000) the string is the portion of the resource name visible in syslockinfo.rsc_text.

rsctype Type of locked resource. Taken from syslockinfo.rsc_type. SQL7 and later.

lstatus Status for the lock. Taken from syslockinfo.req_status. SQL7 and later. For blocked processes check for WAIT to see what they are waiting for.

waittime How long the process has been waiting in milliseconds. Taken from sysprocesses.waittime. Blank when 0. SQL7 and later.

waittype Taken from sysprocesses. This column is only said to be reserved in Books Online. See below for a list of values.

cpu Taken from sysprocesses.cputime.

io Taken from sysprocesses.physical_io.

memusg Taken from sysprocesses.memusage.

now The time portion of getdate(), of when the procedure started, so you can relate to the other time columns.

login_time When the processes logged into SQL Server. Taken from sysprocesses.

last_batch When the last batch was sent to SQL Server. Taken from sysprocesses.

last_since The difference between last_batch and now in seconds, including milliseconds. Blank in this columns is special: it means that the value is actually ≥ one million seconds. This value can be negative, if the process submitted a batch, after aba_lockinfo started executing, but before it arrived at reading sysprocesses.

delay The delay in milliseconds from when aba_lockinfo retrieved the lock information until it ran DBCC INPUTBUFFER for this process. The longer the delay, the higher the probability that the process has emitted a new batch, and inputbuffer does thus not relate to the rest of the snapshot.

inputbuffer The last command sent from the client, taken from DBCC INPUTBUFFER. Newlines are replaced with spaces to keep the output on one line.

current_sp The currently executing stored procedure or trigger. This is not necessarily the same as the same procedure that you might see in the inputbuffer column, but may be a procedure somewhere up the call stack. The column is blank if the process is not active, or the current scope is a loose batch of SQL statements. Note that the latter includes dynamic SQL invoked by EXEC() or sp_executesql!
  This column is available only in the versions for SQL 2000 SP3 and SQL 2005.

curstmt This column is only populated if current_sp is and contains the current statement of that procedure at the time of the process snapshot (and thus, may not completely match the locks listed.)
   You will not get any data if the procedure was created WITH ENCRYPTION.
  This column is available in the versions for SQL 2000 SP3 and SQL 2005. On SQL 2000, it's limited to 255 characters, on SQL 2005 it's unlimited.
   Note that if the process is running parallel threads for a query, curstmt is populated only for the main thread, ecid = 0.

stmtoff This column gives the start and end offset for the current statement. This column is only useful if there is data in current_sp, but not in curstmt, when the procedure is encrypted. If you have access to the source from somewhere else, you can use these values, to locate the statement. The offset is in characters, from the first character in the batch that created the procedure.
   If you have data in curstmt, this column is completely redundant.
  This column is available in the versions for SQL 2000 SP3 and SQL 2000.

There is a final column, which is a newline for the last row for a process. Only if @fancy = 1.

The data is sorted by spid, ecid, lstatus (descending, so WAIT comes first) and object. On SQL 6.5, the sort order is by spid only.

waittype

There are two KB articles on waittype values, KB244455 and KB822101. (Thanks to Jerry Cohen and Dave Patrick to making me aware of them.) Jerry Cohen also provided me with this list of values:

Waittype	CodeDefinition
0x0005	Waiting on exclusive page lock
0x0013	Waiting on buffer resource lock (exclusive) request
0x0020	Waiting on buffer in I/O
0x0022	Waiting on buffer being dirtied
0x1300	Waiting on buffer resource lock (exclusive) request
0x2300	Waiting on buffer being dumped
0x5000	Waiting on exclusive page lock
0x6000	Waiting on shared page lock
0x8000	Waiting on network I/O completion
0x8001	Waiting on exclusive table lock
0x8003	Waiting on exclusive intent lock
0x8005	Waiting on exclusive page lock
0x8006	Waiting on shared page lock
0x8007	Waiting on update page lock
0x8011	Waiting on buffer resource lock (shared) request
0x8100	Waiting on writelog

Special considerations for SQL 6.5

aba_lockinfo for SQL7 and SQL2000 uses temp tables (and on SQL 2000 table variabels) and the procedure can be put in any database.

On SQL 6.5 locking situations in tempdb is a major problem. For this reason aba_lockinfo for SQL 6.5 does not use temp tables. Rather aba_lockinfo on SQL6.5 creates permanent tables, which it drops at the end of execution. Therefore, it is recommendable to let aba_lockinfo for SQL 6.5 to reside in a database of its own. Note that this arrangement has the consequence of making aba_lockinfo non-reentrant. That is, two users cannot run the procedure simultaneously. If two or more users need to be able use aba_lockinfo, you could resolve this by having more than one database containing aba_lockinfo. IMPORTANT: whichever database you use, on 6.5 don't place aba_lockinfo in tempdb!

The database with aba_lockinfo can be kept fairly moderate in size, as long as you don't use @details = 1 when there many locks on the system. 50 MB or maybe smaller should do fine. On 6.5 I never bother to separate data and log.

WARNING! On SQL6.5 Don't run aba_lockinfo with SHOWPLAN on. In this case the SP will not terminate, and you must kill it with the KILL command. (Don't try the red button in Enterprise Manager.)

Contact info

If you have questions or comments and not the least suggestions for improvements just mail me, Erland Sommarskog, on esquel@sommarskog.se.

Revision history

2007-07-27 – More bug fixes for DBCC INPUTBUFFER on SQL 2005. Aba_lockinfo now has a successor, beta_lockinfo that is the proper solution for SQL 2005. Added some more text about running aba_lockinfo on SQL 2005.

2006-03-21 – Bug fix: database names with special characters in them, cause syntax errors, because I did not use quotename(). Fixed for SQL7, SQL 2000 and SQL 2000 SP3. Added some text on SQL 2005.

2004-12-27 – Added stmtoff to result set for the SQL 2000 SP3 version.

2004-03-28 – Added link to one more KB article on waittype values.

2004-02-22 – Rewritten all but the SQL 6.5 versions for better performance. New parameter @fancy.

Back to my home page.

spid	The process id.
ecid	Thread number within the process. Only on SQL7/SQL2000. If there is more than one ecid per spid, this indicates that there is parallelism in progress.
cnt	When @details = 0: The number of instances of this lock combination. 0 when the process does not hold a lock. When @details = 1: syslockinfo.req_refcnt (not on SQL 6.5.)
login	Login name in SQL Server.
status/prcstatus	Taken from sysprocesses. Same as in sp_who. Called status in SQL6.5, prcstatus in SQL7/2000.
command	Taken from sysprocesses. Same as in sp_who
dbname	Current database of the process.
host	Host name of the client.
appl	Name of program at the client.
opntrn	Number of open transactions, taken from sysprocesses.open_trans. SQL7 and later.
lvl	This column relates to blocking. When blank the process is not involved in blocking. Double exclamation marks (`!!`) indicates that the process is blocking without being blocked itself, and thus may be the culprit in a situation where many processes are blocked. A number indicates how far back in the locking queue the process is. 1 means that the process is blocked by the !!-process, 2 means that the process is blocked by a process with lvl = 1, etc.
blkby	Which process the process is blocked by. Same as blk in sp_who.
locktype	What kind of lock. Same as in sp_lock. For SQL7 and later see the documentation of syslockinfo.req_mode for an explanation. For 6.5 look for values ending in –blk, these are blocking locks.
ownertype	Type of object associated with the lock. Transation (Xact), cursor, session or exsession. Take from syslockinfo.req_ownertype. SQL7/2000 only.
object	Name of object the process is locking. In SQL7 and later the string may contain an index name. Blank if the process does not hold any locks. If the name includes the string MISSING?, aba_lockinfo was not able to translate the object id, presumably because the object no longer exists. For an application lock (SQL2000) the string is the portion of the resource name visible in syslockinfo.rsc_text.
rsctype	Type of locked resource. Taken from syslockinfo.rsc_type. SQL7 and later.
lstatus	Status for the lock. Taken from syslockinfo.req_status. SQL7 and later. For blocked processes check for WAIT to see what they are waiting for.
waittime	How long the process has been waiting in milliseconds. Taken from sysprocesses.waittime. Blank when 0. SQL7 and later.
waittype	Taken from sysprocesses. This column is only said to be reserved in Books Online. See below for a list of values.
cpu	Taken from sysprocesses.cputime.
io	Taken from sysprocesses.physical_io.
memusg	Taken from sysprocesses.memusage.
now	The time portion of `getdate()`, of when the procedure started, so you can relate to the other time columns.
login_time	When the processes logged into SQL Server. Taken from sysprocesses.
last_batch	When the last batch was sent to SQL Server. Taken from sysprocesses.
last_since	The difference between last_batch and now in seconds, including milliseconds. Blank in this columns is special: it means that the value is actually ≥ one million seconds. This value can be negative, if the process submitted a batch, after aba_lockinfo started executing, but before it arrived at reading sysprocesses.
delay	The delay in milliseconds from when aba_lockinfo retrieved the lock information until it ran DBCC INPUTBUFFER for this process. The longer the delay, the higher the probability that the process has emitted a new batch, and inputbuffer does thus not relate to the rest of the snapshot.
inputbuffer	The last command sent from the client, taken from DBCC INPUTBUFFER. Newlines are replaced with spaces to keep the output on one line.
current_sp	The currently executing stored procedure or trigger. This is not necessarily the same as the same procedure that you might see in the inputbuffer column, but may be a procedure somewhere up the call stack. The column is blank if the process is not active, or the current scope is a loose batch of SQL statements. Note that the latter includes dynamic SQL invoked by EXEC() or sp_executesql! This column is available only in the versions for SQL 2000 SP3 and SQL 2005.
curstmt	This column is only populated if current_sp is and contains the current statement of that procedure at the time of the process snapshot (and thus, may not completely match the locks listed.) You will not get any data if the procedure was created WITH ENCRYPTION. This column is available in the versions for SQL 2000 SP3 and SQL 2005. On SQL 2000, it's limited to 255 characters, on SQL 2005 it's unlimited. Note that if the process is running parallel threads for a query, curstmt is populated only for the main thread, ecid = 0.
stmtoff	This column gives the start and end offset for the current statement. This column is only useful if there is data in current_sp, but not in curstmt, when the procedure is encrypted. If you have access to the source from somewhere else, you can use these values, to locate the statement. The offset is in characters, from the first character in the batch that created the procedure. If you have data in curstmt, this column is completely redundant. This column is available in the versions for SQL 2000 SP3 and SQL 2000.
	There is a final column, which is a newline for the last row for a process. Only if @fancy = 1.