Quick tips for SQL Server performance issues during consulting

As a software developer, have you experienced the following issues when troubleshooting SQL Server performance issues?

1. There are overwhelming number of articles on the internet to troubleshoot my problem.
2. I am not great at SQL Server, I do not need to deep dive each topic.

If you answered yes to the above, this blog is for you. It will provide quick recipes for common performance issues that you might experience.

During our job interviews, we have been asked about the difference between clustered index and non-clustered index. We rarely see someone apply this to action in real world.

Clustered index is much more important than non-clustered index. Please consider fixing clustered index in prior to non-clustered index. SQL Server defaults the primary key as clustered index, which is inappropriate most of the time.

Below is a good example that you can apply clustered index

1. Supermarket branch locations in Online Order table

In this example, there might have thousands of orders, but probably only 20 branches. In this ratio, branch location is the best candidate for clustered index. It is another way of horizontal partitioning. More importantly, please modify the query to include searching by branch location in the predicate. For example

SELECT ColA FROM OnlineOrder WHERE BranchLocation=@BranchLocation AND …

Each branch location is not interfered with each others. Hence the chance of blocking and deadlocking can be reduced.

There are many cases, where converting multiple columns non-clustered index to “included columns” in non-clustered index will benefit. However, if you need to include many columns in non-clustered index. Please reconsider whether clustered index is appropriate. Clustered index can be viewed as an index to include all the columns.

This is a no-brainer change. For columns that have a high percentage of NULLs, we can change the column to sparse column. Please don’t underestimate the performance gain as shown here.

We all have some basic ideas about how deadlock occurs. However, we might not know some quick ways to resolve them. Using correct clustered index as mentioned above is a good way to remove deadlock because the table is horizontally partitioned. Details can be found here.

Another technique is to delete / update the record one by one instead of batches. A good example is when we are archiving old records. Instead of:

DELETE FROM OrderTable WHERE OrderDate < DATEADD(month, 2, GETDATE()) 

We can correct it to run the following inside a while loop:

WHILE condition
BEGIN
               --Make sure only 1 record is deleted             
               DELETE FROM OrderTable WHERE OrderId=@OrderId    
END

You might even consider using Delayed Durability if you are archiving old records for performance.

SELECT ColA FROM TableA WITH (NOLOCK)

Looks familiar, right? The NOLOCK is used to prevent blocking between reading the record and updating the record. You can apply Read committed snapshot isolation or Snapshot isolation. I have applied Read Committed snapshot for few clients without drama. However, testing is required to prevent some edge case scenarios. Details can be found here.

Although SQL Server has statistics and a superb query plan engine, it still requires some guidance from us occassionally. Writing a long query with too many joins does not help. The query optimiser might not be smart enough to pick up a fast plan. We can consider using CTE or temp table to break down the query.

Here is an example of applying CTE for faster performance.

Long queries will also result in consuming a lot of memory in a SQL Server. In a memory limited environment, SQL Server has bottleneck in requesting memory for the query to run, thus the query will be waiting in a memory queue. I have seen queries requesting 1GB of memory to run each time, and SQL Server cannot allocate memory for it to run. Details can be found here.

Although statistics are great, it does come with limitations. Please consider the following SQL.

SELECT * FROM TableA INNER JOIN TableB ON TableA.Col LIKE  TableB.Col + '%'  

INNER JOIN TableC ON ...

In this case, statistics cannot be determined correctly because of the LIKE keyword. Putting the inner join result between TableA and TableB in a CTE or temp table in prior can guide the query plan for the correct joining order.

Before SQL2019, the estimated number of table variables are always 1. This will cause problem in query plan execution, as the query analyser will always think there is only one row in a table variable. This is improved in SQL2019.

Here is an article to show the importance of temporary table in SQL Server.

Here is an article to show how SQL2019 fix this issue.

Before SQL 2019, it is advised not to use scalar function in a large query because

1. It executes the function in a for loop manner, If the result returns 1000 rows, the function will be executed 1000 times. This is a CPU intensive operation.

2. The scalar function is hiding inside the query plan.

Here is an article to show the performance difference between them

Here is an article to show how SQL2019 improve this functionality.

After you are using inline table valued function, you might need to use CROSS APPLY to join to another table. Details can be found here.

This is a no brainer for everyone. The default SQL Server setting for cost threshold is not recommended. Cost threshold controls how high the cost of a SQL statement must be before a parallel plan will even be considered. The default value will result in a lot of CPU contention in a high traffic scenario. Setting it to 50 is a recommended minimum.

Details can be found here.