One of the most important parts of database administration is knowing the state of SQL Server instances. Instance optimization and regular health checks are a time-consuming task that can be made easier using the SQL manage instance tool.
Before starting a full SQL Server instance optimization, a SQL Server instance needs to be added. This process is pretty straightforward, and detailed information on this subject can be found in How to discover SQL Server instances, SSRS, SSAS and SSIS services across the network article.
For the purpose of this article, SQL Server 2019 GA will be used, and it will be connected manually. This can be done in the Inventory tab, under the Add option by clicking on the Add manually button.
On the Connect to SQL Server window, add a SQL Server from the Server drop-down list. Choose a SQL instance and select the Windows authentication method under the Authentication drop-down list.
Click the OK button to proceed to the next step:
For easier management, a folder can be chosen where to add the SQL Server instance. By default, there are three built-in folders:
In our case, the SQL Server will be added to the built-in Production folder:
Additional custom folders that will address specific user environments and scenarios can be created. By clicking the folder button, the Create new folder window will appear, in the box enter a name for a folder and click the OK button:
Once the desired SQL Server is added in the SQL manage instance tool, go to the Health tab and run a health check. Click the Run button, and in the Run health check window under the Servers tab specify on which SQL Server instance to run the performance analysis:
Rules in ApexSQL Manage are divided into several categories which serve to ease SQL Server health check processes and target specific areas for analysis:
All rules are located under the Rules tab of the Run health check window:
Quick tip: Default rules cannot be changed or deleted. This option is only applicable to custom written rules. |
In the Summary tab, an overview of the SQL Server and rules selection are shown:
Upon executing the rules, the Result summary window will be shown, where statistical information regarding health check analysis can be found:
Since SQL Server is added to the Production folder, selecting just the folder will show grid results for all SQL Server located in the folder, given that a health check has previously been performed on said instances.
Selecting just the SQL Server in a folder will only show grid results relating to that SQL Server.
Additionally, after running a health check, the server icon now has a red dot in the left upper corner, which is indicating that some issues were detected with this instance:
Upon performing an instance health check, the SQL manage instance tool shows results in the form of a grid, where a list of all the previously selected rules and detailed information for each of them is shown.
Result grid is divided into several columns:
Detailed information regarding executed rules is divided into 5 tabs:
Detailed information regarding failed rules are divided into tabs and shown in the example below:
Result tab shows information regarding the rule we executed.
Key points here are:
The Description tab shows detailed information regarding the selected rule. Additionally, some rules have external links leading to official Microsoft websites for further information on the subject related to the selected rule:
The Violation tab shows the Severity and Advice. The Severity level for default rules cannot be changed. The Advice message contains recommended actions and external links for additional information on the subject. Advice and external links are dependent on the rule type, and for some rules, only advice will be shown.
The FixSQL tab shows the script which can be executed on the target SQL Server in order to correct the detected issue. Each rule has its own FixSQL script, but in some cases, there is no script, and a manual correction will be required based on the advice shown in the Violation tab.
FAQ:
A: Yes. The export option is located below the Health tab in the Results section of the main ribbon.
Supported export formats are DOCX, PDF, XLSX:
A: Yes. The option to ignore or un-ignore rules is located below the Health tab in the Results section of the main ribbon:
A: Yes. Filter options are located in the Filters section of the Health tab . Individual health check results can be shown for rules that have resulted in Pass, Issues or Ignored:
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center