-
Title
Perform health check analysis on SQL Server instance configuration -
Description
This article describes how to run a health check analysis of SQL Server instance configuration in SQL manage instance tool.
Fine-tuning the performance and configuration of SQL Server instances is a broad topic and the best results are usually determined based on the user environment. To avoid time-consuming troubleshooting there are specific best practices which can eliminate numerous hours of fine-tuning. ApexSQL Manage provides the means to spot the troublesome areas by performing a fast configuration diagnostics and best practices for enforcement.
-
Resolution
Performing a configuration health check analysis on the target SQL instance
To run a configuration check, go to the Health tab and click the Run button.
Selection of SQL instances
Run health check window is comprised of three tabs:
- Servers
- Rules
- Summary
By default, the first tab is Servers. Check SQL instances for the health check, and proceed to the next tab:
For the discovery of new SQL Server instances in ApexSQL Manage, refer to the article How to discover SQL Server instances, SSRS, SSAS and SSIS services across the network
Selection of health check rules
Rules for the health check can be specified in the Rules tab. This selection can be done individually i.e. per rule or by category. For this article, rules from the Configuration category will be checked:
For this example, the following rules will be executed:
- SQL Server memory must be set to tolerable level – This rule checks if SQL Server Memory is less than 2000 megabytes (MB). The best practice is to set the max server memory server configuration option to a value that will ensure that the memory required by applications running with SQL Server is not allocated by SQL Server
- SQL Server auditing login failures should be enabled – This rule checks if SQL Server is auditing login failures
- SQL Server blocked process limit must be set to minimum – Blocked process threshold option is used to specify the threshold, in seconds, at which blocked process reports are generated. This rule checks if the block process limit is greater then the recommended value that is 5
- SQL Server backup folder and database files must be located on different disk drives – This rule checks if the backup folder of the selected SQL Server instance is not on the same disk as other SQL Server binaries. In case of disaster, all backup files will be lost, and further recovery will not be possible
- SQL Server max degree of parallelism value must be 0 – This rule checks if the max degree of parallelism is set to 0
- SQL Server max worker thread must be set to tolerable level – This rule checks if the max worker thread is set to the default value
- SQL Server min and max memory levels must be set and diverge from defaults – This rule checks if the min and max server memory is set to the default settings
- SQL Server remote access must be disabled – This rule checks if SQL Server remote access is enabled. The best practice and official advice from Microsoft is to disable it
Summary of Configuration health check
The Summary tab is used to inspect the final selection of rules before the health check has been run.
Click OK to execute the configuration health check:
Result summary
The Results summary window is shown after every health check. In this window, the results have been composed to provide a graphical representation of the SQL Server health state:
FixSQL
Based on the type of rule and the resulting outcome, FixSQL can be used to fix the detected issue.
Check the failed rule and click on the FixSQL button to inspect the script:
Provided SQL script can be modified to suit the needs of the user environment and saved for future reference.
Click on the Execute button to run the script and resolve the issue:
