Please find the 18 differences between Checkpoint and Lazy Writer in SQL Server.
SNo
|
CHECKPOINT
|
LAZY
WRITER
|
1
|
Checkpoint is used by sql engine to keep
database recovery time in check
|
Lazy writer is used by SQL engine only to make
sure there is enough memory left in sql buffer pool to accommodate new pages
|
2
|
Check point always mark entry in T-log before
it executes either sql engine or manually
|
Lazy writer doesn’t mark any entry in T-log
|
3
|
Checkpoint only check if page is dirty or not
|
Lazy writer clears any page from memory when
it satisfies all of 3 conditions.
1. Memory is required by any object and available memory is full 2. Cost factor of page is zero 3. Page is not currently reference by any connection |
4
|
Checkpoint is affected by two parameters
1. Checkpoint duration: is how long the checkpoint can run for. 2. Recovery interval: affects how often it runs. |
Lazy writer is affected by
1. Memory pressure 2. Reference counter of page in memory |
5
|
Flush
dirty pages to Disk
|
Flush
dirty pages to disk.
|
6
|
Flush
only Data pages to disk
|
Check
for available memory and removed Buffer pool (execution plan/compile plan/
Data pages /Memory objects)
|
7
|
Default,
Occurs approximately every 1 minute, run as per defined frequency
|
Occurs
depending upon memory pressure and resource availability
|
8
|
Can
be managed with sp_confige -recovery interval option
|
It is
lazy; SQL Server manages by its own.
|
9
|
Does
not check the memory pressure
|
Monitor
the memory pressure and try maintaining the available free memory.
|
10
|
Crash
recovery process will be fast to read log as data file is updated.
|
No
role in recovery
|
11
|
Occurs
for any DDL statement
|
Occurs
per requirement
|
12
|
Occurs
before Backup/Detach command
|
Occurs
per requirement
|
13
|
Depends
upon the configuration setting, we can control.
|
Works
on Least recent used pages and removed unused plans first, no user
control.
|
14
|
for
simple recovery it flush the tlog file after 70% full.
|
No
effect on recovery model.
|
15
|
can
manually /Forcefully run command “Checkpoint”
|
No
command for Lazy Writer
|
16
|
Very
Less performance impact
|
No
performance impact
|
17
|
Members of the SYSADMIN, DB_OWNER and
DB_BACKUPOPERATOR can execute checkpoint manually
|
Not Applied
|
18
|
To get checkpoint entry in error log DBCC
TRACEON(3502, -1)
|
Not Applied
|
19
|
Checkpoint is affected by Database recovery
model
|
Lazy writer doesn’t get impacted with recovery
model of database
|
20
|
Auto frequency can be controlled using
recovery interval in sp_configure
|
Works only @ memory pressure , It uses clock
algorithm for cleaning buffer cache
|
21
|
It keeps no. of dirty pages in memory to
minimum
|
It helps to reduce paging
|
