How important are transaction log VLFs for performance?

Question

How important are VLFs when considering database performance? What would describe an optimal situation for VLFs?

Answer 1

What is a virtual log file?

SQL Server divides the transaction log file for each database into smaller chunks, called 'virtual log files' (or VLFs for short). Their primary function is as truncation markers at a log backup, i.e. the SQL Server will only clear (and mark available for re-use) VLFs that are completely empty. MSDN has a piece on Transaction Log - Physical Architecture.

What determines the number of VLFs?

Each time a log file grows (whether via autogrowth or manual growth), the new section of the log file is divided into a number of VLFs, purely based on the size of the new section (the existing transaction log is left alone). So, small autogrowth settings (i.e. the 10% autogrowth that is the default) will cause a large number of VLFs to be created.

What are the implications of a large number of VLFs?

The primary issue a large number of VLFs causes are:

• Slow recovery (recovery is a phase during database restore where completed transactions are written to the data pages and incomplete transactions are rolled back).
• Slow log-reader performance in a database mirroring setup.
• Slow performance during database snapshot creation (note that this also includes certain functions of DBCC since they use database snapshots in the background to facilitate consistency checks without blocking).

How can I find out how many VLFs my database has?

DBCC LOGINFO will return 1 row for each VLF in your database's transaction log. This question has a couple of useful scripts for applying that across all databases on a server.

How many VLFs is too many?

That's a judgment call you'll have to make for yourself. My personal rule of thumb is that under 50 isn't worth messing with, and over 100 (or so) and I fix autogrowth settings and make a mental note to (in the next maintenance window) shrink and regrow the log (as below).

Help! I have eleventy billion VLFs and my database recovery takes all day!

Short outline (from Kimberly Tripp's blog):

• Make sure you're the only connection active in the database (so, do this during a maintenance window)
• Backup the transaction log to disk (using BACKUP LOG)
• Run DBCC SHRINKFILE with TRUNCATEONLY to shrink the log file to the smallest size possible.
• Run ALTER DATABASE [...] MODIFY FILE [...] SIZE=newsize to resize your transaction log back up in a single step**.

** Note - if you have a very large log file (tens of GBs or more), you may want to resize in multiple steps to get an appropriate number of VLFs with an appropriate size to avoid excessively 'chunky' log backups. Since VLFs are the unit of truncation they also determine the log backup sizes, as detailed in Kim's blog.