Query Cached Execution Plans

Often I need to examine an execution plan from a production server. Just as often, I can’t re-run the stored procedure in that environment. There are several techniques for simulating the environment on a test server such as exporting statistics and using the un-documented DBCC AUTOPILOT command.

These methods take a bit of setup and time. So my first step is usually to hit up the cached execution plan, assuming that the query or procedure has been run since the last server restart. Below is a simple script to return the execution plan and some stats for any object whether it be a stored procedure, function, etc.

Query the query cache

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) sqlT
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
INNER JOIN sys.objects o ON sqlT.objectid = o.object_id
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE o.name = 'object_name'
AND s.name = 'schema_name';

This article has 1 comment

  1. awesome. it is great i keep learning sql server tricks from you 🙂

Leave a Reply