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.
Never imagined this way, very good article.
ReplyDeleteAll 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?
ReplyDeleteHi,
ReplyDeleteAutogrowth 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!