Have you ever wondered why DBCC CHECKDB, for instance, doesn’t flush the buffer pool when it runs on a database that is larger than the memory capacity of the server?

There’s cool mechanism that it uses called disfavoring. 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 from the database being consistency checked.

This mechanism isn’t anything to do with DBCC, but DBCC makes good use of it. DBCC CHECK* commands, DBCC SHOWCONTIG, DBCC UPDATEUSAGE, and the sys.dm_db_index_physical_stats DMV (which is really part of DBCC) all use this.

Let me show you…

On our fast test machine at home (rack-mounted Dell R720) I’ve got a 500GB database called AdventureWorks_Big residing on four Fusion-io SSDs. First off I’ll show you the table I’m going to use:

SELECT  [page_count]
FROM sys.dm_db_index_physical_stats (
DB_ID (),
	OBJECT_ID (N'Production.TransactionHistoryEnlarged'),
	1,
	NULL,
	N'LIMITED');
GO

page_count
-----------
18114867

It’s more than 138GB, and my buffer pool is 56,000MB. Now I’ll make sure the buffer pool is empty, and then run DBCC CHECKTABLE on that table:

DBCC DROPCLEANBUFFERS;
GO

DBCC CHECKTABLE (N'Production.TransactionHistoryEnlarged') WITH NO_INFOMSGS;
GO

Five minutes later it completes. But how much of the table did it read into the buffer pool?

SELECT *,
	[DirtyPageCount] * 8 / 1024 AS [DirtyPageMB],
	[CleanPageCount] * 8 / 1024 AS [CleanPageMB]
FROM
	(SELECT
		(CASE WHEN ([database_id] = 32767)
			THEN N'Resource Database'
			ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
		SUM (CASE WHEN ([is_modified] = 1)
			THEN 1 ELSE 0 END) AS [DirtyPageCount],
		SUM (CASE WHEN ([is_modified] = 1)
			THEN 0 ELSE 1 END) AS [CleanPageCount]
	FROM sys.dm_os_buffer_descriptors
	GROUP BY [database_id]) AS [buffers]
ORDER BY [DatabaseName];
GO

DatabaseName        DirtyPageCount CleanPageCount DirtyPageMB CleanPageMB
------------------- -------------- -------------- ----------- -----------
AdventureWorks_Big  0              188            0           1
master              3              77             0           0
model               0              32             0           0
msdb                1              39             0           0
Resource Database   0              24             0           0
tempdb              260            179            2           1

Cool eh?

There are some other places that disfavor buffers when they’re done with them, including:

• 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.

Bottom line – some of your common maintenance activities don’t damage the buffer pool, which is a good thing.