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.

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.

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