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

骑猪看日落 2022-06-13 09:55 209阅读 0赞

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

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