This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(134.4, 13%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EUD%3C/text%3E%3C/svg%3E)
Hi,
I had recently faced an issue. My AWS EC2 instance CPU suddenly started hitting 100% and it did not come down for sometime. Only option I had was to reboot the node so that all DBs failed over to mirror partner.
I could see this multiple abnormal buffer pool scan messages in SQL server log recorded during the issue period.
Buffer Pool scan took 25 seconds: database ID 22, command 'CHECKPOINT', operation 'FlushCache', scanned buffers 3126941, total iterated buffers 26200740, wait time 918 ms. See 'https://go.microsoft.com/fwlink/?linkid=2132602' for more information.
Could not get much details about this error. Its the same usual workload and nothing changed in the application. I did check my workload at that time in foglight PI and most of the queries were waiting for the CPU, so I dont think any workload is a culprit here and my queries were just the victim of this issue, just waiting for the CPU in runnable state.
Does this error severely impact the host and max out the CPU? If this is an issue how can I address this one?
Thanks
If you had actually tried the link in the error message, you would have gotten a good summary of this message.
In your case, I guess you got this message because you decided to fail over. This is a situation where the need to scan the buffer pool to find dirty buffers.
As the article says, on SQL 2019 and earlier you will have to live with this state of affairs. In SQL 2022, they have improved the situation by making the buffer pool scan a parallel operation.
No these buffer pool scan messages were there when CPU was hitting 100% and before I did the failover. I cant able to connect to session to execute sp_whoisactive and see what was active at that time and then I decided to do a failover.
I checked the link in that error message and so I was wondering if this buffer pool scan can bring down SQL Server to its knees, hence posted it here to know from the community.
But it was most likely due to a select query from the investigation I did with foglight PI, regressed queries report in Query store which was doing a clustered Index scan on a big table.
There was a flood of those queries which choked the system.
Thanks for your time.
What does "SELECT @@version" report?
If you are on SQL 2019, I can't see why a buffer-pool scan would bring the machine to 100% CPU, since it's single-threaded. I guess that if a buffer-pool scan is triggered on the wrong occasion on SQL 2022, it could happen, unless they have some safeguards in place.