索引监控数据分析
通过上一节收集的数据组合在一起，并经过分析阶段，制定出对索引的创建、删除、修改方案，然后在实施阶段进行部署
主要关注下面几个部分：
1.审查服务器状态
2.未使用索引
3.索引计划使用
一：审查服务器状态
1.性能计数器
2.等待信息
3.Buffer分配
Use IndexDemo
WITH CounterSummary
AS (
SELECT create_date
,server_name
,MAX(CASE WHEN counter_name = 'Forwarded Records/sec'
THEN Calculated_Counter_value END) ForwardedRecords
,MAX(CASE WHEN counter_name = 'Forwarded Records/sec'
THEN Calculated_Counter_value END)
/ (NULLIF(MAX(CASE WHEN counter_name = 'Batch Requests/sec'
THEN Calculated_Counter_value END),0) * 10) AS ForwardedRecordRatio
FROM dbo.IndexingCounters
WHERE counter_name IN ('Forwarded Records/sec','Batch Requests/sec')
GROUP BY create_date
,server_name
)
SELECT server_name
,MIN(ForwardedRecords) AS MinForwardedRecords
,AVG(ForwardedRecords) AS AvgForwardedRecords
,MAX(ForwardedRecords) AS MaxForwardedRecords
,MIN(ForwardedRecordRatio) AS MinForwardedRecordRatio
,AVG(ForwardedRecordRatio) AS AvgForwardedRecordRatio
,MAX(ForwardedRecordRatio) AS MaxForwardedRecordRatio
,100.*SUM(CASE WHEN ForwardedRecordRatio > 1 THEN 1 END)
/COUNT(*) AS PctViolation
FROM CounterSummary
GROUP BY server_name
创建一个堆表，插入数据，并更新其中一些数据，引发堆分页，最后执行上面的查询检查情况
USE IndexDemo
GO
IF OBJECT_ID('HeapExample', 'U') IS NOT NULL
DROP TABLE HeapExample
CREATE TABLE dbo.HeapExample
(
ID INT IDENTITY ,
FillerData VARCHAR(2000)
)
INSERT INTO dbo.HeapExample
( FillerData
)
SELECT REPLICATE('X', 100)
FROM sys.all_objects
UPDATE dbo.HeapExample
SET FillerData = REPLICATE('X', 2000)
WHERE ID % 5 = 1
GO
SELECT *
FROM dbo.HeapExample
WHERE ID % 3 = 1
当Forwarded Records发生，再次执行代码
如果存在Forward Records问题，通常有3种解决方案
1.更改数据类型，把变长改成定长，如varchar-char，不够这种情况可能不是很通用，而且可能伴随数据截断等问题
2.改变表的存储结构，也就是创建一个聚集索引在上面，从而移除堆表的组织数据机制
3.就是重建堆表
ALTER TABLE dbo.HeapExample REBUILD
2.Access Methods\FreeSpace Scans/sec（关于堆表的另一个计数器）
当在堆表中插入数据时，他会标识发生了什么操作，在插入过程中，可能会引起GAM、SGAM和PFS页的改动。如果插入的频率足够高，可能会在这些页上产生争用
通过汇总最小值、平均值和最大值来进行分析
WITH CounterSummary
AS ( SELECT create_date ,
server_name ,
MAX(CASE WHEN counter_name = 'FreeSpace Scans/sec'
THEN Calculated_Counter_value
END) FreeSpaceScans ,
MAX(CASE WHEN counter_name = 'FreeSpace Scans/sec'
THEN Calculated_Counter_value
END)
/ ( NULLIF(MAX(CASE WHEN counter_name = 'Batch Requests/sec'
THEN Calculated_Counter_value
END), 0) * 10 ) AS ForwardedRecordRatio
FROM dbo.IndexingCounters
WHERE counter_name IN ( 'FreeSpace Scans/sec',
'Batch Requests/sec' )
GROUP BY create_date ,
server_name
)
SELECT server_name ,
MIN(FreeSpaceScans) AS MinFreeSpaceScans ,
AVG(FreeSpaceScans) AS AvgFreeSpaceScans ,
MAX(FreeSpaceScans) AS MaxFreeSpaceScans ,
MIN(ForwardedRecordRatio) AS MinForwardedRecordRatio ,
AVG(ForwardedRecordRatio) AS AvgForwardedRecordRatio ,
MAX(ForwardedRecordRatio) AS MaxForwardedRecordRatio ,
100. * SUM(CASE WHEN ForwardedRecordRatio > 1 THEN 1
END) / COUNT(*) AS PctViolation
FROM CounterSummary
GROUP BY server_name
如果FreeSpace Scans/sec很高，应该集中分析哪个堆有最高的插入数率。可以使用：sys.dm_db_index_operational_stats来查找
SELECT QUOTENAME(DB_NAME(database_id)) AS database_name ,
QUOTENAME(OBJECT_SCHEMA_NAME(object_id, database_id)) + '.'
+ QUOTENAME(OBJECT_NAME(object_id, database_id)) AS ObjectName ,
SUM(leaf_insert_count) AS leaf_insert_count ,
SUM(leaf_allocation_count) AS leaf_allocation_count
FROM dbo.index_operational_stats_history
WHERE index_id = 0
AND database_id > 4
GROUP BY object_id ,
database_id
ORDER BY leaf_insert_count DESC
在查找到根源之后，最好的方法就是加上聚集索引，改变其数据组织机制
3.Access Methods\Full Scans/sec,通过这个计数器可查看Full Scans/sec的值，这个值包含聚集、非聚集索引及堆表。高值意味着查询存在性能问题，这种情况可能会引起Page Life Expectancy（用于衡量内存压力的一个主要计数器）的变动，这将加大数据在内存中的存储时间，并引起I/O问题
下面的脚本用于分析当前Full Scans/sec的值，同样也需要考虑Batch Requests/sec 计数器，如果这两个值的比例超过1000，就需要引起注意
WITH CounterSummary
AS ( SELECT create_date ,
server_name ,
MAX(CASE WHEN counter_name = 'Full Scans/sec'
THEN Calculated_Counter_value
END) FullScans ,
MAX(CASE WHEN counter_name = 'Full Scans/sec'
THEN Calculated_Counter_value
END)
/ ( NULLIF(MAX(CASE WHEN counter_name = 'Batch Requests/sec'
THEN Calculated_Counter_value
END), 0) * 1000 ) AS FullRatio
FROM dbo.IndexingCounters
WHERE counter_name IN ( 'Full Scans/sec',
'Batch Requests/sec' )
GROUP BY create_date ,
server_name
)
SELECT server_name ,
MIN(FullScans) AS MinFullScans ,
AVG(FullScans) AS AvgFullScans ,
MAX(FullScans) AS MaxFullScans ,
MIN(FullRatio) AS MinFullRatio ,
AVG(FullRatio) AS AvgFullRatio ,
MAX(FullRatio) AS MaxFullRatio ,
100. * SUM(CASE WHEN FullRatio > 1 THEN 1
ELSE 0
END) / COUNT(*) AS PctViolation
FROM CounterSummary
GROUP BY server_name
4. Access Methods\Index Searches/sec,大部分情况下，索引查找会比索引扫描有效，这个计数器显示SQL Server 实例上发生索引查找的比率，这个值相对于Full Scans/sec来说越高越好。这个比率是衡量索引是否有效的标准之一，这两个值的比率一般是1000:1
WITH CounterSummary
AS ( SELECT create_date ,
server_name ,
MAX(CASE WHEN counter_name = 'Index Searches/sec'
THEN Calculated_Counter_value
END) IndexSearches ,
MAX(CASE WHEN counter_name = 'Index Searches/sec'
THEN Calculated_Counter_value
END)
/ ( NULLIF(MAX(CASE WHEN counter_name = 'Full Scans/sec'
THEN Calculated_Counter_value
END), 0) * 1000 ) AS SearchToScanRatio
FROM dbo.IndexingCounters
WHERE counter_name IN ( 'Index Searches/sec',
'Full Scans/sec' )
GROUP BY create_date ,
server_name
)
SELECT server_name ,
MIN(IndexSearches) AS MinIndexSearches ,
AVG(IndexSearches) AS AvgIndexSearches ,
MAX(IndexSearches) AS MaxIndexSearches ,
MIN(SearchToScanRatio) AS MinSearchToScanRatio ,
AVG(SearchToScanRatio) AS AvgSearchToScanRatio ,
MAX(SearchToScanRatio) AS MaxSearchToScanRatio ,
100. * SUM(CASE WHEN SearchToScanRatio > 1 THEN 1
END) / COUNT(*) AS PctViolation
FROM CounterSummary
GROUP BY server_name
5.Access Methods\Page Splits/sec 对于堆表上的Forwarded Records，聚集索引上的就是Page Splits，没有聚集索引的表就是堆表，堆表上的非聚集索引还是使用的Forwarded Records。Page Splits是一个比较消耗资源的操作，而且在拆页的时候会对原来的页加上排他锁，并且会产生碎片，所以应尽可能少用。下面的脚本用于汇总这方面的数据，一般的建议是每20个Batch Requests/sec 不应该多于一个Page Split/sec 即Batch Requests：Page Split应该接近20:1
USE IndexDemo
GO
WITH CounterSummary
AS ( SELECT create_date ,
server_name ,
MAX(CASE WHEN counter_name = 'Page Splits/sec'
THEN Calculated_Counter_value
END) PageSplits ,
MAX(CASE WHEN counter_name = 'Page Splits/sec'
THEN Calculated_Counter_value
END)
/ ( NULLIF(MAX(CASE WHEN counter_name = 'Batch Requests/sec'
THEN Calculated_Counter_value
END), 0) * 20 ) AS FullRatio
FROM dbo.IndexingCounters
WHERE counter_name IN ( 'Page Splits/sec',
'Batch Requests/sec' )
GROUP BY create_date ,
server_name
)
SELECT server_name ,
MIN(PageSplits) AS MinPageSplits ,
AVG(PageSplits) AS AvgPageSplits ,
MAX(PageSplits) AS MaxPageSplits ,
MIN(FullRatio) AS MinFullRatio ,
AVG(FullRatio) AS AvgFullRatio ,
MAX(FullRatio) AS MaxFullRatio ,
100. * SUM(CASE WHEN FullRatio > 1 THEN 1
ELSE 0
END) / COUNT(*) AS PctViolation
FROM CounterSummary
GROUP BY server_name
6.Buffer Manager\Page Lookups/sec用于衡量实例中的请求在buffer pool里面查询的单独页数量。这个数值很高时，可能意味着不高效的执行计划，通常需要研究该执行计划。一般数值很高是因为执行计划中产生了大量的Page Lookups和Row Lookups。通常情况下，每个Batch Request/sec不应该超过100次这类的lookups
以下脚本通过对比Page Lookups/sec 和Batch Request/sec来初始化数据，包括最小值、最大值和平均值。
USE IndexDemo
GO
WITH CounterSummary
AS ( SELECT create_date ,
server_name ,
MAX(CASE WHEN counter_name = 'Page Lookups/sec'
THEN Calculated_Counter_value
END) PageLookups ,
MAX(CASE WHEN counter_name = 'Page Lookups/sec'
THEN Calculated_Counter_value
END)
/ ( NULLIF(MAX(CASE WHEN counter_name = 'Batch Requests/sec'
THEN Calculated_Counter_value
END), 0) * 100 ) AS PageLookupRatio
FROM dbo.IndexingCounters
WHERE counter_name IN ( 'Page Lookups/sec',
'Batch Requests/sec' )
GROUP BY create_date ,
server_name
)
SELECT server_name ,
MIN(PageLookups) AS MinPageLookups ,
AVG(PageLookups) AS AvgPageLookups ,
MAX(PageLookups) AS MaxPageLookups ,
MIN(PageLookupRatio) AS MinPageLookupRatio ,
AVG(PageLookupRatio) AS AvgPageLookupRatio ,
MAX(PageLookupRatio) AS MaxPageLookupRatio ,
100. * SUM(CASE WHEN PageLookupRatio > 1 THEN 1
ELSE 0
END) / COUNT(*) AS PctViolation
FROM CounterSummary
GROUP BY server_name
7.Locks(*)\Lock Wait Time(ms).这类计数器更偏重于检查索引的压力情况。可以衡量SQL Server花在索引、表、页上锁资源的时间。没有可参考的值，但是可以作为历史数据，然后用最近监控的数据和这个历史数据对比，比值应该越低越好。
用下面的脚本来监控该计数器的值，以便建立一个基准
USE IndexDemo
GO
WITH CounterSummary
AS ( SELECT create_date ,
server_name ,
instance_name ,
MAX(CASE WHEN counter_name = 'Lock Wait Time (ms)'
THEN Calculated_Counter_value
END) / 1000 LockWaitTime
FROM dbo.IndexingCounters
WHERE counter_name = 'Lock Wait Time (ms)'
GROUP BY create_date ,
server_name ,
instance_name
)
SELECT CONVERT(VARCHAR(50), MAX(create_date), 101) AS CounterDate ,
server_name ,
instance_name ,
MIN(LockWaitTime) AS MinLockWaitTime ,
AVG(LockWaitTime) AS AvgLockWaitTime ,
MAX(LockWaitTime) AS MaxLockWaitTime ,
STDEV(LockWaitTime) AS StdDevLockWaitTime
FROM CounterSummary
GROUP BY server_name ,
instance_name
UNION ALL
SELECT 'Baseline: ' + CONVERT(VARCHAR(50), start_date, 101) + ' -->'
+ CONVERT(VARCHAR(50), end_date, 101) ,
server_name ,
instance_name ,
minimum_counter_value / 1000 ,
maximum_counter_value / 1000 ,
average_counter_value / 1000 ,
standard_deviation_counter_value / 1000
FROM dbo.IndexingCountersBaseline
WHERE counter_name = 'Lock Wait Time (ms)'
ORDER BY instance_name ,
CounterDate DESC
由于锁通常具有实时性、易丢失性，所以这部分的监控可能需要稍微加大频率，并且做好历史记录。
8。Locks(*) NUmber of Deadlocks/sec。极端的情况下，不良的索引设计和过渡锁阻塞会引起死锁（Deadlocks），这种情况是绝对不能容忍的，可以使用下面的脚本监控并研究起因、解决方案
USE IndexDemo
GO
WITH CounterSummary
AS ( SELECT create_date ,
server_name ,
Calculated_Counter_value AS NumberDeadlocks
FROM dbo.IndexingCounters
WHERE counter_name = 'Number of Deadlocks/sec'
)
SELECT server_name ,
MIN(NumberDeadlocks) AS MinNumberDeadlocks ,
AVG(NumberDeadlocks) AS AvgNumberDeadlocks ,
MAX(NumberDeadlocks) AS MaxNumberDeadlocks ,
STDEV(NumberDeadlocks) AS StdDevNumberDeadlocks
FROM CounterSummary
GROUP BY server_name
（2）
USE IndexDemo
GO
WITH WaitStats
AS ( SELECT DENSE_RANK() OVER ( ORDER BY w.create_date ASC ) AS RankID , --相同的排序相同，下一个小的会紧挨着排序
create_date ,
wait_type ,
waiting_tasks_count ,
wait_time_ms ,
max_wait_time_ms ,
signal_wait_time_ms ,
MIN(create_date) OVER ( ) AS min_create_date ,
MAX(create_date) OVER ( ) AS max_create_date
FROM dbo.wait_stats_history w
WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH',
'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP',
'CLR_MANUAL_EVENT',
'CLR_AUTO_EVENT',
'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT',
'XE_DISPATCHER_WAIT',
'XE_DISPATCHER_JOIN',
'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'ONDEMAND_TASK_QUEUE',
'BROKER_EVENTHANDLER',
'SLEEP_BPOOL_FLUSH',
'DIRTY_PAGE_POLL',
'HADR_FILESTREAM_IOMGR_IOCOMPLETION' )
)
SELECT wait_type ,
DATEDIFF(ms, min_create_date, max_create_date) AS total_time_ms , --返回两个日期的时间
SUM(waiting_tasks_count) AS waiting_tasks_count ,
SUM(wait_time_ms) AS wait_time_ms ,
CAST(1. * SUM(wait_time_ms) / SUM(waiting_tasks_count) AS DECIMAL(18,
3)) AS avg_wait_time_ms ,--将某种数据类型的表达式显式转换为另一种数据类型
CAST(100. * SUM(wait_time_ms) / DATEDIFF(ms, min_create_date,
max_create_date) AS DECIMAL(18,
3)) AS pct_time_in_wait ,
SUM(signal_wait_time_ms) AS signal_wait_time_ms ,
CAST(100. * SUM(signal_wait_time_ms) / NULLIF(SUM(wait_time_ms), 0) AS DECIMAL(18,
3)) AS pct_time_runnable
FROM WaitStats
WHERE RankID <> 1
GROUP BY wait_type ,
min_create_date ,
max_create_date
ORDER BY SUM(wait_time_ms) DESC
Buffer分配
查看数据库中使用了多少内存的Buffer，具体数量可能不重要，但是对比不同数据库中的比例情况就很重要
可以通过sys.dm_os_buffer_descriptors来检查，这个DMV会显示在内存中的每个数据页，以及其中的信息，并且通过计算显示出每个库所使用的Buffer情况
SELECT LEFT(CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE DB_NAME(database_id) END,20) AS Database_Name, COUNT(*) AS Buffered_Page_Count, CAST(COUNT(*)*8/1024.0 AS NUMERIC(10,2)) AS Buffer_Pool_MB FROM sys.dm_os_buffer_descriptors GROUP BY DB_NAME(database_id),database_id order BY Buffered_Page_Count desc
查看什么库使用了最多的Buffer，还可以改进一下，用它来查看占用了内存最多的表或者索引
WITH BufferAllocation
AS ( SELECT object_id ,
index_id ,
allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id
AND ( au.type = 1
OR au.type = 3
)
UNION ALL
SELECT object_id ,
index_id ,
allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id
AND au.type = 2
)
SELECT t.name ,
i.name ,
i.type_desc ,
COUNT(*) AS Buffered_Page_Count ,
CAST(COUNT(*) * 8 / 1024.0 AS NUMERIC(10, 2)) AS Buffer_MB
FROM sys.tables t
INNER JOIN BufferAllocation ba ON t.object_id = ba.object_id
LEFT JOIN sys.indexes i ON ba.object_id = i.object_id
AND ba.index_id = i.index_id
INNER JOIN sys.dm_os_buffer_descriptors bd ON ba.allocation_unit_id = bd.allocation_unit_id
WHERE bd.database_id = DB_ID()
GROUP BY t.name ,
i.index_id ,
i.name ,
i.type_desc
ORDER BY Buffered_Page_Count DESC
2.无用索引
对于索引分析，其中一个重要的部分是移除无用索引,因为索引是有成本的，包括存储成本和修改成本，所以没有被用过的索引应该去除，一直都没有用户操作使用过的索引，由于某些索引是用于维护数据的一致性的，因此可能也会没有用户操作发生在上面。下面的脚本用于查找无用索引。
SELECT OBJECT_NAME(i.object_id) AS table_name ,
COALESCE(i.name, SPACE(0)) AS index_name ,
ps.partition_number ,
ps.row_count ,
CAST(( ps.reserved_page_count * 8 ) / 1024. AS DECIMAL(12, 2)) AS size_in_mb ,
COALESCE(ius.user_seeks, 0) AS user_seeks ,
COALESCE(ius.user_scans, 0) AS user_scans ,
COALESCE(ius.user_lookups, 0) AS user_lookups ,
i.type_desc
FROM sys.all_objects t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id
AND i.index_id = ps.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = DB_ID()
AND i.object_id = ius.object_id
AND i.index_id = ius.index_id
WHERE i.type_desc NOT IN ( 'HEAP', 'CLUSTERED' )
AND i.is_unique = 0
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND COALESCE(ius.user_seeks, 0) <= 0
AND COALESCE(ius.user_scans, 0) <= 0
AND COALESCE(ius.user_lookups, 0) <= 0
ORDER BY OBJECT_NAME(i.object_id) ,
i.name
以上的脚本可能会把主键查出来，主键主要用于维护数据行的可表示性
SELECT OBJECT_NAME(i.object_id) AS table_name ,
COALESCE(i.name, SPACE(0)) AS index_name ,
ps.partition_number ,
ps.row_count ,
CAST(( ps.reserved_page_count * 8 ) / 1024. AS DECIMAL(12, 2)) AS size_in_mb ,
COALESCE(ius.user_seeks, 0) AS user_seeks ,
COALESCE(ius.user_scans, 0) AS user_scans ,
COALESCE(ius.user_lookups, 0) AS user_lookups ,
i.type_desc
FROM sys.all_objects t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id
AND i.index_id = ps.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = DB_ID()
AND i.object_id = ius.object_id
AND i.index_id = ius.index_id
WHERE i.type_desc NOT IN ( 'HEAP', 'CLUSTERED' )
AND i.is_unique = 0
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND COALESCE(ius.user_seeks, 0) <= 0
AND COALESCE(ius.user_scans, 0) <= 0
AND COALESCE(ius.user_lookups, 0) <= 0
ORDER BY OBJECT_NAME(i.object_id) ,
i.name
3.索引计划使用：
用于查看多少个查询中使用了特定索引的所有执行计划，只需要替换脚本中的执行计划@IndexName。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
DECLARE @IndexName SYSNAME = 'PK_SalesOrderHeader_SalesOrderID';
SET @IndexName = QUOTENAME(@IndexName, '[');
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/SQL Server/2004/07/showplan')
,IndexSearch
AS (
SELECT qp.query_plan
,cp.usecounts
,ix.query('.') AS StmtSimple
FROM sys.dm_exec_cached_plans cp
OUTER APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY qp.query_plan.nodes('//StmtSimple') AS p(ix)
WHERE query_plan.exist('//Object[@Index = sql:variable("@IndexName")]') = 1
)
SELECT StmtSimple.value('StmtSimple[1]/@StatementText', 'VARCHAR(4000)') AS sql_text
,obj.value('@Database','sysname') AS database_name
,obj.value('@Schema','sysname') AS schema_name
,obj.value('@Table','sysname') AS table_name
,obj.value('@Index','sysname') AS index_name
,ixs.query_plan
FROM IndexSearch ixs
CROSS APPLY StmtSimple.nodes('//Object') AS o(obj)
WHERE obj.exist('//Object[@Index = sql:variable("@IndexName")]') = 1
通过增加物理操作符的名称来定位
DECLARE @IndexName SYSNAME = 'IX_SalesOrderHeader_SalesPersonID';
DECLARE @op SYSNAME = 'Index Scan';
;
WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/SQL Server/2004/07/showplan')
SELECT
cp.plan_handle
,DB_NAME(dbid) + '.' + OBJECT_SCHEMA_NAME(objectid, dbid) + '.' + OBJECT_NAME(objectid,
dbid) AS database_object
,qp.query_plan
,c1.value('@PhysicalOp','nvarchar(50)')
,c2.value('@Index','nvarchar(max)')
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY query_plan.nodes('//RelOp') r(c1)
OUTER APPLY c1.nodes('IndexScan/Object') AS o(c2)
WHERE c2.value('@Index','nvarchar(max)') = QUOTENAME(@IndexName,'[')
AND c1.exist('@PhysicalOp[. = sql:variable("@op")]') = 1
通过这些脚本定位可能有问题的执行计划，通过执行计划分析得出解决方案
找出使用频率最高的20%的查询
SELECT TOP 20 PERCENT
cp.usecounts AS '使用次数' ,
cp.cacheobjtype AS '缓存类型' ,
cp.objtype AS [对象类型] ,
st.text AS 'TSQL' ,
--cp.plan_handle AS '执行计划',
qp.query_plan AS '执行计划' ,
cp.size_in_bytes AS '执行计划占用空间(Byte)'
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
ORDER BY usecounts DESC
