Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
For further reading, see my question at dba.stackexchange.com/questions/25408/… –  Max Vernon Aug 12 '13 at 3:05
    
Cool - I can see this being helpful for reporting on performance –  DBAWaffle Aug 12 '13 at 11:16
    
@DBAWaffle Refer to : Experiments with SQL Server VLFs, Part 1 from Glenn Berry --> sqlskills.com/blogs/glenn/… –  Kin Aug 13 '13 at 0:42
    
thanks for the thorough article :) –  DBAWaffle Aug 13 '13 at 17:29
add comment

1 Answer

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.

share|improve this answer
    
Thanks, do you think that multiple VLFs would more obviously hinder performance in a high OLTP environment where data is being changed regularly, a large number of commits to go through? –  DBAWaffle Aug 12 '13 at 11:13
    
Large numbers of VLFs are only really notable in the situations I outlined above (recovery, snapshots, mirroring). They might hinder performance a bit in an OLTP environment, but only marginally so. –  Simon Righarts Aug 12 '13 at 20:36
1  
For me, Recovery of 20+ hours was what convinced me of the importance of maintaining a low (sub 1,000) VLF count. –  Max Vernon Aug 13 '13 at 1:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.