beta_lockinfo

An SQL text by Erland Sommarskog, SQL Server MVP. Latest revision: 2014-02-01.

If you ever tried to track down a blocking situation by running sp_who, DBCC INPUTBUFFER and the various DMV:s while the users are screaming, beta_lockinfo is the thing for you.

   What it is
   The way it works
   Parameters
   Interesting and Half-interesting Processes
   Sessions, Execution Contexts and Requests
   How to Find the Blocker and the Blockee
   Result set
   Using Archive Mode
   Permissions
   How Much Resources Does beta_lockinfo Take?
   Contact info and Revision History

What it is

beta_lockinfo is a stored procedure that provides information about processes and the locks they hold as well their active transactions. beta_lockinfo is designed to gather as much information about a blocking situation as possible, so that you can instantly find the culprit and kill the blocking process if the situation is desperate. Then you can sit back and analyse the output from beta_lockinfo to understand how the blocking situation arose and figure out what actions to take to prevent the situation from reoccurring. The output from beta_lockinfo shows all active process as well as passive processes with locks, which objects they lock, which command they last submitted and which statement they are executing. You also get the query plans for the current statements. Normally, you run beta_lockinfo to look at the output directly, but there is also an archive mode where the data is saved to table. This is not the least useful, if you want someone to send you the output from beta_lockinfo at a site you don't have access to yourself.

Download

The latest version is SourceSafe version 16, checked in 2014-01-29 21:47.

There are three versions of beta_lockinfo. Download the one that fits your SQL Server version:

To export the data from beta_lockinfo to a file that you can mail, use any of the BAT files included in beta_lockinfo.zip. See the section Using Archive Mode for more details.

For previous versions of SQL Server (down to SQL 6.5), use my older aba_lockinfo instead.

You can put the procedure in any database you like as long as the database has compatibility level 90 or higher.

The way it works

beta_lockinfo compiles information from a number of DMVs in this order:

  1. sys.dm_os_waiting_tasks from which beta_lockinfo unwinds the blocking chains. beta_lockinfo includes all waiting tasks that have a session id (spid). It also includes some waiting tasks without session ids, but excludes the most common system waits that are benign. A very interesting spid-less wait is THREADPOOL – if you see this wait, your system may be running out of worker threads.
  2. Active transactions from sys.dm_tran_session_transactions and sys.dm_tran_active_transactions.
  3. Process information from a number of places: sys.dm_exec_sessions, sys.dm_exec_requests, sys.dm_os_tasks, sys.db_dm_session_space_usage and sys.dm_db_task_space_usage. On SQL 2005 and SQL 2008, this also includes sys.sysprocesses as some important information is only available here (added to sys.dm_exec_sessions in SQL 2012).
  4. Lock information from sys.dm_tran_locks. In order to make this information more easily digestible, beta_lockinfo aggregates on a number of columns, so if a process holds 10000 locks a big table, you will only see a few lines in the output. Colleting the locks is often the most expensive operation in beta_lockinfo.
  5. beta_lockinfo retrieves the most recently submitted command for each process with DBCC INPUTBUFFER.
  6. Using the sql and plan handles obtained in step 3, beta_lockinfo retrieves the current statement for active processes using sys.dm_exec_sql_text as well as the query plans for this statement with sys.dm_exec_text_query_plan.
  7. beta_lockinfo retrieves the name of all objects on which there are locks, by reading from the catalog views sys.objects et al in each database.

Note that since beta_lockinfo reads the information at different points in time, the various pieces will not be fully consistent. For instance, a new process may log in and grab a lock after beta_lockinfo has read the process information but before beta_lockinfo reads the locks. Or the statement that was current when the process information was collected may not match the locks you see, because the process has moved on.

Parameters

@allprocesses
tinyint
If 0 (the default) beta_lockinfo, lists only "interesting" processes. If 1, beta_lockinfo also lists "half-interesting" processes. If ≥ 2, beta_lockinfo lists all processes. See the section below for the definition of "interesting" and "half-interesting" processes.
@textmode
bit
If 1, the result set is returned to make it easier to read when you have set SSMS to return data in text mode: columns are trimmed to be as wide as the widest data in the column (an idea taken from sp_who2), and there is a blank line inserted between each process. If 0 (the default), the result set is returned without these extra thrills. This mode is also known as grid mode, since in you would typically use this setting when you have selected grid mode in your query tool.
@procdata
char(1)
A List process data on all lines.
F List process data only on the first row(s) for a process, as a header, and remaining rows for the process are blank. This – hopefully! – makes it easier to see the boundaries between the processes in the output in grid mode.
The default is F in grid and archive mode, and A in text mode.
@archivemode
int

When this column has a non-NULL value, beta_lockinfo runs in archive mode. No data is returned as you run the procedure. Instead the data is saved to the table guest.beta_lockinfo. (The guest schema exists in all databases, and cannot be dropped.) This permits you take several consecutive snapshots for later analysis. It is also useful if you want someone at a remote site to send you data for analysis. The value of the parameter specifies that data in the table that is older than @archivemode minutes should be deleted from the table. A special case is when you specify 0: in this case beta_lockinfo does not collect any data, but drops and recreates the table. For more details on archive mode, see the section Using Archive Mode.

