Operations that scan SQL Server buffer pool are slow on large memory machines

Applies to: Microsoft SQL Server

Summary


Certain operations in SQL Server trigger a scan of the buffer pool (the cache that stores database pages in memory). Here are some operations that may trigger a buffer pool scan:

  • Database startup
  • Database shutdown/restart
  • AG failover
  • Dropping a database
  • Removing a file from a database
  • Full/Differential Backup of a database
  • Restoring a database
  • Transaction log restore
  • Online restore
  • DBCC CheckDB/CheckTable

On systems with a large amount of memory (1TB or higher), scanning the buffer pool takes a long time, which slows down the operation that triggered the scan.

There's currently no fix for this issue. If it is critical that the operation in question complete quickly, consider clearing the buffer pool using these commands:

USE <DatabaseName>; CHECKPOINT; GO-- If the server has more than one database, repeat the commands above for all user databases on the server-- Once all the databases on the server have been checkpointed, run the following commandDBCC DROPCLEANBUFFERS;

Warning: Dropping clean buffers from the buffer pool removes all non-modified database pages from memory. This requires subsequent queries to read the data from the database files on disk and could cause severe performance degradation.

More information


For more information on issues that may arise from large buffer pools, see the following blog: 

SQL Server : large RAM and DB Checkpointing