July 29, 2021 at 11:11 am
Are there known issues with Batchmode on rowstore in CU11?
One specific complex query is getting "Transaction (Process ID 65) was deadlocked on generic waitable object resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
The deadlock sofar triggers when the optimizer decides to go for BatchModeOnRowStoreUsed="true"
OPTION(USE HINT('DISALLOW_BATCH_MODE')) or leaving a part of the selected data out doesn't result in the deadlock.
With batchmode it also starts with another table and it is a non-parallel operation (the other operators are parallel)
There isn't any other activity going on.
I don't post the query because it's gotten an alternative version and sofar is has been the only case for the deadlock to appear.
July 29, 2021 at 11:49 am
Sounds like a parallelism deadlock. They're generally rare, but absolutely do happen. Usually, but not always, there are tuning opportunities that can make them go away; changes to the code, new or adjusted indexes, that sort of thing. You could also look at the cost threshold for parallelism to adjust that. Use a max dop hint for the query, use the hint you're already using, look to see if there are other hints such as a hash join that change the behavior.
Experiment with them all to see what works best in your given situation, but it really is usually just query tuning fundamentals that fixes it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 29, 2021 at 3:24 pm
Thanks for the keyword "parallelism deadlock" Grant. Lead to intra-query parallelism with more information on the internet.
As by your recommendation solved with a query hint.
July 29, 2021 at 5:38 pm
Thanks for reporting back what worked.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply