Change Automatic to Indirect checkpoint

Question

On SQL 2014 SP3 ( FCI instance) I regularly get the FlushCache message in the log as below. the server is used for DSS (not application backend)

"FlushCache: cleaned up 1350000 bufs with 390000 writes in 70000 ms (avoided 20000 new dirty bufs) for db 8:0

average throughput: 150.67 MB/sec, I/O saturation: 300, context switches 5600

last target outstanding: 9600, avgWriteLatency 20"

There is 100 GB allocated to Buffer Manager
As I have seen the recommendation is to change automatic to indirect checkpoint for large RAM (2016 has indirect as default)
Is there any issue in changing to indirect checkpoint on this version of SQL? I know it was an issue for FCI up to SP1 in the past but was fixed for SQL 2014 in KB3166902 as CU for SP1, so way before SP3.
Is there anything else needed other then update per each database that shows up in the log (the instance configuration ‘recovery interval (min)' is O), my example uses 60 seconds.

USE [master]
GO
ALTER DATABASE [DatabaseName] SET TARGET_RECOVERY_TIME = 60 SECONDS WITH NO_WAIT
GO

Unfortunately I do not have pre-prod or testing cluster server for this environment to test pre/post Perfmons.

Thanks,

Answer 1

2016 has indirect as default

Please note that the default is applicable only to Model database and there by to Tempdb, as tempdb is recreated after restart from copy of model database. Indirect checkpoint is not applicable by default to mater, msdb and user databases.

You have MS blog on this Indirect Checkpoint and tempdb – the good, the bad and the non-yielding scheduler.

Looking at your flush cache messages it seems like you may benefit from Indirect checkpoint but before that have you made sure that your storage is fast and there is no I/O pending message.

About the drawbacks of enabling indirect checkpoint the blog I have shared has some details. Your backup can take longer time. Apart from this you nothing much as only thing changes and getting affected is checkpoint running. Like already pointed above make sure you have good storage when you enable indirect checkpoint.

PS: Going forward for large workloads with heavy transaction you would see MS recommending this. I find this feature quite good when quick recovery is at back of your head.

Answer 2

Hi @rodrag ,

>Is there anything else needed other then update per each database that shows up in the log (the instance configuration ‘recovery interval (min)' is 0

The recovery interval affects only databases that use the 0 target recovery time, please look at:
https://learn.microsoft.com/en-us/sql/relational-databases/logs/database-checkpoints-sql-server?view=sql-server-ver15#InteractionBwnSettings

Also please check the following blogs for more imformation.
https://techcommunity.microsoft.com/t5/sql-server-support/sql-server-large-ram-and-db-checkpointing/ba-p/318973
https://littlekendra.com/2016/06/14/target-recovery-interval-and-indirect-checkpoint-new-default-of-60-seconds-in-sql-server-2016/