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. Note that beta_lockinfo requires SQL 2005 SP2. For previous versions of SQL Server (down to SQL 6.5), use my older aba_lockinfo instead.

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

Get the source to beta_lockinfo here.  (SourceSafe version 12, checked in 2009-06-26 23:20.)

You need to have SQL 2005 SP2 or later to run beta_lockinfo. If you would like to run beta_lockinfo on SQL 2005 RTM or SP1, see at end of the article, which changes you need to make to the code.

You can put the procedure in any database you like. However, the database must be in compatibility mode 90 or later.

Notes for old aba_lockinfo users

If you have used my old aba_lockinfo in the past, you will find that beta_lockinfo is the same in essence, but there are nevertheless several changes. The parameter list is new, and I've abolished the alternative to display all locks. You can only get them aggregated. The parameter @fancy is now called @textmode. By default, process data is only displayed for the first row(s) in grid mode. Some columns in the old result set are replaced with more interesting columns from the DMV:s in SQL 2005. The column memusage is gone entirely. The columns have also been rearranged a bit.

I hope you will appreciate the changes.

I want your input!

I would certainly appreciate to get any feedback on problems you face, be that error messages or incorrect results. Please appreciate that it may be difficult to recreate error situations, as they may relate to conditions at your site. The more information you can include, the better.

I am also interested in hearing from you, if you think that anything should be added to or dropped from the output, or if you have opinions on the output format as such. Given the wealth of information available in the DMV:s in SQL 2005, it has been a delicate affair to select what to include or not.

As for known issues, please see the end of the document.

You reach me at esquel@sommarskog.se.

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 then 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 here only), sys.dm_exec_requests, sys.dm_os_tasks, 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 new 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:

@allprocesses If 0 (the default) beta_lockinfo, lists only "interesting" processes. If 1, beta_lockinfo lists all processes. See below for a definition of an "interesting process".
@textmode If 1, the result set is returned to make it easier when you have set SSMS/QA 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.
   In aba_lockinfo, this parameter is called @fancy.
@procdata
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 mode, and A in text mode.
@debug 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 processes

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

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.

Sessions, execution contexts and requests

