SQL Server Concurrency Issues with Parallel Query Plans

Problem

Parallelism can reduce concurrency. This is a strong reason not to allow SQL Server to execute queries aggressively in parallel mode. In this tip, I will set up a demo to show that parallelism reduces query performance on a server with high concurrency.

Solution

Parallelism enables SQL Server to execute queries across multiple CPU cores simultaneously. The query optimizer determines whether to execute a query in parallel or not based on cost. When a query is complex and contains expensive operators (such as sorts, aggregations, etc.) and processes many rows, it is more likely to result in a parallel plan than a simple query that processes a few rows.

SQL Server Parallelism Configurations

When we are discussing parallelism, there are two SQL Server configurations to consider:

• Cost Threshold for Parallelism
• Maximum Degree of Parallelism

The Cost Threshold for Parallelism is a configuration that determines when SQL Server will use a parallel plan for a query based on its cost. It is a server level configuration and the default value for the cost threshold is 5.

The SQL Server Maximum Degree of Parallelism (MAXDOP) is also a server level configuration. This option limits the maximum number of processors that SQL Server can use for a parallel query execution. The default value for MAXDOP is 0, which allows SQL Server to use all available processors (up to 64 processors) for a single query in parallel.

If the default value for Cost Threshold for Parallelism is too low, this causes SQL Server to execute queries in parallel mode, which reduces concurrency. It is important to note that parallelism is not inherently good or bad. Some queries should execute in parallel mode to achieve reasonable performance. On the other hand, allowing SQL Server to run queries aggressively, especially on a server with high concurrency, reduces the performance of the queries. This is the exact point where setting the cost threshold for parallelism and MAXDOP come into play.

Set Up Test Environment

I use SQL Server 2022 and StackOverflow database for examples. The StackOverflow database is an open-source database from StackOverflow.com. I set the maximum degree of parallelism to 2 (my laptop has four logical CPU cores) and changed the cost threshold for parallelism to 50.

Use master
GO
Exec sys.sp_configure N'show advanced options', N'1'
GO
EXEC sys.sp_configure N'cost threshold for parallelism', N'50'
GO
EXEC sys.sp_configure N'max degree of parallelism', N'2'
GO
RECONFIGURE WITH OVERRIDE
GO

I set the database compatibility level to 160.

Alter Database StackOverflow Set Compatibility_Level = 160
GO

The StackOverflow database has a table for storing users’ information, which has a clustered primary key on the Id column. I start the demo by creating a non-clustered index on the Reputation column:

Use StackOverflow
GO
Create Index IX_Reputation On dbo.Users (Reputation)
With (Data_Compression = Page)
GO

Initial Query

The following query selects the top 100 rows of the DisplayName and Reputation columns from the Users table where Reputation is either 27 or 48. The results will be sorted in ascending order based on DisplayName. To view the actual execution plan, simply press Ctrl + M in SSMS.

Select Top 100 u.DisplayName, u.Reputation 
From dbo.Users u 
Where u.Reputation in (27, 48)  
Order By u.DisplayName
GO

The image shows that the SQL Server performed a non-clustered index seek, followed by a key lookup, to retrieve and display the results. Also, we can see that each operator has two arrows on the bottom right of the operators which signifies running in parallel. Here is a partial view the overall query plan.

To view the query execution time and CPU time, right-click on the SELECT operator in the execution plan and choose Properties. Then, go to the QueryTimeStats section in the open window. The query execution time is 136ms, and the CPU time is 263ms. We can also see the query was run in parallel because the CPU time was about twice as long as the elapsed time.

Query Using MAXDOP 1

Now, I will add the “OPTION (MAXDOP 1)” hint to the query and execute it. This ensures that the query will only use one processor to perform its operations.

Select Top 100 u.DisplayName, u.Reputation 
From dbo.Users u  
Where u.Reputation in (27, 48)  
Order By u.DisplayName
Option (Maxdop 1)
GO

The QueryTimeStats image below illustrates that the query ran in serial execution mode, since the CPU time and elapsed time are both 250ms.

If you hover the mouse over the SELECT operator in the execution plan, the tooltip appears, and you can see the Estimated Subtree Cost is 58.4 for our query. It is more than the cost threshold for parallelism that I set to 50.

Running Queries Concurrently

If multiple users run the query concurrently, the query runs faster in serial execution mode. Let’s see this first hand.

To simulate concurrency, I use the SQL Query Stress tool. Further details about this tool can be found here.

First, I run the query without using the OPTION (MAXDOP 1) hint. The image below demonstrates that the average query execution time is approximately 450ms when executed concurrently in a parallel mode.

Now, I run the query using the OPTION (MAXDOP 1) hint. The image below shows that the average query execution time is around 350ms when executed concurrently in serial mode.

The above test showed that on average the query ran faster in serial mode during concurrent executions.

Estimated Subtree Cost

As previously mentioned, the Estimated Subtree Cost for the query was 58.4 when it ran in serial mode. As a result, the query will execute in parallel due to the cost threshold for parallelism being 50.

I changed the cost threshold for parallelism to 60 and execute it without the Maxdop 1 option.

EXEC sys.sp_configure N'cost threshold for parallelism', N'60'
GO
RECONFIGURE WITH OVERRIDE
GO
Select Top 100 u.DisplayName, u.Reputation 
From dbo.Users u 
Where u.Reputation in (27, 48)  
Order By u.DisplayName
GO

The latest image shows the query execution plan. There is no parallelism icon in the execution plan, which indicates that the query was executed in serial mode.

Summary

The default value for the Cost Threshold for Parallelism is 5, which is too low and causes SQL Server to execute queries in parallel aggressively, and this results in reduced concurrency. You need to change this setting based on your workload to achieve better performance, especially when there is high concurrency on your server.

You can start by setting the Cost Threshold for Parallelism to 50 and MAXDOP to 8 (depending on your server resources) and then monitor the performance metrics (like wait types, expensive queries, and so on) to adjust them.

Next Steps

• Parallelism in SQL Server Execution Plan
• Testing SQL Server Query Performance Using Different Levels of Parallelism
• Specifying Max Degree of Parallelism in SQL Server for a Query

Mehdi Ghapanvari

Mehdi Ghapanvari is an SQL Server database administrator with 6+ years of experience. His main area of expertise is improving database performance. He is skilled at managing high-performance servers that can handle several terabytes of data and hundreds of queries per second, ensuring their availability and reliability. To enhance the performance of the database, he has implemented various performance-tuning techniques, including query optimization and index tuning. He has also developed backup and recovery strategies to protect critical data in case of disasters. Mehdi currently manages the SQL Server databases for a large organization.  Mehdi is actively seeking new opportunities to apply his expertise and contribute to impactful projects. You can reach him at SQLDBA.Mehdi@Gmail.com.

• MSSQLTips Awards: Rookie of the Year – 2023