Hello Geeks and welcome to the Day 34 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.
Yesterday we have seen how to get the execution plan for a running request using sys.dm_exec_query_plan. This DMV is good if you are dealing with smaller batches. What if you have multiple batches in your request and need the plan only at a statement level. Sys.dm_exec_text_query_plan helps you get this granular level plan.
Unlike sys.dm_exec_query_plan sys.dm_exec_text_query_plan accepts statement_start_offset and statement_end_offset to get the plan of the exact statement running. The other benefit from this DMV is to use this output in USE PLAN query hint or using PLAN GUIDE.
To demonstrate more on sys.dm_exec_text_query_plan I will modify the procedure I used in last two blogs. I will be adding another statement to the procedure.
CREATE PROCEDURE usp_getPerson_prc
SELECT * FROM person.person
SELECT * FROM person.Address
Now execute the above stored procedure. While the above stored procedure is running and if you are fast enough you can run the below query twice during execution. You will find the below output for two different statements during the procedure execution.
(((CASE WHEN er.statement_end_offset = -1
ELSE er.statement_end_offset END) - er.statement_start_offset)/2) + 1)
text AS batch,
CAST(etqp.query_plan AS XML) AS stmnt_plan
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) est
CROSS APPLY sys.dm_exec_text_query_plan(er.plan_handle, er.statement_start_offset, er.statement_end_offset) etqp
WHERE session_id = 54 --Change the session_id as needed
The execution plans for individual statements in a batch can be collected like this using sys.dm_exec_text_query_plan. So start using it today.
Tomorrow I will be covering another execution related DMV. So, stay tuned. Till then