Dirty Page, Checkpoint, Commit Tran

A dirty page is a page that has not yet been written to the disk. You can (and most often will) have many pages that are different in memory as opposed to the copy on disk. They are called dirty, because the application has "smudged" them with new data. Once they are written to disk and both the disk copy and the memory copy agree, then it is no longer dirty.




As for the Buffer cache, I thought that the procedure cache was separate from the buffer cache. At least in 6.5, it was the same pool, but different "sides". You had to define how much of your memory allocation was for the proc cache. SQL Server defaulted to 30%. Anyone who came into this business after SQL 7.0 doesn't remember a bit of that trivia ;-). Generally, I think of the buffer cahce as containing any data buffer, so that would be log as well as data. The log contains "data" of it's own. Usually page numbers, transaction id's, etc..



Checkpoints have to happen in full and simple modes. Otherwise, no one's database would ever come back from a reboot. Checkpoints are done asynchronously by one of the background processes (spids 1-15 or so). This is to keep the I/O of writing of data from interfering with your reading in other pages from disk. Checkpoints can be triggered by a number of things ranging from the manual issuing of the checkpoint command to a need for free buffers caused by a huge table scan. Oddly enough, a shutdown does not give a checkpoint. If you have long re-start times, you may want to consider issuing a checkpoint before a shutdown (assuming it is a planned shutdown).



A commit transaction will mark your transaction as "completed" in the trasnaction log. Remember: All data is written first to the transaction log, then to the data files. All data is updated in memory first, as it is faster, but the hope is that data makes it to disk eventually. If something does not make it into your transaction log, it has no business on your data file. On recovery (such as after reboot), SQL Server goes through the transaction log to make sure that the current copy of the data is consistent. If it is not consistent, then it is suspect.