1

We normally set our file growth in percent (10%,20%) or fixed size (10 MB, 20 MB etc). If the disk response time is too high for the requested size to grow, then users will be affected and have to wait until the file growth completed. We can see the response time in micro seconds at default trace file column named as 'Duration'.

My question is, what is the optimal response time for every growth in milliseconds?

1
  • Aside: Don't put too much faith in any time that a PC reports in microseconds. A typical clock tick is 15.6ms. Ref. Unless you have absolutely clear documentation defining precisely what is being measured then treat it as a SWAG.
    – HABO
    May 4, 2013 at 0:54

3 Answers 3

1

First of all you want to make sure that your database is sized appropriately up front. Usually I like to plan for 6 months to a years worth of growth. That being said your data file(s) should be the only ones growing on a regular/irregular basis. Your log file once it hits a certain point should not grow much if at all, assuming you are taking regular backups. Given that, you can use a server setting called Instant file initialization that will make your database growth very very quick, regardless of how much space you are adding.

Here is a link you can read to start you on it. http://msdn.microsoft.com/en-us/library/ms175935(v=sql.105).aspx

If you have this turned on then you can afford to set your auto growth to larger sizes so that you don't have to grow as often.

1
  • Just to clarify, instant file initialization only works for data files, the log file has to be zeroed out so it can't be instantly initialized. May 4, 2013 at 5:10
0

My question is, what is the optimal response time for every growth in milliseconds?

Data file or log file?

For recovery and security purposes, by default, SQL Server writes out zeros to all newly allocated files (or portions of files). The amount of time it takes to create or grow a file depends on its size and the speed of your I/O subsystem. This write will always be sequential. Based on those parameters, you can make an estimate for your environment. It's simply not possible to give you a concrete answer to the question as asked if the files need to grow.

For data files specifically, there's a feature called Instant File Initialization (I prefer to call it Instant Data File Initialization, because it only applies to data files) which significantly speeds up the process of creating or growing data files. I've explained this in more depth (with a demo video showing how to enable the feature), including why you might choose to not enable this feature in your environment. Note: Instant Data File Initialization isn't a direct substitute for poor auto-growth settings -- you still want to set the growth sizes to reasonable values.

All that being said, the optimal time is, of course, zero. In other words, monitor file usage and pre-grow them before you need to rely on auto-growth. I realize in some environments this may not be feasible or easy, and I understand that, but you asked for the optimal solution, so there it is.

0

The optimal response time would be zero or as close to zero as you can get. A reasonable response time would be a second or two, in my humble opinion.

For the typical run of the mill database I set the file growth to be 64MB for both data and logs. For larger systems I will double that to 128MB. If you have autogrowth as a percentage, the the initial growth will be fast but often. Too many auto-growths can lead to fragmentation: http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

Why Microsoft leaves the default setting as 10% is a mystery to me. Do we really need a 5MB database auto-growing by 500KB? And a 1TB database auto-growing by 100GB? Crazy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.