ssms 缺少索引信息_SQL Server索引重建

要点：

- 检查索引碎片fragmentation: average percentage, page count

- 采用ALTER INDEX REBUILD指令

- 手动设置Timeout参数

- 设置计数器以控制SSMS停止响应时间

- 增加暂停以释放被挂起的进程

- EXEC sp_who2以检查死锁

1. 背景

有许多系统把部分逻辑写在数据库中，随着时间的流逝，数据和索引逐渐增加，因而数据库的性能将逐渐减小。

其中一个重要的影响因素是索引。

当数据和索引增加时，其碎片化程度也将随之增加。

根据微软的文档“Reorganizing and Rebuilding Indexes(https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms189858(v=sql.90))”, 当碎片化率在5%~30%时，我们应当使用REORGANIZE指令重组索引，当碎片化率超过30%时，我们应当REBUILD重建索引。

我们可以使用下述SQL列出所有碎片化率超过30%的索引：

-------------------------------------------------------------------------------------

SELECT dbschemas.[name] AS 'Schema'

,dbtables.[name] AS 'Table'

,dbindexes.[name] AS 'Index'

,indexstats.avg_fragmentation_in_percent

,indexstats.page_count

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = DB_ID()

AND indexstats.avg_fragmentation_in_percent > 30

--AND dbschemas.[name] = 'dbo'

--AND dbtables.[name] = 'RPV_MAGPLANBEL'

--ORDER BY indexstats.page_count DESC;

ORDER BY indexstats.avg_fragmentation_in_percent DESC;

-------------------------------------------------------------------------------------

另有一个重要参数是page_count，即索引页数，每页约有8KB数据，因此当页数非常大时，意味着此索引的重建时间也会很长。

2. 需要考虑的因素

1) 使用ALTER INDEX index_name还是ALTER INDEX ALL

使用ALTER INDEX index_name ON Table REBUILD只能重建指定索引。

使用ALTER INDEX ALL ON Table REBUILD能够重建此表的所有索引，但是也需要更长的等待时间，因此在重建过程中指向这些索引的新SQL将被挂起SUSPENDED等待。

2) 手动设置远程timeout，以避免被服务器超时断开，如执行以下SQL：

EXEC sp_configure 'remote query timeout', 600 ; -- 设置为600秒

RECONFIGURE ;

因为重建过程可能较长，超出默认的TIMEOUT，因此最好手动设置此项参数。

3) 设置计数器以控制停止响应时间

即使我们在SSMS中通过PRINT()指令输出中间过程的信息，相关消息也只有在SQL指令块执行完毕之后才会显示。

在SQL执行过程中，消息框是空白的，因而在此期间我们无从得知SQL执行的具体情况。

因而最好将完整的执行过程分割成许多个小的过程，我们可以通过计数器予以控制。

现在我们修改一个查询碎片化的SQL，采用page_count逆向排序：

-------------------------------------------------------------------------------------

SELECT --dbschemas.[name] AS 'Schema',

dbtables.[name] AS 'Table',

dbindexes.[name] AS 'Index',

indexstats.avg_fragmentation_in_percent,

indexstats.page_count

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = DB_ID()

AND indexstats.avg_fragmentation_in_percent > 30

AND dbschemas.[name] = 'dbo'

--AND dbtables.[name] = 'RPV_MAGPLANBEL'

AND dbindexes.[name] IS NOT NULL

ORDER BY indexstats.page_count DESC;

--ORDER BY indexstats.avg_fragmentation_in_percent DESC;

-------------------------------------------------------------------------------------

对于page count较大(如超过10万)的索引，我们可以每次只重建1个索引。

对于page count较小的索引，我们可以根据情况设置一个较大的计数器数据。

4) 设置暂停以释放挂起的进程

当我们利用游标，在同一个进程中执行多个REBUILD指令时，我们应当在两个指令间设置至少5秒的暂停时间，此时间可用来释放被系统挂起的进程(这些进程会引用到正在重建的索引)。

相关SQL:

WAITFOR DELAY '00:00:05'; -- 暂停5秒

5) 在REBUILD之前和进行中检查实时死锁deadlocks

相关指令：

