Remove all clean buffers from the buffer pool using DBCC DROPCLEANBUFFERS

To test query performance and for performance tuning of the queries, you can drop clean buffers from the buffer pool without shutting down and restarting the server using DBCC DROPCLEANBUFFERS command (don't use DBCC DROPCLEANBUFFERS on production systems).

To list all the data pages in SQL Server buffer pool you can use the dynamic management view sys.dm_os_buffer_descriptors:

select * from sys.dm_os_buffer_descriptors

To see the number of pages cached in SQL Server buffer pool and the amount of RAM used in MB:

select count(*) AS Page_Count
,count(*) * 8 / 1024 as Cached_Size_MB
from sys.dm_os_buffer_descriptors

To see the number of pages cached in SQL Server buffer pool and the amount of RAM used in MB for each database:

SELECT count(*)AS Page_Count
,count(*) * 8 / 1024 as Cached_Size_MB
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Page_Count DESC;

To see the count of pages cached in SQL Server buffer pool for each object in the current database:

SELECT count(*) AS Page_Count
,object_name ,index_id
FROM sys.dm_os_buffer_descriptors AS bfd
INNER JOIN
(
SELECT object_name(object_id) AS object_name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS alu
INNER JOIN sys.partitions AS pt
ON alu.container_id = pt.hobt_id
AND (alu.type = 1 OR alu.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bfd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY object_name, index_id
ORDER BY Page_Count DESC;

To drop clean buffers from the buffer pool, first use CHECKPOINT to force all dirty pages for the current database to be written to disk and then use DBCC DROPCLEANBUFFERS command to remove all clean buffers from the buffer pool.

USE Database_name
CHECKPOINT

DBCC DROPCLEANBUFFERS

It is not recommended to issue the DBCC DROPCLEANBUFFERS command on production systems for testing purposes, due to the impact to the overall SQL Server performance. Use these commands in testing environments, and it is best to conduct one test at a time, so that the CHECKPOINT and DBCC DROPCLEANBUFFERS commands don't affect the other tests results.

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">