Run SQL scripts to fix SQL Server health check issues (4262735)

SQL Server instance health issues can be difficult to pinpoint and fix. The process can be long and tiresome, from analyzing the state of the SQL Server instance, summarizing the issues, and most importantly, to writing SQL scripts in order to resolve the issues.

This article describes the most important part, how to run the predefined FixSQL scripts and fix SQL Server health check issues in ApexSQL Manage.

Best practices for SQL Server configuration is a vast subject that heavily depends on the specific needs of a SQL Server and the user-environment. This article is meant to address the topic of common issues in SQL Server configuration that can hinder performance, pose potential security risks and indicate a lack of proper database maintenance.

Before jumping to FixSQL execution, a health check must be performed on the desired SQL Server, detailed instructions on this process can be found in How to perform health checks on SQL Server instances with ApexSQL Manage article.

Once the health check has been performed and the results are shown in the main grid, they can be sorted by the Result column for easier management:

Alternatively, the health check results can be grouped by dragging the Result column to the sorting area:

Grouping by columns provides an easier overview of the issues that need attending to:

In this article, the rule SQL Server max degree of parallelism value must be 0 from the Configuration category has been chosen as an example.

Max degree of parallelism (MAXDOP) controls the number of processors that are used in a parallel plan for the execution of a query. Configuring MAXDOP to the value of 0 will allow the SQL Server to utilize all available CPUs cores to determine the best degree of parallelism. Optimum MAXDOP settings can vary depending on the user environment and specific needs of the SQL Server instance. In certain cases, configuring MAXDOP to the value of 0 is not the best option. Additional, in-depth information on the subject of MAXDOP can be found in official Microsoft documentation Configure the max degree of parallelism Server Configuration Option.

Before executing the FixSQL for MAXDOP, the settings in SQL Server were configured to the value of 4:

To execute a FixSQL script, first select the desired rule that has failed the health check and click on the FixSQL button:

This action will prompt the FixSQL window to open and clicking on the Execute button will correct the detected issue:

When the rule is executed successfully on the SQL Server instance, a message of the execution procedure will be shown with information on the steps taken:

Now that the MAXDOP FixSQL is executed, the settings of the SQL Server instance are configured to the value of 0:

Since MAXDOP settings have changed, running the health check again will show the rule result as Pass:

Additionally, FixSQL can be saved into .txt file for future reference by clicking on the Save As button and saving the file: