Welcome back and Happy Saturday!

Today I wanted to share with you that some DBAs seem to overlook the transaction log when thinking about optimizing database performance.  I myself was guilty of not considering optimizing the transaction logs for my production databases.  I am going to blog about Virtual Log Files (VLFs) and why it is important to consider this option when talking about overall database health and performance.  Here is a little background first… I first learned about VLFs when I attended the last Dev Connections Conference in Las Vegas for SQL Server 2008.  At his conference I learned about VLFs from one of the prominent SQL Server experts Kimberly Tripp.  Here is a more detailed breakdown of Virtual Log Files:

Virtual Log Files
The Transaction Log is divided into Virtual Log Files
To get more information about Log file utilization you can run DBCC LogInfo
When you add space or Autogrow
< 64 MB creates 4 new VLFs
>= 64 MB and 1 < GB creates 8 new VLFs
>= 1 GB creates 16 new VLFs
A VLF fills up before moving on to the next
Log Backup does not clear a VLF until you have moved into the next one
(so you don’t want a very small number of huge VLFs)
Too many and you get poor write performance
If more than 50 VLFs time to trim
Ideally around 20 VLFs

For Performance Impact please refer to Linchi Shea’s post on SQLBlog (link must be cut and pasted into your browser):

http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx

For more information, please refer to Kimberly’s posts on her website (also, these must be cut and pasted into your browser as the direct links will not work):

http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

I hope I have given all of you something new to ponder as you are getting ready to optimize performance on your production databases.