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