beta_lockinfo

An SQL text by Erland Sommarskog, SQL Server MVP. Latest revision: 2024-05-01.
Copyright applies to this text. See here for font conventions used in this article.

Initial Overview

If you ever tried to track down a blocking situation by running sp_who, DBCC INPUTBUFFER and the various DMVs 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 re-occurring. The output from beta_lockinfo shows all active process as well as passive processes with locks, which objects they lock, what 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 a 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 28, checked in 2022-12-30 20:20. (The version for Azure SQL Database is from 2024-04-30.)

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

• One for SQL 2016 and later as well as Azure SQL Managed Instance. (More precisely, you need to be at least on SQL 2016 SP1 CU2.)
• One for Azure SQL Database.
• One for SQL 2012 and SQL 2014. (And SQL 2016 lower than SP1 CU2.)
• One for SQL 2008.
• One for SQL 2005. (Service Pack 2 or later required.)
  

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 older 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. beta_lockinfo displays these spid-less tasks with negative numbers < -1000.
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. SQL 2016 and Azure SQL Database only: the most recently submitted command from sys.dm_exec_input_buffer and the top 5 waits for each process from sys.dm_exec_session_wait_stats.
5. 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 on a big table, you will only see a few lines in the output. Collecting the locks is often the most expensive operation in beta_lockinfo.
6. The versions of beta_lockinfo for SQL 2014 and earlier retrieves the most recently submitted command for each process with DBCC INPUTBUFFER.
7. 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.
8. To get the query plans beta_lockinfo uses sys.dm_exec_query_statistics_xml (if available) and sys.dm_exec_text_query_plan. See further the section Query Plans below.
9. 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

For the impatient, here is a complete list of the parameters. And if these five are too many for you, don't be worried. You can run beta_lockinfo without any parameters, and this is what I do myself all the time.

General concepts

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:

• Is blocking another process.
• Is holding one or more locks beyond the single shared lock with owner type SHARED_TRANSACTION_WORKSPACE for their current databases which all processes hold (unless they are in tempdb or master.)
• Has an active task, and which is not:
  • The process that runs beta_lockinfo.
  • Waiting with the wait type BROKER_RECEIVE_WAITFOR.
• Does not exist in sys.dm_exec_sessions (see below).

To clarify the two exception points for an active task: normally you don't want to see the process that runs beta_lockinfo in the output. That is, as long as you are not disrupting anyone else. If you previously had started a transaction and had acquired a lock, you will see yourself listed. (If someone else is running beta_lockinfo in parallel with you, you will see that process.)

Note: there is a known issue for SQL 2008 only: The process running beta_lockinfo may be listed because a lock on the tempdb database. However, the issue does not occur if you run beta_lockinfo from tempdb or master.

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.

• Since beta_lockinfo reads various points of information at different times, a process that beta_lockinfo found in sys.dm_os_waiting_tasks could have disconnected when beta_lockinfo read the process information. Likewise, beta_lockinfo could find a process in sys.dm_tran_locks that logged in after beta_lockinfo read the process information.
• 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. Of particular interest is the wait type THREADPOOL, as this is a task that is waiting to get a worker thread. If you see this on a larger scale, you are likely to have run out of worker threads. This is a condition that typically prevents users from logging in.
• The way beta_lockinfo is written, it may also display system tasks without a session id that are waiting for something else than a list of common system wait types. These waits may or may not be of interest.
• The session is gone, but the locks are left orphaned. This would be a bug in SQL Server.

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:

• Has an open transaction.
• Has an active task with the wait type BROKER_RECEIVE_WAITFOR.
• Has allocated more than 1000 pages in tempdb.

You may see a process holding a lock on a database without any other activity with the owner type SESSION. Two reasons for such locks are:

• The process has set the database in single-user mode. (And by holding a lock, it keeps other processes out.)
• The process has suspended the database for snapshot backup (SQL 2022 and later).

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 columns session_id and 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. A special case of MARS are incoming connections from other SQL Server instances using your instance as a linked server.

An SQL command can be executed single-threaded or by parallel threads. Each such thread is known as an execution context, identified by the column exec_context_id. The main thread for the request has exec_context_id = 0, and any non-zero number for an 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, it 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 exec_context_id and the request_id both 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.

There are two situations that I know of when the blocker is not tracked in sys.dm_os_waiting_tasks:

1. EXECSYNC waits. These occur with parallelism, and you can easily tell which thread all the other threads are waiting for. But since this is not tracked in the internal structure that sys.dm_os_waiting_tasks mirrors, this also means that if there is a deadlock with a different process, it is never detected.
2. The process is trying to write to a database which is frozen for snapshot backup with ALTER DATABASE SET SUSPEND_FOR_SNAPSHOT_BACKUP (SQL 2022 and later). The blocked process will typically have a WRITELOG wait. The process that has suspended the database for snapshot will have BULKOP_BACKUP_FREEZE in the rscsubtype column.

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.

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.

Query Plans

Display in SSMS

