Sqlserver——日常维护——定期重建索引

大致的重建范围是 逻辑密度碎片>30以及页码总数>8以上的数据库索引碎片

直接上代码（附带生成执行完之后生成日志记录表）

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
 
Create PROCEDURE [dbo].[p_index_rebuild_test]
AS
 
 
 
DECLARE @DatabaseName NVARCHAR(255)--数据库名
DECLARE @SchemaName NVARCHAR(255)--架构名
DECLARE @TableName NVARCHAR(255)--表名
DECLARE @IndexName NVARCHAR(255)--索引名
DECLARE @IndexType NVARCHAR(255)--索引类型
DECLARE @IndexColums NVARCHAR(MAX)--索引列清单
DECLARE @DuringTime INT--重建时间
DECLARE @Before_avg_fragmentation_in_percent DECIMAL(19,8)--重建前的逻辑扫描碎片
DECLARE @After_avg_fragmentation_in_percent DECIMAL(19,8)--重建后的逻辑扫描碎片
 
 
SET XACT_ABORT ON 
SET NOCOUNT ON 
 
-----------定期重建索引执行计划--------
 
BEGIN TRAN
 
----------1、创建记录表,写入本次索引重构的日志
--日志表名
DECLARE @LogTableName NVARCHAR(255)='ReBuildIndexLog_'+CONVERT(VARCHAR(100),GETDATE(),112)
 
DECLARE @sql NVARCHAR(MAX)=''
 
 
 
IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(@LogTableName))
BEGIN
 
SET @sql='drop table '+@LogTableName
EXEC (@sql)
 
END
 
SET @sql ='Create Table '+@LogTableName+'
		   (
		   DatabaseName nvarchar(255),
		   SchemaNam nvarchar(255),
		   TableName nvarchar(255),
		   IndexName nvarchar(255),
		   IndexType nvarchar(255),
		   IndexColums nvarchar(255),
		   DuringTime int,
		   Before_avg_fragmentation_in_percent DECIMAL(19,8),
		   After_avg_fragmentation_in_percent DECIMAL(19,8)
		   ) 
		   '
EXEC (@sql);
SELECT @sql
 
---------2、查询逻辑扫描碎片大于30,并且数据页大于8以上的数据表,并插入到临时表中
 
SELECT  database_id,
		object_id,
		index_id,
		index_type_desc,
		IDENTITY(INT,1,1) AS ID,
		partition_number
		INTO #Temp_A
FROM    sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), NULL, NULL, NULL,
                                       'Sampled')
WHERE   avg_fragmentation_in_percent > 30
        AND page_count > 8;
 
 
 
--------3、循环所有的临时表记录
 
DECLARE @i INT =1
DECLARE @q INT =(SELECT MAX(ID) FROM #Temp_A)
DECLARE @Btime DATETIME--开始时间
DECLARE @Etime DATETIME--结束时间
DECLARE @IndexID INT--索引ID
DECLARE @PartitionNum INT --分区编号
WHILE @i<=@q
BEGIN
SELECT  @DatabaseName = d.name ,--数据库名
        @SchemaName = 'dbo' ,--架构名
        @TableName = t.name ,--表名
		@IndexID=a.index_id,--索引ID
        @IndexName = i.name ,--索引名
        @IndexType = a.index_type_desc,--索引类型
		@PartitionNum=a.partition_number,--分区编号
        @IndexColums = ( SELECT c.name + ','
                         FROM   sys.index_columns ic
                                INNER JOIN sys.columns c ON ic.object_id = c.object_id
                                                            AND ic.index_column_id = c.column_id
                         WHERE  ic.object_id = a.object_id
                                AND ic.index_id = a.index_id
                       FOR
                         XML PATH('')
                       )--索引列
FROM    #Temp_A a
        INNER JOIN sys.databases d ON a.database_id = d.database_id
        INNER JOIN sys.tables t ON a.object_id = t.object_id
        INNER JOIN sys.indexes i ON a.object_id = i.object_id
                                    AND a.index_id = i.index_id
		WHERE a.ID=@i
 
SET @Btime=GETDATE()
 
--PRINT '循环:'+CONVERT(NVARCHAR(20),@i)
 
 
SET @Before_avg_fragmentation_in_percent = ( SELECT TOP 1 avg_fragmentation_in_percent
                                             FROM   sys.dm_db_index_physical_stats(DB_ID(@DatabaseName),
                                                              OBJECT_ID(@TableName),
                                                              @IndexID, @PartitionNum,
                                                              'Sampled')
                                           );
SET @sql='alter index '+@IndexName+' on '+@TableName+' REBUILD With(FILLFACTOR=90)'
 
EXEC(@sql);
 
 
SET @After_avg_fragmentation_in_percent = ( SELECT TOP 1 avg_fragmentation_in_percent
                                             FROM   sys.dm_db_index_physical_stats(DB_ID(@DatabaseName),
                                                              OBJECT_ID(@TableName),
                                                              @IndexID, @PartitionNum,
                                                              'Sampled')
                                           );
 
 
SET @Etime=GETDATE()
 
SET @DuringTime=DATEDIFF(MILLISECOND,@Btime,@Etime)
 
 
 
SET @sql='insert into '+@LogTableName+'
		  select '+CHAR(39)+@DatabaseName+CHAR(39)+',
				 '+CHAR(39)+@SchemaName+CHAR(39)+',
				 '+CHAR(39)+@TableName+CHAR(39)+',
				 '+CHAR(39)+@IndexName+CHAR(39)+',
				 '+CHAR(39)+@IndexType+CHAR(39)+',
				 '+CHAR(39)+@IndexColums+CHAR(39)+',
				 '+CHAR(39)+CONVERT(NVARCHAR(255),@DuringTime)+CHAR(39)+',
				 '+CHAR(39)+CONVERT(NVARCHAR(255),@Before_avg_fragmentation_in_percent)+CHAR(39)+',
				 '+CHAR(39)+CONVERT(NVARCHAR(255),@After_avg_fragmentation_in_percent)+CHAR(39)+'
				 '
EXEC(@sql)
 
 
SET @i=@i+1;
END
 
 
 
 
COMMIT TRAN
 
 
 
 
 
 
GO

　来源：https://blog.csdn.net/qq_40205468/article/details/87289730