Microsoft SQL Server 2005
Performance Tuning
Best Practices

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
 
 
SQLUSA.com Home Page