Archive mode is incompatible with @textmode = 1.

@debug
bit
If, 1 beta_lockinfo prints progress information about its operations. If you find that beta_lockinfo takes a long time to run, I'm interested to see the output when @debug = 1, particularly if any other step than compiling lock information needs a lot of time.

Interesting and Half-interesting processes

An interesting process is a process which is doing something that potentially could affect other processes directly. That is, a process that fulfils any of these conditions:

To clarify the two exception points for an active task: you will normally not see the process that runs beta_lockinfo in the output. But if you previously had started a transaction and acquired a lock, you will see yourself listed. (If someone else is running beta_lockinfo in parallell with you, you will see that process.) The wait type BROKER_RECEIVE_WAITFOR is the typical state for a Service Broker procedure that is waiting for a message to arrive. In an application using Service Broker, you can have quite a few of these, and including them in the default output has proven to produce too much noise.

Why would a process be missing from sys.dm_exec_sessions? There are a couple of reasons why this could happen.

A half-interesting process is a process which has some level of activity, but is not likely to directly affect other processes. Any of the following conditions qualifies a process to be added to the output when @allprocesses = 1:

Sessions, Execution Contexts and Requests

Up to SQL 6.5, life was very easy. A process was a connection was a session. These days there are a whole bunch of different concepts: sessions, execution contexts, requests, connections and tasks. At least. This section describes how beta_lockinfo uses these concepts.

Let's start with a connection, because it's the easiest in this context: beta_lockinfo completely ignores the DMV sys.dm_exec_connections, right or wrong.

The main concept is the session, as listed in sys.dm_exec_sessions. A session is identified with a session_id, or what old-time users of SQL Server knows as a spid.

A session may be idle, or it may be executing one or more requests. A request is a batch of SQL commands or an RPC call to a stored procedure. In the very most cases, a session runs only one at request at a time, but SQL 2005 introduced MARS, Multiple Active Result Sets. When MARS is in effect, a session may submit a new command batch, even if the previous batch has not completed. The batches are executed in an interleaved fashion. Requests appear in the view sys.dm_exec_requests. A request is identified by the session_id and the request_id. As long as MARS is not in effect, request_id is usually 0. When MARS is in effect the requests are numbered 1, 2 etc. Beside MARS, I have noticed one more case when request_id is non-zero: requests executed by sessions connected through HTTP endpoints. (A feature introduced in SQL 2005, deprecated in SQL 2008 and removed in SQL 2012.)

An SQL command can be executed single-threaded or by parallel threads. Each such thread is known as an execution context, identified by the exec_context_id. The main thread for the request has exec_context_id = 0, and any non-zero number for a exec_context_id indicates parallel execution. There is no view as such that defines the execution contexts, but all execution contexts have a row in sys.dm_os_tasks. Now, here is a funny thing: if a request with a non-zero request_id requires a parallel plan, the request_id for the threads with a non-zero exec_context_id is nevertheless zero, meaning that if a session has multiple requests running, you cannot tell which request the non-zero execution contexts belong to. (I assume, though, that all contexts belong to the same request, since multiple requests are interleaved, and not truly multi-process.)

A task is something that is listed in sys.dm_os_tasks, and is identified with a varbinary(8) value. All execution contexts, and thus all requests, have a row in sys.dm_os_tasks. There are also non-session entries in sys.dm_os_tasks. beta_lockinfo displays these if they are waiting and the wait type is an interesting wait type. That is, not a wait type commonly used by system processes that are almost always waiting.

In the output, beta_lockinfo includes session_id, exec_context_id and request_id. But instead of one column for each, beta_lockinfo merges them into one string with the ids separated by slashes, for instance 56/0/2 (request 2 for session 56, the main thread) or 65/7/0 (execution context 7 for session 65, unknown for which request). In the very common case that the both exec_context_id and the request_id are 0, beta_lockinfo suppresses them, and lists only the session_id. Thus, you will not see 64/0/0, only 64. Session-less tasks are displayed with a session id that is < -1000. You may also see -9 for exec_context_id. This happens when there is a session-less task in sys.dm_os_waiting_tasks that can be connected back to a session through the column parent_task_address in sys.dm_os_tasks. (This never occurs on SQL 2005 as parent_task_address is not available in this version.)

