Pages in Buffer Pool fall in to two different types.......

August 8, 2017 Execution Plans 491 Views


Pages in Buffer Pool fall in to two different types.......

What are the types of Pages in Buffer Pool?

Pages in Buffer Pool fall into two categories:

  1. Dirty Pages
  2. Clean Pages

Dirty Pages:
These are the pages that have been modified as a result of an INSERT, UPDATE or DELETE. SQL Server will persist such pages back on disc based on an internal schedule by Lazy Writer.

Clean Pages:
Clean Pages are the opposite of Dirty Pages. To put it in other words, Clean Pages are pages that have not been modified; Or, if modified, they have been written back to disc. Therefore, the contens of Clean Pages match exactly with the contents of the relevant pages on disc.

One reason a page is clean is that it has not been modified at all. You might be wondering why such pages should be in Buffer Pool at all. The reason is quite simple. They might have been loaded in Buffer Pool as a result of a SELECT query. SQL Server needs to load data to Buffer Pool in order to access them. It cannot even read data without first loading them into Buffer Pool.

How do you rate this topic?

Further reading:
Some SQL functions are constants
Unit of data in Buffer Pool
SQL Server Pages and Extents