Operations that trigger a buffer pool scan may run slowly on large-memory computers
This article describes how scanning the SQL Server buffer pool might take a long time to finish on large-memory computers.
Applies to: SQL Server
Original KB number: 4566579
Certain operations in Microsoft SQL Server trigger a scan of the buffer pool (the cache that stores database pages in memory). On systems that have a large amount of RAM (1 TB of memory or greater), scanning the buffer pool may take a long time. This slows down the operation that triggered the scan.
Operations that cause a buffer pool scan
Here are some operations that may trigger a buffer pool scan to occur:
- Database startup
- Database shutdown or restart
- AG failover
- Database removal (drop)
- File removal from a database
- Full or differential database backup
- Database restoration
- Transaction log restoration
- Online restoration
Error log shows that a scan took a long time
Starting with SQL Server 2016 SP3, SQL Server 2017 CU23 and SQL Server 2019 CU9, an error message was added to the SQL Server Error log to indicate that a buffer pool scan took a long time (10 seconds or longer):
Buffer Pool scan took 14 seconds: database ID 7, command 'BACKUP DATABASE', operation 'FlushCache', scanned buffers 115, total iterated buffers 204640239, wait time 0 ms. See 'https://go.microsoft.com/fwlink/?linkid=2132602' for more information.
Extended Event to diagnose a long scan
Also, starting with the same builds SQL Server 2016 SP3, SQL Server 2017 CU23 and SQL Server 2019 CU9 the buffer_pool_scan_complete Extended event was introduced to help you identify long buffer pool scans.
If a scan takes more than 1 second, the XEvent will be recorded as follows when the event is enabled.
The threshold is in the XEvent is smaller to allow you to capture information at a finer-granularity.
There's currently no way to eliminate this problem. If an operation must finish quickly, clear the buffer pool by using the following commands before you execute it.
CHECKPOINTon each database
USE <DatabaseName> CHECKPOINT GO
If the SQL Server hosts multiple databases, repeat the
CHECKPOINTcommands for all user databases.
After all the databases on the server have been checkpointed, reduce the size of the buffer pool with following command:
Dropping clean buffers from the buffer pool may result in a significant, but temporary, performance degradation. This command removes all unmodified database pages from memory which will cause subsequent query executions to re-read the data from the database files on disk. This process of accessing data via disk I/O causes queries to be slow. However, once the pages are read into cache again, SQL Server will continue to read them from there.
Perform the operation that results in a buffer pool scan, for example, full database backup.
For more information about problems that can occur in large buffer pools, see SQL Server : large RAM and DB Checkpointing.