SQL Server 索引管理——禁用无用索引

SQL Server 索引管理——禁用无用索引

 

前文中，对于不再使用的索引，直接生成删除的脚本，在文中，也提到了直接删除无用索引存在的风险。为了保险起见，如果我们使用的是SQL Server 2005及以后版本，我们可以使用其新增加的功能：索引禁用。这样如果后期发现禁用掉的索引是需要的，我们就可以及时重建索引，保证数据库的性能，如果通过足够时长的观察，确定索引确实无用，则可以将禁用索引删除（备份、删除禁用索引的方式将在后续文章中给出）。对于的禁用后的索引，需要重新启用，只需要使用索引重建就可以启用。

禁用、启用索引脚本如下：

USE [DBName]
GO
--禁用索引语法
ALTER INDEX indexName ON tableName DISABLE
GO
--启用索引语法
ALTER INDEX indexName ON tableName REBUILD
GO

下面我们根据记录索引使用状态的动态视图sys.dm_db_index_usage_stats 生成不用索引的禁用脚本。这里的索引不使用指的是：

• 用户查找（user_seeks）次数为0

• 用户扫描（user_scans）次数为0

• 用户书签查找（user_lookups）次数为0

• 索引更新（user_updates）次数大于0

即无用索引为该索引在用户搜索、扫描、查找中都没有使用到。注意视图中的user_updates指的是表的插入、删除、更新等使得索引维护更新的次数。如果user_seeks、user_scans、user_lookups均为0，而user_updates大于0，说明索引不但对性能提升无益，还需要消耗额外的性能来维护索引，这种索引最好禁用甚至删除（当然是在确定确实没有使用的情况下）。

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = stuff((
SELECT 'ALTER INDEX '+ QUOTENAME(ind.NAME,'[')+' ON '+QUOTENAME(sch.NAME,'[')+'.'+QUOTENAME(obj.NAME,'[') +' DISABLE '+CHAR(10)
FROM sys.dm_db_index_usage_stats ius
    INNER JOIN sys.indexes ind on ius.index_id = ind.index_id and ius.OBJECT_ID=ind.object_id
    INNER JOIN sys.objects obj on ius.OBJECT_ID = obj.OBJECT_ID
    INNER JOIN sys.schemas sch on obj.schema_id = sch.schema_id
WHERE  OBJECTPROPERTY(ius.OBJECT_ID, 'IsSystemTable') = 0
    AND LEFT(obj.NAME, 3) NOT IN ('sys','sql','que','fil')
    AND UPPER(ind.type_desc) = 'NONCLUSTERED'
    AND ind.is_primary_key = 0
    AND ind.is_unique_constraint = 0
    AND ius.user_seeks = 0
    AND ius.user_scans = 0
    AND ius.user_lookups = 0
    AND ius.user_updates > 0
    AND ius.database_id = db_id()
    AND sch.name <> 'sys'
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,0,'')
SELECT @SQL FOR XML PATH('')

脚本中我们排除了系统表的索引，以及聚集索引，主键及有唯一约束的索引。这里最终返回的是XML数据（点击XML，即可以查看生成的文本），而不是使用PRINT打印出来，是防止文本过长而截断，另外，细心的读者会发现，在构造索引禁用语句最后加了CHAR（10）换行字符，使得最终的语句每条一行。

注意：动态视图记录信息在数据服务重启、数据库分离后信息将会被清除，所以要在数据库运行足够长时间后方可使用本脚本生成禁用索引脚本。

如果数据库实例足够长时间没有重启或者分离过，有用的索引都会记录到索引使用状态视图中，而没有记录到视图的这部分索引既不会被查询用到，也不需要消耗性能进行索引维护（一般出在废弃的表中）。如果硬盘空间紧张，需要对数据库进行瘦身，可以对这部分索引进行动手处理，我们给出了如下的禁用脚本生成的脚本：

DECLARE @dbid INT=DB_ID();
DECLARE @sql VARCHAR(MAX);
WITH cte AS(
       SELECT
              [object_id],index_id
       FROM sys.indexes
       EXCEPT --排除在用的对象索引
       SELECT
              [object_id],index_id
       FROM sys.dm_db_index_usage_stats
       WHERE database_id=@dbid)
SELECT @sql=(
       SELECT
              'ALTER INDEX '+QUOTENAME(i.name,'[')+' on '
                       +QUOTENAME(s.name,'[')+'.'+ QUOTENAME(o.name,'[')
                       +' DISABLE '
                       + CHAR(10)  --换行符
       FROM sys.indexes i
       INNER JOIN cte ON cte.[object_id]=i.[object_id] AND cte.index_id=i.index_id 
       INNER JOIN sys.objects o ON i.[object_id]=o.[object_id]
          INNER JOIN sys.schemas s ON o.SCHEMA_ID=s.SCHEMA_ID
       WHERE o.[type] IN ('U','V')--用户表或者视图
                     AND i.[type]>0                    --非堆
       ORDER BY s.name,o.name
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)');
SELECT @sql FOR XML PATH('');

最后，为了帮助理解什么样的操作会触发sys.dm_db_index_usage_stats记录索引使用状态，我们做了如下测试。先创建测试表：

CREATE TABLE indexesUsageStatsTest(
       id INT IDENTITY(1,1),
       name VARCHAR(50),
       TYPE INT
);

表创建后，查看视图sys.dm_db_index_usage_stats

SELECT * FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID= OBJECT_ID('indexesUsageStatsTest',N'U')
       and database_id=DB_ID();

此时视图无数据。

然后分别在表上创建聚集索引、和非聚集索引，并查询视图，发现视图仍没有数据

CREATE CLUSTERED INDEX cix_id ON indexesUsageStatsTest(id);
CREATE NONCLUSTERED INDEX ix_type ON indexesUsageStatsTest(TYPE);

向表中插入数据

INSERT INTO indexesUsageStatsTest(name,TYPE)
SELECT TOP 100 FirstName,BusinessEntityID
FROM Person.Person;

 

插入数据后，执行视图查询，发现视图中增加了两条记录，由于此时，该次插入数据造成所有索引都有一次更新，两个索引的user_updates值均变为1.。接下来执行如下脚本进行全表查询：

SELECT * FROM indexesUsageStatsTest;

执行查询时，打开“执行计划”，可以看到该查询使用了聚集索引扫描（clustered index scan）

再执行视图查询，结果如下：

从结果可以看出，聚集索引（index_id=1）的user_scans值变为1，并且last_user_scan的时间更新为查询执行时间。

在上面的查询的基础上，加上条件id=10

SELECT * FROM indexesUsageStatsTest
WHERE id=10;

此时使用的是聚集索引搜索，我们再执行视图的查询，并查看结果

聚集索引（index_id=1）的user_seeks值变为1，并且last_user_seek的时间更新为查询执行时间。

为获取书签查找，查询不包含在任何索引中的列name，并强制使用索引ix_type

SELECT name FROM indexesUsageStatsTest with(index(ix_type))
WHERE TYPE>38 and TYPE<7000;

查询计划中使用ix_type进行索引查找，cix_id进行键查找。再执行视图查询，并查看结果：

聚集索引（index_id=1）进行一次Key Lookup，其user_lookups变为1，并且非聚集索引（index_id=2）进行了移除索引查找，其对应列user_seeks 值更新为1.

UPDATE indexesUsageStatsTest SET TYPE=50
WHERE TYPE=38;

更新使用非聚集索引查找，查看视图可以发现，非聚集索引的user_seeks增加1，并且更新数据引起索引维护，两个索引的user_updates均增加1.

数据删除使用非聚集索引查找，查看视图可以发现，非聚集索引的user_seeks增加1，并且更新数据引起索引维护，两个索引的user_updates均增加1.

DELETE indexesUsageStatsTest
WHERE TYPE=50;

最后我们重启一下实例，并查询视图

发现视图没有记录对应表索引使用信息，因为实例启动时，视图记录清除，索引还没有使用。再次执行

SELECT name FROM indexesUsageStatsTest with(index(ix_type))
WHERE TYPE>38 and TYPE<7000;

并查看记录

发现增加使用的索引信息，并从0开始计数。这也是我们强调使用该脚本的要保证数据库运行足够长时间的原因。

如果喜欢，可以搜索关注 MSSQLServer 公众号，将有更多精彩内容分享：