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 improve the performance of 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:

 

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).

Part 2 - Open the instance properties

Part 2 – Open the instance properties

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 2 - Memory settings

Part 2 – Memory settings

 

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.

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).

The SQL Server Profiler window will show two tabs, “General” and “Event Selection”. First, in the “General” tab, you should enter a “Trace Name” (7).

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).

Part 4 - Configuring the General settings in SQL Profiler

Part 4 – Configuring the General settings in SQL Profiler

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.

Part 4 - Configuring Events Selection settings in SQL Profiler

Part 4 – Configuring Events Selection settings in SQL Profiler

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).

Part 4 - Filtering the database that you want to work on

Part 4 – Filtering the database that you want to work on

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).

Part 4 - Observe the trace and click on stop after a short while

Part 4 – Observe the trace and click on stop after a short while

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.

Part 4 - Save the trace

Part 4 – Save the trace

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

Part 4 - Open Database Engine Tuning Advisor

Part 4 – Open Database Engine Tuning Advisor

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).

Part 4 - Select the trace to analyse

Part 4 – Select the trace to analyse

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:

Tuning or optimising SQL Server 2019 will allow you to achieve greater performance from both your hardware and operating system. In this tutorial, you have learned how to do this in a very short space of time and with relatively few complications.
We hope that this short guide helps you to improve the performance of this service. However, if you do encounter any issues, please don’t hesitate to contact us so that we can help you.
Thanks for reading!

Categories:Servers

Fill out the form and one of our Sales team will contact you soon.

growth@jotelulu.com  |  jotelulu.com 

You can unsubscribe from these communications at any time. For more information,  check our Privacy Policy.

 

We make the difficult easy

Existing Disaster Recovery tools often require advanced knowledge to manage, demanding expertise that is difficult to acquire.

Jotelulu’s Disaster Recovery aims to make the difficult easy and offers a very simple deployment based on a three-step configuration:

Origin (Primary Site)
Determine the origin location of the subscription on which the Disaster Recovery service will be established.

Destination (Recovery Site)
Set the destination location (availability zone) where you want the Recovery Site to be deployed.

Replication characteristics
Specify the data related to the number of copies to be kept and the frequency at which the replication will be performed.