SQLServer索引重建
要点：
- 检查索引碎片fragmentation: average percentage, pagecount
- 采用ALTER INDEX REBUILD指令
- 手动设置Timeout参数
- 设置计数器以控制SSMS停止响应时间
- 增加暂停以释放被挂起的进程
- EXEC sp_who2以检查死锁
背景
有许多系统把部分逻辑写在数据库中，随着时间的流逝，数据和索引逐渐增加，因而数据库的性能将逐渐减小。
其中一个重要的影响因素是索引。
当数据和索引增加时，其碎片化程度也将随之增加。
根据微软的文档“Reorganizingand 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 ONdbtables.[object_id] =indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ONdbtables.[schema_id] =dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexesON dbindexes.[object_id]= indexstats.[object_id]
ANDindexstats.index_id =dbindexes.index_id
WHERE indexstats.database_id= DB_ID()
AND indexstats.avg_fragmentation_in_percent> 30
--ANDdbschemas.[name] = 'dbo'
--ANDdbtables.[name] = 'RPV_MAGPLANBEL'
--ORDERBY indexstats.page_count DESC;
ORDER BY indexstats.avg_fragmentation_in_percent DESC;
-------------------------------------------------------------------------------------
另有一个重要参数是page_count，即索引页数，每页约有8KB数据，因此当页数非常大时，意味着此索引的重建时间也会很长。
2. 需要考虑的因素
使用ALTER INDEX index_name还是ALTERINDEX ALL
使用ALTER INDEX index_name ON Table REBUILD只能重建指定索引。
使用ALTER INDEX ALL ON Table REBUILD能够重建此表的所有索引，但是也需要更长的等待时间，因此在重建过程中指向这些索引的新SQL将被挂起SUSPENDED等待。
手动设置远程timeout，以避免被服务器超时断开，如执行以下SQL：
EXEC sp_configure'remote query timeout', 600 ; -- 设置为600秒
RECONFIGURE ;
因为重建过程可能较长，超出默认的TIMEOUT，因此好手动设置此项参数。
设置计数器以控制停止响应时间
即使我们在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 ONdbtables.[object_id] =indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ONdbtables.[schema_id] =dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexesON dbindexes.[object_id]= indexstats.[object_id]
ANDindexstats.index_id =dbindexes.index_id
WHERE indexstats.database_id= DB_ID()
ANDindexstats.avg_fragmentation_in_percent > 30
ANDdbschemas.[name] ='dbo'
--ANDdbtables.[name] = 'RPV_MAGPLANBEL'
ANDdbindexes.[name] ISNOT NULL
ORDER BYindexstats.page_count DESC;
--ORDERBY indexstats.avg_fragmentation_in_percent DESC;
-------------------------------------------------------------------------------------
对于page count较大(如超过10万)的索引，我们可以每次只重建1个索引。
对于page count较小的索引，我们可以根据情况设置一个较大的计数器数据。
设置暂停以释放挂起的进程
当我们利用游标，在同一个进程中执行多个REBUILD指令时，我们应当在两个指令间设置至少5秒的暂停时间，此时间可用来释放被系统挂起的进程(这些进程会引用到正在重建的索引)。
相关SQL:
WAITFOR DELAY '00:00:05'; -- 暂停5秒
在REBUILD之前和进行中检查实时死锁deadlocks
相关指令：
EXECsp_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 <>;
3. 优化过的SQL:
-------------------------------------------------------------------------------------
Declare @getTables CURSOR; -- Cursor to list out allindexes 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 printout
DECLARE @i INT;-- Counter
-- setremote 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');
--EXECsp_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 ONdbtables.[object_id] =indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ONdbtables.[schema_id] =dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexesON dbindexes.[object_id]= indexstats.[object_id]
ANDindexstats.index_id =dbindexes.index_id
WHERE indexstats.database_id= DB_ID()
ANDindexstats.avg_fragmentation_in_percent > 30
ANDdbschemas.[name] ='dbo'
--AND dbtables.[name]= 'RPV_MAGPLANBEL'
ANDdbindexes.[name] ISNOT NULL
ORDER BY indexstats.page_count DESC;
--ORDERBY 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 biggernumber 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'ALTERINDEX ' + @IndexName + N' ON ' + @TableName + N' REBUILD;';
PRINT(@command);
--PRINT('');
--WAITFORDELAY '00:00:01';
BEGIN TRY
EXEC (@command);
SELECT@DT = CONVERT(NVARCHAR, GETDATE(), 121);--YYYY-MM-DD HH:MI:SS
PRINT(@DT);
PRINT('Rebuilding Indexcompleted.');
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)参数
在运行ALTERINDEX (ALL) REBUILD指令时，有一个可选参数ONLINE。
如果设置ONLINE=ON，则在重建过程的大部分时间旧索引依然可用，因而对生产环境的影响较小，但是此参数仅适用于企业版SQL SERVER。
本文来源https://www.modb.pro/db/85734
相关文章