In the output for a process, any non-zero requests appear first, with one line per request. Next follow one or more lines with the session_id proper. There is always at least one such line for a session, even if there is no row for the session in sys.dm_os_tasks. Finally, any execution contexts with a non-zero exec_context_id for the session are listed in order. Occasionally, you may see the same exec_context_id listed twice. This is because two tasks can have the same exec_context_id. (Why it is so, I don't know. Maybe it's a bug. But I've seen it happen.)

Locks are displayed per session. In sys.dm_tran_locks they are listed per execution context and request, but I've opted to collapse them on session level to reduce the number of lines in the output for a massively parallel query. Since it's not possible to (easily) determine to which request an execution context belongs to, it was logical to collapse the requests as well. After all, I don't expect multiple requests to be that common. (Then again, that may reflect my attitude against MARS, a feature that I find to be of dubious value.)

Finally, there is process, which is not really an SQL Server concept, but which I use in this document somewhat loosely. Sometimes I refer to entire session, sometimes to the unique combination of session, execution context and request.

How to Find the Blocker and the Blockee

The key column in the result set is the blklvl column. If this column has double exclamation marks !! this process is a lead blocker, blocking one or more other processes (which could be other execution contexts of the same session) without being blocked itself. If there is a number, the process is blocked. 1 indicates that the process is blocked by a lead blocker, 2 indicates that the process is blocked by a process which in its turn is blocked by a lead blocker and so on. A process is not involved in blocking has a blank in this column.

The values in blklvl may be in parentheses. That is, instead of !! you see (!!). This happens when you have a parallel query, where the various threads are blocking each other, but they are neither blocked by any other session, nor blocking any other session. Thus, if you are investigating a blocking issue, you can ignore sessions where blklvl values are in parentheses.

The column blkby shows which process the process is directly blocked by. The blkby column is formed as a string with session_id, exec_context_id and request_id concatenated with slashes, and with the latter two suppressed if both are zero. Sometimes you may see a string like 54/-1/-1. This means that when beta_lockinfo read sys.dm_os_waiting_tasks there was a blocking task, but when beta_lockinfo read sys.dm_exec_sessions, sys.dm_os_tasks etc, the task had exited. You may also see something like (+4) after the process string. This means that the process is in fact blocked by four more processes but the one listed. (Which one that is listed when there are several blockers is fairly arbitrary.)

There are two other possible values for blklvl: DD and ??. DD means that the process is involved in an on-going deadlock. If you look at blkby, and then go to that spid, look at its blkby and so on, you can expect to come back where you started sooner or later. If a process is blocked by a deadlocked process without being involved in the deadlock itself, blklvl displays 1, 2 etc as with regular blocking. The value ?? means that the process was listed in sys.dm_tran_locks as waiting for a lock, but it was not listed in sys.dm_os_waiting_tasks. This indicates that by the time beta_lockinfo read sys.dm_os_waiting_tasks, the process had not yet requested the lock. 

To see what a process is waiting for, look at the lstatus and waittype columns. In most cases, a process is waiting for a lock, which is indicated by WAIT or CONVERT in the lstatus column. All non-granted locks appear first in the output for a process. In the case of parallel execution, the main thread or a sub-thread may be waiting for another thread to complete. In this case waittype will be CXPACKET.

Result set

As outlined above, the result is organised so that there is essentially one row per group of locks. Locks are aggregated on the following columns in sys.dm_tran_locks: resource_type, resource_subtype, resource_database_id, resource_description (application locks only) resource_associated_entity_id (for the resource types it applies to), request_mode, request_status and request_owner_type.

To this come rows for processes with non-zero request ids, listed before the locks, and processes with non-zero execution context ids, listed after the locks. When the parameter @procdata is 'F', process-data is listed only for the first lock row, as well for any request rows listed before the locks. But it is not repeated for every subsequent lock row or rows for execution contexts.

In several cases, NULLs and other values that mean "nothing" are displayed with blank space to make the output easier to read. I've retained NULL in the cases where I think NULL has something to tell.

spidThe process id with the execution-context id and the request id added, if any of them are non-zero, as described above.
commandThe current command for the request, taken from sys.dm_exec_requests.request_command. This is the same as you see in sp_who, except that for an idle process, you will not see AWAITING COMMAND, but a NULL value.
login

The login the process logged into to SQL Server as. If the process has engaged in impersonation through EXECUTE AS, either through an explicit statement or by calling a stored procedure with an EXECUTE AS clause, the currently impersonated login is shown in parentheses. If the impersonation is on user level, you will see a SID in the parentheses.

If the process is a system process (sys.dm_exec_sessions.is_user_process = 0), login reads SYSTEM PROCESS, and any impersonated login is not shown.

host The host the session connected from. Keep in mind that most APIs permits the application/user to set the host name to whatever they like.
hostprcThe PID (process id) of the client application. Taken from sys.dm_exec_sessions.host_process_id.
endpointThe endpoint for the process. Most of the time you will see things like TSQL Default TCP, but it could also be the name of for instance a SOAP endpoint. Taken from sys.endpoints.name via sys.dm_exec_sessions.endpoint_id.
applThe name the client application used to identify itself when it connected.
dbname The current database for the process. If there are requests with non-zero ids, it can happen that the request has a different current database than the main session. The database for a running request is taken from sys.dm_exec_requests.database_id. The session-level database is taken from sys.dm_exec_sessions.database_id. (sys.sysprocesses.dbid on SQL 2005/2008.)
prcstatus The status of the process. For a running request, this is taken from sys.dm_os_tasks.task_state. For an idle session without a request, the data is taken from sys.dm_exec_sessions.status. You can easily tell them apart, as former is all uppercase and the latter is all lowercase.
ansiopts A list of ANSI-related SET options that deviate from the default setting. These are the values you could encounter:
qid QUOTED_IDENTFIER is OFF.
ARITH ARITHABORT is ON.
ADEF All ANSI options are ON, including IMPLICIT_TRANSACTIONS and CURSOR_CLOSE_ON_COMMIT.
ando ANSI_NULL_DFLT_ON is OFF..
awarn ANSI_WARINGINS is OFF.
apad ANSI_PADDING is OFF.
anull ANSI_NULLS is OFF.
cnyn CONCAT_NULL_YIELDS_NULL is OFF.
It's perfectly normal that ARITHABORT is ON (this is the default in SSMS but not elsewhere), but other settings should be regarded with suspicion (although many system processes runs with all of them OFF). The settings are taken from sys.dm_exec_requests, or from sys.dm_exec_sessions if the process has no active request.
spid_This is the spid column repeated for convenience. However, there is one case where this column will be NULL, and that is when the session appears in sys.dm_tran_locks only. In this case, only the first spid column has the value.
trnopts

Lists transaction-related SET options with values that deviate from the default settings. There are three fields. The first of them reflects SET TRANSACTION ISOLATION LEVEL. The value can be any of Read uncommitted, Repeatable read, Serializable, Snapshot and Unspecified. (As for the latter value, I don't know what it is, but it's listed in Books Online) When the isolation level is the default Read Committed, nothing is listed.

The second field reflects SET LOCK_TIMEOUT, and displays the lock timeout prefixed with LT=. If no lock timeout is set (= -1) this is not displayed.

The third field is for SET DEADLOCK_PRIORITY and displays the chosen priority prefixed by DP=. These settings are taken from sys.dm_exec_requests, or from sys.dm_exec_sessions if the process has no active request. The default priority of 0 is not displayed.

opntrnThe value of @@trancount for the process, blank if this is 0. Note that if a process has a value here, but has NULL in command, and thus is idle, this indicates an orphaned transaction, something which can leads to trouble if the process is holding locks.
trninfo A hyphenated value of five fields giving various titbits about the transaction. Please see the section Transaction Information below.
blklvl When blank the process is not blocked and is not blocking anyone. Double exclamation marks !! indicate that the process is a lead blocker that blocks other processes, without being blocked itself. DD indicates that the process is involved in a deadlock. A number indicates how far back in the blocking queue the process is. 1 means that the process is blocked by a !! process or a DD-process, 2 means that the process is blocked by a process with blklvl = 1, etc. If the value is in parentheses, the block relates to threads in the same session only, and no other session is affected. blklvl can also be ?? which means that the process is waiting for a lock, but was not listed in sys.dm_os_waiting_tasks.
blkbyWhich process the process is blocked by. A string that is formed from the session id, execution-context id and the request id in the same manner as spid.
cntThe columns following cnt details a group of locks, and the cnt column is the number of lock in such a group.
object

The object on which the process has a lock, or is waiting to get a lock on. In most cases, you will see database.schema.table.index here. If the lock is on a key in a clustered index, beta_lockinfo suppresses the index name, to remind you that a key lock in a clustered index is really a row lock on the data.

If you see a number in parentheses, the table is partitioned, and the number is the partition number of the table or index. Even if the table/index is partitioned, beta_lockinfo never shows partition number 1.

If the lock is on an allocation unit, beta_lockinfo includes sys.allocation_units.type_desc in brackets in the string.

For some resource types, you will only see the database name, because the lock is on database level, or an object which is not schema-bound, for instance an XML schema collection. You may also only see the database name if the entity id in sys.dm_tran_locks did not translate, for instance because the object disappeared while beta_lockinfo was running.

Temp tables are displayed without any database prefix, since they always are in tempdb. If you see something like #temp (x3), this means that the process has created three different temp tables with the same name. This can happen if you have a recursive procedure, but also if a process inside a transaction repeatedly calls a procedure that creates a temp table. The three temp tables are distinct entries in tempdb.sys.objects, but beta_lockinfo aggregates the entries to reduce the number of rows in the output.

Table variables are entered in the system catalogue as # + eight hex digits. This combination can also refer a temp table that has been dropped, but where SQL Server has cached the table definition. beta_lockinfo aggregates all these tables to a single row per type of lock with the text #(tblvar or dropped temp table) followed by, for instance (x4), if there is more than one such table.

Sometimes you may see things like dbname.<12345978>.  The number in that case is an object id that beta_lockinfo was not able to find. This could be because the table was dropped before the beta_lockinfo came round to translate the names. It can also occur if you run beta_lockinfo with reduced permissions, see below.

For application locks, the value is taken straight from sys.dm_tran_locks.resource_description.

If an error occurs when retrieving the object name, you will see an error message in this column. Most likely all objects from the same database will have the error message, as the object names are retrieved batchwise per database.

rsctype What sort of resource that is locked. Taken from sys.dm_tran_locks.resource_type. See the topic for sys.dm_tran_locks in Books Online for details.
locktypeWhat sort of lock the process has taken out/is waiting to get. Taken from sys.dm_tran_locks.request_mode. See the topic Lock Modes in Books Online for details.
lstatusWhether the lock has been granted or not. Taken from sys.dm_tran_locks.request_status. In order to make WAIT more visible, beta_lockinfo puts the very common grant in lowercase.
ownertype The entity that owns the lock. Taken from sys.dm_tran_locks.request_owner_type, except that beta_lockinfo shortens SHARED_TRANSACTION_WORKSPACE to STW, as almost all processes have a Sch-S on database level with this owner type.
rscsubtype

This column holds two pieces of locking information.

1. The contents of the column sys.dm_tran_locks.resource_subtype which further details what sort of entity that is being locked.
2. An aggregation of the locking partitions, taken from sys.dm_tran_locks.resource_lock_partition. SQL Server utilises lock partitions only on systems that have 16 or more CPUs, why you will never see any values on smaller systems. For details on lock partitioning, please see Books Online. beta_lockinfo displays the lock partitions as a list in brackets. On SQL 2012 and later, adjacent partitions are collapsed into a range. On SQL 2005 and SQL 2008 each individual partition is listed, why the list can be quite long.
waittimeHow long the process/request has been waiting in seconds (with three decimals). Blank if the process is not waiting. Taken originally from sys.dm_os_waiting_tasks.wait_duration_ms.
waittypeWhat the process/request is waiting for. Blank if the process is not waiting. Taken from sys.dm_os_waiting_tasks.wait_type.
spid__Another repetition of the process id. NULL if the process appears in sys.dm_tran_locks only.
cpuTaken from sys.dm_exec_sessions.cpu_time. If the process is running a request, beta_lockinfo shows the value of sys.dm_exec_requests.cpu_time in parentheses.
physio The sum of the column reads and writes in sys.dm_exec_sessions. If the process is running a request, beta_lockinfo also displays the sum of reads and writes from sys.dm_exec_requests in parentheses.
logreadsTaken from sys.dm_exec_sessions.logical_reads. If the process is running a request, beta_lockinfo shows the value of sys.dm_exec_requests.logical_reads in parentheses.
memgrant

The memory grant for the running request in megabytes with three decimals, taken from sys.dm_requests.granted_query_memory. Blank for sessions that are not running requests.

progress How much of the command that has been completed in per cent. The value comes from sys.dm_exec_requests.percent_complete. SQL Server only presents a value for some commands like DBCC, BACKUP, RESTORE and ROLLBACK. See Books Online for a full list. Blank when the source column is 0.
tempdb

Space usage in tempdb. The column is the sum of the counter values in sys.dm_db_session_space_usage. More precisely that means the sum of allocated pages minus the sum of deallocated pages. The value can be negative in some situations. The value can also be inflated, because when a process fills a temp table with many rows and then drops it, the deallocations are credited to a system process that performs deferred space deallocation. (Sebastian Meine discusses this in detail in this excellent blog post.) If the process is running a request, beta_lockinfo shows the same sum from sys.dm_db_task_space_usage, aggregated per request in parentheses. (The DMV has value per task, but I opted to have a consistent display with other resource columns.)

now The value of sysdatetime() when beta_lockinfo started running, so you can relate to the other time columns. In grid mode and text mode, the value includes only the time portion. In archive mode, you get the full value.
login_time When the processes originally logged into SQL Server. Taken from sys.dm_exec_sessions.login_time. The date is only displayed, if the login was on an earlier day. The format for the date is YYMMDD.
last_batch When the process most recently submitted a command batch. Taken from sys.dm_exec_sessions.last_request_start_time. The date portion is handled as for login_time.
trn_start If the process has an active transaction, this is when this transaction started. Taken from sys.dm_tran_active_transactions.transaction_begin_time.
last_since

The difference between now and last_batch in seconds with three decimals. This value can be negative, if the process submitted a batch after beta_lockinfo started executing but before it arrived at reading sys.dm_exec_sessions. If the process has been running for more than 115 days, you will only see asterisks in this column.

trn_since The difference between now and trn_start in seconds with three decimals. This value can be negative, if the process started a transaction, after beta_lockinfo started executing.
clr This column reads CLR, if the process has a CLR object (procedure, function, trigger etc) somewhere on the call stack. Or at least the text for the column sys.dm_exec_requests.executing_managed_code seems to say so. My experience does not really agree with Books Online. Rather the flag appears to be set if the request at some point has executed CLR code.
nstlvlThe current value of @@nestlevel for the process if it is running a request.
spid___Yet a repetition of the process id. NULL if the process appears in sys.dm_tran_locks only.
inputbuffer The last command sent from the client, taken from DBCC INPUTBUFFER. In text mode, beta_lockinfo replaces newlines with spaces to keep the output on one line. Note that since beta_lockinfo runs this command after having collected the locks (which takes some time if there are many of them), the command you see here may not match other information in the output.
current_sp

The stored procedure, function or trigger that the process was executing when beta_lockinfo collected process information. This can be the same the procedure that you see in the inputbuffer column, but in many cases it is different due to nesting.

The column is blank if the process is not active, or if the current scope is a loose batch of SQL statements. Note that the latter includes dynamic SQL invoked by EXEC() or sp_executesql!

curstmt

The statement that the process was executing when beta_lockinfo collected process information. If current_sp is non-blank, this is a statement in current_sp. If current_sp was created WITH ENCRYPTION, beta_lockinfo cannot retrieve the query text, but instead displays the text "-- ENCRYPTED, pos start/stop", where start and stop are the positions in characters for where the statement starts and stops. If you have access to the source code, you can use these values to determine the statement.

The statement is displayed in full on the first row for the process, but for remaining occurrences only the first 50 characters are shown.

If curstmt is blank, this could be because the process is not active. But it could also be involved in activity outside a statement, for instance compilation of a stored procedure not in cache.

In text mode, beta_lockinfo replaces newlines with spaces to keep the output on a single line.

queryplan

The query plan for curstmt as an XML document. This column is presented in grid and archive mode, but is absent in text mode.

In most versions of SSMS, you can double-click the XML document and you will see the graphic plan. However, this does not work with some versions of SSMS 2008 R2, See this blog post from SQL Server MVP Aaron Bertrand how to repair this. This feature is not available in SSMS 2005, but you will have to save the XML document with the extension .sqlplan and reopen it. If an error occurs when retrieving the plan, you will see a document with a single <ERROR> element. (SQL Server can generate plans with a nesting level that exceeds what is supported by the xml data type in SQL Server.)

rowno This column appears only in archive mode. This is a numbering to retain the ordering used for grid mode and text mode.
 In text mode there is a final blank column which is used to generate the blank lines to keep different processes apart.

The data is sorted by spid (with non-zero request ids first, non-zero execution-context ids last), lstatus (with CONVERT and WAIT first), object and rsctype.

Transaction Information

The column trninfo holds information from various columns in the transaction DMVs that I preferred to collapse into a single column to make it easier to read. The column can have up to five fields separated by hyphens:

U/S
U The transaction is a user transaction, that is, started by BEGIN TRANSACTION, or because SET IMPLICIT_TRANSACTIONS is in effect.
S The transcation is a system transaction, typically a transaction defined by a single statement. (But which may invoke other modules, for instance triggers or INSERT-EXEC.)
Transaction type This field reflects sys.dm_tran_active_transactions.transaction_type.
RW Read-write transaction
R Read-only transaction.
SYS System transaction.
DIST Distributed transaction.
Number An undocumented value.
Transaction state Taken fom sys.dm_tran_active_transactions.transaction_state. Books Online indicates that the possible values are 0 through 8, but I've seen other values when working with distributed transactions. In most cases you will see 2 (= active transaction) here. See the topic for sys.dm_tran_active_transactions in Books Online for a complete listing.
DTC state Taken from sys.dm_tran_active_transactions.dtc_state. This field is only included if this column has a value <> 0, in which case the fields reads one of DTC:ACTIVE, DTC:PREPARED, DTC:COMMITTED, DTC:ABORTED, DTC:RECOVERD. This field indicates that the transaction is distributed, but interesting enough, the transaction type can be DIST, while the dtc_state column still is 0.
Bound If the transaction is a bound transaction (that is, it shares the transaction space with another session), you see BND in the fifth field. (Or more probably the fourth, since I would not expect a bound transaction to have a value in dtc_state.)

When using MARS, a session may have multiple active transactions. In that case, beta_lockinfo displays information about the oldest transaction.

Using Archive Mode

In archive mode, beta_lockinfo writes the output to the table guest.beta_lockinfo in the same database as you have put beta_lockinfo. (The guest schema exists in every database and cannot be dropped.) This table has the same columns as the output in grid mode and the same formatting with two exceptions:

These two columns constitute the primary key of the table.

All columns but three in guest.beta_lockinfo are varchar or nvarchar with the collation Latin1_General_BIN2. The exceptions are now (datetime2(3) on SQL 2008 and later, datetime on SQL 2005), queryplan (xml) and rowno (int).

When you run in archive mode, beta_lockinfo adds a final row with asterisks in all string columns, so that when you look at the output later, you can easily tell different executions apart. The row with asterisks is added even if beta_lockinfo collected no other rows to present. The columns now and rowno are populated for the asterisk rows like they are for other rows.

One way to use archive mode is to run beta_lockinfo several times in quick succession, for instance during a test, to look at the output later. You may also be tempted to run beta_lockinfo as a scheduled job or similar. But since beta_lockinfo formats the output for reading, the content of the table is not really optimal for analysis through queries. If you decide to schedule beta_lockinfo, I recommend that you don't run it more frequently than every five minutes. (See the section How Much Resources Does beta_lockinfo Take? below for a discussion on how resources beta_lockinfo takes.)

Another way to use archive mode is when you need to get information from a site that you don't have access to yourself. In this case you could ask the local DBA to run beta_lockinfo in archive mode, use BCP to copy out the data in the table and send the BCP file to you. This is better than just copying the output from grid mode into an Excel sheet, where the line breaks in the SQL statements cause a mess. To facilitate the use of archive mode at a site where the DBA is not so savvy, there is a zip file beta_lockinfo.zip, which includes:

  1. The code to beta_lockinfo itself. (All three versions for SQL 2005, SQL 2008 and SQL 2012 and later.)
  2. Two format files for guest.beta_lockinfo. (One version for SQL 2005, and one for later versions.)
  3. Three .BAT files, beta_lockinfo.bat, beta_lockinfo_2008.bat and beta_lockinfo_2005.bat, that all do the following:
    1. Load the corresponding version of beta_lockinfo.
    2. Run beta_lockinfo with @archivemode = 60.
    3. Bulk-copy the contents of guest.beta_lockinfo to beta_lockinfo.bcp.
  4. A short README with instructions how to run the BAT files.

The three beta_lockinfo.bat files all take four parameters of which the first is mandatory:

  1. The instance to connect to.
  2. The database where to load beta_lockinfo (and thus create guest.beta_lockinfo if needed). The default is tempdb.
  3. Username for SQL authentication. Default is Windows authentication.
  4. Password for SQL authentication.

The DBA can run the BAT file several times, and each BCP operation will export the entire table. Note that if you want the DBA on the other site to collect data for more than 60 minutes, you need to instruct him or her to change the parameter value for @archivemode; recall that 60 means that data that is older than 60 minutes should be deleted from the table.

When you have received the BCP file, you and load it in the server and database of your choice like this:

EXEC beta_lockinfo @archivemode = 0

BULK INSERT guest.beta_lockinfo FROM 'C:\temp\beta_lockinfo.bcp'
WITH (FORMATFILE = 'C:\temp\beta_lockinfo.fmt')

SELECT * FROM guest.beta_lockinfo ORDER BY now, rowno

The first line drops guest.beta_lockinfo and recreates it without collecting any information from the server you are running it on. The BULK INSERT statement loads the data into the table. Be careful to change the statement to use the actual tasks for your system. Beware that if you load data from SQL 2005, you need to use the format file beta_lockinfo_2005.fmt. Your version of SQL Server does not have to match the version of the source server. That is, you can load data from SQL 2014 on SQL 2008. However, there is one exception: on SQL 2005, you will only be able to load data from SQL 2005. (Because SQL 2005 does not have the data type datetime2(3) used for the now column.) If you want to view query plans, it certainly helps if your version of SSMS is at least of the same version as the source system.

Permissions

Normally, you would run beta_lockinfo from an account that is a member of the sysadmin fixed role or that has been granted CONTROL SERVER. However, you still can run beta_lockinfo from a less privileged account, as long you have the permission VIEW SERVER STATE. In case you are not running beta_lockinfo as sysadmin, you should beware of this:

How Much Resources Does beta_lockinfo Take?

Unfortunately, beta_lockinfo is not as lean as I would like it to be. The chief reason for this is that access to sys.dm_tran_locks is nowhere cheap when there are plenty of locks on the system. A quick test on my desktop, beta_lockinfo needs 32 seconds to complete on SQL 2008 when I have 2.3 million locks on an idle server (in a real case when you have that many locks, your server is anything but idle). The good news is that in SQL 2012 Microsoft reimplemented sys.dm_tran_locks to be faster, and for the same situation, beta_lockinfo completes in 12 seconds. Which still a long time when you have urgent situation to sort out.

There are some other queries that are potentially resource-intensive, since beta_lockinfo works hard on things that normally belong in the presentation layer. When I test on an idle server, all that is dwarfed compared to collecting the locking information. But on a server that is loaded, and particularly one where the CPU utilisation is 100 %, executing beta_lockinfo certainly will add even more pain to your server. To mitigate this, I have taken steps to make sure that no statement in beta_lockinfo will use a parallel plan, and I've also added hints to prevent recompilation.

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

SELECT request_session_id, COUNT(*)
FROM   sys.dm_tran_locks
GROUP  BY request_session_id

This completes in 10 seconds for the same situation on SQL 2008 on my hardware; on SQL 2012 it takes 8 seconds.

Contact info and Revision History

If you have questions or comments and not the least suggestions for improvements just mail me, Erland Sommarskog, on esquel@sommarskog.se. If you run into some error message or incorrect results, please understand that it may be difficult for me to reproduce the problem, since it is may be dependent on situations on your site that I was not able to predict. The more information you include, the better the chance that I will understand what happened. The output from @debug = 1 can be helpful.

2014-02-01

Lots of changes:

  • The parameter @allprocesses now takes three values 0, 1, 2. The previous concept of "interesting processes" has been split in two: "interesting" and "half-interesting" processes to reduce the noise level when you run beta_lockinfo without parameters. With the default value of 0, you only get to see the truly interesting processes, while the value of 1 adds the "half-interesting" processes. Use the value 2 if you want to see all processes. See further the section Interesting and Half-interesting Processes.
  • Introducing archive mode, controlled by the new parameter @archivemode. In archive mode, beta_lockinfo does not produce any output, but writes the data to table guest.beta_lockinfo. I also provide BAT files so that you easily can ask someone at a remote site to run beta_lockinfo for you. See further the section Using Archive Mode.
  • There are now three versions of beta_lockinfo: one for SQL 2005, one for SQL 2008 and one for SQL 2012 and later.
  • New column: memgrant that shows the memory grant for the current query for a process.
  • The column rscsubtype has been augmented to also display locking partitions on servers with 16 or more schedulers.
  • beta_lockinfo no longer shows a blank value for last_since if the process has been logged in for more than 20 days. (But if the process has been logged in for more than 115 days, you will only see asterisks.)
  • Bugfix: a lock timeout on more than 32 767 ms would cause arithmetic overflow in beta_lockinfo.
  • Bugfix: text mode was prone to yield an error about string truncation when there was blocking between exec contexts.
  • On SQL 2012, the statement text was stripped out from the query plans. (Because beta_lockinfo corrected for a bug which exists in SQL 2008 and SQL 2005, and which is fixed in SQL 2012.)
  • Changed the order in which beta_lockinfo retrieves information, so that locks are now retrieved after the processes. This will not reduce the amount of inconsistencies you will see on a busy system, but they will be somewhat different in nature. :-)
