SQLServer 索引重建

SQL Server 索引重建脚本

　　在数据的使用过程中，由于索引page碎片过多，带来一些不利的性能问题，我们有时候需要对数据库中的索引进行重组或者重建工作。通常这个阈值为30%，大于30%我们建议进行索引重建，小于则进行重组操作。以下脚本可以针对固定的数据库进行自动的索引重建和重组工作：

　　脚本来源于网络，如侵犯版权请联系删除：

set nocount on  
--使用游标重新组织指定库中的索引,消除索引碎片  
--R_T层游标取出当前数据库所有表  
declare R_T cursor  
    for select name from sys.tables  
declare @T varchar(50)  
open r_t  
fetch next from r_t into @t  
while @@fetch_status=0  
 begin  
 --R_index游标判断指定表索引碎片情况并优化  
 declare R_Index cursor  
 for select t.name,i.name,s.avg_fragmentation_in_percent from sys.tables t  
   join sys.indexes i on i.object_id=t.object_id  
   join sys.dm_db_index_physical_stats(db_id(),object_id(@T),null,null,'limited') s  
    on s.object_id=i.object_id and s.index_id=i.index_id  
 declare @TName varchar(50),@IName varchar(50),@avg int,@str varchar(500)  
 open r_index  
 fetch next from r_index into @TName,@Iname,@avg  
 while @@fetch_status=0  
 begin  
   if @avg>=30  --如果碎片大于30,重建索引  
   begin  
    set @str='alter index '+rtrim(@Iname)+' on dbo.'+quotename(rtrim(@tname))+' rebuild'  
   end  
   else   --如果碎片小于30,重新组织索引  
   begin  
    set @STR='alter index '+rtrim(@Iname)+' on dbo.'+quotename(rtrim(@tname))+' reorganize'  
   end  
   print @str  
   exec (@str)  --执行  
   fetch next from r_index into @TName,@Iname,@avg  
 end  
 --结束r_index游标  
 close r_index  
 deallocate r_index  
 fetch next from r_t into @t  
 end  
 --结束R_T游标  
 close r_t  
 deallocate r_t  
 set nocount off 

 

　　上述代码可以针对指定的数据库，进行索引的重组重建的工作，我们也可以针对数据库中的所有数据库进行相应的工作，以下代码来源于 ： https://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/ 

DECLARE @Database VARCHAR(255)   
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT 

SET @fillfactor = 90 

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM master.dbo.sysdatabases   
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')   
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + 
  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES 
  WHERE table_type = ''BASE TABLE'''   

   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor   

   FETCH NEXT FROM TableCursor INTO @Table   
   WHILE @@FETCH_STATUS = 0   
   BEGIN   

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
       BEGIN
           -- SQL 2005 or higher command 
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 
           EXEC (@cmd) 
       END
       ELSE
       BEGIN
          -- SQL 2000 command 
          DBCC DBREINDEX(@Table,' ',@fillfactor)  
       END

       FETCH NEXT FROM TableCursor INTO @Table   
   END   

   CLOSE TableCursor   
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor

　　大家可以根据代码灵活的选择重建和重组索引，并设置不同的阈值。微软件推荐索引的填充因子为90.