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.
Read more: Adventures in SQL
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.
Read more: Adventures in SQL