01 January 2010

Common Mistakes in SQL Server - Part 5

Last to Last week we discussed how Null Values can cause a trouble in Common Mistakes in SQL Server – Part 4. This I will discuss about Auto Growth feature of SQL Server, and how it can impacts us.

What is Auto Growth?

Auto Growth is a feature that allowed database files (primary, secondary and log) to expand when database file becomes full - without manual intervention.

Auto Growth feature is really useful when we do not want to increase the size of database files manually. There are two ways you can set auto growth – using SQL Server Management Studio (SSMS here after) and T-SQL. Auto Growth can be configured – In Percent and In Megabytes.






How Auto Growth can impact us?
Now that we know what Auto Growth feature in SQL Server is; I’ll describe how this feature can impact us badly if we do not  configured this option carefully . Below is the table that will give you an idea what would happen if database file reaches to its threshold value and it needs to expand!!
See the calculation below when Auto Growth option is configured with in Percentage:

Configured to grow "In Percentage"







File Size (before growth)
Growth - 10%
File Size (after growth)
1st time
5000
500
5500
2nd time
5500
550
6050
3rd time
6050
605
6655
4th time
6655
665.5
7320.5
5th time
7320.5
732.05
8052.55
6th time
8052.55
805.255
8857.805
7th time
8857.805
885.7805
9743.5855
8th time
9743.5855
974.35855
10717.94405
9th time
10717.94405
1071.794405
11789.73846
10th time
11789.73846
1178.973846
12968.7123




You will notice that the database growth is exponential when it is configured with Percentage i.e. 10%.  This is because this is calculated in cumulative manner; the value I have used here is 20 times lower than what we used to work on production systems generally.  If we set Auto Growth in Percentage it will occupy our HD space unnecessarily and creates fragmentation. Moreover when database files are expanding you will notice high volume in CPU spikes and I/O cycle.   
What is the best practice for Auto Growth option?
Best practice is to configure Auto Growth option in static value. See the sample calculation below:


File Size (before growth) MB
Growth - 500MB
File Size (after growth) MB
1st Time
5000
500
5500
2nd Time
5500
500
6000
3rd Time
6000
500
6500
4th Time
6500
500
7000
5th Time
7000
500
7500
6th Time
7500
500
8000
7th Time
8000
500
8500
8th Time
8500
500
9000
9th Time
9000
500
9500
10th Time
9500
500
10000


Database grows in a controlled manner when Auto Growth is configured with the static/fix value in comparison to Percentage i.e. 10%.  The value I have used here is 20 times lower than what we used to work on production systems generally.   
 

It is true that now a day’s storage costs are lower down for desktops or home systems but it is really very costly when it comes to servers and data centers and this scenario will just add overhead to costing for storage solutions. So, the best bet is to configure Auto Growth with static value.
I would advise to configure Auto Growth option to static value only.

3 comments:

  1. Never imagined this way, very good article.

    ReplyDelete
  2. All my databases are more than 40GB and they have a autogrowth as 10%. I want to change this value. Can you please suggest if instead of going with a 10% increment if I go with a 1GB growth in size?

    ReplyDelete
  3. Hi,

    Autogrowth value can be configured depending on growth pattern/estimated growth and environment. If you haven't started monitoring growth pattern for your database please do it, and do periodic review and then you can conclude what amount would be good. You can use this http://searchsqlserver.techtarget.com/tip/Monitoring-server-disk-space-in-SQL-Server if you do not have one already.

    Cheers!

    ReplyDelete

I appreciate your time, thanks for posting your comment. I will review and reply to your comment as soon as I can.

Thank you
Hemantgiri