Pages in Buffer Pool fall into two categories:
- Dirty Pages
- Clean 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 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.