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

转载地址：https://segmentfault.com/a/1190000018022330

 

查看表的索引情况：

use [数据库名]
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;