SQLSACT (11/9/2012)[quote]I'm trying to understand what happens to clean pages in the buffer pool (when a select statement brings pages into the buffer pool).

What happens to the pages once the select statement is done with them?

Nothing.

Neither checkpoint nor lazywriter 'removes' dirty pages from the buffer pool. They just write the changes back to disk so that the pages are considered clean.

Ok

Now I'm really confused

The way I understood it is:>> Insert, Update or Delete statement is received by SQL Server>> Required pages are copied from disk into buffer pool>> Changes are made to affected pages>> Checkpoint/Lazy Writer process writes the pages back to disk, replacing the original page on disk.

SQLSACT (11/9/2012)[quote]I'm trying to understand what happens to clean pages in the buffer pool (when a select statement brings pages into the buffer pool).

What happens to the pages once the select statement is done with them?

Nothing.

Neither checkpoint nor lazywriter 'removes' dirty pages from the buffer pool. They just write the changes back to disk so that the pages are considered clean.

Ok

Now I'm really confused

The way I understood it is:>> Insert, Update or Delete statement is received by SQL Server>> Required pages are copied from disk into buffer pool>> Changes are made to affected pages>> Checkpoint/Lazy Writer process writes the pages back to disk, replacing the original page on disk.

Am I missing something here?

Thanks

one last step, the now "clean" pages remain in the buffer pool as clean pages until SQL Server needs the memory for something else.

SQLSACT (11/9/2012)[quote]I'm trying to understand what happens to clean pages in the buffer pool (when a select statement brings pages into the buffer pool).

What happens to the pages once the select statement is done with them?

Nothing.

Neither checkpoint nor lazywriter 'removes' dirty pages from the buffer pool. They just write the changes back to disk so that the pages are considered clean.

Ok

Now I'm really confused

The way I understood it is:>> Insert, Update or Delete statement is received by SQL Server>> Required pages are copied from disk into buffer pool>> Changes are made to affected pages>> Checkpoint/Lazy Writer process writes the pages back to disk, replacing the original page on disk.

Am I missing something here?

Thanks

one last step, the now "clean" pages remain in the buffer pool as clean pages until SQL Server needs the memory for something else.

these are clean pages. SQL Server only drops pages from the buffer if there is not enough memory (or the server is restarted or you manually drop the cache). otherwise SQL Server holds the pages in memory for the next time they are accessed. that way the next query that is run the server wont have to read those pages from disk again. if the database is smaller than the ram on the system eventually the entire database may sit in memory and there is very little reading from disk. if the database is larger than the amount of ram on the system SQL Server has a very well designed algorithm for figuring out which pages have not been accessed in a while.