SQL Server: Extracting the Execution Plan from SQL Server Plan Cache

Hi Friends,

There may be a number of reasons why you would want to extract the execution plan from the plan cache – most important for troubleshooting and performance tuning reasons.

So, assume a scenario that a user fires a report -> which in turn sent a query to the database engine. The query executed and returned the results but took hell of a time. You are immediately contacted by the user and he/she reports this to you. Now, you want to investigate. There could be many reasons why the query ran slow but having that execution plan which the optimizer generated will always be handy.

So, let us do this with an example: Before you get going, switch on “Include Actual Execution Plan”

1_Extracting_the_Execution_Plan_from_SQL_Server_Plan_Cache

Now run the following query:

USE Northwind;
GO
 
SELECT ProductName, Products.ProductID
FROM dbo.[Order Details]
  JOIN dbo.Products
    ON [Order Details].ProductID = Products.ProductID
WHERE Products.UnitPrice > 100;
GO

If you click on the Execution Plan tab, you will see the following plan:

2_Extracting_the_Execution_Plan_from_SQL_Server_Plan_Cache

The above is the plan that we want to extract from the cache. Remember the scenario is that the query is fired from the application. I just showed you the above plan so that when you extract the plan from the cache, you can compare and see if they are similar 🙂 – Now let us first see all the plans in the cache:

   
SELECT qplan.query_plan AS [Query Plan]
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_query_plan(qstats.plan_handle) AS qplan;

The above code gives you all the plans in the cache, we want to extract the plan for our query, the one you see above. If you observe, we join the 2 DM objects here using CROSS APPLY. TO filer the plan for our query we need to join another one, sys.dm_exec_sql_text, as follows:

SELECT qplan.query_plan AS [Query Plan],qtext.text
FROM sys.dm_exec_query_stats AS qstats
 CROSS APPLY sys.dm_exec_query_plan(qstats.plan_handle) AS qplan
 cross apply sys.dm_exec_sql_text(qstats.plan_handle) as qtext
 where text like 'SELECT ProductName, Products.ProductID%';

If you observe, we are filtering on text column by penning down the beginning of our statement. When you execute the query, you would probably get only 1 row as follows:

3_Extracting_the_Execution_Plan_from_SQL_Server_Plan_Cache

Click on the link in Query Plan column and you get the plan.

4_Extracting_the_Execution_Plan_from_SQL_Server_Plan_Cache

Compare this with the previous plan when you ran the query. Is the same? Should be… 🙂

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

One Comment on “SQL Server: Extracting the Execution Plan from SQL Server Plan Cache”

  1. Is there any thing like SSIS package execution plan like SQL Server Plan??

    Please help me to understand the same

Leave a Reply

Your email address will not be published.