笔记265 执行计划缓存的相关sql语句 2013-5-5

笔记265 执行计划缓存的相关sql语句  2013-5-5

--执行计划缓存的相关sql语句  2013-5-5
--http://www.cnblogs.com/CareySon/archive/2013/05/04/PlanCacheInSQLServerPart2.html
--http://www.cnblogs.com/CareySon/archive/2013/05/04/PlanCacheInSQLServer.html
SELECT * FROM sys.[dm_exec_cached_plans]

SELECT * FROM sys.[dm_os_memory_cache_counters]

SELECT name,[buckets_count],[buckets_avg_scan_hit_length],[buckets_avg_scan_miss_length]
FROM sys.[dm_os_memory_cache_hash_tables]
WHERE [type] IN ('CACHESTORE_OBJCP','CACHESTORE_SQLCP','CACHESTORE_PHDR','CACHESTORE_XPROC')

--SELECT * FROM  sys.dm_exec_plan_attributes(@handle)

SELECT plan_handle, pvt.set_options, pvt.sql_handle
FROM (
    SELECT plan_handle, epa.attribute, epa.value
    FROM sys.dm_exec_cached_plans
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
    WHERE cacheobjtype = 'Compiled Plan') AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt;
GO