This post can help you to understand the Database Auto Growth Setting Performance Impact in SQL Server. If Auto Growth setting is not taking as a serious option means you are missing a great chance to improve the performance. Well quickly understand few basics and then we’ll see how it impact the performance.

Q. What are the various size/space options available for database files in SQL Server?

Ans:

Initial Size (MB): Indicates the initial reserved space for the database file.

Auto Growth / Max Size:

• Auto Growth: Controls the Auto Growth event. It again offers two options: In Percent || In Megabytes
• Maximum File Size: Defines the space limit. Offers two options: Limited to MB || Unlimited

Q. What is the Auto Growth event and when it occurs?

Ans:

When the Auto Growth property is enabled database Data and Log files are automatically grow when more space is required. How much space it needs to grow is determined based on the setting “In Percent” or “In Megabytes”.

Q. Does enabling “Auto Growth” is a Good or Bad practice?

Ans:

It depends!!! It always depends on multiple things based on your environment, data growth, database traffic etc. Enabling Auto Growth is:

Good Practice When:

• We follow the best practice in setting the growth value. Ex: We have monitored our database usage and MDF file size is increasing approx. 1 GB per week and you kept 1024 MB as Auto Growth setting then Auto Growth event may occur once a week.
• My database is small or middle sized and the database traffic is limited.
• We need not monitor the space usage, database grows when space is required and we have enough room space available to handle the data growth

Bad Practice When:

• We don’t follow the best practice in setting the auto growth value. Ex: Database is growing 500 MB per day and the Auto Growth value is setup to 10 MB. Now can you imagine how many times the Auto Growth event occurs in a given day: 50 Times per day which impact the performance badly.
• We have a critical database where the file space is growing really fast and that requires the frequent Auto Growth events leads to disk fragmentation.
• The instance is handling multiple critical databases and the space really maters Ex: When dealing with huge databases we don’t enable Auto Growth option instead we pre allocate the space based on the usage statistics. Also we need to monitor the space usage and allocate when required.

Q. I still can’t understand what exactly happens when an Auto Growth event occurs. Can you explain it in step by step?

Ans:

It’ll be good if we can take a simple example. Here are the inputs:

• MDF file size: 1024 MB
• Current Usage: 980 MB
• Free Space: 44 MB
• Auto Growth is Enabled: 100 MB

Now there is a new INSERT is started and inserting 1 million rows into a table for which there is 800 MB space required. Since we have only 44 MB free space available there should be an Auto Growth event occurred:

• Auto Growth Event initiated
• On Windows systems when SQL Server needs to write something on disk, first it verifies that the hard disk space is readable. To verify that it writes zeros on the disk space. This process is known as zeroing process.
• SQL Server reserves the space (1024 MB). It may not find the contiguous space which may results into the disk fragmentation.
• Zeroing process is a single threaded operation that means the INSERT statement which initiated this zeroing process should wait till the verification process got completed to reserve the space.
• Auto Growth completed
• INSERT statement executes and insert data (800 MB)

Q. Now I understand how Auto Growth works. Seems like zeroing process plays the tough role, can’t we skip that process?

Ans:

Yes! We can skip the zeroing process for data files with minor security risk. For that we need to enable Instant File Initialization for SQL Server. It skips the zeroing process for data files and reduces the wait time. But for log files it still uses the zeroing process. By enabling Instant File Initialization we can see the performance improvement in:

• Creating a new Database
• Restoring a database from backup file
• Adding space to the data file whether it’s manually or from Auto Growth event
• TEMPDB creation at the time of SQL Server restart

Note: Even when we enable Instant File Initialization SQL Server uses zeroing process for Log File expansion.

Q. Can we check when zeroing process is initiated internally?

Ans:

Yes! Enabling trace flags (3604, 3605) writes zeroing process information into SQL Server error log. If Instant File Initialization is enabled for SQL Server we can’t see zeroing process messages for data file where we can still see zeroing process messages for log files.

Example:

Zeroing D:\XXXX\XXXX\Test.mdf from page 456320 to 456448 (0xded00000 to 0xdee00000)

Zeroing completed on D:\XXXXX\XXXXX\Test.mdf

Q. What are the recommended default sizes for DATA and LOG files?

Ans:

Again it depends on your environment. We can’t determine the optimum values until we know the data growth statistics. For example we can go with these settings: MDF: 1024 MB || LDF: 256 MB

MDF: Install a monitoring mechanism and capture the data file weekly / monthly / quarterly usage. Once we have usage statistics we can pre-allocate the required space.

LDF: Monitor the LDF file usage and pre size it accordingly. While configuring the log file we should consider the recovery model. Also remember this setting can impact the VLF count and size.

Q. Can you take an example and show how File Auto Growth impact the Performance?

Ans:

We’ll take a simple example and see how Auto Growth settings impact the performance in SQL Server. Here are the steps:

With Default Settings

Create a Test database and leaving with the default settings

MDF: Initial File Size: 5 MB || Auto Growth: 1 MB

LDF: Initial File Size: 2 MB || Auto Growth: 10%

Insert Data and Check the Performance

Create a table and insert data (We are using the table [AdventureWorks2014] .SALES.SalesOrderDetail)

USE Test
GO
--It shows the Zeroing process events in output window
DBCC TRACEON(3604,3605)
GO
--Create a Empty Table Structue
SELECT * INTO Test.dbo.Sales FROM [AdventureWorks2014].DBO.SalesTestDetails WHERE 1=2;

--Switch on Table Statistics
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Insert Data
INSERT INTO Test.dbo.Sales(
SalesOrderID,
OrderQty,
ProductID,
UnitPrice,
LineTotal
)
SELECT SalesOrderID,
OrderQty,
ProductID,
UnitPrice,
LineTotal
 FROM
[AdventureWorks2014].DBO.SalesTestDetails; 

Image 2

Observations:

• Auto Growth size is 1 MB and the Table size is 180 MB there of Auto Growth event occurred for 180 times.
• From the above Image 2 you can observe the Zeroing process logged in output window
• Query execution time: 3 Min 21 Sec

With Optimized Settings

Create a Test database and then change the settings to handle the data growth

MDF: Initial File Size: 4096 MB || Auto Growth: 1024 MB || Limited to 60 GB

LDF: Initial File Size: 1024 MB || Auto Growth: 250 MB || Limited to 20 GB

Image 3

Insert Data and Check the Performance

Create a table and insert data (We are using the table [AdventureWorks2014].SALES.SalesOrderDetail). Use the same script used in first part.

Image 4

Observations:

• Initial size is 4 GB and Auto Growth size is set to 1 GB there of no need of Auto Growth events
• From the above Image 4 you can observe that there is no zeroing process occurred.
• Query execution time: 0 Min 37 Sec

Summary:

• From the performance prospect “Auto Growth” setting plays a vital role.
• Do not allow Auto Growth setting to control your database growth on a day-to-day basis.
• Always Pre-Size your database files to handle the data growth
• Monitor and capture the data growth statistics on daily, weekly, monthly and quarterly basis. This allows us to predict the future space requirements and helps us in capacity planning.
• When Auto Growth event occurs on very frequent basis you may need to handle the disk fragmentation
• As you already know always do experiments on Pre-Production before applying it to production instances.