Why does the buffer pool contain so many dirty tempdb pages?

(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.

7 thoughts on “Why does the buffer pool contain so many dirty tempdb pages?

  1. Hi Paul,

    Thanks for those GREAT posts!!!

    One question I have is, as you said in your another post, the transaction log records are cached in memory as well…

    In order to support

    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.

    Then, the transaction log has to be written into disc prior to dirty data page, correct? otherwise, if both dirty data page and transaction records are persisted at the same time during checkpoint, and if disaster happens, then there may be NO transaction log persisted, and then no REDO operation could be done…

    Put it simply, are transaction log records persisted differently?

    Thanks for reading!

    Ta

      1. No – that’s not correct. Log blocks are flushed in a variety of ways: when they reach their maximum size (60KB), when a commit or abort log record is generated for any transaction, to guarantee write-ahead logging where the log goes before data. It doesn’t wait until the end of the transaction before flushing all the log for the transaction.

  2. Paul sir,so automatic checkpoint doesn’t do anything other than log management.so long running transaction (like spill to temps) only occurs by lazy writer in search of free buffers. Am I right sir

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.