In SSMS, you can double-click the XML document and you will see the graphic plan. If an error occurs when retrieving the plan, you will see a document with a single <ERROR> element. This can be due to locking, but also because the nesting level in the XML for the plan exceeds what is supported by the xml data type in SQL Server. (Interesting enough, the more recent sys.dm_exec_query_statistics_xml appears to be able to return such plans in the xml data type nevertheless.)

Estimated or Actual Plan?

If you are on SQL 2014 or earlier, it is simple: the column queryplan will always show you the estimated query plan. While it can be helpful at times to reveal what is going on, it can also be deceivable, because the estimates may be wrong, and the plan is not as peaceful as it may seem.

SQL 2016 SP1 introduced a new DMV sys.dm_exec_query_statistics_xml which permits you to get actual-values-so-far from a running query. This can help you to identify plans with bad estimates without having to wait for the query to complete. The actual- values-so-far are lower than the final actual, but in case of a failed estimate, the actual-values-so-far may still exceed the estimates by a wide margin. Note, however, that generation of actual-values-so-far is not turned on by default on SQL 2016 and SQL 2017. When collecting query plans, beta_lockinfo first attempts sys.dm_exec_query_statistics_xml and if it returns nothing, it gets the traditional estimated plan with sys.dm_exec_text_query_plan.

To get actual-values-so-far on SQL 2016 and SQL 2017 you need to enable the lightweight query-plan infrastructure with trace flag 7412. I recommend that you enable by setting it as a startup trace flag from the SQL Server Configuration Manager.

On SQL 2019 and later as well as Azure SQL Database, the lightweight infrastructure is always active, so no need for trace flags.

Transaction Information

The column trninfo holds information from various columns in the transaction DMVs that I have 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.

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 three exceptions:

• The column now includes the date and is populated for all rows, no matter the setting of @procdata.
• The column rowno is added to retain the ordering in the output.
• On Azure SQL Database the table has a column endpoint, despite the column not appearing in the result set. This column is always NULL.

These columns now and rowno 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 the columns 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 Permissions and Resources below for a discussion on how much 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 five versions for SQL 2005, SQL 2008, SQL 2012/14, Azure SQL Database and SQL 2016 and later.)
2. Three format files for guest.beta_lockinfo. beta_lockinfo_2005.fmt for SQL 2005, beta_lockinfo_2008-14.fmt for SQL 2008/12/14 and beta_lockinfo.fmt for SQL 2016 and later as well as Azure SQL Database.
3. Four .BAT files, beta_lockinfo.bat, beta_lockinfo_2012-14.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 four 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.

You may note that there is no .BAT file for Azure SQL Database. The abundance of login options with Entra ID makes it more difficult to have a pre-cooked file. But you could easily adapt one of the existing files to connect to Azure SQL Database.

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.

Generally, when you have received the BCP file is to load it this way:

EXEC beta_lockinfo @archivemode = 0

BULK INSERT guest.beta_lockinfo FROM 'C:\temp\beta_lockinfo.bcp'
WITH (FORMATFILE = 'C:\temp\beta_lockinfo.fmt')  -- Beware the version!

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 paths for your system. And be careful to use the format file that matches the SQL Server version of the source.

Your SQL Server instance may have a different version than the source system, which can lead to errors if you follow the pattern above. The following applies:

Your local SQL Server instance is:

• SQL 2016 or later or Azure SQL Database: You can load bcp files from these versions painlessly, but not older versions.
• SQL 2008/12/14: You can load bcp files from the same versions as well as SQL 2005 without issues.
• SQL 2005: On SQL 2005 you can only load bcp files from SQL 2005.

If you have a version mismatch, you can always take the version that matches the SQL Server version of the source and grab the CREATE TABLE statement for guest.beta_lockinfo.

Keep in mind that 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.

If you get a run-time error when running archive mode and you recently have installed a new version of beta_lockinfo, try dropping guest.beta_lockinfo as the table definition may have changed.

Permissions and Resources

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 PERFORMANCE STATE (SQL 2022 and later, on earlier versions you need VIEW SERVER STATE). On Azure SQL Database, you need the database permission VIEW DATABASE PEFORMANCE 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 12 seconds to complete 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).

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

But even this query can take several seconds to complete when there are millions of locks.

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.

2024-05-01

There is now a version of beta_lockinfo for Azure SQL Database.

2022-12-30

• beta_lockinfo now correctly recognises Azure SQL Managed Instance, so that you can run the version for SQL 2016 and later on Azure MI.
• On SQL 2022 and Azure MI, the required permission to run beta_lockinfo is the new permission VIEW SERVER PERFORMANCE STATE.
• Previously, beta_lockinfo would fail with an error message when it tried to get the object names for locks in a database in single user mode. beta_lockinfo now checks for this situation and write a text in the objname column that the database is in single-user mode.
• I have made some fine-tuning when it comes to determine what is an interesting process. Previously a granted lock on the resource type DATABASE was not qualifying a process as interesting. I have changed this so that the only "uninteresting" locks are granted shared lock on database with the owner type SHARED_TRANSACTION_WORKSPACE. (Which you only get as soon as you say USE db.) Other database locks are displayed. This includes locks taken because the process has set the database into single-user more or suspended it for snapshot backup (new feature in SQL 2022.)

