Saturday, October 12, 2013

Define checkpoint and types of checkpoint in sql server

Find the solution of following queries-

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

Why check points
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.

Categorization of Checkpoints
Checkpoints are categorized into four categories-
1. Automatic Checkpoint
2. Indirect Checkpoints
3. Internal Checkpoints
4. Manual Checkpoint

Automatic Checkpoint
  • It is issued automatically from the background based on the [recovery interval] defined.
  • [Recovery Interval] parameter is specified in server level.
  • Default [recovery interval] value for automatic checkpoint is 0(zero) in which target recovery interval is 1 minute.
  • Query to define the [recovery interval] 
                    EXEC SP_CONFIGURE '[recovery interval]','seconds'

Indirect Checkpoints

These are new added to SQL Server 2012, provide a configurable database-level alternative to automatic checkpoints. In the event of a system crash, indirect checkpoints provide potentially faster, more predictable recovery time than automatic checkpoints. Indirect checkpoints offer the following advantages:
  • Indirect checkpoints can reduce overall database recovery time.
  • Indirect checkpoints enable you to reliably control database recovery time by factoring in the cost of random I/O during REDO. 
  • This enables a server instance to stay within an upper-bound on recovery times for a given database (except when a long-running transaction causes excessive UNDO times).
  • Indirect checkpoints reduce checkpoint-related I/O spiking by continually writing dirty pages to disk in the background.
  • Query to produce Indirect checkpoint

Internal Checkpoints

Internal checkpoint are generated only when following events occurs:
  •     Database files have been added or removed by using ALTER DATABASE.
  •     A database backup is taken.
  •     A database snapshot is created, whether explicitly or internally for DBCC CHECK.
    An instance of SQL Server is stopped by stopping the SQL Server (MSSQLSERVER) service . Either action causes a checkpoint in each database in the instance of SQL Server.

    Bringing a SQL Server failover cluster instance (FCI) offline.

Manual Checkpoint
  • It is issued when user executes T-SQL Command CHECKPOINT.
  • [Checkpoint duration] parameter is specified in database level.
  • [Checkpoint duration] parameter specifies requested amount of time in seconds for the checkpoint to complete.
  • Query to issue Manual checkpoint
CHECKPOINT [ checkpoint_duration ]
Here checkpoint_duration parameter specifies a requested amount of time, in seconds, for the checkpoint to complete


Powered by Code Imagine