重新组织和重建索引并更新统计信息

目录

介绍

实现

使用代码

1. 数据库完整性

2. 独立索引重组

3. 确定要重建的索引

4. 更新的指数统计

程序代码

---

介绍

在这个提示中，我想分享我关于一个常见问题的解决方案：索引维护。这是一个简单的存储过程，可以添加到数据库中，以保持良好的查询执行性能。

实现

若要在数据库上实现此过程，只需执行creation语句即可。就我个人而言，我建议根据您的数据库大小和复杂性在一周内执行此过程一到两次。

使用代码

存储过程说明

此存储过程可分为4个部分：

1. 检查数据库完整性
2. 重新组织索引
3. 重建索引
4. 更新统计信息

1. 数据库完整性

第一个操作是检查数据库的完整性，如果此步骤返回错误，则系统无法继续索引维护操作。此步骤通过执行以下语句完成：

DBCC CHECKDB WITH NO_INFOMSGS

2. 独立索引重组

完成此步骤的代码基于函数sys.dm_db_index_physical_stats。sys.dm_db_index_physical_stats返回指定数据库的数据和索引的大小和碎片信息。就我个人而言，我选择重新组织所有指数，平均碎片在10%到35%之间。

SELECT  @Reorganize = @Reorganize + ' ' + _
'ALTER INDEX [' + i.[name] + '] ON [dbo].[' + t.[name] + ']
        REORGANIZE WITH ( LOB_COMPACTION = ON )'
FROM    sys.dm_db_index_physical_stats
          (DB_ID(@DBName ), NULL, NULL, NULL , 'DETAILED') fi
        inner join sys.tables t
         on fi.[object_id] = t.[object_id]
        inner join sys.indexes i
         on fi.[object_id] = i.[object_id] and
            fi.index_id = i.index_id
where t.[name] is not null and i.[name] is not null
        and avg_fragmentation_in_percent > 10
        and avg_fragmentation_in_percent <=35
order by t.[name]

3. 确定要重建的索引

与上一步相同，此语句标识平均碎片大于35%的所有索引。对于这些类型的索引，需要执行重新生成操作。

SELECT  @Rebild = @Rebild + ' ' + _
'ALTER INDEX [' + i.[name] + '] ON [dbo].[' + t.[name] + ']
        REBUILD WITH (ONLINE = OFF )'
FROM    sys.dm_db_index_physical_stats
          (DB_ID(@DBName ), NULL, NULL, NULL , 'DETAILED') fi
        inner join sys.tables t
         on fi.[object_id] = t.[object_id]
        inner join sys.indexes i
         on fi.[object_id] = i.[object_id] and
            fi.index_id = i.index_id
where avg_fragmentation_in_percent > 35 and t.[name] is not null and i.[name] is not null
order by t.[name]

4. 更新的指数统计

最后一步是更新数据库统计信息。要更新索引，只需执行以下语句：

EXEC sp_updatestats

程序代码

CREATE PROCEDURE [dbo].[usp_Inxed_Statistics_Maintenance]
@DBName AS NVARCHAR(128)
AS

DECLARE @ERRORE INT
--Check Database Error
DBCC CHECKDB WITH NO_INFOMSGS
SET @ERRORE = @@ERROR
IF @ERRORE = 0 
BEGIN
	DECLARE @RC INT
	DECLARE @Messaggio VARCHAR(MAX)
	DECLARE @Rebild AS VARCHAR(MAX)
	DECLARE @Reorganize AS VARCHAR(MAX)

	SET @Reorganize = ''
	SET @Rebild = ''

	SELECT  @Reorganize = @Reorganize + ' ' + 
	'ALTER INDEX [' + i.[name] + '] ON [dbo].[' + t.[name] + '] 
			REORGANIZE WITH ( LOB_COMPACTION = ON )'
	FROM	sys.dm_db_index_physical_stats
			  (DB_ID(@DBName ), NULL, NULL, NULL , 'DETAILED') fi
			inner join sys.tables t
			 on fi.[object_id] = t.[object_id]
			inner join sys.indexes i
			 on fi.[object_id] = i.[object_id] and
				fi.index_id = i.index_id
	where t.[name] is not null and i.[name] is not null 
			and avg_fragmentation_in_percent > 10   
			and avg_fragmentation_in_percent <=35
	order by t.[name]

	EXEC (@Reorganize)

	SELECT  @Rebild = @Rebild + ' ' + 
	'ALTER INDEX [' + i.[name] + '] ON [dbo].[' + t.[name] + '] 
			REBUILD WITH (ONLINE = OFF )'
	FROM	sys.dm_db_index_physical_stats
			  (DB_ID(@DBName ), NULL, NULL, NULL , 'DETAILED') fi
			inner join sys.tables t
			 on fi.[object_id] = t.[object_id]
			inner join sys.indexes i
			 on fi.[object_id] = i.[object_id] and
				fi.index_id = i.index_id
	where avg_fragmentation_in_percent > 35 and t.[name] is not null and i.[name] is not null
	order by t.[name]

	EXEC (@Rebild)
END

-- if there are not error update statistics
SET @ERRORE = @@ERROR
IF @ERRORE = 0
	BEGIN
		EXEC sp_updatestats
	END
	
;

https://www.codeproject.com/Tips/1079397/Reorganize-and-Rebuild-Indexes-and-Update-Statisti