EXEC sp_who2

我们可以看到死锁的进程(Status=SUSPENDED, 或BlkBy IS NOT NULL), BlkBy指向引起死锁的进程。

只有确保无死锁发生时，才能执行REBUILD。

另一个检查死锁的SQL:

SELECT *

FROM sys.dm_exec_requests

WHERE DB_NAME(database_id) = 'KBS_FDM' –数据库名

AND blocking_session_id <> 0;

3. 优化过的SQL:

-------------------------------------------------------------------------------------

Declare @getTables CURSOR; -- Cursor to list out all indexes have frag % > 30

Declare @TableName varchar(255);

Declare @IndexName varchar(255);

Declare @command nvarchar(4000); -- Detail ALTER INDEX command

DECLARE @DT NVARCHAR(50); -- Datetime to print out

DECLARE @i INT; -- Counter

-- set remote time out

EXEC sp_configure 'remote query timeout', 600 ;

RECONFIGURE ;

SELECT @DT = CONVERT(NVARCHAR, GETDATE(), 121); --YYYY-MM-DD HH:MI:SS

PRINT(@DT);

PRINT('Rebuilding Index started.');

PRINT('');

--SET @getTables = CURSOR for SELECT name FROM sys.objects WHERE type = (N'U');

--EXEC sp_who2 -- to check existing deadlock(BlkBy)

SET @i = 0;

SET @getTables = CURSOR for

SELECT --dbschemas.[name] AS 'Schema',

dbtables.[name] AS 'TableName',

dbindexes.[name] AS 'IndexName'

--indexstats.avg_fragmentation_in_percent,

--indexstats.page_count

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = DB_ID()

AND indexstats.avg_fragmentation_in_percent > 30

AND dbschemas.[name] = 'dbo'

--AND dbtables.[name] = 'RPV_MAGPLANBEL'

AND dbindexes.[name] IS NOT NULL

ORDER BY indexstats.page_count DESC;

--ORDER BY indexstats.avg_fragmentation_in_percent DESC;

OPEN @getTables;

FETCH NEXT FROM @getTables into @TableName, @IndexName;

--WHILE @@FETCH_STATUS = 0

WHILE @i < 1 AND @@FETCH_STATUS = 0 -- Set @i to bigger number when page count is getting smaller

BEGIN;

SELECT @DT = CONVERT(NVARCHAR, GETDATE(), 121); --YYYY-MM-DD HH:MI:SS

PRINT(@DT);

--set @command = N'ALTER INDEX ALL ON ' + @TableName + N' REBUILD;'; -- WITH (ONLINE=ON);'; for enterprise version only

set @command = N'ALTER INDEX ' + @IndexName + N' ON ' + @TableName + N' REBUILD;';

PRINT(@command);

--PRINT('');

--WAITFOR DELAY '00:00:01';

BEGIN TRY

EXEC (@command);

SELECT @DT = CONVERT(NVARCHAR, GETDATE(), 121); --YYYY-MM-DD HH:MI:SS

PRINT(@DT);

PRINT('Rebuilding Index completed.');

PRINT('');

END TRY

BEGIN CATCH

SELECT @DT = CONVERT(NVARCHAR, GETDATE(), 121); --YYYY-MM-DD HH:MI:SS

PRINT(@DT);

PRINT 'CATCHED!';

END CATCH;

WAITFOR DELAY '00:00:05'; -- wait 5s to release suspended sessions

FETCH NEXT FROM @getTables into @TableName, @IndexName;

SET @i = @i + 1;

END;

CLOSE @getTables;

DEALLOCATE @getTables;

SELECT @DT = CONVERT(NVARCHAR, GETDATE(), 121); --YYYY-MM-DD HH:MI:SS

PRINT(@DT);

PRINT('All indexes rebuilt.');

-------------------------------------------------------------------------------------

4. 关于WITH (ONLINE=ON)参数

在运行ALTER INDEX (ALL) REBUILD指令时，有一个可选参数ONLINE。

如果设置ONLINE=ON，则在重建过程的大部分时间旧索引依然可用，因而对生产环境的影响较小，但是此参数仅适用于企业版SQL SERVER。