The last two changes apply to all versions of beta_lockinfo.

2020-09-04

An modification to the update from yesterday: Michelle Durham pointed out that the user may not have permission to msdb..sysjobs, and there is no reason that beta_lockinfo should fail in this case. Thus, the replacement of the job GUID is now wrapped in TRY-CATCH that ignores all errors.

2020-09-03

A small change to the appl column: if the spid is from an Agent job, beta_lockinfo now attempts to translate the hexstring (which represents the job GUID) to the name of the job. This does not happen on SQL 2005.

2020-03-08

Some fairly small modifications (all of them affect all versions of beta_lockinfo).

• For a MARS-enabled process, the columns last_batch and last_since now relate to the individual request and not the most recently submitted statement by the session. For a MARS-enabled process, these two columns are now NULL for request_id = 0.
• If the columns sys.dm_exec_sessions.statement_start_offset and statement_end_offset have the same value, I now show the first 1000 of the text returned from sys.dm_exec_sql_text, as this may be helpful. Examples where this matter is when process is running xp_cmdshell or sp_execute_external_script. You do not see the text of the submitted command, but at least you see what the process is doing.
• Sometimes you could see object names like dbname.<**********>. The asterisks were due to an overly small width in the string conversion. This has now been corrected so that you now see a number. Not that the number is likely to be helpful, since it is a number beta_lockinfo was not able to translate.
• I have also updated the section Query Plans to reflect that on SQL 2019, the lightweight infrastructure for query plans is always on by default. That is, TF 7412 is not needed.

2017-04-08

• In the version for SQL 2016 and higher, beta_lockinfo now makes use of the new DMV sys.dm_exec_query_statistics_xml so it can show plans with current actual values for running queries. See the section Query Plans for more details. As a consequence of this, the minimum version for this version of beta_lockinfo is SQL 2016 SP1 CU2. (The new DMV was added in SP1, and then there was a bug that affected beta_lockinfo that was fixed in CU2.)
• For all versions: added the wait types CLR_SEMAPHORE and HADR_CLUSAPI_CALL to list to filter out for tasks without session_ids.

2016-06-03

• There are now four versions of beta_lockinfo: One for SQL 2016, one for SQL 2012/14, one for SQL 2008 and one SQL 2005.
• (All versions): beta_lockinfo now consolidates constraints created for the same temp table to a single row per constraint and lock type, just like it does for table names.
• (SQL 2014 and later): beta_lockinfo performs more work to work around the locks in sys.partitions for a uncommitted heap and you may now see <heap in trans> in this case.
• (SQL 2016): New column top5waits.
• (SQL 2016): The column inputbuffer is now retrieved through the new DMV sys.dm_exec_input_buffer. The column is now of the type nvarchar(MAX).
• (All versions): if a transaction was open for more than 20 days, this would have led to an overflow.
• (All versions): if you are using MARS, the column inputbuffer now displays the most recently batch for each individual request. Previously, the command for one request was displayed for all requests.
• (All versions): Slight performance fix for the retrieval of query text and query plans.
• (SQL 2012 and later): Slight performance fix in the case when a single plan could not be converted to XML; this is possible thanks to try_convert.

2015-02-01

• Fixed an overflow issue that Chris Canuel ran into. It appears that when a process just has been created the columns login_time and last_batch_request_time can be 1900-01-01, which caused the computation of last_since to overflow. I'm now taking precautions so that there is a NULL in this column in this situation.
• On SQL 2014 you could see a lock-timeout message for objects in tempdb if a temp table had been created in a transaction. This is because SQL 2014 does not always honour READ UNCOMMITTED when accessing metadata, as documented in Books Online. I've employed a workaround to be able to wrestle out as much information as possible. (The precise problem is with getting data about heaps from sys.partitions.)
• I'm now stripping a trailing NUL character for RPC calls from the inputbuffer column. NUL characters and SSMS are not good friends.
• Adapted this page to my new style sheet.

2014-08-12

• Bug fix: if the instance had been up for 115 days or more, beta_lockinfo produced an overflow error.
• The columns last_since and trn_since are now displayed in days + hh:mm:ss.fff

IMPORTANT! If you have used Archive Mode, you must drop the table guest.beta_lockinfo, or else you will get the error String or binary data would be truncated. Note also that the format files have been updated.

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.)
• Bug fix: a lock timeout on more than 32 767 ms would cause arithmetic overflow in beta_lockinfo.
• Bug fix: 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 deviating 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 a column to show tempdb usage.
• Process is "interesting" if it allocates more than 1000 pages in tempdb.
• Bug fix: Had broken procedure name translation, so current_sp was always blank.
• Bug fix: 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.
• Text mode 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 sys.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-28

First release of beta_lockinfo.