SQLPASS: Are You Smarter Than an MCM? VLF Demos

  • Article

Are You Smarter Than an MCM?

 I had a great time on Wednesday co-presenting with some great SQL peeps! We dressed up, had a lot of fun, and shared a few technical tips along the way. My demo was on VLFs, an often forgotten and/or misunderstood part of the transaction log technology. The demo files are attached below. Thanks to everyone who came to the talk! #SQLPASS

The Team for DBA-414-M

Our fabulous “Smarter” team is

VLFs

Question: Cindy has ADHD. In the past she we able to keep up with multiple tasks at once, but lately she has slowed down. Why? 

Answer: Excessive VLFs

 A VLF is the boundary within your log file(s) for log operations such as scans (replication, recovery, mirroring, CDC, etc.) and log clearing/truncating.

  • 100s of VLFs might be ok, 1000s is probably bad, 10s of thousands is almost certainly bad. Bad means much slower operations for activities that operate within VLFs boundaries.
  • DBCC LOGINFO is undocumented and unsupported but is the only way to see your VLF distribution. It returns one row per VLF.
  • Often you get too many VLFs from autogrow/small growths.

 VLF Lessons Learned:

  • Avoid autogrow by pre-sizing your data and log files appropriately. This includes alerting when your free space starts to decrease significantly and planning for data growth over the lifecycle of the system. Make sure you check your system as well as your user databases.
  • Set autogrow values to reasonably large amounts to avoid a series of small autogrows in an unexpectedly high volume period, but not so big the growths themselves slow down the system.
  • Alert on autogrows and check (and possibly correct) VLFs afterwards.
  • Check your VLFs periodically and during performance troubleshooting and consider shrinking/regrowing properly (with the proper presizing) to reduce the total number of VLFs when the number gets "too high".
  • Never grow (manually or via autogrow settings) in increments of exactly 4GB. Values such as 4000MB or 8000MB are good, but there is a bug with multiples of exactly 4GB.
  • Do NOT shrink your data or log files on a regular basis. Shrinking should be extremely rare and in response to a specific issue.
  • And because it can’t be said too often, pre-size your databases to avoid autogrow.
  • Revisit the sizes periodically.

 Thanks everyone for coming to the talk and for the great feedback afterwards! You did fill out your evaluation forms, right? :-)

Pictures:

https://yfrog.com/z/h88chpvj

https://flic.kr/p/awbV6h

VLFDemo.zip

Comments

  • Anonymous
    October 16, 2011
    Robert Davis's demos are here: www.sqlsoldier.com/.../summit2011sessionfiles
  • Anonymous
    October 19, 2011
    The comment has been removed
  • Anonymous
    November 07, 2011
    Correct me if I'm wrong, but as far as I know we must defferentiate OLAP and OLTP workloads when talong to VLFs, right?
  • Anonymous
    November 07, 2011
    talong to VLFs actually means talking about VLFs :-)) sorry for that
  • Anonymous
    November 15, 2011
    VLFs are boundaries within a database log. OLAP vs. OLTP doesn't matter directly.
  • Anonymous
    March 13, 2012
    During the session you mentioned that you will be posting script that shows how to fix excessive VLFs. Do you still plan to do it?I have a db, that doesn't shrink down to less than ~175 VLFs. Do you know of a work around? I was thinking adding a second LDF, sizing it, and then getting rid of the bad one. What do you think?
  • Anonymous
    March 14, 2012
    Mark - there's a VLFDemo.zip at the bottom of the post. In there you will find VLFShrinkFile which has sample steps for shrinking and properly regrowing your log file with a "good" number of VLFs. If there are users or batch jobs (anything writing to the log) on the system you may have trouble shrinking it completely.