In this best practice, the default setting for autogrow is modified for both data files and TempDB. The goal of this recommendation is to tune autogrow to address database space requirements while minimizing the impact on transactions when the database increases the size of a data file or TempDB. Show Category SQL Server 2019 Enterprise Product SQL Server Type of best practice Performance Optimization Day and value Day 3, Fine Tuning Overview In this best practice we modify the default values for both data file and TempDB log to optimize how the database adds space and minimize the impact on transactions. The autogrow default values are as follows:
Database transactions that trigger the autogrowth of log space, along with other transactions using the log space, must wait until for the log files to grow to the configured amount. As each database is different, administrators must determine the proper autogrow value for their specific databases. To validate autogrow best practices, we implemented the following changes to our SQL Server databases:
Recommendation Setting data files and TempDB autogrow to 1 GB did not significantly increase performance for the following metrics:
Although configuring autogrow to 1 GB did not significantly improve performance, this best practice is an important consideration for databases. We recommend that database administrators review the autogrow setting for databases as part of a Day 3, fine tuning exercise. Implementation Steps Perform the following command to modify data files and TempDB autogrow to 1. USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = '/mnt/temp_data/tempdb.mdf', SIZE = 8192MB, FILEGROWTH = 1048576KB); It is essential for the DBA to need to ensure the SQL Server database performance. Performance tuning is an open-ended task, and you need to ensure the monitoring of various database parameters. Overview of Default trace in SQL ServerStarting from SQL Server 2005, SQL Server captures a few critical events in the default traces. The default trace is enabled on each SQL Server instance. It is a lightweight trace consisting of five trace files in the installation directory of SQL Server. We can check the default trace configuration using the following query. SELECT* FROM sys.configurations WHERE configuration_id \= 1568 If the default trace is not running, we can use sp_configure commands to enable it using the following query. sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'default trace enabled', 1; GO RECONFIGURE; GO In the default trace, SQL Server calculates following auto grow and shrink events for the SQL Server database.
Once we create a SQL Server database, we define auto growth for each data and log file. SQL Server expands the size of a database (data and log file) based on the auto growth setting of an individual file to avoid space issues in the existing transactions. Once an auto-growth event occurs, SQL Server holds up transaction processing for the time being. We should monitor and manage the auto growth setting for a database. If the auto-growth event takes a little longer time, it might influence database performance and availability. In case of any auto-growth event, SQL Server looks for additional space in the disk where the data or log file exists. If we have a very frequent auto-growth event, it causes physical fragmentation as well. The physical fragmented database takes a long time to complete the transactions. We should take action to minimize the auto-growth events for highly transactional databases. By default, Auto Growth is set to grow 10% percent of existing file size in the SQL Server database. It might be suitable for small databases, however, if you look at the perspective of a large database file, it is not the right configuration. Let’s look at the following examples. In example 1, SQL Server needs to expand the data file by 100 GB while in example 2, SQL Server expands data file by 10 GB. It will take a long time to expand the data file by 100 GB, and it might put additional load on the database as well. Example 1 Example 2 Database File Size – 1 TB Database File Size – 100 GB Auto Growth – By 10% Auto Growth – By 10% Auto Growth File size increment: 100 GB Auto Growth File size increment: 10 GB We should take the following approach to avoid auto-growth events.
Suppose we set the data file auto growth to 512 MB instead of a 10% setting. In the following table, we can see that auto growth does not require any dependency on the data file size if we use fixed MB growth. Example 1 Example 2 Database File Size – 1 TB Database File Size – 100 GB Auto Growth – By 512 MB Auto Growth – By 512 MB Auto Growth File size increment: 512 MB Auto Growth File size increment: 512 MB We might come across a situation where the database files have sufficient free space, but the disk does not meet the free space threshold (assume we maintain a 20% free disk space threshold). Many times, DBA executes DBCC SHRINKDATABASE command to shrink database and return free space to the disk. We should not shrink a database unless it is very much required to do so. Some of the issues you might face due to shrinking databases are as follows:
Different ways to check Auto Growth and Shrink events for SQL Server databaseAt this point, you should be aware that it is vital for a DBA to monitor the auto growth and database shrink events. It is especially vital for the high transactional database. In this section, we will identify different ways to get details of these events. SSMS Disk Usage ReportConnect to a SQL instance and right-click on a database for which we want to get details of Auto Growth and Shrink Events. Go to Reports -> Standard Reports and Disk Usage It opens the disk usage report of the specified database. In this disk usage report, we get the details of the data file and log file space usage. In the following screenshot, we get a message – No entry found for autogrow/auto shrink event for SQLShackDemo database in the trace log. It shows no auto growth and auto shrink event occurred in the trace log for our SQL Server databases. You should note that as per the message it indicates that only auto events are captured, but default trace captures events that occurred due to executing shrink command. Let’s shrink the transaction log file of SQLShackDemo database using the following DBCC SHRINKFILE command. USE [SQLShackDemo] GO DBCC SHRINKFILE (N'SQLShackDemo_log' , 0, TRUNCATEONLY) GO Rerun the disk usage report, and it shows the event for this. You may notice that it shows the event name Log File Auto Growth. Let’s execute a workload on SQLShackDemo database, and it should cause data and log file growth. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 CREATE TABLE Temp (ID INT, Name CHAR(8000) ); GO SET NOCOUNT ON; GO DECLARE @i INT; SET @i \= 1; WHILE @i < 10000 BEGIN INSERT INTO Temp VALUES (1, 'Rajendra' ); SET @i \= @i + 1; END; Once query execution is completed, rerun the disk usage report. In the following screenshot, you can see data and log file auto-growth events. You need to notice and monitor the frequency of auto-growth along with the duration (ms) for the SQL Server database. It gives you the necessary data to correlated performance issues with auto events and time is taken in completing the request. If the duration is more even for a small increment, you should also consult your storage team for disk-related performance issues. Scripts to check auto growth events in SQL Server database:Let’s first execute the script and get the details. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 DECLARE @current_tracefilename VARCHAR(500); DECLARE @0_tracefilename VARCHAR(500); DECLARE @indx INT; SELECT @current_tracefilename \= path FROM sys.traces WHERE is_default \= 1; SET @current_tracefilename \= REVERSE(@current_tracefilename); SELECT @indx \= PATINDEX('%\%', @current_tracefilename); SET @current_tracefilename \= REVERSE(@current_tracefilename); SET @0_tracefilename \= LEFT(@current_tracefilename, LEN(@current_tracefilename) - @indx) + '\log.trc'; SELECT DatabaseName, te.name, Filename, CONVERT(DECIMAL(10, 3), Duration / 1000000e0) AS TimeTakenSeconds, StartTime, EndTime, (IntegerData * 8.0 / 1024) AS 'ChangeInSize MB', ApplicationName, HostName, LoginName FROM ::fn_trace_gettable(@0_tracefilename, DEFAULT) t INNER JOIN sys.trace_events AS te ON t.EventClass \= te.trace_event_id WHERE(trace_event_id \>= 92 AND trace_event_id <= 95) ORDER BY t.StartTime; We get the following output, and it shows useful information for all file grow events. We also get the application name, hostname and login name that caused this file size growth. Let’s understand the query quickly.
Find-DbaDbGrowthEvent DBATools CommandWe can also use DBATools PowerShell command to get details of auto-growth events in the SQL Server database. It reads the default trace and provides us with the information for the auto- growth the event. The DBATools command to check auto growth events is Find-DbaDbGrowthEvent. Let’s check the syntax, synopsis and description of Find-DbaDbGrowthEvent command. \> get-help Find-DbaDbGrowthEvent Find-DbaDbGrowthEvent command checks for the AutoGrow events in the Default Trace for all databases. It includes the following events.
Let’s execute the command for my SQL instance and get the output in a grid format. \> Find-DbaDbGrowthEvent -SqlInstance Kashish\SQL2019CTP | Out-GridView It opens a new interactive output window, and we can see all auto growth events in this. ConclusionIn this article, we explored the overview of auto growth and shrink activity in the SQL Server database. We also learned a different way to extract the growth events details from the default trace of SQL Server. If you have any comments or questions, feel free to leave them in the comments below. Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience. I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines. I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020. |