SQL Server – Fix Buffer Pool Scan Took 10 Seconds Error

buffer-poolhigh-availabilitysql server

Using SQL server 2017 (14.0.3381.3) with Always On High Availability environment.

Getting error:"Buffer Pool scan took 10 seconds: database ID 8, command 'CHECKPOINT', operation 'FlushCache', scanned buffers 144321190, total iterated buffers 150079215, wait time 42 ms. See 'https://go.microsoft.com/fwlink/?linkid=2132602' for more information"

This is a new feature that is included in the 2017 CU23, under the VSTS bug number 13741858.

I see the list of all the operations that can trigger a scan but none of them are happening on my machine, is there a way to tell what causes the scan? – OR – Is there a Trace Flag that can be used to turn off the log of this error?

Best Answer

I see the list of all the operations that can trigger a scan but none of them are happening on my machine, is there a way to tell what causes the scan?

It's a CHECKPOINT on database ID 8. It says so right in the message.

Do you have INDIRECT CHECKPOINT enabled? What does

select database_id , name, target_recovery_time_in_seconds
from sys.databases
where database_id = 8

return? The default of 60 enables indirect checkpoints.

METHOD #1

Flush all Status Values, Sleep 10 min, Run Query

FLUSH STATUS;
SELECT SLEEP(600) INTO @x;
SELECT round ((P2.variable_value / P1.variable_value),4), 
P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1. variable_name = 'innodb_buffer_pool_read_requests'
AND P2. variable_name = 'innodb_buffer_pool_reads';

METHOD #2

Capture innodb_buffer_pool_read_requests, innodb_buffer_pool_reads, Sleep 10 minutes, Run Query with Differences in innodb_buffer_pool_read_requests and innodb_buffer_pool_reads

SELECT
    P1.variable_value,P2.variable_value
INTO
    @rqs,@rds
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1.variable_name = 'innodb_buffer_pool_read_requests'
AND P2.variable_name = 'innodb_buffer_pool_reads'; 
SELECT SLEEP(600) INTO @x;
SELECT round (((P2.variable_value - @rds) / (P1.variable_value - @rqs)),4), 
P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1.variable_name = 'innodb_buffer_pool_read_requests'
AND P2.variable_name = 'innodb_buffer_pool_reads';

Best Answer

Related Solutions

MySQL InnoDB – Understanding Buffer Pool Active Pages

MySQL – Understanding InnoDB Buffer Pool Hit Rate

METHOD #1

METHOD #2

Give it a Try !!!

Related Question