Sql Server索引重建

公司线上数据有几千万数据，有时候索引碎片会导致索引达不到我们的预期查询效率，这个时候将索引重建将会提升一定效率，不过重建的时候一定得晚上用户少的时候，索引重建需要一定时间。

直接贴自动重建索引脚本吧

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 IN ( 'ChemicalInfo_ALLFULL_New' )
    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;