Why default database Autogrowth settings are bad

File Autogrowth – Never, never use the default database Autogrowth settings. As in the next screen shot, the tempdb default for the primary file is 1MB and the Log is 10%. Allowing these default settings will lead to excessive file fragmentation and impact performance. Better settings for Autogrowth are a minimum growth size of 512MB or 1024MB (data and log settings).

Default File Size and Autogrowth setting for tempdb

clip_image001

Size the database data and log portions to the projected, estimated size.  Sizing your files in advance, will reduce the fragmentation caused by autogrowth. This advice applies to databases other than tempdb as well.

Further, allowing default autogrowth settings for transaction log files will cause VLFs (next).

VLF (Virtual Log Files) – can have a negative effect on database performance. Too many VLFs will slow down the startup, shutdown and read/write performance of any database.

VLFs are created when a transaction log file is allowed to auto-grow. While auto-growth will likely be enabled per database, the transaction log file should already be sized appropriately so it will not need to auto grow.

A general rule of thumb, there should be fewer than 100 VLFs.

For more information on VLFs, reference: https://stevethompsonmvp.wordpress.com/2013/05/14/virtual-log-files-and-impact-on-configmgr-performance/

Advertisements
This entry was posted in ConfigMgr, SQL, SQL Server, SQL Server 2012. Bookmark the permalink.

3 Responses to Why default database Autogrowth settings are bad

  1. Phil Wilcock says:

    Good advice as ever Mr Thompson – one for the Installing SQL boilerplate doc!

  2. Pingback: Installing and configuring SQL Server for Configuration Manager | Steve Thompson [MVP]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s