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.