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:
sys.dm_exec_text_query_plan

Second statement:
sys.dm_exec_text_query_plan

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.

sys.dm_exec_text_query_plan
sys.dm_exec_text_query_plan

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

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published. Required fields are marked *