Understanding of the %%lockres%% and %%physloc%% pseudo columns with index hint

Question

I am experimenting with clustered and non-clustered indexes in SQL Server and I have noticed something interesting.

Here is a simplified version of my T-SQL code:

IF OBJECT_ID('dbo.dept') IS NOT NULL DROP TABLE dbo.dept;
CREATE TABLE dept(id INT, name NVARCHAR(20), address NVARCHAR(50));
GO

INSERT INTO dept(id, name, address) VALUES (1, 'Dept 1', 'Sarjapur Road');
INSERT INTO dept(id, name, address) VALUES (2, 'Dept 2', 'Whitefield');
INSERT INTO dept(id, name, address) VALUES (3, 'Dept 3', 'Electronic City');
INSERT INTO dept(id, name, address) VALUES (4, 'Dept 4', 'Koramangala');
GO

CREATE CLUSTERED INDEX cl ON dbo.dept(id);
CREATE INDEX ncl ON dbo.dept(address);
GO

SELECT *, %%lockres%% lock, %%physloc%% physloc, sys.fn_PhysLocFormatter(%%physloc%%) formatted
FROM dbo.dept WITH (NOLOCK, INDEX (cl))

SELECT *, %%lockres%% lock, %%physloc%% physloc, sys.fn_PhysLocFormatter(%%physloc%%) formatted
FROM dbo.dept WITH (NOLOCK, INDEX (ncl))

And here is the result:

+----+--------+-----------------+----------------+--------------------+------------+
| id | name   | address         | lock           | physloc            | formatted  |
+----+--------+-----------------+----------------+--------------------+------------+
|  1 | Dept 1 | Sarjapur Road   | (de42f79bc795) | 0xB01F000004000000 | (4:8112:0) |
|  2 | Dept 2 | Whitefield      | (9d6bf8154a2a) | 0xB01F000004000100 | (4:8112:1) |
|  3 | Dept 3 | Electronic City | (052c8c7d9727) | 0xB01F000004000200 | (4:8112:2) |
|  4 | Dept 4 | Koramangala     | (1a39e6095155) | 0xB01F000004000300 | (4:8112:3) |
+----+--------+-----------------+----------------+--------------------+------------+

+----+--------+-----------------+----------------+--------------------+-----------+
| id | name   | address         | lock           | physloc            | formatted |
+----+--------+-----------------+----------------+--------------------+-----------+
|  3 | Dept 3 | Electronic City | (b64f1cd4ff4f) | 0x1800000003000000 | (3:24:0)  |
|  4 | Dept 4 | Koramangala     | (4471456166ef) | 0x1800000003000100 | (3:24:1)  |
|  1 | Dept 1 | Sarjapur Road   | (7948805432b9) | 0x1800000003000200 | (3:24:2)  |
|  2 | Dept 2 | Whitefield      | (584262fe5906) | 0x1800000003000300 | (3:24:3)  |
+----+--------+-----------------+----------------+--------------------+-----------+

As you can see, the lock and physloc for the two resultsets are very different. In the past, I always believed that these pseudo columns are revealing information about the dbo.dept table (the clustered index or the heap), since I'm selecting from it. But this testing seems proved that they are showing information about how the data is accessed. Is my understanding correct?

Answer 1

%%physloc%% will give you the physical location of the item which you can see in your results is a file:page:slot location. This is the common notation for identifying an item on a data page (note that there are other structures that may not behave this was such as text trees and fragments). Note that this really only works with items that use traditional pages - for example, this won't work on a Hekaton table (without some other page based index, such as columnstore).

%%lockres%% is the lock hash of the row, as you'd see in the lock manager. This takes various items into account to produce the value, but it doesn't necessarily tell you the same information as %%physloc%% as it's the logical hash and not the physical location.

I don't believe either show you how the data is accessed. Physloc shows you where the data is physically stored, lockres doesn't really expose any attributes as it's a logical output for the set of hashed inputs, since hash functions are one way they don't expose the data as physloc would. You could use it to infer other items about the rows, but there has been instances where the hash algorithm has been shown to not be truly unique.

Answer 2

Enable the option "include actual execution plan" in MSSQLM. Then execute your last two statements again. Look at the properties, outputlist of the rightmost operations. You will see that physloc and lockres come in the first one from the table, in the second one from the index.