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:
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.
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.)
!!) 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.
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
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 (|
|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 |
|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.
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:
|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|
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.)
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.