DBCC dropcleanbuffers dead?

Original source: http://www.sqlskills.com/blogs/paul/when-dbcc-dropcleanbuffers-doesnt-work/

DBCC DROPCLEANBUFFERS is used to clear the memory of the data cache, generally we do performance tuning in order to see the execution time of a statement and physical IO consumption, will be executed before the statement execution of DBCC DROP cleanbuffers.

However, this is not always a very good trick.

One might encounter this phenomenon, even if DBCC DROPCLEANBUFFERS is executed, but the physical reads are still not visible in the output information of SSMs, which is the data read from memory (logical read).

You will be surprised, "not already in the memory of the buffer emptied, how did not read the physical?" Does this phenomenon mean that DBCC DROPCLEANBUFFERS fails? ”

No. The DROP cleanbuffers can be used to clear the in-memory data cache, but only the Clean state cache page is cleared, relative, if the pages are dirty pages, that is, the memory has been modified and not synchronized to the disk data page, then it cannot be purged from the cache, This is for performance reasons.

So when you encounter drop Cleanbuffers "fail", try to manually synchronize the memory and the data page on the disk, executing:

Checkpoint

You can also see the number and size of clean pages and dirty pages in your current memory using the following statement:

SELECT *,[Dirtypagecount] * 8 / 1024x768  as [Dirtypagemb],[Cleanpagecount] * 8 / 1024x768  as [Cleanpagemb] from(SELECT( Case  when([database_id] = 32767) ThenN'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] fromsys.dm_os_buffer_descriptorsGROUP  by [database_id]) as [buffers]ORDER  by [DatabaseName]GO

DBCC dropcleanbuffers dead?