sql-server add tag
Jeremiah Peschka (imported from SE)
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?
Top Answer
Martin Smith (imported from SE)
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][1] 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][2]

(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 

```none
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 

```none
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!

  [1]: http://youtu.be/9n6FzIf5Hy4?t=22m36s
  [2]: https://i.stack.imgur.com/MNIz0.png

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.