SQL Server Query Execution Plan from Cache
There are many ways to obtain the execution plan of a SQL Server query, each of which serves a particular purpose, for example if you can run a query easily, then the best way to get an execution plan is to simply enable the execution plan (either through SSMS or SETting the correct session option), but if the query takes a long time to run, or you simply cannot run the query any other reason, then you might decide to profile the query when it is naturally running (perhaps on the live environment) through SQL Profiler.
In this post I will go through a simple SQL query to extract the execution plan from SQL’s own plan cache. There are many reasons why this might not be an accurate way to extract a query, but it is certainly helpful in situation were you have good understanding of your DB setup.
So whenever a query runs SQL Server generates an execution plan, which you can view graphically or through XML (Text Execution Plans are being retired), this plan will dictate the steps SQL Server will go through/went through to complete the execution of the query. Whenever SQL generates a plan, it also decides whether to cache this plan for reuse or to throw the plan away (and generate a new one every time the query is executed), there are many reasons why SQL might decide to cache the execution plan for reuse, and why it might decide against it. Users can control whether SQL caches an execution plan for a particular query or not.
In order to extract a query from the execution plan, you need to take a look at the following 3 DMVs:
- sys.dm_exec_cached_plans
- sys.dm_exec_sql_text
- sys.dm_exec_query_plan
Combine them together, you get this lovely query:
UseCounts,
Cacheobjtype,
Objtype,
TEXT,
query_plan
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
You can add a filter if you are looking for a particular query, such as:
As I mentioned, this is one of many ways to get the execution plan of a particular query, there is an awesome post on Stack Overflow that describe the many ways of extracting a graphical execution plan from a query. Hope this helps!
Leave a Reply
Want to join the discussion?Feel free to contribute!