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 command DBCC 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.