【SqlServer】统计索引使用情况解决DB的CPU高和IO高的问题

查看索引情况

sp_helpindex 表名;

显示索引使用情况

user_seeks和user_scans字段都为0的，考虑是否为垃圾索引

另外last_user_seek,last_user_scan如果是一个很早的时间，则考虑是否应用变化

导致该索引不被使用了

SELECT i.name indexname,user_seeks,user_scans,last_user_seek,last_user_scan
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON
s.object_id = i.object_id AND
s.index_id = i.index_id
WHERE database_id = db_id('ClntMgr') AND s.object_id = object_id('IDVerifyTbl');

返回指定数据库、表、索引的碎片

对于索引类型为HEAP，一般情况下碎片比例会较大

原因：

• 1.没有聚集索引的表称为堆，意思是其中存储的数据没有特定的顺序。
• 2.在索引重建或者重新组织时，聚集索引依照聚集键和它重 排序 的数据页进行排序。
• 3.但是堆不会在索引重建或重新组织期间被重新生成，所以会脱离控制的增长，占用的数据页比必要的多很多。

SELECT OBJECT_NAME(f.object_id) 表名,
i.name 索引名,f.index_type_desc 索引类型, f.avg_fragmentation_in_percent 碎片比例
FROM sys.dm_db_index_physical_stats(DB_ID('库名'),OBJECT_ID('表名'),NULL,NULL,'limited') f
INNER JOIN sys.indexes i ON
i.[object_id] = f.object_id AND
i.index_id  = f.index_id
ORDER BY f.avg_fragmentation_in_percent DESC;

在线重新生成表的所有索引

ALTER INDEX ALL ON 库名.dbo.表名 REBUILD WITH (ONLINE = ON);

重新组织表的所有索引

ALTER INDEX all ON 库名.dbo.表名 REORGANIZE;

查看表、索引占用磁盘空间情况

SELECT name '表名',
convert (char(11), row_Count) as '数据条数',
(reservedpages * 8) '已用空间(KB)',
(pages * 8) '数据占用空间(KB)',
(CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8 '索引占用空间(KB)',
(CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8 '未用空间(KB)',
LTRIM (STR (reservedpages * 8/1024/1024, 15, 0) + ' GB') as '已用空间(GB)'
from(
SELECT name,SUM (reserved_page_count) as reservedpages ,
SUM (used_page_count) as usedpages ,
SUM (
    CASE
        WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
        ELSE lob_used_page_count + row_overflow_used_page_count
    END
    ) as pages,
SUM (
    CASE
        WHEN (index_id < 2) THEN row_count
        ELSE 0
    END
    )  as row_Count
FROM sys.dm_db_partition_stats
inner join sys.objects on sys.dm_db_partition_stats.object_id=sys.objects.object_id
where type='U'
group by sys.objects.name
union
SELECT sys.objects.name,
sum(reserved_page_count) as reservedpages,
sum(used_page_count) as usedpages,
0 as pages,
0 as row_count
from sys.objects inner join sys.internal_tables on 
 sys.objects.object_id = sys.internal_tables.parent_id 
inner join sys.dm_db_partition_stats on sys.dm_db_partition_stats.object_id=sys.internal_tables.object_id
where sys.internal_tables.internal_type IN (202,204,211,212,213,214,215,216) 
group by sys.objects.name) t
order by '已用空间(KB)' desc

查看表缺失的索引信息

SELECT DatabaseName = DB_NAME(database_id),[Number Indexes Missing] = count(*)
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY [Number Indexes Missing] DESC;

确定开销最高的缺失索引

column_usage的取值有如下几种情况:

• 1.EqualityUsage代表在该列上做了相等运算;
• 2.InequalityUsage代表在该列上做了不等运算;
• 3.Include Cloumns代表包含性列
• 此查询的结果（按"总开销"排序）显示最重要缺失索引的成本以及有关数据库/架构/表和缺失索引中所需列的信息。特别是，此脚本可确定哪些列在相等和不相等 SQL 语句中使用。另外，它还报告应将哪些其他列用作缺失索引中的包含性列。

使用包含性列可以在不从基础页获取数据的情况下满足更多的覆盖查询，因而使用的 I/O 操作更少，从而提高性能。

SELECT TOP 100
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
    ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
    ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;