|
Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software
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')
|