(Quickie post #2 while it's Kimberly's turn to lecture this morning…)

Greg asked a question regarding the script I posted to examine buffer pool contents (paraphrased) – why does the buffer pool seem to contain such a high proportion of dirty tempdb pages on busy production systems?

The answer is to do with the recoverability of the tempdb database. One of the reasons that checkpoint exists is to limit the duration of the "redo" phase of crash recovery – where log records are replayed on disk pages where the updated page image hadn't been written to disk after the transactions committed. Automatic checkpoints are done in databases to do this.

Tempdb, however, isn't recovered after a crash – it's recreated. This means that the time for recovery of tempdb isn't an issue so there's no need for automatic checkpoints. This means that the trigger of an automatic checkpoint for tempdb when it's log file reaches 70% full. For this reason, on busy systems, it's likely that the user databases are being checkpointed way more often than tempdb, and on a system where tempdb is used heavily too, there will likely be way more dirty pages from tempdb in the buffer pool at any time.