Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Want to Make Your SQL Server Run Faster for Free?

I know I do!

The trick is to properly manage your VLFs.

What are VLFs? The short anser is that VLF stands for virtual log file. SQL Server database log files are made up of many smaller virtual log files that make it easier for SQL Server to manage the log files. For a much more in-depth answer see the Transaction Log section Paul Randal’s (Blog|Twitter) TechNet article.

The problem with virtual log files is that having too few or too many can slow your server down. I spoke to someone over the weekend that had to wait 30 hours for their database to complete recovery. When they checked they had 1.6 million virtual log files in their database. That is a lot by any standard. A more constant issue with having too many VLFs is that they can also slow down any log operation including logged statements like insert, update and delete. Check out this article by Linchi Shea (Blog) on the performance impact too many VLFs can have. Kimberly L. Tripp (Blog|Twitter) has a great article on how many VLFs you should have.

So if the trick to making your server run faster for free is to manage your VLFs, then how do you go about doing that? Great question. There are lots of great posts out there on how to do this. Here is an article that I wrote detailing the process that I use and some of the reasoning behind it.

If you find any really high counts in your environment, we’ll say greater than 2 million VLFs, please leave a comment with how many. I am curious to see what the highest value is.

Comments

Posted by Glenn Berry on 19 November 2010

It is nice to see more people talk about this common issue. I would have also mentioned how to use DBCC Loginfo to find out how many VLFs you have in a database.

Posted by David Levy on 19 November 2010

Thanks! I figured it was time for a public service post similar to reminding people to change the batteries in their smoke detectors.

The links in the post cover using DBCC LogInfo so I felt it best to keep the article brief and let people drill through. If you are interested, the last link includes a handy script to shrink and regrow a log file. Saves me a ton of time.

Posted by Jason Brimhall on 19 November 2010

I agree - these kinds of posts need to occur with higher frequency.  It is amazing the difference in performance a ton of VLFs will cause.

Thanks

Posted by Anonymous on 22 November 2010

Pingback from  @Dave_Levy posts Want to Make Your SQL Server Run Faster for Free? | SQL Server Central | sqlmashup

Leave a Comment

Please register or log in to leave a comment.