Daily obligations in a DBA life can be time-consuming, depending on the type of tasks needed to maintain a SQL Server. Problems that can occur range from SQL Server instance configuration to the actual databases it is facilitating. Allowing these issues to happen and persist can interfere with the overall performance of an environment and take away much needed time that can be spent differently.
ApexSQL Manage is a SQL Server instance management tool that can find and resolve potential problems in a SQL Server environment by performing regular health checks of SQL Server instances as well as discovering SQL Server instances, create instance snapshots and comparison between live SQL Server or snapshots and document SQL Server configuration.
Key areas, where potential issues with database configurations can interfere with SQL Server performance, and ApexSQL Manage can help are as following:
- Full Recovery Mode issues
- Database AUTO_CLOSE issues
- Database AUTO_SHRINK issues
- Database auto growth
- Database capacity
- Database compatibility value
- Database collation
- Databases without owners
- Database files
- Database Virtual log file number value
Full Recovery Mode issues
SQL Server’s Full Recovery Model enables you to recover to a point-in-time and provided complete protection against media failure or disaster incidents. To have a viable disaster recovery plan, it is recommended to set all production databases to full recovery mode. This will ensure a healthy chain of database and transaction log backups. Executing this rule will determine if there are databases on a SQL Server instance that are not set to Full recovery mode and offer a FixSQL script to immediately resolve this issue.
To resolve this issue, check the desired rule from the main grid and click on the FixSQL button of the main ribbon. The tool will instantly analyze which databases are in violation of the rule and generate a TSQL script to correct the issue. Click on the Execute button in the FixSQL window to run the query:
Database AUTO_CLOSE issues
When AUTO_CLOSE is set to ON, the database is closed when the last user disconnects from the database. This can hinder performance on high traffic production database since AUTO_CLOSE flushes procedure cache after each connection and increases overhead on the SQL Server instance when the database is repeatedly opened and closed. Best practice for production environments where a database is accessed regularly by different users is to set AUTO_CLOSE to OFF.
To determine if a database has AUTO_CLOSE set to ON, simply run a database health check on that SQL Server and check if the result of the rule is Failed. If the rule is not in compliance with best practices, check the rule and click on the FixSQL button to create a FixSQL script that will resolve the issue. The created script will be shown in the FixSQL window. To execute the script, click on the Execute button:
Database AUTO_SHRINK issues
Unless necessary, do not set the AUTO_SHRINK option to ON. While this is a tempting option that can save up valuable disk space, it has a drastic set of drawbacks, which in the majority of cases outweigh the positives. Setting AUTO_SHRINK to ON can lead to massive database fragmentation since SQL Server is constantly moving the last page in the database to the first available space. Repeating this process every time will put the pages out of order. Additionally, the read and write operations can cause performance issues on the SQL Server instance.
To determine if a database has AUTO_SHRINK set to ON, simply repeat the steps mentioned above, run a database health check, check if the result of the rule is Failed, and click on the FixSQL button from the main ribbon to fix the issue. Once the FixSQL window is opened it will show which databases are affected, click on the Execute button to run the query:
Database auto growth
Log file(s) and data file(s) must increase size with constant value . Set the FILEGROWTH of your database’s transaction log and data files to use memory units instead of a percentage. Setting the log and data file(s) to grow by a percentage can, through time, result in larger growth increments. This can cause the growth operation to slow down, which in turn can lead to slow database performance. Additionally, Auto Growth is a delicate topic, and best practices depend on the user environment. For example, if a database grows 1GB per day, and Auto Growth is set to 100MB, SQL Server will trigger 10 Auto Growth events, which will drastically impact performance on that SQL Server instance. It is important to monitor the database growth and quantity of inserts to determine the best value for Auto Growth.
To set the proper auto-growth run a database health check, check if the result of the rule is Failed, and to fix the issue, click on the FixSQL button from the main ribbon. Once the FixSQL window is opened, it will show the SQL code, which can be further modified to fit the needs of the user environment. Click on the Execute button to run the query:
Database capacity
This rule demonstrates how to check if the data and log file grow too close to full capacity and how to generate a FixSQL query for this issue. The best practice is to grow a database during maintenance periods, preferably when the database is offline. This will provide adequate time to add enough space and analyze how much space will be needed for the next maintenance period.
To check if database capacity is reaching near full, run a database health check. If the result of the database capacity rule is Fail, check the rule and click on the FixSQL button from the main ribbon. Presented FixSQL script will show the TSQL code, which can be used to grow the database. By default, the growth increment will be around 50%. This can further be modified to fit the needs of the user environment. Click on the Execute button to run the query:
Database compatibility value
Sometimes a database has been restored with a different compatibility value and hasn’t been changed since. To avoid potential performance issues where queries can take longer to execute, it is best practice to check the database compatibility with the compatibility specified version of the SQL Server and change it accordingly. Running this rule will determine which databases on the selected SQL Server instance have a mismatch of compatibility levels and provide a FixSQL to resolve the issue:
Database collation
Differences in database collation and SQL Server collation can cause issues when joining between different databases. To avoid such issues, best practice is to keep the same collation across databases and SQL Servers. Executing this rule will compare the database collations to SQL Server collation and create a FixSQL script with databases that have different collations from the SQL Server instance.
Note: In some cases, this difference is a deliberate configuration of the user environment, and executing this FixSQL script will not provide any benefit.
Databases without owners
This issue can occur when the defined owner, a Windows login, has been removed from the Active Directory group or the local machine. Now, the SQL Server instance has an unknown value, and the database has no owner. To resolve this issue, best practice is to assign a SQL Server Login dedicated to that database. Running this rule will provide a FixSQL with databases which do not have an owner and recommend to set system administrator (sa) as the owner. If the sa login is not considered the best fit for the database owner, it is up to the user preference to assign the appropriate owner:
Database files
It is considered best practice to keep the database files and transaction logs on separate drives. In case of disaster on the primary drive, all database files and logs will be lost, and data recovery will not be a viable option. For this rule, there is no FixSQL script since this is not possible with SQL code alone. Instead, the Violation tab offers advice on how to proceed in this scenario:
When a new database is created, it is recommended to specify separate drives for the data and logs. To move the files after the database is created, the database must be taken offline. Following methods can be used to successfully relocate data and log files:
- Restore the database from backup by using the RESTORE DATABASE statement with the WITH MOVE option
- Detach and then attach the database specifying separate locations for the data and log devices
- Specify a new location by running the ALTER DATABASE statement with the MODIFY FILE option, and then restart the SQL Server instance
Database Virtual log file number value
Virtual log files are used by SQL Servers to internally manage the log file. They are separated into smaller files that contain the log data records. Whenever there is a need for new free space, or the active virtual logs are full, a new one is created. Too many virtual log files can cause transaction log backups to slow down and can additionally slow down database recovery. In extreme cases, this can lead to issues on the SQL Server instance that can affect insert/update/delete performance:
Conclusion
To summarize, executing health check analysis for database category using ApexSQL Manage can provide an easier overview of potential issues in managed SQL environments as well as solutions on how to resolve them and maintain a healthy SQL Server. By using this SQL Server instance management tool for regular analysis and best practice enforcement, issues can be detected before they cause major performance impact.
August 6, 2020
