In today’s blog posting I want to show you a undocumented function in SQL Server, and how you can use that function to find out on which pages records are stored.
%%lockres%%
The undocumented function that I want to show you today is called %%lockres%% and has to do with the locking implementation of SQL Server. As you might know, SQL Server implements a locking hierarchy and requests locks on the table level, on the page level, and on the record level. When a lock on the record level is requested, SQL Server doesn’t place the lock on the record itself – SQL Server just generates a hash value and the resulting hash value is finally locked. To calculate that hash value, SQL Server uses the undocumented function %% lockres%% – that you can also call yourself.
When you call that function on a clustered table (a table where a clustered index is defined), %%lockres%% returns you the hash value of the clustered key column.
SELECT %%lockres%%, * FROM Person.Person
The value returned by %%lockres%% is almost meaningless, because it is just a hash value. But it can be very handy, because in the DMV sys.dm_tran_locks you will also find that hash value in the column resource_description. Therefore it is easy to find out on which record a specific lock was requested.
When you want to request a row-level lock on a heap table (a table without a clustered index), there is no key value on which a hash value can be generated. In that case SQL Server places the lock on the RID value – the so-called Row Identifier Value. This value is 8 bytes long and has the following format: FileID:PageID:Slot. And when you call %%lockres%% on a heap table, SQL Server will return you this RID value.
SELECT %%lockres%%, * FROM DatabaseLog
Therefore it is very easy to determine and find out on which file, page, and slot a specific record is stored on a heap table – easy, isn’t it? 😉
Thanks for your time,
-Klaus
2 thoughts on “Heap Tables and %%lockres%%”
I can prove to you that “%%lockres%%” works very fine in production, although is not documented.
Before many years, I had a challenge to develop some
information to user who is trying to edit record,
locked by another user. The goal was to give user
information who is currently editing record. It means,
which user, on which host computer.
It was win form application with pesimistic locking
( it is default locking mechanisam even on sql server 2016 ).
So, when a record is locked by another user, you should be able to see alert in form
“Record is currently locked by user PLACE\Admin on HOST DARKO-W7I7”.
The only way to solve this problem was to start digging into sys.dm_tran_locks.
“%%lockres%%” is saved under column resource_description in dmv sys.dm_tran_locks.
With combining sys.dm_tran_locks with sys.partitions,sys.tables,sys.schemas and sys.dm_exec_sessions, I was
able to collect all needed information.
In .net I put snippet which check resource_description with SELECT %%lockres%% FROM . WHERE =
and these give endUser “friendly” information.
Everything works fine for many years.These include sql server version from 2005 up to 2016.
When I read this article I’m really glad that someone is still writing about it!
Hello Darko,
Thanks for your great comment.
-Klaus