Undocumented Virtual Column: %%lockres%

SQL Server 2005, SQL Server 2008, TSQL
One of my development teams needed a mechanism for identifying the value of a key that was part of a lock (don't ask). I'd never tried doing that before. Obviously if you hit the DMV sys.dm_tran_locks you can see the hash of the key in the resource_description column. But how to pull the value back. After some research, I first found this excellent article by the late, great, Ken Henderson (I really wish he was still around). The article outlined, among other things, the use of an undocumented "virtual" column called %%lockres%%. Some more searching then uncovered this great article by James Rowland-Jones, AKA Claypole. He described how, in a very high volume system, he used %%lockres%% to identify the source of a deadlock as the internal mechanisms that SQL Server uses to manage locks, the hash…
Read More

Dynamic Management Views Put to Work on Blocking

SQL Server 2005, SQL Server 2008, TSQL
This is my first pass at a modern (2005/2008) blocking monitoring script. I think it's a decent blocking script to capture information about blocks as they are occurring. Filters can be applied and it wouldn't be hard at all to add on other information such as execution plans, plan hash, etc. SELECT  tl.request_session_id AS WaitingSessionID        ,wt.blocking_session_id AS BlockingSessionID        ,wt.resource_description        ,wt.wait_type        ,wt.wait_duration_ms        ,DB_NAME(tl.resource_database_id) AS DatabaseName        ,tl.resource_associated_entity_id AS WaitingAssociatedEntity        ,tl.resource_type AS WaitingResourceType        ,tl.request_type AS WaitingRequestType        ,wrt.[text] AS WaitingTSql        ,btl.request_type BlockingRequestType        ,brt.[text] AS BlockingTsql FROM    sys.dm_tran_locks tl         JOIN sys.dm_os_waiting_tasks wt         ON tl.lock_owner_address = wt.resource_address         JOIN sys.dm_exec_requests wr         ON wr.session_id = tl.request_session_id         CROSS APPLY sys.dm_exec_sql_text(wr.sql_handle) AS wrt         LEFT JOIN sys.dm_exec_requests br         ON br.session_id = wt.blocking_session_id         OUTER APPLY…
Read More

More Dynamic Management Views: sys.dm_tran_locks

SQL Server 2005, SQL Server 2008, TSQL
I'm working on the chapter on blocking in the new book. Explaining blocking of course means explaining locks. Prior to 2005, to understand locks, you went to sp_lock. Not anymore. Now you can query sys.dm_tran_locks. It's so much more sophisticated than the old system procedure.  Best of all, the information within it is simply a view into the internal locking infrastructure, so you're not placing extra load or extra processing on the system to marshal this data. A simple query to get basic locking information would look like this:   SELECT tl.request_session_id             ,tl.resource_database_id             ,tl.resource_associated_entity_id             ,tl.resource_type             ,tl.resource_description             ,tl.request_mode             ,tl.request_status   FROM sys.dm_tran_locks tl That just outputs roughly the same information as sp_lock. Lots more detail, not available in sp_lock, is available if you need it. Things…
Read More