Menu
header photo

SQL-Talk

Talks all about SQL Server and Azure

Blog Search

Blog Archive

Comments

There are currently no blog comments.

SQL Server Checkpoints

October 2, 2017

Checkpoint

  • Checkpoint is SQL Server background process to write all dirty pages to disk.
  • It takes modification details from transaction log file and writes respective pages from buffer to data file.
  • The dirty page may consist of committed or uncommitted or both transactions.
  • SQL Server 2000 was limited to ONE checkpoint process but from 2005 onwards we have multi checkpoint concept.
  • Checkpoint truncates T.Log file if the recovery model of database is SIMPLE.
  • According to books online

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.

Why Checkpoints?

  • To reduce crash recovery time. When server was restarted unexpectedly if many dirty pages are in buffer then recovery process takes long time to make database consistent.
  •  To batch I/Os to disk to get better performance.

Checkpoint occurs in the following scenarios

                1. Periodically (Depends on recovery interval of SS)

                        * R.C on Server --> Take properties --> Database settings -->

                                    Recovery interval =1

            2. When the database backup is about to start

            3. If the server is started.

            4. If the T.Log is 70% full and it is in Log truncate mode.

            5. When a new data or T.Log file was added.

            6. Manually using CHECKPOINT command.

                7. When database is detached.

8. When we change recovery model from FULL or BULK_LOGGED to SIMPLE.

            9. When database snapshot is generated.

            10. When Bulk operation was done in bulk logged recovery model.

Checkpoint Types

  1. Automatic
  • These checkpoint runs automatically depends on recovery interval of SQL Server.
  • To check the recovery interval property value (default 0), we can use

sp_configure 'show adv',1

reconfigure

go

sp_configure 'recovery interval (min)'

  1. Manual
  • Manually we can force checkpoint using CHECKPOINT command when required.
  • It runs for the current database only.
  1. Indirect
  • These are introduced in SQL Server 2012
  • Issued in the background to meet a user-specified target recovery time for a given database
  • Takes more priority than automatic checkpoints

use master

go

alter database TestDB set target_recovery_time = 5 seconds with no_wait

  1. Internal
  • The checkpoint which internally runs before starting other activities is called internal checkpoint. The following are different scenarios where internal checkpoint runs
    • Before starting database backup
    • When server was shut down
    • When we change recovery model from FULL or Bulk Logged to SIMPLE.
    • In SIMPLE recovery model when log file size reaches above 70%
    • When new data or log file was added to database
    • When database snapshot is generated
    • When minimally logged operation is done in bulk logged recovery model.

Checkpoint Process Steps

  • Log that checkpoint started in transaction log file.
  • Log some checkpoint information if necessary.
  • Writes all dirty pages to disk
  • Writes LSN of checkpoint in boot page of database
  • If database is in SIMPLE recovery model try to clear the log
  • Log that a checkpoint ended in transaction log file.

Note:   To verify when the checkpoint has raised we can use

                        DBCC LOG('DbName',3)

                        DBCC LOG('Sales',3)

To verify latest checkpoint details

SELECT top 1 f1.[Checkpoint Begin], f2.[Checkpoint End]

FROM    fn_dblog(NULL, NULL) f1

Inner Join fn_dblog(NULL, NULL) f2

On f1.[Current LSN] = f2.[Previous LSN]

WHERE   f2.Operation IN (N'LOP_BEGIN_CKPT', N'LOP_END_CKPT')

order by 1 desc

Step by Step Demo

--step1: Creating sample table in TestDB database

use TestDB

go

create table MyTable(a int,b varchar(40))

go

--step2: Inserting sample data

insert MyTable values(1,'xyz')

--step3: Checking for clean and dirty pages of TestDB database

use TestDB

go

SELECT Page_Status = CASE WHEN is_modified = 1 THEN 'Dirty'

                        ELSE 'Clean' END,

      DBName = CASE WHEN database_id = 32767 THEN 'RESOURCEDB'

                        ELSE DB_NAME(database_id) END,

      Pages = COUNT(1)

FROM sys.dm_os_buffer_descriptors

WHERE database_id = DB_ID()

GROUP BY database_id, is_modified

ORDER BY 2

Output

Page_Status             DBName                        Pages

-----------            ----------------------        ---------------

Clean                TestDB                     653

Dirty                TestDB                     14

--step4: Lets we force checkpoint

use TestDB

go

checkpoint

--step5: Again check for the pages using the above script and there are no dirty pages now.

Page_Status             DBName                        Pages

-----------            ----------------------        ---------------

Clean                   TestDB                         667

--step6: Check checkpoint details in transaction log file

select * from fn_dblog(null,null) where operation like '%ckpt%'

 

Go Back

Comment