2013-03-24 Added a caveat to the description of the tempdb column. The stored procedure itself is unchanged.
2012-11-02
  • Added three new columns ansiopts (to display deviate settings of ANSI-related SET options), trnopts (to display deviating transaction-related SET options and progress (shows how work that has been performed for some types of statements).
  • beta_lockinfo now displays waiting tasks that are not bound to a session if they have an "interesting" wait type, that is, wait types commonly used by system process are ignored. Such tasks have session id < -1000 in the display. The most common such wait type is THREADPOOL which means that you have run out of worker threads.
  • General review of the documentation, that is, this page.
2011-01-27
  • Temp tables are now displayed as #name only. No tempdb prefix, and without the system-generated suffix to make the name unique in the system catalog. Furthermore, if a process has created several temp tables with the same name, the rows for these tables are aggregated to one row per lock type, and a number in parentheses is added to indicate the number of temp tables with that name. (The typical situations when this happens is when a procedure is called several times in the same transaction.) The change does not affect global temp tables.
  • Table variables and other entries in the tempdb system catalog that consists of a # and eight hex digits are now displayed as #(tblvar or dropped temp table). As with temp tables, they are aggregated into a single row per lock with a number added if there are several of them.
2010-11-21
  • Added column to show tempdb usage.
  • Process is "interesting" if it allocates more than 1000 pages in tempdb.
  • Bugfix: Had broken procedure name translation, so current_sp was always blank.
  • Bugfix: procedure name and current statement was missing from text mode.
  • Added permission check, so procedure fails if run without VIEW SERVER STATE. Also added section on permissions to this page.
  • Added LOCK_TIMEOUT and error handling for object-name translation, as you are blocked on SQL 2005 and SQL 2008 on system tables, if you are not sysadmin. In this case, you get the error message instead of the object name.
2009-06-25
  • The script now checks the SQL Server version and the compatibility level, to give more meaningful errors when you don't meet the prerequisites.
  • Added three columns to show information about the current transaction of the process. Also added one more spid column.
  • Textmode was broken. This has been fixed.
2009-01-31
  • The procedure body now reads ALTER PROCEDURE, and the script creates a dummy procedure if beta_lockinfo does not exist.
  • Retrieving query texts separately, to handle the case that a process creates a procedure within a transaction and then executes it without committing the transaction. In this case beta_lockinfo gets locked, and we fall back to get texts spid by spid.
2009-01-10 Fixed bug that caused a NOT NULL violation when there was a lock on a dropped allocation unit.
2008-11-04
  • beta_lockinfo now depicts deadlocks by showing a DD in the blklvl column. Previously, deadlocks did not appear in blkby at all.
  • Another novelty in blklvl is ??. This appears when the process was listed as waiting in sys.dm_tran_locks, but did not appear in sys.dm_os_waiting_tasks.
  • beta_lockinfo now displays the database name for application locks. (An application lock is tied to a database, and it may not be the current database of the process.)
  • Worked around a bug in sys.dm_exec_text_query_plan which returns the full batch text up to the statement for the plan, and not only the statement. This caused some bloat on the network, and when viewing the plan in SSMS. I'm now modifying the XML document to hold the desired statement text only.
  • Include the full statement text on the first line for a process only, and for remaining lines, only show the first 50 characters.
  • When beta_lockinfo cannot translate an object_id, beta_lockinfo now displays the object id in angle brackets.
  • Fixed a bug that caused beta_lockinfo to show multiple lines for METADATA locks with the same properties.
2008-08-16
  • Run with a short lock-timeout when retrieving query plans. According to SQL Server MVP Adam Machanic, this can occur. I'm therefore now including the error message if the query plan cannot be retrieved.
  • Error handling for DBCC INPUTBUFFER, as on SQL 2008 a missing spid raises an error.
  • In inputbuffer, CRLF was incorrectly replaced with the empty string instead of spaces in text mode.
2008-06-06 No changes, but I hereby declare that beta_lockinfo has passed the test well enough to no longer be regarded as a beta version.
2007-12-09
  • Implemented a workaround to avoid the problem with duplicates keys that occur when the two tasks have the same execution context id.
  • Also worked around a case where dm_os_waiting_tasks can include duplicate rows.
  • If a thread is only blocking other threads or requests in the same thread, put the value in blklvl in parentheses.
  • Added an indicator in the blkby column on how many other tasks that may be blocking the process.
  • The spid string now uses slashes as delimiter.
  • -1 can appear in the spid string in blkby indicate that a task was that was blocking had exited before we could connect it to a request or execution context id.
2007-11-18
  • Adding error handling around INSERTs that are known to bomb on PK violation, and produce a debug output, so we can find out what is going on.
  • Added fallback for the possible case that the query plan is not convertible to XML. Kudos to SQL Server MVP Razvan Socol for giving me a test case.
2007-07-28First release of beta_lockinfo.

Back to my home page.