Last week we discussed how Null Values can cause trouble in Common Mistakes in SQL Server – Part 3. This week in this post, Common Mistakes in SQL Server – Part 4 I would like to draw your attention to a database configuration that is often missed. If this value is not configured with care, it can cause I/O and CPU spikes, and can also cause physical fragmentation. In this part of the blog I will shad some lights on Auto Growth configuration for the database. Configuring Auto Growth in fixed MB is important.
What is Auto Growth?
Auto Growth is a feature that allows database files (primary, secondary, and log) to expand when the database file becomes full – without manual intervention.
Auto Growth feature is handy 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 hereafter) and T-SQL. Auto Growth can be configured – In Percent and Megabytes.
How Auto Growth can impact us?
Now that we know what the Auto Growth feature in SQL Server is; I’ll describe how this feature can impact us badly if we do not configure this option carefully. Below is the table that will give you an idea of what would happen if the database file reaches its threshold value and it needs to expand!!
See the calculation below when the Auto Growth option is configured within Percentage (file size is in GB):
You will notice that the database growth is exponential when it is configured with a Percentage i.e. 10%. This is because this is calculated cumulatively ; 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 disk space unnecessarily, more over it creates fragmentation. Apart from that, when database files expand, you will notice high CPU spikes and I/O cycle volume.
What is the best practice for the Auto Growth option?
The best practice is to configure the Auto Growth option in static value. See the sample calculation below:
The database grows in a controlled manner when Auto Growth is configured with the static/fixed 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.
Nowadays storage costs are indeed lower for desktops or home systems but it is very costly when it comes to servers and data centers and this scenario will just add overhead to the cost of storage solutions. So, the best bet is to configure Auto Growth with a static value.
I would advise configuring the Auto Growth option to static value only. If you want to test the scenario, I have uploaded a few scripts on SQLServerCitation GitRepo.
The post Common Mistakes in SQL Server – Part 4 appeared first on SQL Server Citation.