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?