Applies to
Summary
This article describes how to run a health check analysis of SQL Server databases in SQL manage instance tool
Description
There are several ways to configure and fine-tune the performance of SQL Server instances, and the best approach/results are determined by the user environment and overall resources at their disposal. In terms of best practices, ApexSQL Manage provides the means for a fast health diagnostics with an indication of key areas for improvement. One such area is the SQL Server database configuration.
Configuring database health check analysis on the target SQL instance
To initialize this process, go to the Health tab in the main ribbon and click the Run button:
SQL instance selection
In the Run health check window, on the Servers tab, check the desired SQL instance on which the databases are located and proceed to the next tab:
|
Quick tip:
|
If the desired SQL instance is not registered in the tool, please refer to the article How to discover SQL Server instances, SSRS, SSAS and SSIS services across the network for detailed instructions on how to discover and register SQL instances in ApexSQL Manage.
Health check rules selection
In the Rules tab, check the desired rules for the health check analysis. Rules can be checked individually or by category:
For this example, the following rules will be executed:
- Database Full Recovery Mode must be enabled – This rule checks if the databases located on the SQL Server instance are set to Full recovery. If a database recovery model is not set to Full, it can jeopardize the disaster recovery plan
- Database AUTO_CLOSE should be disabled – This rule checks if the AUTO_CLOSE option is set to ON. Leaving this option ON will result in a constant loop of closing and opening of the database whenever the last user disconnects from the database, and the next one connects which will in term result to a performance drop
- Database AUTO_SHRINK should be disabled – This rule checks if the AUTO_SHRINK option is set to ON. Setting it to ON can waste database resources, cause major fragmentation and impact the overall database performance
- Database auto growth – This rule checks if FILEGROWTH is using percentage instead of memory units. When log and data file(s) are set to grow by a percentage, in time and depending on the database size, they will result in larger and larger growth increments. The ever increasing growth operation will, in turn, lead to slow database performance
- Database capacity – This rule checks if the data and log file have grown too close to full capacity
- Database compatibility value – This rule checks if the database compatibility is matched with the compatibility configured on the SQL Server instance
- Database collation – This rule checks if the database collations match the collation configured on the SQL instance
- Databases without owners – This rule checks if there are databases without owners
- Database files – This rule checks if the database and transaction logs files are set on the primary drive. If they are, this scenario is jeopardizing the main disaster recovery plan
- Database Virtual log file number value – This rule checks if there are too many virtual log files used by the SQL instance
Health check configuration summary
Click the Summary tab to inspect the final selection of targeted SQL Server instances and rules for the health check analysis. Click OK to execute the database health check according to the previously configured settings:
Health check results
Upon the health check execution, the Results summary window will be shown. This window provides a graphical representation of the SQL Server instance health state:
FixSQL
The majority of rules have a FixSQL script that provides an easy solution for detected issues. Rules which do not have a FixSQL script provide adequate advice for the scenario in question. To inspect a FixSQL for a rule that has resulted in Fail, select the rule and click the FixSQL button:
The provided solution, in the form of a SQL script, can be further modified to suit the user environment, be saved for future execution or executed immediately on the target SQL instance. To execute the script, click the Execute button:
