Maintaining a SQL Server environment can be a time-consuming task for every DBA. Problems can occur in various scenarios and each of those scenarios can hinder the overall performance of an environment.
To prevent these problems from occurring in the first place, we need a tool used to manage and perform health checks of SQL Server instances as well as to discover SQL Server instances, document SQL Server configuration, create instance snapshots and comparison between live SQL Server or snapshots. One such SQL Server instance management tool is ApexSQL Manage.
Areas, where potential problems can occur, are as following:
- SQL Server memory issues
- Auditing SQL Server login failures
- SQL Server blocked process limit
- SQL Server backup folder and database files on the same location
- SQL Server max degree of parallelism
- SQL Server max worker threads
- SQL Server min and max memory levels
- SQL Server remote access
SQL Server memory issues
In the majority of cases, performance issues are encountered when SQL Server memory is not set to a tolerable level. Setting the max server memory configuration to a value that will ensure that the memory required by applications running with SQL Server is not allocated by SQL Server. This will lead to a smoother performance of the environment. Keep in mind, if there are multiple SQL Server instances hosted on the same machine, setting the memory of one instance too high can cause a choke point where one SQL Server instance will constantly compete with other SQL Server instances on that machine. On the other hand, setting the memory to a minimum will cause memory pressure as well as performance issues.
To determine the so-called sweet spot, ApexSQL Manage will provide a FixSQL script for this problem which will automatically set the SQL Server memory to a tolerable level.
Check the desired rule and click on the FixSQL button from the main ribbon. This will open the FixSQL window. Click on the Execute button to run the query:
Auditing SQL Server login failures
Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. In various scenarios, auditors request the logs for failed and successful logins in order to make an overall security assessment of a given SQL Server environment. Instead of manually going through and checking each and every SQL Server instance to make sure if all managed SQL instances have the same configuration, ApexSQL Manage will present the results for you where a FixSQL is provided to resolve potential issues.
Check the desired rule and click on the FixSQL button from the main ribbon. Once the FixSQL window is opened, click on the Execute button to run the query:
SQL Server blocked process limit
Blocked process threshold is used to identify if a query has been on stand by and waiting for a resource for too long. The threshold is specified in seconds after which blocked process reports are generated. Queries waiting in line for execution can cause performance issues and the recommended settings are to set your blocked process threshold to a minimum of value 5:
SQL Server backup folder and database files on the same location
In a scenario where the volume that contains the database files fails, restoring from backups will not be possible since the backup is also on the same volume. Recommended action is to set the Backup folder to another hard drive. Instead of checking all managed SQL instances to extrapolate where a potential problem can occur, running this check from ApexSQL Manage will provide a list of results on which instance can this problem occur. Running the FixSQL will provide the user with a choice as to where to relocate the backup folder.
–enter path after = (example = N’H:\1′)
Set @BackupDirectory = N’H:\1′ – write the desired backup path here
Upon the desired changes have been made to the FixSQL, all that is left is to run the query:
SQL Server max degree of parallelism
When an instance of SQL Server runs on a machine that has more than one CPU, it will detect the degree of parallelism, e.g. the number of processors used to run a single statement, for each parallel plan execution. Max degree of parallelism option can be used to limit the number of processors SQL Server can use in parallel plan execution or allow a no limit depending on the environment.
SQL Server max worker threads
The max worker threads option is used to set the number of worker threads available to Microsoft SQL Server processes. It is always best to set it to its default value or to a value that’s neither too small, which will cause SQL Server to stop responding, nor too big which wastes too much space. However, depending on the SQL Server environment, setting max worker threads to a specific value sometimes improves performance:
For detailed information on max worker threads, see Configure the max worker threads Server Configuration Option
SQL Server min and max memory levels
SQL Server min and max memory levels must be set to differ from the default values. The default setting for min server memory is 0, and the default setting for max server memory is 2,147,483,647 megabytes (MB). By default, SQL Server can change its memory requirements dynamically based on available system resources. Changing this value from the default will prevent a potential resource confrontation between different SQL Server instances on the same machine end eliminate performance issues:
SQL Server remote access
The remote access option controls the execution of stored procedures from local or remote servers on which instances of SQL Server are running. This default value for this option is 1. This grants permission to run local stored procedures from remote servers or remote stored procedures from the local server which can cause a security risk. To prevent local stored procedures from being executed from a remote server or remote stored procedures from being run on the local server, set the option to 0:
Conclusion
In summary, executing configuration health check analysis of SQL Server instances in ApexSQL Manage provides an easier overview of a managed SQL environment. Main benefits are easy steps to resolve the issue and a centralized overview of all SQL Server instances. This SQL Server instance management tool easily enforces best practices by reviewing and detecting issues before they even occur.
July 24, 2020
