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
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.
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
