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?

  • Is this version-specific? – JNK Dec 3 '13 at 20:24
  • 1
    @JNK - In a perfect world, no, but as long as it works on SQL Server 2012 I don't really worry. – Jeremiah Peschka Dec 3 '13 at 22:50
up vote 20 down vote accepted

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.

enter image description here

(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!

  • 3
    Well, this is one way to spend your Christmas. :-) – RBarryYoung Dec 26 '13 at 3:00
  • 3
    @RBarryYoung Beats playing Trivial Pursuit! – Martin Smith Dec 26 '13 at 3:07
  • If I could give bonus points for appropriate use of a debugger, I would. – Jeremiah Peschka Dec 26 '13 at 23:59
  • 1
    Well done! (And great debugging skills!) – DBArgenis Dec 30 '13 at 21:51
  • @DBArgenis - Thanks! Shame that there doesn't seem to be a practical solution though. It might be quite informative if we could see this easily. – Martin Smith Dec 31 '13 at 17:44

As I mentioned to Mr. Peschka on twitter, this information is kept on the BUF structure that holds the page in memory. DBCC PAGE gives you this information as part of its header.

  • 3
    Begrudgingly, I grant you "the answer", @DBArgenis. I still maintain that DBCC PAGE is a terrible way to find anything, but you do seem to be correct. It's a pity that the data from DBCC PAGE is, effectively, gibberish and doesn't relate to any real system time. – Jeremiah Peschka Dec 4 '13 at 0:40
  • 8
    An example would be a useful addition to this answer. – Mark Storey-Smith Dec 4 '13 at 1:38
  • 3
    @MarkStorey-Smith - I agree. Unless DBArgenis has some trick up his sleeve I can't see how this is useful. – Martin Smith Dec 25 '13 at 21:43
  • 2
    No reference to DBCC PAGE ever amounts to anything useful. – Jeremiah Peschka Dec 26 '13 at 20:54

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.