Share via

Execution Plan Parallel Deadlock Issue Persisting in SQL Server 2022

Steven Lam 20 Reputation points
2024-05-10T15:07:55.6733333+00:00

Hi Msft team!

I hope this message finds you well. I am writing to report a persistent issue with execution plan parallel deadlocks that still exists in SQL Server 2022. Despite updates and patches, this bug appears to remain unresolved, and I have been able to consistently reproduce it under specific conditions.

 

 

Environment:

SELECT @@VERSION

Microsoft SQL Server 2022 (RTM-CU12) (KB5033663) 16.0.4115.5 (X64)  Mar 4 2024 08:56:10  Copyright (c) 2022 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (X64) (Build 14393): (Hypervisor)

 

 

 

Issue Description:

The problem arises under certain parallelism conditions when executing queries that are supposed to handle large computations across multiple processors. Here is the SQL script used to reproduce the issue:

 


-- Creating a table with a primary key and a fill factor CREATE TABLE Numbers (     Number INT NOT NULL,     CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)     WITH FILLFACTOR = 100 );
-- Populating the table with values INSERT INTO Numbers SELECT     (a.Number * 256) + b.Number AS Number FROM     (SELECT number FROM master..spt_values WHERE type = 'P' AND number <= 255) a,     (SELECT number FROM master..spt_values WHERE type = 'P' AND number <= 255) b;
-- Example query that completes quickly with an odd MAXDOP setting SET STATISTICS TIME ON; SELECT     maxN = MAX(num.number) FROM dbo.numbers AS num WHERE     num.number < 30000     AND CONVERT(INTEGER, CONVERT(VARCHAR(MAX), num.number)) % 2 = 0 OPTION (     MAXDOP 3, QUERYTRACEON 8649 );
-- Example query that leads to a deadlock with an even MAXDOP setting SELECT     maxN = MAX(num.number) FROM dbo.numbers AS num WHERE     num.number < 30000     AND CONVERT(INTEGER, CONVERT(VARCHAR(MAX), num.number)) % 2 = 0 OPTION (     MAXDOP 4, QUERYTRACEON 8649 );  

Issue:

When the MAXDOP is set to an odd number (e.g., 3, 5, 7), the query returns results quickly without any issues. However, changing MAXDOP to an even number (e.g., 2, 4, 6) results in significantly longer execution times, and using the Trace Profiler, we detect a deadlock.

It appears that the deadlock occurs specifically when parallel execution plans are generated with an even number of threads. This issue might be rooted in how SQL Server manages parallel process threads and resources.

 

Request for Assistance:

Could the SQL Server team look into this matter and provide a fix or workaround? This issue impacts performance significantly and could affect many users working with large data sets and relying on parallel processing for performance optimization.

Thank you for your attention to this matter. I look forward to your prompt response and a resolution to this persistent issue.

QQ截图20240510230546

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,859 questions
0 comments
Accepted answer
  1. MikeyQiao-MSFT 650 Reputation points Microsoft Vendor
    2024-05-11T10:09:23.53+00:00

    Hi，Steven Lam

    Parallel execution inevitably may lead to deadlocks, which is an unchangeable fact.

    However, regarding the example you mentioned:

    1. When accessing base table data, a clustered index scan is used, but the scan direction is backward. In SQL Server, only forward scans can be executed in parallel; backward scans can only be performed serially.
    2. Therefore, when distributing data to various threads during the exchange (distribute streams), a round-robin method is used to distribute the data. This inevitably causes odd and even data to be separated according to threads before flowing to the next filter operator.
    3. At the filter stage, odd-numbered data is filtered out, and the corresponding threads are left without data.
    4. Thus, during the final data collection phase in the exchange (gather streams), some threads have no data, which can lead to a deadlock. This is indeed a bug inherent to SQL itself, and we can only offer appropriate suggestions. For the specific bug, you can only report it through the platform mentioned by Erland and wait for the attention of the product department.

    Best regards，

    Mikey Qiao

    If the answer is helpful to you, don't forget to accept it.This will help many people who have had similar experiences to yours.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 102.2K Reputation points
    2024-05-10T21:14:37.6866667+00:00

    Hi Msft team!

    You don't find the Msft team here. This is a peer-to-peer forum for questions and answers.

    If you are running into an SQL Server bug, there are two options. You can report it on

    https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0

    This is to let Microsoft to know about the issue. There are no guarantees that they will react, but you seem to have a good repro for the issue and that definitely helps. Nevertheless, don't expect a fix coming shortly.

    The other alternative is to open a support case, and this is the step you need to take if this is a blocking issue and you are in need of a fix. When opening a support case, you may be charged an amount initially, but if the issue is admitted as a bug, you will be refunded.

    By the way, in SQL 2022, you can use the new function generate_series to populate the Numbers table. Even better, you don't need the Numbers table in your code at all. (Well, maybe in this case, since it is a bug repro.)