Hello Gail,

Thank you for the extensive answer.

It makes sense to me that 5.000.000 transactions in the logfile instead of 1 transaction (for inserting 5.000.000 rows) might have impact on the processing/management in the transaction log file. I understand now this might explain the different lead times. Great!!

Regarding the dirty pages I'm still confused. I understand there can only be one version of a page in the datafile and it would be very difficult to manage multiple versions.

I'll try to explain why I'm still confused : Consider max memory setting = 800KB, meaning you can only have 100 pages in the buffer cache. I write an update statement that updates 300 pages in one transaction. What will happen now? The first 100 pages are loaded in RAM, the update is logged in the transaction log file and the 100 pages in RAM become dirty pages. Now we have 100 pages in RAM that are different from the corresponding 100 pages on disk ... and therefor are called dirty. Correct? Now SQLServer has to load the next 100 pages in RAM to update page 100 to 200. In order to do this SQL Server has to create free space in the buffer cache, but all pages in the cache are dirty, BUT also not commited yet. You say the dirty pages will not be written as a second version ... but I should think SQL server must store somewhere both versions of the page : the dirty version to enable commit and the Original version in case rollback is required??? I probably make a logical mistake somewhere in my perception, but I don't see it at this moment.

Kind regards,

Peter.