sys.dm_exec_query_plan – Day 33 – One DMV a Day

Hello Geeks and welcome to the Day 33 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.

So far in the execution related DMVs we have covered the connections, sessions, requests and sql text. Today we will touch the most crucial DMV in this group. If you are troubleshooting a query performance the first step most of the DBAs start with is execution plan. To pull out the execution plan of a statement or procedure from the cache we use sys.dm_exec_query_plan.

Sys.dm_exec_query_plan is a dynamic management function which accepts the plan handle parameter and displays the estimated execution plan from the plan cache. Remember this only shows estimated plan and not the actual plan. To get the actual plan you can use extended events or add showplan options to your session. A new DMV is introduced in SQL 2014, which I am going to cover later in this series, which give most of the actual plan related details.

To see the output of sys.dm_exec_query_plan let us run the procedure which I created yesterday (refer sys.dm_exec_sql_text). While the procedure is executing in session 52 (this may change in your case) run the below query to get the plan. Remember to run this query within 10 seconds after you run the procedure.

SELECT er.session_id, est.text, eqp.query_plan
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text (er.sql_handle) est
CROSS APPLY sys.dm_exec_query_plan (er.plan_handle) eqp
WHERE session_id = 52

sys.dm_exec_query_plan

From the above output of sys.dm_exec_query_plan we can see the estimated execution plan from the plan cache. This will be an xml link. Once you click you can confirm the plan to be estimated one from the below screenshot.

sys.dm_exec_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 *