SQLServer 索引重建存储过程

SQLServer建议索引碎片在5%--30%之间用重新组织索引（REORGANIZE），超过30%的重新生成索引（REBUILD）

生成索引可以在线也可以离线（ONLINE = OFF）：离线索引不能访问。

USE [zws]

GO


/****** Object:  StoredProcedure [dbo].[p_index_rebuild]    Script Date: 2018/1/9 11:05:22 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO






CREATE  PROCEDURE [dbo].[p_index_rebuild]
AS


BEGIN
        -- 动态创建日志表
IF NOT EXISTS (SELECT 1 FROM SYS.objects A WHERE A.object_id=OBJECT_ID('DBO.SYS_REINDEX_LOG'))
BEGIN
CREATE TABLE DBO.SYS_REINDEX_LOG(start_time datetime,
 end_time   datetime,
 DBName     VARCHAR(100),
 SchemaName VARCHAR(100),
 table_name    VARCHAR(100),
 index_name    VARCHAR(100),
 index_type    VARCHAR(100),
 Duration      INT,         -- 执行时间(秒)
 Sql_Text      VARCHAR(500),
 is_success    int
 )
END


-- 动态创建重建索引列表
IF NOT EXISTS (SELECT 1 FROM SYS.objects A WHERE A.object_id=OBJECT_ID('DBO.REINDEX_LIST'))
BEGIN
CREATE TABLE DBO.REINDEX_LIST   (DBName     VARCHAR(100),
 SchemaName VARCHAR(100),
 table_name    VARCHAR(100),
 index_name    VARCHAR(100),
 index_type    VARCHAR(100),
 page_count    int,
 fragment_per  numeric(18, 6),
 rn            int
 )
END


    --判断数据库版本
--根据索引类型排序，聚集索引放在前面
DECLARE @sqlversion INT,@sql NVARCHAR(2000);
IF (@@VERSION LIKE '%Enterprise%' OR @@VERSION LIKE '%Evaluation%' OR @@VERSION LIKE '%Developer%') SET @sqlversion=1;
IF SUBSTRING(@@VERSION,22,4) < '2008' SET @sqlversion=-1;
IF @sqlversion=-1
set @sql='insert into DBO.REINDEX_LIST (DBName,
SchemaName,
table_name,
index_name,
index_type,
page_count,
fragment_per,
rn
)
SELECT '''' DBName,
'''' SchemaName,
o.name table_name,
i.name as index_name,
i.type_desc index_type,
0 as page_count,
0 AS fragment_per,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) rn
FROM sys.indexes i INNER JOIN sys.objects o ON o.object_id=i.object_id 
WHERE o.type=''U'' AND i.type IN (1,2)
ORDER BY charindex(i.type_desc,'',CLUSTERED,NONCLUSTERED,'')';
ELSE
SET  @sql='insert into DBO.REINDEX_LIST (DBName,
SchemaName,
table_name,
index_name,
index_type,
page_count,
fragment_per,
rn
)
SELECT DB_NAME(A.database_id) DBName,d.name SchemaName,b.name table_name,c.name as index_name,a.index_type_desc index_type,a.page_count,a.avg_fragmentation_in_percent fragment_per,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) rn
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,''LIMITED'')  as a  
INNER JOIN sys.objects AS b ON a.object_id =b.object_id   and b.type=''U''
INNER JOIN sys.indexes AS c ON a.object_id=c.object_id and a.index_id=c.index_id 
INNER JOIN sys.schemas AS d ON b.SCHEMA_ID=d.schema_id 
WHERE a.index_id>0 
AND a.page_count>8
and b.type=''U''
and a.avg_fragmentation_in_percent>10
ORDER BY charindex(a.index_type_desc,'',CLUSTERED INDEX,NONCLUSTERED INDEX,'' )';


--截断表
truncate table  DBO.REINDEX_LIST;
exec(@sql);


declare @n int;
     declare @i int=1;
declare @tart_time datetime;
declare @end_time  datetime;
declare @DBName    VARCHAR(100);
declare @SchemaName VARCHAR(100);
declare @table_name VARCHAR(100);
     declare @index_name VARCHAR(100);
     declare @index_type VARCHAR(100);
declare @sqlindex NVARCHAR(1000);


select @n=count(*) from DBO.REINDEX_LIST;
while  isnull(@n,0)>0 and @i<=@n
      begin
       SQLBEGIN:
                BEGIN
                 select @DBName=DBName,@SchemaName=SchemaName,@table_name=table_name,@index_name=index_name,@index_type=index_type from DBO.REINDEX_LIST where rn=@i;
                END
      
   BEGIN TRY
       if @sqlversion=-1
  begin
      SET @sqlindex=N' DBCC DBREINDEX('+ @table_name +',['+@index_name+'])';
  end
if @sqlversion=1
  begin
      SET @sqlindex=N' ALTER INDEX ['+@index_name+'] ON '+ @SchemaName +'.'+ @table_name +' REBUILD' 
  end


  set @tart_time=getdate();
  exec(@sqlindex);
  set @end_time=getdate();
     insert into DBO.SYS_REINDEX_LOG(start_time,
 end_time,
 DBName,
 SchemaName,
 table_name,
 index_name,
 index_type,
 Duration,         -- 执行时间(秒)
 Sql_Text,
 is_success
         )
values(@tart_time,@end_time,@DBName,@SchemaName,@table_name,@index_name,@index_type,DATEDIFF(ss,@tart_time,@end_time),@sqlindex,1);
SET @i=@i+1;
                END TRY


       BEGIN CATCH
         SET @i=@i+1
                      insert into DBO.SYS_REINDEX_LOG(start_time,
 end_time,
 DBName,
 SchemaName,
 table_name,
 index_name,
 index_type,
 Duration,         -- 执行时间(秒)
 Sql_Text,
 is_success
         )
values(@tart_time,@end_time,@DBName,@SchemaName,@table_name,@index_name,@index_type,DATEDIFF(ss,@tart_time,@end_time),@sqlindex,0);
         GOTO SQLBEGIN
       END CATCH


      end;






END;








GO