USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[IndexMaintain]    Script Date: 6/14/2013 1:46:17 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  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)
     )

 --去除DataBass为READ_ONLY
 declare dbstatus cursor for
  select name
  from sys.databases
  where DatabasePropertyEx(name,‘Updateability‘)<>‘READ_ONLY‘AND state_desc=‘ONLINE‘
 open dbstatus
 fetch next from dbstatus into @DBName
 while @@FETCH_STATUS=0
 begin
  set @sql=‘insert into #Frag(DBname, TableName,SchemaName,IndexName,AvgFragment,avg_page_space_used)
   select ‘‘[‘+@DBName+‘]‘‘ 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 ‘+@DBName+‘.sys.dm_db_index_physical_stats(DB_ID(‘‘‘+@DBName+‘‘‘),NULL,NULL,NULL,‘‘Sampled‘‘) AS s
   join ‘+@DBName+‘.sys.indexes i on s.Object_Id=i.Object_id and s.Index_id=i.Index_id
   join ‘+@DBName+‘.sys.tables t on i.Object_id=t.Object_ID
   join ‘+@DBName+‘.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‘
  exec(@sql)
  fetch next from dbstatus into @DBName
 end
 close dbstatus
 deallocate dbstatus

 --定义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
    --Fragment between 20.0 and 40.0 ,使用 Alter INDEX reorganize整理碎片
    if @avg_fragmentation_in_percent_old between 20.0 and 40.0 
    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
    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] SDS DB Index Maintainence failed from ‘ + @@SERVERNAME 
 EXEC msdb.dbo.sp_send_dbmail
 @profile_name = ‘SDSCSQL‘,                    
 @recipients = ‘SDSDBADMIN_ITDESK@quantacn.com‘,
 --@copy_recipients =‘Andy.Fan@quantacn.com;Frank.Zhao@quantacn.com‘,
 --@blind_copy_recipients =‘‘,
 @body = @EXCEPTION,
 @subject = @MailSubject
 
END
 
GO

SQL Server Rebuild Index