Understand sqlserver dirty pages

Dirty pages are mentioned in many books about sqlserver, and dirty pages involve database recovery mode, read/write mode, concurrency, and so on,

What is a dirty page, when the client sends a T-SQL statement to the database, the database engine performs a change to the data,

If no data is found in the memory, the data is first read from the disk to the memory (this process is called page fault, page missing error ),

Then modify the data in the memory. Before the database does not perform the checkpoint operation or the inertia writer does not perform the write operation,

The data in the disk and memory is inconsistent, so the modified data pages in the memory are called dirty pages.

/*
View dirty pages
*/
-- View the dirty page. The dirty page information of the DBO. t_partition table is not found in the dirty page information of the database obtained in this step.
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
Go

-- update data first,
Update DBO. t_partition set number = 600
go
-- get the dirty page. In this case, DBO. the database where the t_partition table is located displays dirty page information
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
go
-- remove the clean page
DBCC dropcleanbuffers
go
-- view the dirty page and DBO. the dirty page information of the database where the t_partition table is located still exists
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
go
-- run the checkpoint, write dirty pages into memory
Checkpoint 1
go
-- View dirty pages, DBO. the dirty page information of the database where the t_partition table is located is written to the disk, and the dirty page information is cleared.
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
go

-- the preceding Code shows that
-- 1. The database first modifies data in the memory, then, you can write data to the disk through the checkpoint (the inert writer cannot perform an experiment) to complete the database persistence feature.
-- 2. When writing data to the database, write logs first (there is no experiment here. You can learn about this in log recovery mode), write the logs to the memory, and then write the data to the disk.