Assess performance-related database configuration parameters
In this we will learn how to assess performance-related database configuration parameters.
Set the AUTO_CLOSE Database Option to OFF
This rule checks whether the AUTO_ CLOSE option is set OFF. When AUTO_CLOSE is set ON. However, this option can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection.
Considerations for the autogrow and autoshrink settings in SQL Server
The default autogrow and autoshrink settings are appropriate on many SQL Server systems. However, there are environments where you may have to adjust the autogrow and autoshrink parameters.
How do I configure the settings
- You can configure or modify the autogrow and autoshrink settings by using one of the following:
- Firstly, SQL Server Management Studio
- Secondly, an ALTER DATABASE statement
- Use File and Filegroup options to modify auto growth settings
- Use SET options to configure AUTO_SHRINK settings.
You can also configure the autogrow option when you create a database. To view the current settings, run the following Transact-SQL command:
SQL
sp_helpdb [ [ @dbname= ] ‘name’ ]
- Lastly, keep in mind that the autogrow settings are per file. Therefore, you have to set them in at least two places for each database. 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.
Considerations for AUTO_SHRINK
AUTO_SHRINK is a database option in SQL Server. When you enable this option for a database, this database becomes eligible for shrinking by a background task. This background task evaluates all databases that satisfy the criteria for Shrinking and shrink the data or log files.
however, you have to carefully evaluate setting this option for the databases in a SQL Server instance. Frequent grow and shrink operations can lead to various performance problems.
- Firstly, if multiple databases undergo frequent shrink and grow operations, then this will easily lead to file system level fragmentation. This can have a severe impact on performance. This is true whether you use the automatic settings or whether you manually grow and shrink the files frequently
- Secondly, after AUTO_SHRINK successfully shrinks the data or log file, a subsequent DML or DDL operation can slow down significantly if space is required and the files need to grow.
- Thirdly, the AUTO_SHRINK background task can take up resources when there are many databases that need shrinking.
- Lastly, the AUTO_SHRINK background task will need to acquire locks and other synchronization that can conflict with other regular application activity.
Considerations for AUTOGROW
- Firstly, 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.
- Secondly, 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.
- Lastly, 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
Reference: Microsoft Documentation, Documentation 2