x

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

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.

[2]: http://ask.sqlservercentral.com/questions/31282/what-should-an-accidental-dba-know-about-vlfs
more ▼

asked Jan 19, 2011 at 02:46 AM in Default

Håkan Winther gravatar image

Håkan Winther
15.9k 35 37 48

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Jan 19, 2011 at 04:28 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

@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?
Jan 19, 2011 at 08:00 AM Oleg

@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
Jan 19, 2011 at 10:42 AM WilliamD
@WilliamD This seems to be a perfect approach. Thank you.
Jan 19, 2011 at 10:54 AM Oleg
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.
Jan 19, 2011 at 12:21 PM Håkan Winther
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.
Jan 19, 2011 at 12:32 PM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
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
more ▼

answered Jan 19, 2011 at 02:49 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1853
x252
x68
x5

asked: Jan 19, 2011 at 02:46 AM

Seen: 2709 times

Last Updated: Jan 19, 2011 at 02:51 AM