SQL Server Architecture - SQL Server 2012 Internals and Troubleshooting - page 19

A dirty page is simply a page that has changed in memory since it was loaded from disk and is now
different from the on-disk page. You can use the following query, which is based on the
sys . dm _ os _ buffer _ descriptors DMV, to see how many dirty pages exist in each database:
SELECT db_name(database_id) AS 'Database',count(page_id) AS 'Dirty Pages'
FROM sys.dm_os_buffer_descriptors
WHERE is_modified =1
GROUP BY db_name(database_id)
ORDER BY count(page_id) DESC
Running this on my test server produced the following results showing that at the time the query
was run, there were just under 20MB (2,524*8\1,024) of dirty pages in the People database:
Database Dirty Pages
People 2524
Tempdb 61
Master 1
These dirty pages will be written back to the database i le periodically whenever the free buffer list
is low or a checkpoint occurs. SQL Server always tries to maintain a number of free pages in cache
in order to allocate pages quickly, and these free pages are tracked in the free buffer list.
Whenever a worker thread issues a read request, it gets a list of 64 pages in cache and checks whether
the free buffer list is below a certain threshold. If it is, it will try to age-out some pages in its list,
which causes any dirty pages to be written to disk. Another thread called the lazy writer also works
based on a low free buffer list.
Lazy Writer
The lazy writer is a thread that periodically checks the size of the free buffer list. When it's low, it
scans the whole data cache to age-out any pages that haven't been used for a while. If it i nds any
dirty pages that haven't been used for a while, they are l ushed to disk before being marked as free
in memory.
The lazy writer also monitors the free physical memory on the server and will release memory
from the free buffer list back to Windows in very low memory conditions. When SQL Server is
busy, it will also grow the size of the free buffer list to meet demand (and therefore the buffer pool)
when there is free physical memory and the coni gured Max Server Memory threshold hasn't been
reached. For more on Max Server Memory, see Chapter 3.
Checkpoint Process
A checkpoint is a point in time created by the checkpoint process at which SQL Server can be sure
that any committed transactions have had all their changes written to disk. This checkpoint then
becomes the marker from which database recovery can start.
The checkpoint process ensures that any dirty pages associated with a committed transaction will
be l ushed to disk. It can also l ush uncommitted dirty pages to disk to make efi cient use of writes
but unlike the lazy writer, a checkpoint does not remove the page from cache; it ensures the dirty
page is written to disk and then marks the cached paged as clean in the page header.