Up to SQL 6.5, life was very easy. A process was a connection was a session. With SQL 2005 there are now 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 a new feature, Multiple Active Result Sets, known as MARS. When MARS is in effect, a session may submit a new command batch, even though the previous batch has not completed, and 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.

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. (You may recall that sysprocesses in SQL 2000 has a column with the cryptic name ecid. That's the same thing.) 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, but there are also non-session entries in sys.dm_os_tasks. However, these are completely neglected by beta_lockinfo.

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 these, and list only the session_id. Thus, you will not see 64/0/0, only 64.

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 such 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. My experience from aba_lockinfo, which displays locking information for per execution context, is that this generate lots of lines in the output for a massively parallel query, but this mainly serves to make the output difficult to read. And 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, multiple requests should not 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 not 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 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 processes. 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 sysprocesses 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 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:

U/S
UThe 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 an 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.
RWRead-write transaction
RRead-only transaction.
SYSSystem transaction.
DISTDistributed transaction.
Number An undocumented value.
Transaction stateTaken 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 here. See the topic for sys.dm_active_transactions in Books Online for a complete listing.
DTC stateTaken 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.
BoundIf 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.

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 means "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.

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.
loginThe login the process logged into to SQL Server with. If the process have 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 impersonated is a mere database user, 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.
hostThe host the session connected from. Keep in mind that most APIs permits application 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.
dbnameThe 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_sessions.database_id. The session-level database is taken from sys.sysprocesses.dbid.
prcstatusThe status of the process. For a running request, this is taken from sys.dm_os_tasks.task_state. For a 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.
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.
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.
trninfoA hyphenated value of five fields giving various tidbits about the transaction. Please see the table above for details.
blklvlWhen 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 locking 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 parentheses, the block is within a 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.
objectThe 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.
   If you see table starting with # followed by 8 eight hex digits, this is a table variable, or a temp table that has been dropped and cached.
   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.
   For application locks, the value is taken straight from sys.dm_tran_locks.resource_description.
rsctypeWhat sort of resource that is locked. Taken from sys.dm_tran_locks.resource_type. See the topic for sys.dm_tran_locks 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. (The complete path is: SQL Server Database EngineAccessing and Changing Database DataLocking and Row VersioningLocking in the Database EngineLock Modes.)
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.
ownertypeThe 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 has a Sch-S on database level with this owner type.
rscsubtypeFor some resource types, the column sys.dm_tran_locks.resource_subtype further details what sort of entity that is being locked. I have mainly seen it with the resource type METADATA.
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.
physioThe sum of 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.
nowThe value of getdate() (time portion only) when beta_lockinfo started running, so you can relate to the other time columns.
login_timeWhen the processes originally logged into SQL Server. Taken from sys.dm_exec_sessions.login_time.
last_batchWhen the process most recently submitted a command batch. Taken from sys.dm_exec_sessions.last_request_start_time.
trn_startIf the process has an active transaction, this is when this transaction started. Taken from sys.dm_tran_active_transactions.transaction_begin_time.
last_sinceThe difference between now and last_batch in seconds, including milliseconds. Blank in this column is special: it means that the value is actually > 20 days. 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.
trn_sinceThe difference between now and trn_start in seconds, including milliseconds. This value can be negative, if the process started a transaction, after beta_lockinfo started executing.
clrIf the process has a CLR object (procedure, function, trigger etc) somewhere on the call stack, this column reads CLR, else it is blank.
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.
inputbufferThe 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.
current_spThe currently executing stored procedure, function or trigger. This is not necessarily the same as the same procedure that you see in the inputbuffer column, as this procedure may have invoked another procedure. The column is blank if the process is not active, or the current scope is a loose batch of SQL statements. Note that the latter includes dynamic SQL invoked by EXEC() or sp_executesql!
curstmtThe statement that the process is currently executing. 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 on full on the first row for the process, but for remaining occurrences only the first 50 characters are shown.
   In text mode, beta_lockinfo replaces newlines with spaces to keep output on a single line.
current_planThe query plan for curstmt as an XML document. This column is only presented in grid mode, and is absent in text mode. If an error occurs when retrieving the plan, you will see a document with a single <ERROR> element.
   Tip: if you are using SQL Server Management Studio 2005, click the value and it will open in a separate tab. If you then save the document in a file with the extension .sqlplan, you can open the file in Mgmt Studio to see the plan graphically. In SSMS 2008 it's really neat: click the XML document and the graphic plan will open directly, oh-la-la!
 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.

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. I had a situation with 2.3 million locks, and a mere SELECT COUNT(*) took 20 seconds to run. The SELECT COUNT(*) with GROUP BY that beta_lockinfo performs took 70 seconds. This was on a workstation, not a real server. On the other hand, this was on an idle machine and this was a convoluted test case. Normally when you have 2.3 million locks, your server will be anything but idle. An execution time of five minutes is unfortunately not to be unexpected in such a case. Again, I like to stress that the decisive factor is the number of locks.

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

It appears that as long as you do not include any character columns in the output, the GROUP BY operation does not take that much more time than a plain SELECT COUNT(*). (For which 20 seconds is still far too much for my taste.)

Running beta_lockinfo on SQL 2005 RTM and SP1

The version of beta_lockinfo I have posted here, does not run on SQL 2005 RTM and SP1. If you still want to run beta_lockinfo on these versions of SQL Server, this section details the changes you need to make to get it work.

First find these two lines:

coalesce(nullif(es.original_login_name, ''),
         suser_sname(es.original_security_id)),

Replace with:

coalesce(nullif(es.login_name, ''), suser_sname(es.security_id)),

(This is the same as the line above.) If there has been impersonation, you will only see the currently impersonated login in the column login; the original login is not available.

Next find these two lines:

OUTER  APPLY sys.dm_exec_text_query_plan(er.plan_handle,
             er.statement_start_offset, er.statement_end_offset) AS etqp

Here you have two choices. Either delete this line entirely, and in such case you should change the line reading

convert(xml, etqp.query_plan),

To simply read "NULL,". You can also change the OUTER APPLY to read

 OUTER APPLY sys.dm_exec_query_plan(er.plan_handle)AS etqp

If you do this, current_plan will still have a value, but for a stored procedure that will be the plans for all statements in the procedure.

I did not want to put out a proper version of beta_lockinfo for RTM and SP1, since I've not performed any serious testing on these versions.

Contact info

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

Revision history

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 allocatoin 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.