Is it possible to view LRU-K values in SQL Server?

In SQL Server's sys.dm_os_memory_cache_entries, it is possible to view both the original cost of an entry in the cache as well as the current cost of the cache entry (original_cost and current_cost respectively). The DMV sys.dm_os_buffer_descriptors contains a record of the pages that are currently in memory as well as some metadata about the pages. One interesting chunk of info not available in the DVM are the LRU-K values for the data pages.

Is it possible to get the LRU-K values for data pages in buffer pool in SQL Server? If so, how?

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 bo

StackExchange Database Administrators Q#54377, answer score: 24