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

Symptoms

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
  • DBCC CHECKDB or DBCC CHECKTABLE operation

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.

name database_id elapsed_time_ms command operation scanned_buffers total_iterated_buffers
buffer_pool_scan_complete 7 1308 BACKUP DATABASE FlushCache 243 19932814

Note

The threshold is in the XEvent is smaller to allow you to capture information at a finer-granularity.

Workaround

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.

  1. Run CHECKPOINT on each database

    USE <DatabaseName>
    CHECKPOINT
    GO
    

    If the SQL Server hosts multiple databases, repeat the CHECKPOINT commands for all user databases.

  2. After all the databases on the server have been checkpointed, reduce the size of the buffer pool with following command:

    DBCC DROPCLEANBUFFERS
    

    Warning

    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.

  3. Perform the operation that results in a buffer pool scan, for example, full database backup.

More information

For more information about problems that can occur in large buffer pools, see SQL Server : large RAM and DB Checkpointing.