What is Indirect
Checkpoint Feature in SQL Server 2012?
The
Indirect Checkpoints feature in SQL Server 2012 provides facility to change the
Target Recovery Time (Seconds) parameter values from its default value of ZERO
to a number which is suitable for the database to recover quickly in the event
of an unexpected shutdown or Crash.
What is the use of
changing Target Recovery Time (Seconds) Parameter value?
Changing
the value of Target Recovery Time
(Seconds) parameter higher than ZERO changes the CHECKPOINT behavior of
that particular database from Automatic Checkpoint to Indirect Checkpoint.
When
the Recovery Interval (MIN) value is set to ZERO which is the default value
then SQL Server Instance will rely on Automatic Checkpoints and when Recovery
Interval (MIN) value is higher than ZERO SQL Server Database Engine relies on
Indirect Checkpoint for that particular database. Automatic Checkpoints occur
for the database approximately once a minute for all the active databases and
the recovery time for the database will be typical less than a minute.
Indirect Checkpoint helps
to reduce the overall database recovery time. It also provides the predictable
recovery time than in the automatic check points.
We may also experience
performance degradation, if we enable indirect checkpoint feature on an OLTP environment
which experience heavy workload. Because the background writer used by indirect
checkpoint might increases the total write load for a server instance.
This
option is an advanced option and should be changed only by an experienced
database administrator or certified SQL Server technician.
It
is recommended that we keep the recovery interval at 0, unless we experience
performance problems. If we decide to increase the recovery-interval setting, it
is recommended that increasing it gradually by small increments and evaluating
the effect of each incremental increase on recovery performance.
What is the use of Checkpoint in SQL Server?
For performance reasons,
the Database Engine performs modifications to database pages in memory—in the
buffer cache—and does not write these pages to disk after every change. And the
Database Engine periodically issues a checkpoint on each database. A checkpoint writes the current in-memory modified pages
(known as dirty pages) and transaction log
information from memory to disk and, also, records information about the
transaction log.
In the earlier versions of
SQL Server a DBA can change the recovery interval (min) value at the instance
level from its default value of ZERO using SP_CONFIGURE System Stored Procedure
or using SQL Server Management Studio. There was no possibility to change the
recovery interval for a particular database.
Configuration Steps
The following
example sets the target recovery time of the Test database to 30
seconds.
USE [master]
GO
ALTER DATABASE
Test SET TARGET_RECOVERY_TIME = 30 SECONDS
We can also configure through SQL Server Management Studio by
right click on the specific database, select properties, go to options and in
the Recovery panel, under the Target
Recovery Time (Seconds) field
No comments:
Post a Comment