Autogrowth Events

Frequent autogrowth events can severely impact SQL Server performance, potentially blocking all access to a database while the file grows. It is generally recommended to manage database growth manually by pre-allocating sufficient space. However, having autogrowth enabled acts as a safety net in case your database unexpectedly runs out of space.

Autogrowth events, particularly during peak usage, can degrade performance due to file fragmentation and the overhead associated with dynamically growing files.

Why Autogrowth Events Are a Problem

  • Performance Impact: SQL Server blocks database operations while the autogrowth completes. This can affect all users accessing the database.
  • Fragmentation: Frequent autogrowth events cause file fragmentation, impacting both read and write performance.
  • Unpredictable Growth: Allowing SQL Server to handle file growth automatically can result in inefficient storage usage and increased I/O overhead during peak times.

How to Monitor and Handle Autogrowth Events

Autogrowth events can be monitored using SQL Server traces or by querying the default trace for autogrowth events. Aireforge Advisor checks for recent autogrowth events and flags them as potential performance bottlenecks.

To manually monitor autogrowth events, you can use the following query to track recent occurrences:

DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000)) 
FROM ::fn_trace_getinfo(DEFAULT) 
WHERE traceid = 1 AND property = 2;

-- Rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.', @filename);
SET @ec = CHARINDEX('_', @filename) + 1;
SET @efn = REVERSE(SUBSTRING(@filename, 1, @bc));
SET @bfn = REVERSE(SUBSTRING(@filename, @ec, LEN(@filename)));

-- Set filename without rollover number
SET @filename = @bfn + @efn;

-- Process all trace files for autogrowth events
SELECT 
DB_NAME(ftg.databaseid) AS DatabaseName,
ftg.StartTime, 
te.name AS EventName, 
ftg.Filename, 
(ftg.IntegerData * 8) / 1024.0 AS GrowthMB, 
(ftg.duration / 1000) AS DurMS 
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg 
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id 
WHERE ((ftg.EventClass = 92) OR (ftg.EventClass = 93)) 
AND DB_NAME(ftg.databaseid) = DB_NAME() 
ORDER BY ftg.StartTime;

This query returns the database name, event name, size of the growth in MB, and the duration of the autogrowth event. It helps you identify how often and for how long these events are occurring.

Suggested Actions

  1. Increase File Sizes Proactively: To reduce the frequency of autogrowth events, consider manually increasing the file sizes of your data and log files based on the expected growth rate of your database.
  2. Monitor Growth Patterns: Set up alerts when your data and log files reach a certain percentage of free space (e.g., less than 10%). This allows you to act before an autogrowth event becomes necessary.
  3. Enable Instant File Initialization: For data files (not log files), enabling instant file initialization can reduce the time required to allocate space during autogrowth events, improving performance.
  4. Optimize Autogrowth Settings: Make sure autogrowth is configured to grow in sensible increments rather than as a percentage of the file size, which can lead to unpredictable growth patterns and performance issues.

How to Configure Autogrowth Settings

You can configure autogrowth settings in SQL Server Management Studio (SSMS) by right-clicking on the database, selecting Properties, and navigating to the Files page. From there, you can adjust the file growth settings for both data and log files.

How Aireforge Detects Autogrowth

Aireforge continuously checks for autogrowth events by analyzing SQL Server trace data within a user-defined period (e.g., the past 30 days). If multiple autogrowth events are detected, Aireforge flags them and provides actionable recommendations, such as adjusting file sizes or autogrowth settings. By addressing these flagged events, you can proactively prevent performance degradation and manage database growth efficiently.