Run database Servers faster, more secure and more stable 8 Tips for SQL Server 2019 on Windows Server 2019/2022
SQL Server 2019 is a common database server. In this article, we show eight tips that can be used to make the server faster, more stable and more secure and that make sense to implement on any database server with SQL Server 2019.
The installation of SQL Server 2019 is basically completed quickly. There are a lot of things to consider, and a lot of settings can also be changed during the installation. After the installation, however, further measures are useful to bring the server to a stable state. Below we show eight tips that can be implemented quickly.
Tip 1: Customize the properties of instances in SQL Server Management Studio
You can adjust many settings for an SQL Server instance in the SQL Server Management Studio in the properties of the instance on the left. The settings can be found via the menu item “Properties” via the context menu of the entry. After installation, all settings of an instance should generally be reviewed once.
Tip 2: Adjust the memory of the instances
Via the context menu of an instance on the left side in SQL Server Management Studio, important settings regarding the memory usage of the server can be found with “Properties” and the menu item “Memory”. Here, with “Maximum server memory in MB”, the maximum amount of RAM should be indicated – minus two gigabytes for the operating system. If other applications are still active on the server, storage space should also be deducted for them.
Tip 3: Customize Server Authentication and Login Monitoring after installation
Using the “Security” menu item in the properties of an instance in SQL Server Management Studio, you can specify whether “Windows authentication mode” or “SQL Server and Windows authentication mode” should be used for “Server Authentication”.
By default, “Login Monitoring” only enables the monitoring of incorrect logins. On secure servers, it may be useful to monitor successful logins. However, this is also at the expense of server performance. Therefore, the settings should be well thought out and activated under observation.
Tip 4: Customize Advanced Settings – Parallelism Cost Threshold
The “Advanced” menu item allows you to make options for SQL Server 2019 that apply to all instances and, above all, affect performance. The menu item “Parallelism” is interesting here. The setting “Cost threshold for parallelism” controls when the server should work with parallel plans. This can quickly lead to overhead if too many parallel actions are performed.
For datawarehouses and larger environments, it makes sense to significantly increase this threshold so that the server does not perform too many parallel actions. Instead of the value 5, the value 40 can be recommended here. More about this value can be found on the page “Configuring the server configuration option Cost threshold for parallelism”. In many environments it makes sense to deal with the topic.
In the case of “Max degree of parallelism”, the number of CPU cores that the server should use for this purpose should be selected. If only SQL Server is used on a server, the maximum number of cores can be entered here. In general, however, the value should not exceed the number 8, otherwise the administrative overhead will be too large.
Tip 5: Customize SQL Server Logs
The “Configure” menu item is available via the context menu of “AdministrationSQL Server Logs”. Here you can set the server to limit the number of error log files. The option is not active by default, and the default value is set to 6. It may be useful to activate the option and at the same time set the value to “25-50”. At this point you can also define the size of the log files.
Tip 6: A new error log file every day – sp_cycle_errorlog
If you deposit an automated task via the SQL Server Agent in the SQL Server Management Studio and execute the procedure “sp_cycle_errorlog” every day, the SQL server creates a new error log file every day. In addition to the SQL Server Agent, the procedure itself can also be executed as a query:
EXEC sp_cycle_errorlog ; GO
Better, of course, is the automated execution of the procedure via the SQL Server Agent as a job.
Tip 7: Use Resource Governor
In the SQL Server Management Studio, you can call up the properties of the resource control via the context menu of “Resource Governor”. Here you can specify how many resources individual queries are allowed to use. For “Workload groups for resource pool”, the value “25 percent” is defined for “Memory allocation”. If you increase the value, the performance of smaller systems can be significantly improved, since individual queries can then also retrieve more performance from a server. Values of up to over 90 percent are therefore quite reasonable on smaller servers. However, you have to be careful here that this does not paralyze a server because a query consumes too many resources.
Tip 8: Filtering Objects in the Object Explorer
If there are many objects on a server, it may be useful to set a filter that controls the view using the context menu with “FilterFilter Settings”.
