There’s cool mechanism that the buffer pool has called disfavoring, that sometimes kicks in to prevent pages from a different database being flushed from the buffer pool when a large operation occurs. When a buffer is no longer required, the buffer can be marked as the least recently used of all the buffers in the buffer pool, meaning that it will used next when the buffer pool needs a new buffer to read a page from disk. This means that a small number of buffers will be used repeatedly instead of filling the buffer pool with pages for some operations.
The places that disfavor buffers when they’re done with them include:
- During redo of log records, if a page already has a log record’s effects on it and so doesn’t need to be changed
- During bulk load into a heap
- Large table scans that are more than 10% of the buffer pool size will disfavor pages instead of forcing pages from other databases to be flushed from memory
And backups don’t use the buffer pool at all so they’re not going to cause buffer pool flushing either.
You can watch disfavoring happening for table scans using the leaf_page_disfavored Extended Event.
DBCC CHECKDB can cause interesting buffer pool behavior as it doesn’t use this method of disfavoring. It does do disfavoring, but doesn’t reuse the pages it’s disfavored during the executing operation. For instance, let’s say you have SQL Server set to use a maximum of 5GB of memory, and have 5 databases that are 1.5GB each. If you cause three of the databases to be fully read into memory, the remaining buffer pool space is only a few hundred MB. If you then run DBCC CHECKDB on the fourth database using WITH TABLOCK, it will read all the pages from that database into memory, and push pages from one of the other databases out. Those pages from the fourth database are all marked as disfavored though, so if you run DBCC CHECKDB on the fifth database using WITH TABLOCK, it will push all the pages from the prior DBCC CHECKDB out and use that space.
Furthermore, if you allow DBCC CHECKDB to create a database snapshot, it will read all the pages in the context of the snapshot, and then drop the snapshot. This could cause some of the rest of the buffer pool to be thrown out, potentially even pages from the database being consistency-checked!
And Resource Governor doesn’t help here at all, as that just controls the query execution memory grant, not the amount of buffer pool space used when reading pages.