笔记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
