How to get an execution plan for a query involved in deadlock?
So, you have an XDL file and want to find the execution plans for all queries involved in it. Understanding which execution plan was used can help you identify the root cause of a deadlock and determine how to resolve it.
To locate the execution plan, you’ll need to examine the contents of the XDL file — since each XDL file is essentially an XML file, you can open it in your preferred text editor.
Sometimes XDL files can be difficult to read due to inconsistent formatting. My preferred way to work with deadlock files is by using Notepad++:
- Rename XDL extention to XML
- Open Notepad++
- Go to Plugins -> Plugins Admin… and install XML Tools plugin (if yo don’t have it already installed)
- Open XML file in Notepad++
- To format XML file click on Plugins -> XML Tools -> Pretty print
Moving next. Each process has an execution stack with a set of frames inside it, like this:
1<frame procname="dbo.my_beautiful_proc" line="28" stmtstart="840" stmtend="1020"
2 sqlhandle="0x03000700a23e5b5cf4fd7e00848d00000100000000000000">
That’s exactly what we are looking for! Grab stmtstart, stmtend, and sqlhandle. Let’s try to find an execution plan in the Execution Plan Cache using this query:
1-- Note: you need to run this query on the server where the deadlock occured
2select convert(xml, pl.query_plan) query_plan_xml,
3 te.[text] query_sql_text
4from sys.dm_exec_query_stats qs
5 cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset,
6 qs.statement_end_offset) pl
7 cross apply sys.dm_exec_sql_text(qs.[sql_handle]) te
8where qs.[sql_handle] = <sqlhandle>
9 and qs.statement_start_offset = <stmtstart>
10 and qs.statement_end_offset = <stmtend>
Unfortunately this may not work for all cases, because execution plan may not exist in the Execution Plan Cache anymore. There are many possible reasons for this — for example, you might just be too busy (or too lazy) to dig into it right after the deadlock occurs.
However, there’s another way to find the execution plan — by using the Query Store, provided it’s enabled for your database. To locate the relevant execution plan in the Query Store, you’ll need the same stmtstart, stmtend, and sqlhandle values, along with the exact date and time when the deadlock occurred. Here is the query:
1-- Note: you need to run this query on the server and database where the deadlock occured
2select object_schema_name(qsq.[object_id]) [schema_name],
3 object_name(qsq.[object_id]) [object_name],
4 qsq.query_id,
5 qst.query_sql_text,
6 convert(xml, qsp.query_plan) query_plan_xml,
7 rs.execution_type_desc
8from sys.query_store_query_text qst
9 join sys.query_store_query qsq
10 on qsq.query_text_id = qst.query_text_id
11 join sys.query_store_plan qsp
12 on qsp.query_id = qsq.query_id
13 join sys.query_store_runtime_stats rs
14 on rs.plan_id = qsp.plan_id
15 join sys.query_store_runtime_stats_interval rsi
16 on rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
17where qsq.last_compile_batch_sql_handle = <sqlhandle>
18 and qsq.last_compile_batch_offset_start = <stmtstart>
19 and qsq.last_compile_batch_offset_end = <stmtend>
20 and '<deadlock_datetime_in_utc>+00:00' between rsi.start_time and rsi.end_time
Note:
- schema_name and object_name will be NULL for non-stored procedures
- execution_type_desc will be Aborted or Exception for the deadlock victim
If luck’s not on your side again, you can hunt down the sqlhandle using this query - just search by the query text:
1-- Note: you need to run this query on the server and database where the deadlock occured
2select qsq.last_compile_batch_sql_handle,
3 object_schema_name(qsq.[object_id]) [schema],
4 object_name(qsq.[object_id]) [object_name],
5 qsq.query_id,
6 qst.query_sql_text,
7 convert(xml, qsp.query_plan) query_plan_xml,
8 rs.execution_type_desc,
9 rsi.start_time,
10 rsi.end_time
11from sys.query_store_query_text qst
12 join sys.query_store_query qsq
13 on qsq.query_text_id = qst.query_text_id
14 join sys.query_store_plan qsp
15 on qsp.query_id = qsq.query_id
16 join sys.query_store_runtime_stats rs
17 on rs.plan_id = qsp.plan_id
18 join sys.query_store_runtime_stats_interval rsi
19 on rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
20where rs.execution_type <> 0 -- Regular execution (successfully finished)
21 and qst.query_sql_text like '%<my_query_text>%'
22 and '<deadlock_datetime_in_utc>+00:00' between rsi.start_time and rsi.end_time
Once you have the query_id in hand, you can go to the Query Store UI in SSMS and find the query using the Tracked Queries. This may provide additional insights into execution plan variations.