peter.roothans (3/3/2015)
Hello Gail,Thanks for clarifying that. Now I wonder whether you can have two versions of the same page in a datafile?
No. A page's page number*8kb is it's offset within the file. Hence there's no way you could possibly have two of the same page, as they'd have to occupy the same location in the file.
I presume I will have a page in the mdf that contains the data as it was before the start of the transaction (a kind of snapshot) and I will have a (dirty) page (or two in case of page split) ... in the mdf as the data will be after commit.
No you won't.
Think for a moment whether what you've suggested is at all practical. What you're suggesting would be hell complex, a pain to keep track of and require that the DB is twice or more the size that it would be now.
If this guess is correct, I assume, after commit or rollback the checkpoint mechanism will decide which pages to keep (the original pages in case of rollback) and which page to remove (the original page in case of commit)??
No, because there's only one copy of a page, so there's nothing to 'remove'
Checkpoint writes dirty pages to disk and, in simple recovery, runs log truncation. That's all. Lazy writer does a bit more as it also manages clock hands for caches, but in terms of IO it just writes dirty pages to disk in response to memory pressure.
PS1:
It turns out (on our hardware configuration) using one big explicit transaction instead of 5.000.000 small implicit transaction improves the lead time of the test from about 40minutes to 10minutes. So, I guess, the IO pattern on the data file(s) changes when using one big transaction? Perhaps I get less but larger IO's, which might explain the performance impact?
No. Neither checkpoint nor lazy writer care much about transactions, so neither option will affect writes to the data file.
Changing from lots of little transactions to one large transaction will make the log writes more efficient though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability