THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance impact: a large number of virtual log files – Part II

In my previous post on the performance impact of having a large number of virtual log files (VLFs) in a transaction log, I showed that a large number of VLFs could be very bad for SQL Server 2008 performance. The test workloads were large batch delete, update, and insert. In other words, they were single monolithic transactions that generated a large number of transaction records. Intuitively, these large transactions would cause SQL Server to cross many VLF boundaries and incur the penalty of crossing these boundaries.

 

The question then became: how about workloads that submit small transactions such as common OLTP workloads?

 

As it turns out, a typical OLTP workload may not experience any performance degradation at all, even when the number of VLFs is into the 20,000 range.

 

The supporting evidence lies in the test results of a TPC-C like benchmark that I ran against the same test databases dbVLF_small and dbVLF_large, whose setup was described in the previous post. These two test databases were configured identically except that one had 16 VLFs in its transaction log and the other had 20,000 VLFs in its transaction log. The test workload used the standard TPC-C read-write transaction mix, which means that the workload was relatively write heavy.

 

The test environment was the same as described in the previous post. The test database was sized for 100 TPC-C warehouses, which translates into ~9GB for all the data and indexes.

 

But I’ll skip any details regarding the test setup and test data, and go straight to present the test results, which are highlighted in the following chart:

 

 

For this OLTP workload, the number of VLFs in the transaction log did not make any significant difference. This, I guess, is good news. Did I hear a sigh of relief?

 

Published Thursday, February 12, 2009 1:40 AM by Linchi Shea

Attachment(s): VLF002.gif

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Greg Linwood said:

Any chance you could tailor a test to tempdb, targeting things like temp tables, cursors & rowversioning? I'd expect most systems would suffer more from tempdb VLF problems than user db VLF problems as high end user db tlogs are usually well configured whilst tempdb tlogs are generally left to auto-grow & more often than not also performs more IO than user db tlogs too.

February 12, 2009 2:46 AM
 

John Parker said:

Excellent post(s). So I guess if you've got a data warehouse make sure you have a small number of VLFs.  If you have a pure OLTP, it doesnt matter but if your OLTP does some moonlighting as an OLAP database, keep them small too.

So in general, keeping a smaller number is the best advice just in case you need the odd batch job. Great stuff.

February 12, 2009 6:56 AM
 

Kevin Boles said:

I am not surprised too much by your findings on this, but certainly glad to see the proof!! Thanks again for your community knowledge give-back, Linchi!

February 13, 2009 10:09 AM
 

Linchi Shea said:

Greg;

I was actually going to write something up about tempdb VLFs. But my test results on tempdb VLFs are all over the places, and I'm not sure what to make of that. For all my previous posts, I always make sure that I can repeat the test results before I post the results. I don't want to have to come out to retract a result. With tempdb, although I generally don't see the performance of these large INSERT, UPDATE, and DELETE to differ as significantly as I do with a regular user database between a log with a small # of VLFs and a log with a large # of VLFs, I do see the test numbers vary quite widely, and am not sure if I can really draw any sound conclusion there.

Note that tempdb does logging differently than does a regular user database.

February 13, 2009 11:25 AM
 

Kevin Kline said:

The industry's best resources for maximizing Microsoft SQL Server backup and recovery. Don't get burned!

December 8, 2011 3:40 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement