Browse: Wetory / Scripting library / Get cached execution plans

SQL Server - Get cached execution plans

by Tomáš RybnickýNo Comments

Look into your cache and see what execution plans live there. You can quickly identify what SQL Server stored in plan cache and see how many time was this plan used. This can be useful when you need to get know SQL Server load better.  I am using it also for decision when setting Optimize for Ad-Hoc workloads configuration to true.

What is great here, you can directly click the QueryPlan column value and you will see that execution plan in graphical way.

TIP: Use WHERE clause on line 10 to filter out plans only fro given object.

SELECT 
	cp.objtype AS ObjectType
	,OBJECT_NAME(st.objectid, st.dbid) AS ObjectName
	,cp.usecounts AS ExecutionCount
	,st.TEXT AS QueryText
	,qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
--WHERE OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName'

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
ObjectTypenvarchar(16)Type of object. Below are the possible values and their corresponding descriptions. Proc: Stored procedure Prepared: Prepared statement Adhoc: Ad hoc query. Refers to Transact-SQL submitted as language events by using osql or sqlcmd instead of as remote procedure calls. ReplProc: Replication-filter-procedure Trigger: Trigger View: View Default: Default UsrTab: User table SysTab: System table Check: CHECK constraint Rule: Rule
ObjectNamesysnameDatabase object name for schema-scoped objects.
ExecutionCountintNumber of times the cache object has been looked up. Not incremented when parameterized queries find a plan in the cache. Can be incremented multiple times when using showplan.
QueryTextnvarchar(max)Text of the SQL batch that is identified by the specified sql_handle. Text of the SQL query. Is NULL for encrypted objects.
QueryPlanxmlContains the compile-time Showplan representation of the query execution plan that is specified with plan_handle. The Showplan is in XML format. One plan is generated for each batch that contains, for example ad hoc Transact-SQL statements, stored procedure calls, and user-defined function calls. Column is nullable.

Leave a Reply