Home Forums SQL Server 2014 Administration - SQL Server 2014 What happens when the number of dirty pages grows beyond the number of pages in the buffer cache? RE: What happens when the number of dirty pages grows beyond the number of pages in the buffer cache?

  • Hello Gail,

    I could not move my mind away from this topic. After my last reply, I kept thinking about the statement "checkpoints can write uncommitted dirty pages to disk" ... and by doing so ... removing the data as it was before the update AND removing the data as it should be in case of rollback.

    I've read your answer again and I think I understand now how it works ...

    Checkpoint or lazy writer flushes the dirty pages from buffer cache to disk, even if the transaction that changed the page has not been committed yet. I tought this is a problem, but actually it's not since there is a lock on the page just until the transaction is committed or rolled back. Other users will not be able to read that data (when using read committed isolation level) until the transaction is completed and the lock is removed.

    So, indeed there is only one version left of the page, the uncommitted version, but that's not a real problem. In case of a rollback the original page can be recovered by reading the page back into the buffer cache and rolling back the changes based on the information in the transaction log file. In my example (updating 2400KB with max mem on 800KB) this might have a performance penality since a lot of physical IO has to be done to put back the data into it's original state, but that is acceptable since we might consider commit being regular behaviour and rollback being exceptional behaviour. Correct?

    One moment I thought the uncommitted dirty pages written to disk should be flagged as being uncommitted, but actually that's not necessary either since it's the lock manager that gives the required information to other sessions. So, when another session (using read committed) can execute a physical read of a page, that means that page has been committed (or rolled backed) ... and when another session (using the "dangerous" read uncommitted isolation level) reads the page ... it doesn't matter wether the page is in a state that still can be rolled back.

    Please let me know if I made wrong assumptions again in this hypothesis.

    Kind regards,

    Peter.