Checkpoint Process in SQL Server:
A checkpoint creates
a known good point from which the SQL Server Database Engine can start applying
changes contained in the log during recovery after an unexpected shutdown or
crash.
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. Rather,
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.
The Database Engine supports several types of checkpoints:
automatic, indirect, manual, and internal. The following table summarizes the
types of checkpoints:
Name
|
Description
|
Automatic
|
This is the most common checkpoint which
runs as a process in the background to make sure SQL Server Database can be
recovered in the time limit defined by the Recovery Interval – Server
Configuration Option.
Issued automatically in the background to
meet the upper time limit suggested by the recovery interval server
configuration option. Automatic checkpoints run to completion. Automatic
checkpoints are throttled based on the number of outstanding writes and
whether the Database Engine detects an increase in write latency above 20
milliseconds.
Query to define the
[recovery interval]
EXEC SP_CONFIGURE '[recovery interval]','seconds'
|
Indirect
|
This is new in SQL Server 2012. This
also runs in the background but to meet a user-specified target recovery time
for the specific Database where the option has been configured. Once the Target_Recovery_Time
for a given database has been selected this will override the
Recovery Interval specified for the server and avoid Automatic Checkpoint on
such DB.
Query to produce
indirect checkpoint
ALTER DATABASE … SET
TARGET_RECOVERY_TIME = target_recovery_time {SECONDS | MINUTES}
Issued in the background to meet a
user-specified target recovery time for a given database. The default is 0,
which indicates that the database will use automatic checkpoints, whose
frequency depends on the recovery interval setting of the server instance.
|
Manual
|
This one runs just like any other T-SQL
statement, once you issue checkpoint command it will run to its completion.
Manual Checkpoint runs for your current Database Only. You can also specify
the Checkpoint_Duration which is optional, this duration specifies the time
in which you want your checkpoint to complete.
Query to issue Manual checkpoint
CHECKPOINT [checkpoint_duration]
|
Internal
|
Issued by various server operations such as backup and
database-snapshot creation to guarantee that disk images match the current
state of the log.
|
Checkpoint is an internal
process that writes all the current in-memory modified pages (known as dirty
pages) dirty pages from Buffer Cache to Physical disk, apart from this it
also writes the log records from log buffer to physical file. Writing of Dirty pages from buffer cache to data file is also
known as Hardening of dirty pages. It
is a dedicated process and runs automatically by SQL Server at specific
intervals. SQL Server runs checkpoint process for each Database individually.
Checkpoint helps to reduce the recovery time for SQL Server in the
event of unexpected shutdown or system crash\Failure.
How do database
checkpoint occur?
1. Shutdown initiates a
Checkpoint operation on all databases except when Shutdown is not clean
(Shutdown with nowait)
2. If the recovery model gets changed from Full\Bulk-logged to Simple.
3. While taking Backup of the Database. Before a backup, the database engine performs a checkpoint, in order that all the changes to database pages (dirty pages) are contained in the backup.
4. If your DB is in Simple Recovery model, checkpoint process executes automatically either when the log becomes 70% full, or based on Server option-Recovery Interval.
5. Alter Database command to add or remove a data\log file also initiates a checkpoint.
6. Checkpoint also takes place when the recovery model of the DB is Bulk-Logged and a minimally logged operation is performed.
7. DB Snapshot creation.
8. When a CHECKPOINT statement is explicitly executed. A checkpoint occurs in the current database for the connection.
9. When the “SHUTDOWN WITH NOWAIT” is used, it does not execute checkpoint on the database.
10. An activity requiring a database shutdown is performed. For example,AUTO _CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.
11. The database is in truncation mode, when is in simple recovery model and after a backup statement has been executed.
2. If the recovery model gets changed from Full\Bulk-logged to Simple.
3. While taking Backup of the Database. Before a backup, the database engine performs a checkpoint, in order that all the changes to database pages (dirty pages) are contained in the backup.
4. If your DB is in Simple Recovery model, checkpoint process executes automatically either when the log becomes 70% full, or based on Server option-Recovery Interval.
5. Alter Database command to add or remove a data\log file also initiates a checkpoint.
6. Checkpoint also takes place when the recovery model of the DB is Bulk-Logged and a minimally logged operation is performed.
7. DB Snapshot creation.
8. When a CHECKPOINT statement is explicitly executed. A checkpoint occurs in the current database for the connection.
9. When the “SHUTDOWN WITH NOWAIT” is used, it does not execute checkpoint on the database.
10. An activity requiring a database shutdown is performed. For example,
11. The database is in truncation mode, when is in simple recovery model and after a backup statement has been executed.
SQL server
Recovery Interval
A note on the recovery interval –
the time between checkpoints is determined by the recovery interval and the
number of records in the transaction log. The recovery interval is set for an
entire instance of SQL Server. The value represents the number of minutes that
you choose to allow SQL server for automatic recovery. SQL server uses an
algorithm to determine when to perform the next check point. The algorithm will
create checkpoint to ensure that in the event of system failure the recovery
will not take longer than the time specified in your recovery interval.
Does checkpoint
only writes pages from committed transactions. NO
This is linked to a
misunderstanding of how the overall logging and recovery system works. A
checkpoint always writes out all pages that have changed (known as being marked
dirty) since the last checkpoint, or since the page was read in from disk. It
doesn't matter whether the transaction that changed a page has committed or not
– the page is written to disk regardless. The only exception is for tempdb,
where data pages are not written to disk as part of a checkpoint.
FAQ: - What is difference between
checkpoint and lazy writer?
Ans:
Checkpoint reads all committed transaction
details from previous checkpoint and it submits the details like LSN no’s and effected page ids to lazy writer. Lazy writer remove
dirty pages from buffer cache and make free space available for other process.
Note
The -k SQL
Server advanced setup option enables a database administrator to throttle
checkpoint I/O behavior based on the throughput of the I/O subsystem for some
types of checkpoints. The -k setup option applies to automatic checkpoints and
any otherwise un-throttled manual and internal checkpoints.
Important
Long-running
uncommitted transactions increase recovery time for all types of checkpoints.In SQL Server 2012 there are four types of Checkpoints:
Automatic: This is the most common checkpoint which runs as a process in the background to make sure SQL Server Database can be recovered in the time limit defined by the Recovery Interval – Server Configuration Option.
Indirect: This is new in SQL Server 2012. This also runs in the background but to meet a user-specified target recovery time for the specific Database where the option has been configured. Once the Target_Recovery_Time for a given database has been selected this will override the Recovery Interval specified for the server and avoid Automatic Checkpoint on such DB.
Manual: This one runs just like any other T-SQL statement, once you issue checkpoint command it will run to its completion. Manual Checkpoint runs for your current Database Only. You can also specify the Checkpoint_Duration which is optional, this duration specifies the time in which you want your checkpoint to complete.
Internal: As a user you can’t control Internal Checkpoint.
Indirect: This is new in SQL Server 2012. This also runs in the background but to meet a user-specified target recovery time for the specific Database where the option has been configured. Once the Target_Recovery_Time for a given database has been selected this will override the Recovery Interval specified for the server and avoid Automatic Checkpoint on such DB.
Manual: This one runs just like any other T-SQL statement, once you issue checkpoint command it will run to its completion. Manual Checkpoint runs for your current Database Only. You can also specify the Checkpoint_Duration which is optional, this duration specifies the time in which you want your checkpoint to complete.
Internal: As a user you can’t control Internal Checkpoint.
Checkpoint helps to reduce the recovery time for SQL Server in the event of unexpected shutdown or system crash\Failure.
-default checkpoint interval – which is set to 1 minute. (2012)
-you could change this value – using sp_configure to toggle the ‘recovery interval’ setting.
-You can monitor checkpoint I/O activity using Performance Monitor by looking at the “Checkpoint pages/sec” counter in the SQL Server:Buffer Manager object
-Turning on trace flags 3605 and 3502 will let you see which database is doing a checkpoint. Use the command:
DBCC TRACEON (3502, 3605, –1);
-If you want more details about what’s happening during the checkpoint, you can use trace flag 3504 as well. It will expand the output
-If a checkpoint fails or is interrupted and a recover is required, the database engine cannot recover from the “failure” checkpoint, is necessary to recover from the last successful checkpoint.
-The database engine performs any modification to database pages in memory (for performance reasons) it cause dirty pages, those dirty pages are not written to disk on each modification performed, those are written to disk just when a checkpoint occur.
To set the recovery interval
- In Object Explorer, right-click a server and select Properties.
- Click the Database settings node.
- Under Recovery, in the Recovery interval (minutes) box, type or select a value from 0 through 32767 to set the maximum amount of time, in minutes, that SQL Server should spend recovering each database at startup.
"The default value is 0, indicating automatic configuration"
Example (This code will advise SQL Server to issue checkpoint in every 5 minutes interval):
1: ALTER DATABASE [CheckpointDB] SET TARGET_RECOVERY_TIME = 10 SECONDS
2: GO
or
it can be set using the Database Properties window in SSMS.
As execution of checkpoint on a database has a direct impact of transaction log truncation capability.
However you can request SQL Server to issue a checkpoint on a database after a certain interval using below syntax: use [database name]
CHECKPOINT [interval in minutes]
Example (This code will advise SQL Server to issue checkpoint in every 5 minutes interval):
use testCHECKPOINT 5
From SQL Server level, you can control the behavior of checkpoint executing by modifying “Recovery interval property” using below syntax:
EXEC sys.sp_configure N'recovery interval (min)',[interval in minutes]
GO
RECONFIGURE WITH OVERRIDE
GO
It is recommended to leave this value to its default (0) which allows SQL Server to take best course of action for each database to issue checkpoint as discussed above.
No comments:
Post a Comment