Considerations for the
"autogrow" and "autoshrink" settings in SQL Server
The default autogrow and autoshrink settings will
work for you with no tuning on many SQL Server systems. However, there are
environments where you do not have to turn the settings on or where you may
have to adjust the autogrow and autoshrink parameters. This
article gives you some background information to guide you when you select the
settings for your environment.
Here are some things to consider if you decide to tune your autogrow
and autoshrink parameters.
How do I
configure the settings?
1.
You can configure the autogrow and autoshrink
settings by using one of the following:
o An ALTER
DATABASE statement (not available in SQL Server 7.0)
o SQL
Server Management Studio or SQL Enterprise Manager
o The sp_dboption
stored procedure (deprecated in SQL Server 2005)
Note If you
are using SQL Server 2005, use SQL Server Management Studio instead of SQL
Enterprise Manager. For more information about how to set these settings in SQL
Server 2005, visit the following Microsoft Developer Network (MSDN) Web sites:
How to: Add Data or Log Files to a Database (SQL
Server Management Studio)
http://msdn2.microsoft.com/en-us/library/ms189253.aspx
Database Properties (Files Page)
http://msdn2.microsoft.com/en-us/library/ms180254.aspx
http://msdn2.microsoft.com/en-us/library/ms189253.aspx
Database Properties (Files Page)
http://msdn2.microsoft.com/en-us/library/ms180254.aspx
You can also configure the autogrow
option when you create a database.
You can view the current settings through the database properties in SQL Enterprise Manager (SEM). Or, you can run the following Transact-SQL command:
You can view the current settings through the database properties in SQL Enterprise Manager (SEM). Or, you can run the following Transact-SQL command:
sp_helpdb [ [ @dbname= ]
'name' ]
2.
Keep in mind that the autogrow settings are per file.
Therefore, you have to set them in at least two places for each database (one
for the primary data file and one for the primary log file). If you have
multiple data and/or log files, you must set the options on each file. Depending
on your environment, you may end with different settings for each database
file.
What are
the performance implications?
·
If you run a transaction that requires more log space than is
available, and you have turned on the autogrow option for the
transaction log of that database, then the time it takes the transaction to
complete will include the time it takes the transaction log to grow by the
configured amount. If the growth increment is large or there is some other
factor that causes it to take a long time, the query in which you open the
transaction might fail because of a timeout error. The same sort of issue can
result from an autogrow of the data portion of your database. To change your autogrow
configuration, see the "ALTER DATABASE" topic in SQL Server Books
Online.
·
If you run a large transaction that requires the log to grow,
other transactions that require a write to the transaction log will also have
to wait until the grow operation completes.
·
If you combine the autogrow and autoshrink options,
you might create unnecessary overhead. Make sure that the thresholds that
trigger the grow and shrink operations will not cause frequent up and down size
changes. For example, you may run a transaction that causes the transaction log
to grow by 100 MB by the time it commits. Some time after that the autoshrink
starts and shrinks the transaction log by 100 MB. Then, you run the same
transaction and it causes the transaction log to grow by 100 MB again. In that
example, you are creating unnecessary overhead and potentially creating
fragmentation of the log file, either of which can negatively affect
performance.
·
Physical fragmentation from changing the size of the data or log
files can have a severe affect on your performance. This is true whether you
use the automatic settings or whether you manually grow and shrink the files
frequently.
·
If you grow your database by small increments, or if you grow it
and then shrink it, you can end up with disk fragmentation. Disk fragmentation
can cause performance issues in some circumstances. A scenario of small growth
increments can also reduce the performance on your system.
·
In SQL Server 2005 or in later versions, you can enable instant
file initialization. Instant file initialization speeds up file allocations
only for data files. Instant file initialization does not apply to log files.
·
If you have many file growths in your log files, you may have an
excessively large number of virtual log files (VLF). This can lead to
performance problems with database startup/online operations, replication,
mirroring, and change data capture (CDC). Additionally, this can sometimes
cause performance problems with data modifications.
Best
Practices
·
For a managed production system, you must consider autogrow
to be merely a contingency for unexpected growth. Do not manage your data and
log growth on a day-to-day basis with autogrow.
·
You can use alerts or monitoring programs to monitor file sizes
and grow files proactively. This helps you avoid fragmentation and permits you
to shift these maintenance activities to non-peak hours.
·
AutoShrink and autogrow must be
carefully evaluated by a trained Database Administrator (DBA); they must not be
left unmanaged.
·
Your autogrow increment must be large enough to avoid the
performance penalties listed in the previous section. The exact value to use in
your configuration setting and the choice between a percentage growth and a
specific MB size growth depends on many factors in your environment. A general
rule of thumb to you can use for testing is to set your autogrow setting
to about one-eight the size of the file.
·
Turn on the <MAXSIZE> setting for each file to prevent any
one file from growing to a point where it uses up all available disk space.
·
Keep the size of your transactions as small as possible to prevent
unplanned file growth.
Why do I
have to worry about disk space if size settings are automatically controlled?
·
The autogrow setting cannot grow the database size beyond
the limits of the available disk space on the drives for which files are
defined. Therefore, if you rely on the autogrow functionality to size
your databases, you must still independently check your available hard disk
space. The autogrow setting is also limited by the MAXSIZE parameter you
select for each file. To reduce the possibility of running out of space, you
can monitor the Performance Monitor counter SQL Server: Databases Object
:Data File(s) Size (KB) and set up an alert for when the database reaches a
certain size.
·
Unplanned growth of data or log files can take space that other
applications expect to be available and might cause those other applications to
experience problems.
·
The growth increment of your transaction log must be large enough
to stay ahead of the needs of your transaction units. Even with autogrow
turned on, you can receive a message that the transaction log is full, if it
cannot grow fast enough to satisfy the needs of your query.
·
SQL Server does not constantly test for databases that have hit
the configured threshold for autoshrink. Instead, it looks at the
available databases and finds the first one that is configured to autoshrink.
It checks that database and shrinks that database if needed. Then, it waits
several minutes before checking the next database that is configured for autoshrink.
In other words, SQL Server does not check all databases at once and shrink them
all at once. It will work through the databases in a round robin fashion to
stagger the load out over a period of time. Therefore, depending on how many
databases on a particular SQL Server instance you have configured to
autoshrink, it might take several hours from the time the database hits the
threshold until it actually shrinks.
For more information about how to grow and shrink your database
and log files, click the following article numbers to view the articles in the
Microsoft Knowledge Base:
317375 A transaction log grows unexpectedly or becomes full on a computer
that is running SQL Server
949523 The latency of a transactional replication is high in SQL Server
2005 when the value of the "Initial Size" property and the value of
the Autogrowth property are small
For more information about database file initialization, visit the
following Microsoft Developer Network (MSDN) Web site:
For more information about enabling instant file initialization, visit
the following Microsoft Web site:
For more information about transaction log physical architecture,
visit the following MSDN Web site:
No comments:
Post a Comment