asked
Is the VLF performance issues solved in SQL 2008 and later?
Read more about the issue at [
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx][1] At my current client I have seen some databases (like ReportServer database) with more than 2000 VLF files, due to the fact that initial size have been too small and autogrow setting of the log file have been 10%. If I create a database I always tries to size the data and log files as correct as possible, but I haven't been aware about the 8Gb recommendation. Does it affect the performance or is it solved? I have read this question [
http://ask.sqlservercentral.com/questions/31282/what-should-an-accidental-dba-know-about-vlfs][2] but there are no answers about the performance impact. [1]:
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx [2]:
http://ask.sqlservercentral.com/questions/31282/what-should-an-accidental-dba-know-about-vlfs
The recommendation is not necessarily 8GB (it always depends), but that the VLFs are created depending upon the size the log file should be (or should grow by). 8GB just means that the VLFs are 512MB (8GB / 16VLFs), which means that 512MB will be cleared from a VLF when it has been processed - this is a lot nicer than if you pre-allocate 64Gb which results in 16*4GB VLFs. E.G. If you need a 64GB log file, then start by making it 8Gb, then extend to 16 etc. until you hit 64GB, this will ensure that you have 128 VLFs each 512MB in size. I think it may be something that you wouldn't necessarily notice in a low load system, but it is a good thing to manage if you are so inclined. The number of VLFs directly impacts performance of activity in the transaction log and can affect tran log backups too ([Kim Tripp][1]). p.s. The 4GB bug mentioned in the question you linked to has been fixed as of SQL 11. I would hope they retro-fit SQL 2008 via an SP, but maybe they won't. [1]:
http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
@WilliamD This is true that, as Håkan mentioned in his question, that the initial default size of the log file is too small, which makes the VLFs sizes small as well. However, this initial out of the box size is not pulled out of the thin air but is rather inherited from the current setting in the **model** database. So the question is: is it worth it to alter **model** database to have its respective database and log file set to an appropriate amount depending on the needs, or the inevitable waste of space outweighs the benefits?
@Oleg - I would (and in fact do) set model to a sensible default size and growth (256MB across the board). If someone then creates a DB who is not in the know (it sadly does happen) - they at least have some sort of advantage. If I create a DB, I presize according to sensible metrics (or guess if the metrics aren't provided), then monitor the DB over the following weeks/months
I think it would be a good idea to take a look at policy based management in SQL server 2008 and see if autogrowth size can be handled, otherwise alter the model database.
The problem is that it is to damn easy to install SQL server and add databases without any basic knowledge about database design and performance. On the other hand, even I didn't know about the 8 Gb recommendation.
I havent heard that its resolved so am avoiding it until I do... I may have been day-dreaming when the announcement went out though
