In a SQL Errorlog we saw a lot of events like this.
Buffer Pool scan took 10 seconds: database ID 6, command 'CHECKPOINT', operation 'FlushCache', scanned buffers 341183509, total iterated buffers 345933594, wait time 75 ms. See 'https://go.microsoft.com/fwlink/?linkid=2132602' for more information.
The refered article "Operations that trigger a buffer pool scan may run slowly on large-memory computers" states that
Prior to SQL Server 2022, there was no way to eliminate this problem.
But then another event came around where we looked at Indirect Checkpoint configuration. It turned out that the database originally was so old that the "Target Recovery Time" was 0 (zero).
When we changed this configuration to 60 then the Buffer Pool scan messages no longer showed up in the SQL Errorlog.
Another benefit was that spikes and average on Disk Write Latency were reduced significantly.
Kendra Little wrote some time ago a useful article (link) with good references and details when Target Recovery Interval changed default value.
Posts
