| How to
list cached query plans ? |
|
Execute the following
script in Query Editor to obtain a list of cached query plans. If you don't see any, execute a few sprocs and run the script again:
USE AdventureWorks
GO
SELECT ObjectName = o.name,
QueryPlan = query_plan,
CacheObjectType = objtype
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_query_plan(p.plan_handle) e
JOIN sys.objects o
ON o.object_id = e.objectid
WHERE CAST(query_plan as varchar(max))
LIKE '%' AND
cacheobjtype = 'Compiled Plan' AND
objtype in ('Proc', 'Adhoc', 'Prepared')
|
| The Best SQL Server
2005 Training in the World |
|