Edit

Operations that trigger a buffer pool scan may run slowly on large-memory computers

  • Article

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

Prior to SQL Server 2022, there was no way to eliminate this problem. It is not recommended to perform any action to clear the buffer pool as dropping clean buffers (DBCC DROPCLEANBUFFERS) from the buffer pool may result in a significant performance degradation. Removing database pages from memory 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.

In SQL Server 2022, this problem is mitigated because buffer pool scans are parallelized by utilizing multiple cores. There will be one task per 8 million buffers (64 GB) where a serial scan will still be used if there are less than 8 million buffers. For more information, watch Buffer Pool Parallel Scan.

More information

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