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”
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:
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:
Click on the link in Query Plan column and you get the plan.
Compare this with the previous plan when you ran the query. Is the same? Should be… 🙂