It is generally known that having a large number of virtual log files (VLFs) in a database transaction log is undesirable. A blog post by the Microsoft support team in Stockholm showed that a large number of virtual log files could seriously lengthen the database recovery time. Tony Rogerson also reported that lots of virtual log files were bad for triggers and log backups on SQL Server 2000.
This blog post explores two questions:
- Is a large number of VLFs in a transaction log still a significant performance factor in SQL Server 2008?
- Does a large number of VLFs have an adversely impact on application-related operations such as INSERT, UPDATE, and DELETE?
As I’ll show in the rest of this post, the answers to both questions are yes. Let me first describe the test, and then present the test results.
The test databases
I created two databases: dbVLF_small and dbVLF_large on the same SQL Server 2008 instance (Enterprise x64 Edition and the build number was 10.0.1600). The host was an HP DL585 with four single core 2.2GHz Opteron processors and 32GB of RAM (28GB of which was allocated to the buffer pool). The following table highlights the key properties of these two test databases:
|
Property |
Property value |
|
|
dbVLF_small |
dbVLF_large |
|
Data file size |
20GB |
20GB |
|
Data file location |
E: drive |
E: drive |
|
Log file size |
10GB |
10GB |
|
Log file location |
F: drive |
F: drive |
|
Recovery mode |
Full |
Full |
|
Number of VLFs |
16 |
20,000 |
As you see, the only masterial difference between these two databases was the number of virtual log files. The dbVLF_small database had 16 VLFs (a small number of VLFs), and the dbVLF_large database had 20,000 VLFs (a large number of VLFs). It’s probably a bit extreme for a database to have 20,000 virtual log files. But it’s not completely unreasonable if you let your database log file to grow in small increments. Note that my intention is to highlight the impact in case if your database does end up with a large number of VLFs.
The test table
An identical table, called customer, was created in both databases. The DDL for the customer table is as follows:
CREATE TABLE customer
(
c_id int,
c_d_id tinyint,
c_w_id int,
c_first char(16),
c_middle char(2),
c_last char(16),
c_street_1 char(20),
c_street_2 char(20),
c_city char(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since datetime,
c_credit char(2),
c_credit_lim numeric(12,2),
c_discount numeric(4,4),
c_balance numeric(12,2),
c_ytd_payment numeric(12,2),
c_payment_cnt smallint,
c_delivery_cnt smallint,
c_data char(500)
)
go
CREATE UNIQUE CLUSTERED INDEX ci_customer on customer(c_w_id, c_d_id, c_id)
go
This was the customer table used in the TPC-C benchmark.
The test data
3,000,000 rows—with data as specified for the TPC-C customer table— were bulk copied into the customer table in both databases. The index was then rebuilt, and sp_spaceused showed the table size to be about 2GB.
The tests
Three simple tests were run against the customer table in each database. The common characteristics of these three tests were that they were a single large transaction.
The insert test
SELECT * INTO customer_tmp
FROM customer
WHERE 1=2;
go
INSERT customer_tmp
SELECT * FROM customer;
The update test
UPDATE customer
SET c_data = lower(c_data);
The delete test
DELETE FROM customer;
The test results
The following table summarizes the test results:
|
Test |
Elapsed time (sec)
in database: dbVLF_small |
Elapsed time (sec)
in database: dbVLF_large |
|
The insert test |
281 |
1069 |
|
The update test |
174 |
1554 |
|
The delete test |
193 |
936 |
The following chart gives a better visual representation of the same results:
In all the three cases, it was significantly (should I say dramatically) slower to perform these common SQL operations when the database had 20,000 VLFs than when the database had 16 VLFs. The insert was about four times slower. The update was about eight times slower, and the delete was about five times slower.
So the test results confirm that it is definitely a significant performance issue in SQL Server 2008 to have a large number of virtual log files in a transaction log. Moreover, the test results show that the problem can be felt by common SQL operations such as insert, update, and delete in large batch processes.
What does this all mean? First, pay attention to the often-repeated best practice advice: do not grow your database log files in small increments (because that is often how you introduce a large number of VLFs into your transaction log). For instance, if you know you’ll need 10GB for your log, it’s best to allocate 10GB in one fell swoop. Secondly, if you do find a large number of VLFs in your transaction log, it may be worth the maintenance effort to reduce the number. You can reduce the number of VLFs by first shrinking the log file to a minimum size, and then allocating the required space in one ALTER DATABASE statement. (SQL Server MVP Tibor Karaszi has a thorough discussion on shrinking log files at his site)
------
By the way, the 20,000 virtual log files were generated as follows. First, create the database in the full recovery mode with a 512KB initial log file size and 512KB file growth increment. Then, backup the database, and create a dummy table with a single char(8000) column. Finally, inserting data into this table in a loop until the log file size reaches 5000MB. This is essentially the same procedure used by the Microsoft support team in Stockholm in their post.
To prepare for the insert, update, and delete tests, backup the log, and expand the log file to 10GB.