How to Prevent Partitioned Columnstore Deadlocks on SELECT
Since you're on SQL Server 2016, it's worth mentioning that there is at least one public bug fix for parallel deadlocks involving columnstore indexes:
FIX: A deadlock occurs when you run a parallel query on a clustered columnstore index in SQL Server 2016 and 2017
(thanks to Denis Rubashkin for providing the link initially)
This was released as part of SP1 CU7. If you're not up to that CU, you should give that a shot. This fix would also be included in SP2 (any of the CUs).
In general, the two approaches for fixing intra-query parallelism deadlocks:
- avoid parallelism (by tuning the query so that it doesn't go parallel, using a
MAXDOP
hint, etc.) - this is covered in the other answer by Thomas Costers - apply the latest service pack / cumulative updates to SQL Server
Have you checked following blog on Intra-Query Parallel Thread Deadlocks
The SyncPoint
resource indicates the use of an exchange event if I'm not mistaking.
Looking at the participants of your deadlock you can see that they are all coming from the same spid(55) and batch(0), but are using different threads.
This indicates that they are all part of the same parallel query and is confirmed by the fact that you don't receive any deadlocks whenever you run the query with MAXDOP 1
.
In the case of Intra-Query Parallel Thread Deadlocks, the threads of a single query will end up deadlocking each other waiting for synchronisation objects, SyncPoints in your case.
Last time when I witnessed this kind of behaviour, I was able to further optimise the query and thus preventing the query from using a parallel execution plan. I suspect you did the same thing by limiting your result set to 32 records or by using a different index.
Another option would be to add MAXDOP 1
to your query, though not a great fan of this option.
But before fiddling around with those two options, first check if you are on the latest SP/CU.