There is in fact no useful way to do this as far as I can see.
The other answer mentions DBCC PAGE
and leaves it up to the reader to figure out the details. From experimentation I assume they mean bUse1
.
This fails to take account that DBCC PAGE
is itself a use of the page and the value gets updated before it is shown to us.
A script demonstrating this is below (takes 12 seconds to run).
USE tempdb;
CREATE TABLE T(X INT);
INSERT INTO T VALUES(1);
DECLARE @DBCCPAGE NVARCHAR(100);
SELECT @DBCCPAGE = 'DBCC PAGE(0,' + CAST(file_id AS VARCHAR) + ',' + CAST(page_id AS VARCHAR) + ',0) WITH TABLERESULTS;'
FROM T CROSS APPLY sys.fn_PhysLocCracker (%%physloc%%)
DECLARE @DbccResults TABLE
(
ID INT IDENTITY,
ParentObject VARCHAR(1000)NULL,
Object VARCHAR(4000)NULL,
Field VARCHAR(1000)NULL,
ObjectValue VARCHAR(MAX)NULL
)
INSERT INTO @DbccResults EXEC(@DBCCPAGE)
WAITFOR DELAY '00:00:07'
INSERT INTO @DbccResults EXEC(@DBCCPAGE)
WAITFOR DELAY '00:00:05'
INSERT INTO @DbccResults EXEC(@DBCCPAGE)
SELECT *
FROM @DbccResults
WHERE Field = 'bUse1'
ORDER BY ID
EXEC(@DBCCPAGE)
DROP TABLE T
Typical results are
+----+--------------+-------------------------+-------+-------------+
| ID | ParentObject | Object | Field | ObjectValue |
+----+--------------+-------------------------+-------+-------------+
| 8 | BUFFER: | BUF @0x00000002FE1F1440 | bUse1 | 54938 |
| 49 | BUFFER: | BUF @0x00000002FE1F1440 | bUse1 | 54945 |
| 90 | BUFFER: | BUF @0x00000002FE1F1440 | bUse1 | 54950 |
+----+--------------+-------------------------+-------+-------------+
With the second result being
+---------+-------------------------+--------------+--------------------+
| BUFFER: | BUF @0x00000002FE1F1440 | bpage | 0x00000002F4968000 |
| BUFFER: | BUF @0x00000002FE1F1440 | bhash | 0x0000000000000000 |
| BUFFER: | BUF @0x00000002FE1F1440 | bpageno | (1:120) |
| BUFFER: | BUF @0x00000002FE1F1440 | bdbid | 8 |
| BUFFER: | BUF @0x00000002FE1F1440 | breferences | 0 |
| BUFFER: | BUF @0x00000002FE1F1440 | bcputicks | 0 |
| BUFFER: | BUF @0x00000002FE1F1440 | bsampleCount | 0 |
| BUFFER: | BUF @0x00000002FE1F1440 | bUse1 | 54950 |
| BUFFER: | BUF @0x00000002FE1F1440 | bstat | 0x9 |
| BUFFER: | BUF @0x00000002FE1F1440 | blog | 0x1c9a |
| BUFFER: | BUF @0x00000002FE1F1440 | bnext | 0x0000000000000000 |
+---------+-------------------------+--------------+--------------------+
The output after the 7 second delay is incremented by 7 and after the 5 second delay by 5.
So it seems clear that these LRU values are seconds since some epoch. Restarting the SQL Server service does not alter the epoch but restarting the machine does.
The value rolls over every 65,536 seconds so I presume that it just uses something like system_up_time mod 65536
This does leave one unanswered questions in my mind (any takers?). SQL Server uses LRU-K
with K=2
according to the internals book. Shouldn't there be a bUse2
? If so where is that?
There is one way of observing the bUse1
value without changing it that I know of though and that is demonstrated by Bob Ward here.
Attach a debugger to the SQL Server process and display referenced memory for the memory address of the buffer structure (shown to be 0x00000002FE1F1440
above).
I did this immediately after running the script above and saw the following.
(From previous experimentation I'd found the highlighted bytes were the only ones that changed between runs so these are definitely the right ones).
One surprising aspect is that SELECT CAST(0xc896 as int)
= 51350
.
This is exactly 3600 (one hour) less than reported by DBCC PAGE
.
I believe this to be some attempt to disfavour pages being kept in cache by calling DBCC PAGE
itself. For a "normal" page select this one hour adjustment does not occur. After running
SELECT *
FROM T
SELECT ((ms_ticks) % 65536000) / 1000 AS [Roughly Expected Value]
FROM sys.dm_os_sys_info
The value shown in memory is as expected.
The DBCC
command actually updates that value twice. Once at
sqlmin.dll!BPool::Touch() + 0x3bfe bytes
sqlmin.dll!BPool::Get() + 0x12e bytes
sqlmin.dll!LatchedBuf::ReadLatch() + 0x14f bytes
sqlmin.dll!UtilDbccDumpPage() + 0x364 bytes
sqlmin.dll!DbccPage() + 0xfa bytes
sqllang.dll!DbccCommand::Execute() + 0x153 bytes
With the higher value then again at
sqlmin.dll!LatchedBuf::FreeAndUnlatch() + 0x71 bytes
sqlmin.dll!UtilDbccDumpPage() + 0x545 bytes
sqlmin.dll!DbccPage() + 0xfa bytes
sqllang.dll!DbccCommand::Execute() + 0x153 bytes
With the lower one.
I'm not aware of any way to get buffer addresses for pages without using DBCC BUFFER
/ DBCC PAGE
any way though and using both of these changes the value we are trying to inspect!