sys.dm_exec_text_query_plan – Day 34 – One DMV a Day

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.

USE AdventureWorks2012
GO
CREATE PROCEDURE usp_getPerson_prc
AS
BEGIN
	 SELECT * FROM person.person
	 SELECT * FROM person.Address
END

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.

SELECT er.session_id,
	 SUBSTRING(est.text, statement_start_offset/2+1,
		  (((CASE WHEN er.statement_end_offset = -1
			   THEN datalength(est.text)
			   ELSE er.statement_end_offset END) - er.statement_start_offset)/2) + 1)
		  AS current_stmnt,
	 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

First statement:

Second statement:

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

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook