重建(重新组织)索引的存储过程

本文介绍了一个用于批量重建 SQL Server 数据库索引的存储过程,支持按数据库、表及索引名称进行筛选,并可根据碎片率阈值选择是否进行重建。此外,还提供了一个更为复杂的存储过程示例,该过程能够根据不同碎片率自动选择索引重建或重新组织。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

重建(重新组织)索引的存储过程

USE [你的dbname]
GO
/****** Object:  StoredProcedure [dbo].[P_BatchRebuildIndex]    Script Date: 2017/7/4 15:23:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: <2017-07-01>
-- Description:    <批量重建索引>
-- EXEC P_BatchRebuildIndex  'dbo','','',30
-- =============================================
Create proc [dbo].[P_BatchRebuildIndex]
--@DataBase nvarchar(50)=null,--数据库名称,必输项
@Scheme nvarchar(50)=null,--架构名称
@TableName nvarchar(50)=null, --要重建索引的表名称,为空表示所有表
@IndexName nvarchar(200)=null ,--要重建的索引名称
@FragmentPercent decimal(10,5)=0 --碎片率为多少以上的药重建
AS
BEGIN

DECLARE @Sql nvarchar(2000); 
DECLARE @RebuildSql nvarchar(2000); 
DECLARE @ERROR nvarchar(500);
DECLARE @Tables table(TableName nvarchar(100), Indexid int,IndexName nvarchar(200), FragmentPercent decimal(10,5));

----判断数据库是否存在
--IF DB_ID(@DataBase) is null
--BEGIN
--        RAISERROR('数据库不存在,请输入正确的数据库名称!',16,1);
--        RETURN -1;
--END 

 
--判断架构是否存在
IF isnull(@Scheme,'')<>'' and SCHEMA_ID(@Scheme) is null
BEGIN
        RAISERROR('架构不存在,请输入正确的架构名称!',16,1);
        RETURN -1;
END

 
--表存不存在
IF isnull(@TableName,'')<>''
BEGIN
        --有架构
        IF isnull(@Scheme,'')<>'' and SCHEMA_ID(@Scheme) is null
        BEGIN
                IF OBJECT_ID(@Scheme+'.'+@TableName) is null
                BEGIN
                        SET @ERROR='表不存在,请输入正确的表名称!'
                        RAISERROR(@ERROR,16,2);
                        RETURN -1;
                END
        END
        --没有架构
        IF not exists(select 1 from sys.tables as A  where A.name=@TableName   ) 
        BEGIN
                SET @ERROR='表不存在,请输入正确的表名称!'
                RAISERROR(@ERROR,16,5);
                RETURN -1;
        END
END

--索引存不存在
IF isnull(@IndexName,'')<>''
BEGIN
        IF not exists(select 1 from sys.SysObjects where name=@IndexName and (xtype=N'UQ' OR xtype=N'PK') )
        BEGIN
                RAISERROR('索引不存在,请输入正确的索引名称!',16,3);
                RETURN -1;
        END
END

--如果表名为空表示所有表,如果索引为空表示所有索引
--默认查询所有表,所有索引,所有百分比
 
SET @Sql='SELECT D.name+''.''+object_name(a.object_id) [TableName] ,a.index_id ,B.name [IndexName] ,avg_fragmentation_in_percent   
        FROM sys.dm_db_index_physical_stats ( DB_ID() , NULL , NULL, NULL, NULL ) AS A
        JOIN sys.indexes AS B ON a.object_id = b.object_id AND a.index_id = b.index_id
        inner JOIN sys.tables AS C ON a.object_id=C.object_id
        inner JOIN sys.schemas AS D ON C.schema_id=D.schema_id
        WHERE   a.index_id > 0 '

IF isnull(@Scheme,'') <>''
BEGIN
        SET @Sql +=' and D.name= '''+@Scheme+''' ';
END
IF  isnull(@TableName,'')<>''
BEGIN
        SET @Sql +=' and object_name(a.object_id)= '''+@TableName+''' ';
END
IF  isnull(@IndexName,'')<>''
BEGIN
        SET @Sql +=' and B.name= '''+@IndexName+''' ';
END

IF  isnull(@FragmentPercent,0)>0
BEGIN
        SET @Sql +=' and avg_fragmentation_in_percent>= '+convert(nvarchar(10),@FragmentPercent)+' ';
END
 
INSERT INTO @Tables
EXEC (@Sql)
select '重建前',* from @Tables

DECLARE cur cursor for
        select TableName,  IndexName from @Tables 
OPEN cur
DECLARE @tbName NVARCHAR(100),@IXName NVARCHAR(200)
FETCH NEXT FROM cur INTO @tbName,@IXName
WHILE(@@fetch_status=0)
BEGIN
        SET @RebuildSql='alter index ['+@IXName+'] on '+@tbName+' rebuild' --要加上[],否则索引里有空格会报错
        EXEC (@RebuildSql)
		print @RebuildSql
        FETCH NEXT FROM cur INTO @tbName,@IXName
END
CLOSE cur
DEALLOCATE cur

--重建后查询
delete @Tables
INSERT INTO @Tables
EXEC (@Sql)
select '重建后',* from @Tables

Print N'执行完毕!';
return 0;
END

这个sp在sqlserver 2012,2014上均运行过没有问题,这个用于批量重建比较方便,如果是针对单个表也可以运行这样的sql语句来做如下:

alter index [IX_T_NewsTags_NewsId] on dbo.T_NewsTags rebuild
当然在 sqlserver management studio 中打到单个表,打开,在索引上右键,可以针对单个表的某个索引进行重建或重新组织。

下面附上一个即有重建,也有重新组织索引的存储过程

USE [msdb]
GO
/****** Object:  StoredProcedure [dbo].[IndexMaintain]    Script Date: 09/14/2012 17:59:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 -- =============================================
-- Author
-- Create date:  2012/01/17
-- Description:    IndexMaintain
-- ==============================================
ALTER  procedure  [dbo].[IndexMaintain]  
as   
SET NOCOUNT on 
 
BEGIN TRY 
    declare @EXCEPTION VARCHAR(MAX)
    declare @MailSubject NVARCHAR(255)
    declare @DBName NVARCHAR(255)
    declare @TableName NVARCHAR(255)
    declare @SchemaName NVARCHAR(255)
    declare @IndexName NVARCHAR(255)
    declare @avg_fragmentation_in_percent_old DECIMAL(18,3)
    declare @avg_page_space_used_in_percent_old DECIMAL(18,3)
    declare @avg_fragmentation_in_percent_new DECIMAL(18,3)
    declare @avg_page_space_used_in_percent_new DECIMAL(18,3)
            
    declare @Defrag NVARCHAR(max)
    declare @Sql NVARCHAR(max)
    declare @ParmDefinition nvarchar(500)
    set @EXCEPTION=''
    
    --删除#Frag
    if exists(select *  from sys.objects where object_id=object_id(N'#Frag'))
    drop table #Frag 
       
    --定义临时表#Frag保存index Fragment    
    create table #Frag(
                 DBname  NVARCHAR(255),
                 TableName NVARCHAR(255),
                 SchemaName NVARCHAR(255),
                 IndexName NVARCHAR(255),
                 AvgFragment DECIMAL(18,3),
                 avg_page_space_used DECIMAL(18,3)
                 )
                 
    --遍历DB中所有table上的index,并将Fragment保存到临时表#Frag中.
    exec sp_MSforeachdb @command1= 'insert into #Frag(DBname, TableName,SchemaName,IndexName,AvgFragment,avg_page_space_used)
                                    select ''[?]'' AS DBName, t.Name AS TableName, sc.Name AS SchemaName, i.name AS IndexName, s.avg_fragmentation_in_percent, s.avg_page_space_used_in_percent  
                                    from [?].sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''Sampled'') AS s
                                    join [?].sys.indexes i on s.Object_Id=i.Object_id and s.Index_id=i.Index_id
                                    join [?].sys.tables t on i.Object_id=t.Object_ID 
                                    join [?].sys.schemas sc on t.schema_id=sc.SCHEMA_ID
                                    where s.avg_fragmentation_in_percent  >20 and t.type=''U'' and s.page_count>8 and i.allow_page_locks=1 and i.allow_row_locks=1 
                                    order by  TableName,IndexName '

    --定义CURSOR遍历临时表#Frag,根据Fragment大小采取不同的方案维护index.
    declare cList CURSOR for
            select *  from #Frag 
    open cList 
    fetch next from cList into @DBName,@TableName,@SchemaName,@IndexName,@avg_fragmentation_in_percent_old,@avg_page_space_used_in_percent_old
    while @@FETCH_STATUS=0
    begin 
       set @TableName ='['+ @TableName +']'
       --Fragment between 20.0 and 40.0 ,使用 Alter INDEX reorganize整理碎片
       if @avg_fragmentation_in_percent_old between 20.0 and 40.0 AND @DBName <>'[TOPCOA]' 
       begin          
         --整理碎片
         set @Defrag=N'Alter INDEX   '+''+@IndexName+' on '+@DBName+'.'+@SchemaName+'.'+@TableName+' reorganize'
         exec sp_executesql @Defrag
                  
         --获取index被整理后的碎片比例
         set @Sql=N'USE '+@DBName+'; select @avg_fragmentation_in_percent_new_temp=s.avg_fragmentation_in_percent,@avg_page_space_used_in_percent_new_temp= s.avg_page_space_used_in_percent 
                    from  '+@DBName+'.sys.indexes i  
                    inner join '+@DBName+'.sys.dm_db_index_physical_stats(db_id(replace(replace('''+@DBName+''',''['',''''),'']'','''')), object_id('''+@TableName+''''+'),null,null,''sampled'') as s on   i.index_id=s.index_id  
                    where i.object_id=object_id('''+@TableName+''''+')and i.name='''+@IndexName+''''
         set @ParmDefinition=N'@avg_fragmentation_in_percent_new_temp  DECIMAL(18,3) output,@avg_page_space_used_in_percent_new_temp DECIMAL(18,3) output'
         exec sp_executesql @Sql,@ParmDefinition ,@avg_fragmentation_in_percent_new_temp=@avg_fragmentation_in_percent_new output, @avg_page_space_used_in_percent_new_temp=@avg_page_space_used_in_percent_new output
         
         --write log
         insert [dbo].IndexDefrag values(@DBName,@TableName,@SchemaName,@IndexName,getdate(),@avg_fragmentation_in_percent_old,@avg_page_space_used_in_percent_old,@avg_fragmentation_in_percent_new,@avg_page_space_used_in_percent_new,'0')
       end 
        --Fragment大于40.0 ,使用 Alter INDEX rebuild整理碎片
       else if @avg_fragmentation_in_percent_old >40.0 AND @DBName <>'[TOPCOA]'
       begin          
         --整理碎片 
         set @Defrag=N'Alter INDEX    '+''+@IndexName+' on '+@DBName+'.'+@SchemaName+'.'+@TableName+' rebuild'
         exec sp_executesql @Defrag 
         
         --获取index被整理后的碎片比例
         set @Sql=N'USE '+@DBName+';select @avg_fragmentation_in_percent_new_temp=s.avg_fragmentation_in_percent,@avg_page_space_used_in_percent_new_temp= s.avg_page_space_used_in_percent 
                    from  '+@DBName+'.sys.indexes i  
                    inner join '+@DBName+'.sys.dm_db_index_physical_stats(db_id(replace(replace('''+@DBName+''',''['',''''),'']'','''')), object_id('''+@TableName+''''+'),null,null,''sampled'') as s on   i.index_id=s.index_id  
                    where i.object_id=object_id('''+@TableName+''''+')and i.name='''+@IndexName+''''
         set @ParmDefinition=N'@avg_fragmentation_in_percent_new_temp  DECIMAL(18,3) output,@avg_page_space_used_in_percent_new_temp DECIMAL(18,3) output'
         exec sp_executesql @Sql,@ParmDefinition ,@avg_fragmentation_in_percent_new_temp=@avg_fragmentation_in_percent_new output, @avg_page_space_used_in_percent_new_temp=@avg_page_space_used_in_percent_new output
         
         --write log
         insert [dbo].IndexDefrag values(@DBName,@TableName,@SchemaName,@IndexName,getdate(),@avg_fragmentation_in_percent_old,@avg_page_space_used_in_percent_old,@avg_fragmentation_in_percent_new,@avg_page_space_used_in_percent_new,'1')
        end 
      fetch next from cList into @DBName,@TableName,@SchemaName,@IndexName,@avg_fragmentation_in_percent_old,@avg_page_space_used_in_percent_old
    end 
    close cList 
    deallocate cList 
END TRY
BEGIN CATCH
    SET @EXCEPTION = ERROR_MESSAGE() 
END CATCH

IF @EXCEPTION<>''
BEGIN 
    SET @MailSubject='[Important]DB Index Maintainence failed from ' + @@SERVERNAME  
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'mail',                     
    @recipients = 'xxxxxx@xxx.com',
    @body = @EXCEPTION, 
    @subject = @MailSubject 
    
END


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值