Performance impact of lots of VLFs in the transaction log

One of the things I mentioned in my recent TechNet Magazine article on Understanding Logging and Recovery was the need to manage the transaction log so the number of VLFs (virtual log files) does not get too large (with too large being more than, say, 100 VLFs). Linchi Shea (a fellow MVP) has just posted some performance numbers comparing insert/update/delete performance between a database with 16 VLFs and one with 20000 VLFs, representing a poorly managed transaction log. You can see his blog post here (2018: sadly it’s been taken down – sorry!)

For those wishing to take charge of unruly transaction logs, checkout Kimberly’s blog posts – starting with this one. And if you want to quickly know how many VLFs your transaction log has, use the undocumented DBCC LOGINFO command – the number of lines of output is the number of VLFs you have.

Next posts coming up will be photos – we’re in Bangkok right now and I’ve got 3 Where In The World Are Paul and Kimberly posts queued up – St. Lucia, Hyderabad, and Bangkok.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.