Search
Close this search box.

Common Mistakes in SQL Server – Part 4

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.

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.

004-1_AutoGrowth

004-2_Audting_AutoGrowth

004-3-FixAutoGrowthSetting


Leave a Reply

Your email address will not be published. Required fields are marked *

Picture of Hemantgiri Goswami

Hemantgiri Goswami

Throughout my extensive 24-year tenure in the IT industry, I have honed my expertise in SQL Server and cloud technologies. My qualifications include certifications in ITIL, Azure, and Google Cloud, and my professional journey boasts a consistent record of delivering top-notch, dependable, and efficient solutions across diverse clients and domains. In recognition of my dedication and impact, I am honored to have received the Microsoft MVP award for SQL Server on six occasions. Additionally, I actively contribute to various online forums and blogs, acting as a moderator and facilitator of meaningful discussions. My ultimate mission revolves around empowering organizations to enhance the reliability and efficiency of their SQL Server implementations while fostering a culture of continuous learning and growth within the SQL Server community.