patternsqlModerate
Why are there Victimless Entries on the Deadlock Graph?
Viewed 0 times
whythegrapharedeadlockvictimlessthereentries
Problem
I am trying to learn how to analyze SQL Server 2008's deadlock graph, and I'm finding alot of entries with an empty `` node. I don't understand what these entries represent: if there is no victim, how can I identify the waitresource that is causing the deadlock? What do these entries mean?
Here's a quick example of the entries i'm seeing:
edit
As requested, here is the query used to identify a query by it's sqlhandle:
from RyanBoyer.net
Here's a quick example of the entries i'm seeing:
edit
As requested, here is the query used to identify a query by it's sqlhandle:
select sql_handle as Handle,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS Text
from sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
where sql_handle = --0x04000D00E3572A56542E4601CE9E00010100001000000000from RyanBoyer.net
Solution
ExchangeEvent & e_waitPipeNewRow suggests you've run into what Bart Duncan refers too as Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks".
Most intra-query parallelism deadlocks are considered bugs, although
some of them can be risky bugs to fix so a fix may not be possible.
If you run into one and you're already on the latest SQL service pack,
your best bet may be to investigate workarounds.
So, not much you can do other than:
Most intra-query parallelism deadlocks are considered bugs, although
some of them can be risky bugs to fix so a fix may not be possible.
If you run into one and you're already on the latest SQL service pack,
your best bet may be to investigate workarounds.
So, not much you can do other than:
- Ensure you're on the latest service pack and cumulative update.
- Try to identify indexes and/or other optimisations to improve the performance of the query. You mention that inputbuf isn't populated but you may be able to identify the query in play via the sqlhandle in the graph XML. If you get nothing from that try running a trace and correlating with the times these deadlocks occur.
- Reduce
MAXDOPfor this query or tryMAXDOP(1)to force single-threaded execution. Be aware that you might fix the deadlocks but introduce a different set of performance issues by restricting parallelism.
- Open a support call with Microsoft. Possible that a) they have a non-public hotfix for this scenario or b) as these intra-query deadlocks are deemed to be bugs they may want to work with you to find a fix.
Context
StackExchange Database Administrators Q#21767, answer score: 12
Revisions (0)
No revisions yet.