beta_lockinfo

An SQL text by Erland Sommarskog, SQL Server MVP.

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

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, the command they last submitted and which statement they are executing. You also get the query plans for the current statements.

Download

The latest version is SourceSafe version 15, checked in 2011-11-18 19:14.

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

• One for SQL 2008 and later.
• One for SQL 2005. (Service Pack 2 or later required.)
  

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 first compiles information about all locks in the system from the DMV 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 2-3 lines in the output. beta_lockinfo then unwinds the blocking chain from the DMV sys.dm_os_waiting_tasks.

beta_lockinfo proceeds to find information about active transactions from sys.dm_tran_session_transactions and sys.dm_tran_active_transactions.

Next beta_lockinfo retrieves process information from a number of places: sys.dm_exec_sessions, sys.sysprocesses (yes, some information is available only in this old compatibility view), sys.dm_exec_requests, sys.dm_os_tasks, sys.db_dm_session_space_usage, sys.dm_db_task_space_usage, sys.dm_exec_sql_text and sys.dm_exec_text_query_plan. By default, beta_lockinfo only looks at "interesting" processes, as explained below.

beta_lockinfo then goes on to retrieve the most recently submitted command for each process. The final task before returning the result set is to translate the ids from to sys.dm_tran_locks to object and index names through the catalog views.

Note that since beta_lockinfo reads the various types of information at different points in time, the information may be out of sync. For instance, a process that held a lock could have exited before beta_lockinfo retrieves the process information. It could also have moved on to a new statement, so the lock information does not match the current statement.

Parameters

beta_lockinfo takes four parameters, @allprocesses, @textmode, @procdata and @debug, detailed in the table below:

Interesting processes

An interesting process is a process which is doing something interesting:

• Holding or waiting for a lock (beyond a single Sch-S lock on database level which about all processes hold).
• Being blocked or blocking some other process.
• Has a task in sys.dm_os_tasks, and this is not a system processes. (See note below.)
• Has an open transaction.
• Is not the process running beta_lockinfo.
• Does not exist in sys.dm_exec_sessions (see below).
• Has allocated more than 1000 pages in tempdb.

Note that since activation procedures for Service Broker runs as system processes (sys.dm_exec_sessions.is_user_process = 0), such a process will not be listed if it holds no other lock than the standard schema-stability lock on the database. Had I not included the condition on is_user_process, beta_lockinfo would also list various background tasks which would be too much noise.

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

• Since beta_lockinfo retrieves locking and process information separately, a process that held a lock may have gone when beta_lockinfo reads sys.dm_exec_sessions.
• The topic for sys.dm_tran_locks in Books Online lists two cases where the column request_session_id can be negative to indicate locks without a session.
• It is a task in sys.dm_os_tasks that has not (yet) been assigned a session id. These processes are generally interesting, unless they have a wait type commonly used by system processes that are always waiting. Particularly interesting is if you see such processes that have the wait type THREADPOOL, which means that you have run out of worker threads. This is a condition that typically prevents users from logging in.
• The session is gone, but the locks are left orphaned. This would be a bug in SQL Server.

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 process 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 is 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 us 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. (Note: This -9 can never appear 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 of more other processes (which could be another execution context of the same thread) 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 dm_os_waiting_tasks there was a blocking task, but when beta_lockinfo read dm_exec_sessions, 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 could indicate that by the time beta_lockinfo read sys.dm_os_waiting_tasks, the process had been granted 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.

Transaction Information

The output from beta_lockinfo includes a couple of columns related to active transactions. Of these, opntrn is taken from sys.dm_exec_requests (or sysprocesses if the process is idle) and mirrors the current value of @@trancount for the process.

The columns trn_start and trn_since are also straightforward. They reflect when the current transaction started. The first column gives the absolute point in time, while the second tells you for how many seconds the transaction has been running. This value can in fact be negative, which indicates that the transaction started after beta_lockinfo started running. You can use these columns to determine whether a transaction has been orphaned. This is particularly likely if trn_start is before last_batch, but keep in mind that it's perfectly legal to start a transaction from client level.

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:

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

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, and it is also listed for any request rows listed before the locks.

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 a NULL has something to tell.

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.

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:

• If you don't have access to all databases on the server, you can obviously not retrieve object names from these databases. (With CONTROL SERVER you have implicit access to all databases.)
• The same applies if you have access to the database, but you do not have VIEW DEFINITION on locked objects. In this case you will only see the object id.
• For objects created in the same transaction, you may get a lock-timeout error, even if you have VIEW DEFINITION on schema or database level. It seems that this does not happen if you are member of db_owner in the database. (beta_lockinfo reads all data with READ UNCOMMITTED, but permission checks require schema stability locks, and such locks are required also in READ UNCOMMITTED.)

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 has reimplemented sys.dm_tran_locks on SQL 2012 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.

Of the other parameters @textmode = 1, will take some more resources that you may want to avoid on a swamped server.

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

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.

Revision history