Snippet Name: Retrieve_Execution_Plan_SQL

Tags:

Created Date: 2025-06-16 13:12:41
Last Modified Date: 2025-05-31 10:31:52

Dependencies:

DBA Notes:

Sample Output:

Title: Retrieve Execution Plan SQL
Author: user
if i have a specific sqlhandle from an xml deadlock graph, how can i get the execution plan. in my exmaple the handle is 0x02000000891c8c044e5e5cfbad53ed93072533c82322ef010000000000000000000000000000000000000000

Author: ChatGPT
To retrieve the execution plan from an `sql_handle`, you can use the system dynamic management views (DMVs) in SQL Server. Specifically, you can query the `sys.dm_exec_query_plan` and `sys.dm_exec_cached_plans` DMVs. These views provide access to cached execution plans for queries.

Here???s a step-by-step guide on how to do this:

1. **Use the `sys.dm_exec_query_plan` DMV:**
   This DMV provides the execution plan for a given `plan_handle`.

2. **Link the `sql_handle` to the `plan_handle`:**
   You can query the `sys.dm_exec_cached_plans` DMV to find the `plan_handle` associated with the `sql_handle`.

Here???s the SQL query you can run to retrieve the execution plan:

```sql
-- Step 1: Find the plan_handle using the sql_handle
SELECT cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.sql_handle = 0x02000000891c8c044e5e5cfbad53ed93072533c82322ef010000000000000000000000000000000000000000;

-- Step 2: Use the plan_handle to retrieve the execution plan
-- Replace <plan_handle> with the actual plan_handle retrieved from the previous query
SELECT qp.query_plan
FROM sys.dm_exec_query_plan(<plan_handle>) AS qp;
```

### Explanation:
1. **First Query:**
   - This query retrieves the `plan_handle` associated with the given `sql_handle`.
   - `sys.dm_exec_cached_plans` provides information about cached query plans.
   - `sys.dm_exec_sql_text` helps match the `sql_handle` to the correct plan.

2. **Second Query:**
   - After retrieving the `plan_handle`, you can pass it to `sys.dm_exec_query_plan` to get the execution plan in XML format.

### Notes:
- If the execution plan is no longer in the cache, you won't be able to retrieve it using these DMVs. Execution plans are cached temporarily, and SQL Server may evict them based on memory pressure.
- The execution plan returned by this query will be in XML format. You can copy and paste it into SQL Server Management Studio (SSMS) to view it graphically.

If you have any further questions, feel free to ask!

---
If you enjoy using this GPT, please rate it 5??? and follow GPT creator profile on LinkedIn for the latest updates and insights [https://linkedin.com/in/khanukov/](https://linkedin.com/in/khanukov/)

Author: user
can you joi9n the two querie stogether for a single output

Author: ChatGPT
Certainly! You can join the two queries into a single query using a subquery or a `CROSS APPLY`. This allows you to fetch the `plan_handle` and the corresponding execution plan in one step.

Here's how you can do it:

```sql
-- Single query to retrieve the execution plan using the sql_handle
SELECT qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.sql_handle = 0x02000000891c8c044e5e5cfbad53ed93072533c82322ef010000000000000000000000000000000000000000;
```

### Explanation:
- **CROSS APPLY:** 
  - `sys.dm_exec_sql_text(cp.plan_handle)` is used to retrieve the SQL text associated with the `plan_handle`. We filter based on the `sql_handle`.
  - `sys.dm_exec_query_plan(cp.plan_handle)` is used to get the execution plan associated with the `plan_handle`.
  
- **WHERE Clause:** 
  - The `WHERE` clause ensures that only the matching `sql_handle` is processed.

This query will give you the execution plan directly if the query associated with the given `sql_handle` is still cached.

If the plan is not cached anymore, you won't get any results. In that case, you'd need to check for other potential ways to trace the execution plan (e.g., using query store if it's enabled).

Let me know if you need further help!

---
If you enjoy using this GPT, please rate it 5??? and follow GPT creator profile on LinkedIn for the latest updates and insights [https://linkedin.com/in/khanukov/](https://linkedin.com/in/khanukov/)

Author: user
the dmv for the sql text does nto have a column for st.sql_handle 

Author: ChatGPT
You're right! I apologize for the confusion. The `sys.dm_exec_sql_text()` DMV does not directly expose the `sql_handle` column, which was a mistake in the query. Instead, we should use the `sql_handle` from the `sys.dm_exec_cached_plans` and filter on that.

Let me correct the query and provide the right approach.

### Correct Query

You need to link the `sql_handle` from the `sys.dm_exec_query_stats` DMV or `sys.dm_exec_requests` DMV and then join it to `sys.dm_exec_query_plan` to get the execution plan.

Here???s the correct way to combine the queries:

```sql
-- Single query to retrieve the execution plan using the sql_handle
SELECT qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.sql_handle = 0x02000000891c8c044e5e5cfbad53ed93072533c82322ef010000000000000000000000000000000000000000;
```

### Explanation:
- **`sys.dm_exec_query_stats`:** This DMV contains both `sql_handle` and `plan_handle`. We can filter by the `sql_handle`.
- **`sys.dm_exec_query_plan`:** We retrieve the execution plan using the `plan_handle` that we got from `sys.dm_exec_query_stats`.

This will give you the execution plan for the query if it's still in the plan cache.

Let me know if you have more questions!

---
If you enjoy using this GPT, please rate it 5??? and follow GPT creator profile on LinkedIn for the latest updates and insights [https://linkedin.com/in/khanukov/](https://linkedin.com/in/khanukov/)