How to Improve the Performance of SQL Server 2019

In this tutorial, you will learn how to optimise the configuration of your SQL Server in order to get the very best performance from your system and hardware.

A common problem with business databases is a lack of optimisation. They are often installed by simply clicking “Next” repeatedly, without taking the time to consider what the best settings might be to achieve optimal performance. Over time, performance worsens and the workload placed on the system increases until eventually, the database is practically unusable.

This can be a serious problem for daily operations or data recovery in the case of a system failure.

In this tutorial, you are going to learn about some of the simple things you can do to improve the performance of your database server.

How to improve the performance of SQL Server 2019

To successfully complete this tutorial and optimise SQL Server, you will need:

• To be registered with an organisation on Jotelulu and have logged in.
• To have registered for a Servers subscription.
• To have a Windows server deployed on your subscription.
• To have SQL Server 2019 installed on the server.

Part 1 – Things to consider before installation

Whilst this tutorial assumes that you have already installed your database engine, we are still going to make some recommendations that you might want to consider before installing SQL Server.

Firstly and most importantly, this is an extremely important service and you should carefully plan how and where it is installed. We recommend taking the time to think about where the execution binaries will be installed, where your database files will be saved, where your logs will be installed, how you are going to make security copies and how frequently, what server resources you require, etc.

In particular, you should consider where you are going to save temporary files, database files and logs. These files are going to comprise the bulk of the workload for your server, with a great deal of read and write activity. As a result, we recommend always keeping them on a secondary disk separate from the operating system. Otherwise, these files could easily put excessive strain on the system.

Depending on how the database will be used, you may also want to consider using high-performance disks to avoid bottlenecks during normal operation.

Also, even though it’s not strictly a performance issue, you should also consider your user management strategy, both for operators and administrators.

Part 2 – Setting RAM thresholds

The first way to improve your SQL Server performance is by checking the amount of memory available.

This is a recurring issue when using SQL Server since these types of applications tend to be very memory-hungry and often use up all the available RAM. This means that it is best to set some upper and lower limits to avoid performance issues.

If you do not do this, SQL Server could end up saturating the member and affecting other applications or even the operating system itself.

To change the RAM settings, launch the Microsoft SQL Server Management Server (SSMS).

Then, right-click on the instance name (1) and select “Properties” (2).

Next, click on “Memory” in the left-hand menu (3).

Here there are two sections:

• Server memory.
• Other memory options.

With regard to the server memory (4), we recommend setting a minimum value, which will depend on how the database is to be used. As for the maximum value, you should be sure to leave a certain % of memory available for the operating system and other applications so that they can operate without any issues.

This is a very important point as the maximum memory shown in the screenshot is far greater than the memory we have available on the server. I tend to leave around 2GB free for the operating system on smaller installations, but it would be a good idea to leave at least 4GB. If you have a 16GB system, setting the maximum limit to 10GB of RAM for the database would leave 6GB for the operating system and other applications.

With respect to other memory options (5), you can leave the default settings as they are.

Part 3 – Optimising SQL queries

SQL queries are another factor that can impact database performance. A query can be made in different ways, so it is important to think about the most efficient way to get the best performance out of your database.

A poorly generated query can lead to greater usage of system resources, but a large number of poorly generated queries can be disastrous for the system.

Here are some tips for writing SQL queries:

• Specify the schema/owner so that the database engine does not have to check it.
• Limit the use of DISTINCT, UNION, ORDER BY and GROUP BY operators unless they are absolutely necessary.
• Wherever possible, always delete the “*” operator and instead indicate the columns that you are interested in.
• Use column_list for SELECT and INSERT expressions to help optimise processes and avoid potential errors that might lead to poor use of resources.
• Wherever possible, reduce the transaction size to avoid generating an excessive workload that could lead to errors or rollbacks.

There is a lot of documentation available about optimising queries which we recommend reading thoroughly.

Part 4 – Using SQL Server Tuning Advisor

The final measure that we recommend for optimising SQL Server 2019 is to use Tuning Advisor. This is a tool included in the paid and Developer versions of SQL Server 2019 but is not available in the Express version.

This tool offers a way of reviewing the status of your database and making improvements. The process is not very complicated, but you will need to perform a series of steps in a specific order to get the most out of it.

NOTE: It is important that nobody else is using this trace application as this could create problems and generate false results.

First, launch SSMS (SQL Server Management Studio) and enter the instance you wish to connect to, your username and password. Then, go to the “Tools” menu and select “SQL Profiler” (6).

Next to “Use the template” (8), there is a drop-down menu using which you can select different profiles. For this tutorial, we will select “Tuning” as we are looking to improve performance.

Next, click on the “Events Selection” tab (9).

In the “Events Selection” tab, check that everything has been selected (10) to get the most information possible and then click on “Column Filters” (11) to access additional settings.

In the Edit Filter window, select “DatabaseName” (12) and under “Like”, enter the name of the database (13) that you want to work with.

Next, click on “Ok” (14) to save your changes and click on “Run” (15).

You will now see a message saying “Trace start” (16) and the program will now execute the necessary processes to measure the database performance.

After a while, you will need to stop the process to retrieve the data. To do this, click on “Stop” (17).

At this point, you should save the trace in order to use it. Click on “File > Save as > Trace File” (18), select a destination folder and save the file. It will be stored as a .trc (trace) file.

Next, click on “Tools > Database Engine Tuning Advisor” (19).

Here, you should enter a Session name (20), select the trace file that you just saved (21), and select the database to analyse (22) and (23).

Next, analyse the trace that you just saved by clicking on “Actions > Start Analysis” (24).

At this point, the analysis will begin and SQL Tuning Advisor will display a list of recommendations in accordance with Microsoft best practices for databases and specifically for SQL Server 2019 (v 15.0).

As well as this report, it will also show an estimate of the expected improvement if you make the recommended changes.

Conclusions and next steps: