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
