Buffer Cache page replacement policy in SQL Server 2008


  • I am working on a SQL Server Performance Utility as a college project part of which involves some analysis of datatypes against existing data. Thus potentially it could involve scanning a table larger than the buffer cache.

    Whilst this could potentially/ideally be run against a restored backup on a non production server or tablesample could be used to scan only some of the pages I want to discuss the implications of doing a full scan against a large table on a production server.

    This is what I have read so far (from the SQL Server 2008 internals book).

    • The buffer pool is periodically scanned from beginning to end.
    • This task is carried out by individual Workers after they schedule an asynchronous read and by the  Lazy Writer process (which wakes up periodically and checks the size of the free list)
    • During the scan a value is associated with each buffer based on its usage history . When the value gets low enough the dirty page indicator checked and if required a write is scheduled to write the modification to disc. After the write (if required) the page is freed, the buffer is put on the free list.
    • SQL Server uses an LRU-K/2 algorithm.

    As far as I understand it the gist of the LRU-K/2 algorithm is that it replaces the page whose penultimate  access is least recent among all penultimate accesses.

    I am unclear as to how this applies to my case however. If a useful page has been referenced twice before a big scan begins and the scan brings in pages which have only been read once but more recently which will be evicted?

    Edit: I have also just read the following phrase

    The latest builds of SQL Server have moved to Time of Last Access (TLA) algorithms to determine which buffers to eject from cache. The older algorithms were based on a reference counting scheme and TLA has been found to be more efficient.

    That would seem to indicate that the behaviour has altered to make it more likely that a large scan will cause problems in this case? Anyone got any details on that?

    Additionally is there any way I can get insight into the process myself? I have looked at DBCC PAGE and assume that breferences and bUse1 are both of relevance but am unclear of how to interpret these values? 

    Sunday, June 19, 2011 11:29 AM

All replies

  • Hi Martin,

    Please refer to this former thread about SQL Server IO mechanism,Paper The LRU-K page replacement algorithm for database disk buffering  ,and  Chapter 2 SQL Server 2005 Architecture Memory from Page 49-56 of Inside Microsoft SQL Server 2005:The Storage Engine

    Hope this helps.

    Best Regards,

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, June 22, 2011 2:00 AM
  • I've unproposed this as an answer as nowhere does it answer the very specific question I asked in the body of the question (though probably my fault for making the question in the title far too general. Sorry and corrected!).

    So far I've read 2 out of the 3 resources and they do not cover the question asked. I haven't read the SIGMOD paper yet as I need to sort out a login to get it through my College.

    But also please see my edit to the question that indicates that the classic algorithm is not necessarily in use anyway.

    Thursday, June 23, 2011 10:51 AM