Can log files growth affect DML? Post-CU Update

Hello all,

A while back I blogged on how log files can affect DML performance, depending on their structure and growth ratio. It recently came to my attention (thanks Paul Randal) that a fix was published late last February, in KB2455009, aimed at scenarios where one might experience slow performance when recovering a database, if there are many VLFs inside the transaction log in SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2. This fix is included in the Cumulative Update 6 for SQL Server 2008 R2, Cumulative Update 3 for SQL Server 2005 SP3 (not yet included in SP4 CUs) and Cumulative Update 12 for SQL Server 2008 SP1.

That being said, I became curious. Would applying this fix impact on my previous findings? Perhaps the LogReader and LogWriter processes were somewhat “tampered” in this fix? And so I did all the benchmarks once again.

The test setup is comprised of a VM with 3GB of RAM and 2 contiguous 20GB VHDs on my newer 64-bit laptop (Core i7 at 1.6Ghz, running Windows Server 2008 R2 and SQL Server 2008 R2 with Cumulative Update 7). Again, two fully logged databases were needed for this test case, both with a 5120MB data file and a 2048MB log file, growth limited to 2192MB on the log and unlimited on the data file. Both have the same file growth size on the data file, which again did not grow at all for the duration of the tests. Refer to here for all the DML.

I followed all the stages as before (3 times over):

1. Pre-growing log files on both databases:
   - LowVLF DB (log grown by 1024MB increments) spawning 20 VLFs.
   - HighVLF DB (log grown by 1MB increments) spawning 8000 VLFs.
2. Creating a test table in both databases.
3. Initializing the backup chain to avoid automatic log truncation.
4. Executing an update loop 40000 times in each database.
5. Executing a log backup after the loops.

Additionally, I executed steps 2 thru 4 without pre-growing the log,  to get the overhead of log auto growth, just like in the previous test case. All the following charts include the previous (Pre-Fix) and current (Post-Fix) results for trend comparison.

Take log growth for example. While the 1st results showed an impressive 70% overtime (on average) when growing by shorter amounts and spawning more VLFs, on my new faster drive, the overtime was boosted by roughly 6000%.
And I did restart the VM between tries just to be sure. So it is safe to claim the trend is the same between my previous tests and those I ran today, taking into account I have a faster machine now.

For verification purposes, here is the ErrorLog for the instance showing the log files being zeroed. The LowVLF database log (in red) took 2 seconds on all 3 runs. The HighVLF database log (in blue) took about 2 minutes, which is consistent in all 3 runs.

 

Now for the Update loop test with pre-grown transaction logs. The numbers show a clear difference between running the same T-SQL as before, under the same conditions, except for the number of VLFs, just as it did in my previous tests. Again, the Post-Fix tests are on newer and better hardware, but the trend clearly remains.

The same Update loop test, where I purposely allowed auto-grow to kick in. No surprise here. There is still a big difference between the LowVLF and HighVLF databases, although minimized by the better hardware.

 

Even with better hardware, we can observe how log backup times are very much affected by the sheer number of VLFs, and again the numbers show it clearly. For roughly the same amount of pages, a log backup of the HighVLF database took almost twice the time of the LowVLF database in my previous tests. On those I ran today, the differences are still apparent (better hardware remember?), even though the number of backed up pages is almost the same in both HighVLF and LowVLF backups.

 

In essence, the fundamental conclusions in my previous post still apply. Also, it is without surprise that I can demonstrate that the only reasonable preventive measure for VLF issues, be those DML, recovery or backup performance, is still not to have too many. How? Avoiding excessive and uncontrolled log growths. Because of the extensive information in the mentioned KB article, I strongly recommend you to read it. It describes a multitude of scenarios where a poor log growth strategy can have a serious impact, if you’re not by now aware.

Until next time!

Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.