Perform health check analysis on SQL Server instance databases (4262569)

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: Multiple SQL instances can be targeted for a health check analysis using the same preset of rules

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: