Undocumented Virtual Column: %%lockres%
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_os_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 of the key. Oh, and he opened an incident on Connect, which seems to be closed, but vote on it anyway, I did. %%lockres%% is also covered in Kalen Delaney’s excellent book on SQL Server 2008 Internals and even warrants a bit of discussion in Professional SQL Server 2008, but that was written by James Rowland-Jones, so I’m not sure it counts.
In the meantime, while I was investigating this stuff, evidently the development team was looking into it on their own. They came to the same set of resources and decided to use the virtual column as part of their real-time, transactional application. Yeah, an undocumented “virtual” column going into a major application. Since I would probably be unable to do anything about this, I decided to at least look into how this thing behaves so I can be aware of what types of problems I might run into.
First, a simple query:
SELECTa.City
–,%%lockres%%
FROM Person.Address AS a
WHERE a.AddressID = 432
If you run this query and take a look at the execution plan you’ll see a nice clean clustered index seek, just as you would suspect. If you take away the comment and run it again, the execution plan is identical. On the version of AdventureWorks2008 currently installed on my machine, I get two page reads, regardless of whether or not I include %%lockres%% or not. With the comments removed, it returns the hash of the primary key: (b0004e040bc2). This looks pretty painless, free even.
If we want to see %%lockres%% in action, it’s not too difficult:
BEGIN TRAN
UPDATE Person.Address
SET City = ‘dude’
WHERE AddressID = 432;
–ROLLBACK TRAN
Obviously this will put a key lock on that row in the table. If I just select against sys.dm_os_tran_locks, the data returned looks like this:
resource_type resource_description resource_associated_entity_id request_mode
KEY (b0004e040bc2) 72057594043564032 X
The original request from the development team was for a way to get the key value back when you know that a table is locked, such as the case here. I wrote this simple query to make that happen:
SELECT a.AddressID
FROM person.address(NOLOCK) AS a
JOIN sys.dm_tran_locks AS dtl
ON a.%%lockres%% = dtl.resource_description
WHERE dtl.resource_type = ‘KEY’
This query works and returns our key value of 432 just as you would want. But, take a look at the execution plan:
Yes, that’s a clustered index (or table, same thing) scan followed by a Sort followed by a merge join, processing 19614 rows to return one. But hey, it was only 341 reads. To say the least, I’m not excited about seeing this in a production system. This was explicitly cautioned in Kalen Delaney’s book. While it appears that the remote scan operator, which is how the DMV is accessed in this case, is 59% of the operation, that’s the estimated cost and has been pointed out before, isn’t the best measure of real cost in the system.
The development team went off and developed their own query, they had said they were looking for the key value, but evidently they were looking for who was holding the lock on a particular key value:
SELECT s.nt_user_name
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s
on l.request_session_id = s.session_id
inner join sys.partitions p on l.resource_associated_entity_id = p.hobt_id
where OBJECT_NAME(p.object_id) = ‘Address’ and
l.resource_description in (select %%lockres%%
from person.Address(NOLOCK) a WHERE a.AddressID = 432)
I actually had to adjust their query just a bit to get it to work correctly, but basically they had the right idea. Here’s the final execution plan:
This was still not terribly inspiring a thing to think about running in a production system although it only had one scan and seven reads. Whether or not putting this in a transactional system is a good idea, it certainly adds yet another tool, albeit an undocumented one, to the tool belt.
MS Field Engineer’s on Performance Troubleshooting
Do you want to get a glimpse into how the Microsoft Field Engineers would go about troubleshooting performance issues on your server? Then go and read this blog entry by Denzil Ribeiro. Not only is this an excellent how-to on troubleshooting performance problems, but Mr. Ribeiro provides multiple links that describe the concepts he’s dealing with further, making it a great piece of documentation.
The MS Field Engineer blog is not terribly active, but what gets posted there is worth reading. If you don’t have it on your feed list, you should.
2008 Index Fragmentation
I forgot all about this, but a script I wrote on using all the new functionality of dynamic management views & functions to do index defragmentation and rebuilds got published over at SQL Server Central.
It could stand a bit of tweaking, but gets the job done on several of the systems I’ve tested it on so far.
Dynamic Management Views Put to Work on Blocking
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 sys.dm_exec_sql_text(br.sql_handle) AS brt
LEFT JOIN sys.dm_tran_locks AS btl
ON br.session_id = btl.request_session_id;
More Dynamic Management Views: sys.dm_tran_locks
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 like resource_lock_partition to identify which partition a lock is on or request_reference_count which shows how often the same lock has been requested by a given resource. Then, armed with this data, you can go after other dmv’s. Take, for a GLARING example, sys.dm_os_waiting_tasks. Hmmm if we were to combine something that showed locks with something that showed tasks that were waiting, what might you arrive at? BLOCKING!
The BOL shows a neat little query for just such an occasion:
SELECT
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;
Clearly there is more here to explore. You can even go on to combine these dmv’s with the one’s that show the procedure cache so you can capture the execution plan of queries that are blocking or being blocked. I know dmv’s have been featured in a lot of articles and presentations lately, but I still think lots of people are unaware of just how useful they are. You need to examine this resource further if you’re working in SQL Server 2005/2008. More to come on sys.dm_os_waiting_tasks.
UPDATE: Typo corrected in the first paragraph. Thanks Jack.
ANOTHER UPDATE: More Typo’s corrected in the first paragraph. Thanks Gail
Home of the Scary DBA 
