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

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

 1 --执行计划缓存的相关sql语句  2013-5-5
 2 --http://www.cnblogs.com/CareySon/archive/2013/05/04/PlanCacheInSQLServerPart2.html
 3 --http://www.cnblogs.com/CareySon/archive/2013/05/04/PlanCacheInSQLServer.html
 4 SELECT * FROM sys.[dm_exec_cached_plans]
 5 
 6 SELECT * FROM sys.[dm_os_memory_cache_counters]
 7 
 8 SELECT name,[buckets_count],[buckets_avg_scan_hit_length],[buckets_avg_scan_miss_length]
 9 FROM sys.[dm_os_memory_cache_hash_tables]
10 WHERE [type] IN ('CACHESTORE_OBJCP','CACHESTORE_SQLCP','CACHESTORE_PHDR','CACHESTORE_XPROC')
11 
12 --SELECT * FROM  sys.dm_exec_plan_attributes(@handle)
13 
14 SELECT plan_handle, pvt.set_options, pvt.sql_handle
15 FROM (
16     SELECT plan_handle, epa.attribute, epa.value
17     FROM sys.dm_exec_cached_plans
18         OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
19     WHERE cacheobjtype = 'Compiled Plan') AS ecpa
20 PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt;
21 GO

 

posted @ 2013-08-04 20:28  桦仔  阅读(314)  评论(0编辑  收藏  举报