Paul van Oordt  SQL Services

Freelance SQL Server specialist and troubleshooter, Utrecht / Antwerp

+31 627400408 - WhatsApp -
LinkedIn - curriculum vitae - Nederlands

What to know about SQL Server performance

Performance problems are well known in database systems. It is very common for an application to initially work well and then become slower, sometimes gradually, sometimes dramatically with many timeouts or deadlocks. This leads to dissatisfied users and rising costs for hardware, licenses or cloud contracts. Whether you experience the problem now, or you are trying to avoid it, the information on this page can help you make SQL Server run well.

Regarding my services, I have tuned and troubleshooted worked on hundreds of systems. I always work closely with the customer's employees and my intention is to help you swiftly. Knowledge transfer is key. I have no interest in staying for a long time. Typically an assignment takes a single day, sometimes a bit more. I deliver a report with findings and recommendations that gets you going in performance improvement.

Below I will give some general findings about SQL Server performance, and then some possible actions to improve performance. But first some remarks on ..

SQL Server performance on a physical or virtual box and in the cloud (IaaS, PaaS)

Almost everything in the following sections applies to SQL Server on a physical or virtual machine, on premise or with an external party, or on infrastructure as a service in the cloud. Most of it also applies to platform as a service Azure SQL Database. A big difference between physical and virtual systems is of course the ease of scaling up. That is why a performance problem often presents itself differently. On a physical system there may be an interruption of service where on virtual servers, and certainly in the cloud, this can often be prevented by adjusting the resources and paying the corresponding bills. Cloud computing offers many benefits, but may be expensive. Looking at my experience with SQL Server workloads and resources, most systems can down scale considerably with just a few days of tuning. A good performance scan is the first step and an excellent investment.

General findings about SQL Server performance

Poor performance can have all kinds of causes. I divide them into three categories, roughly according to decreasing impact.

  1. the way SQL Server is used, the data model and the code
  2. the indexing of tables
  3. the platform

With this I mean that by adding extra memory or CPUs (platform) you might get the system a few times faster. Properly indexing a problem query often produces a gain of 10 to 100 times as fast. And it is no exception that rewriting bad code leads to a factor 1000 faster code. This is all very approximate of course, but it is what emerges from the hundreds of systems that I have worked on. And yes, there are always multiple factors involved, interacting in all sorts of ways.

The use of SQL Server

So, the biggest performance problems are usually caused by incorrect programming (querying) of SQL Server. There are many ways to do it wrong. See my recommendations for SQL Server development for good programming of SQL Server. If you follow those recommendations you will get code that is understandable and easy to maintain and that usually performs well too and keeps doing that. Good code is scalable code.

Some of the ways to do it wrong are:

Again, to see how it's done well: ✓ recommendations for SQL Server development.

Indexing of tables

Individual queries that are well written can still be slow, especially when querying large tables. These queries can often be tuned by creating the right indexes. With good indexing, the size of a table doesn't really matter, and vice versa, the larger the table, the more important good indexing, and the bigger the performance penalty if it isn't.

Indexing tables by hand requires a thorough understanding of how the SQL Server engine works. This gives the best results, because all index features can be included: clustered and non-clustered, filtered, included columns, the fill factor, column store indexes and indexed views. (There are also XML and spatial indexes.)

SQL Server also makes index suggestions. You will find these in the query plan, in the missing index statistics and via the Database Engine Tuning Advisor. Never just create an index without looking at what is already there. SQL Server does not prevent you from creating redundant or even identical indexes. It is often the case that an existing index can be adjusted by adding key or included columns. Also know that sometimes dubious indexes are suggested, such as on non-selective columns, that some missing indexes are never found, for instance when a trivial plan is used, and that the estimated performance gain is sometimes very much off.

The platform

You will want to have some basic information about the platform, at least about the following three resources: CPU, memory and I/O.

If the platform suffers form memory shortage, you can of course add extra memory, but if you have not looked carefully at the way in which SQL Server is used and at the indexing of tables, it will only give limited results. There is also a chance that you will purchase additional core licenses, or use a more expensive cloud version, while rewriting or tuning some queries solves the problem better, cheaper and more sustainable.

Note that is possible that a system has abundant resources and is still slow. This happens in case of excessive blocking, but also if parallelism is not used properly.

Addressing SQL Server performance issues

Performance problems can be so complicated, and the toolkit is so extensive that I am not trying to give a general recipe. I will talk about a couple of indicators and actions that may be part of the tuning process.

The workload

It is important to know what workload (queries) cause a slow system, and whether the slowness is mainly the result of high use of resources such as CPU, or of blocking.

You can find a snapshot of current activity most easily by using the sp_who2 procedure. It shows the resources used per connection, such as CPU and I/O, and also any blocking.

An overall view can be seen through the dynamic management view sys.dm_os_wait_stats. Showing for example whether there are CPU queues (SOS_SCHEDULER_YIELD, and a high signal_wait_time), whether there is a lot of blocking (look at both the number, duration and maximum duration of all waits with type LCK_M_ x ), whether there are memory or I/O issues (PAGEIOLATCH_SH, PAGEIOLATCH_EX). This gives a very global picture of the state of the SQL Server since the last restart. Note that these numbers can be strongly influenced by, for example, (maintenance) tasks that run outside of production hours. If in doubt, use a script that returns the values for a single hour, or even better, for a single query.

Query tuning

Queries that take a long time to complete or take a lot of resources can be found in the Activity Monitor, in the procedure cache or in the Query Store (which you must first enable for this). You can replay those queries in the SQL Server Management Studio, and tune them by rewriting, adding indexes etcetera. It may be important that the connection settings are equal to those in the production environment.

Analyze and tune the most expensive queries. See if the code is written according to the principles in recommendations for SQL Server development. If that is the case, or there is (currently) no possibility to rewrite the code, view the query plan. Pay particular attention to:

Tuning the platform

You will want to do a couple of things at the platform level, even though the result is usually less dramatic. I give some quick checks and actions here.


(c) Paul van Oordt, Last modified: 28 December 2022