您的位置: 首页  >  技术问答  >  在Sql Server维护计划中重组索引与重建索引

在Sql Server维护计划中重组索引与重建索引

分类: 技术问答 • 2025-08-24 17:37:28
问题描述：

在更好的SQL Server数据库的SSW规则中，有一个完整的数据库维护计划示例：SSW。在这个例子中，他们运行重组索引，然后重建索引，然后更新统计信息。这有什么意义吗？我认为重组指数是一个快速但不太有效的重建指数版本？并且索引重建也会自动更新统计信息（至少在聚集索引上）。在Sql Server维护计划中重组索引与重建索引

答

在相同索引上做REORGANIZE然后REBUILD毫无意义，因为REORGANIZE的任何更改都会因执行REBUILD而丢失。

更糟糕的是，在SSW维护计划图中，它首先执行SHRINK，这会将索引碎片化为其释放空间方式的副作用。然后REBUILD在REBUILD操作期间再次为数据库文件分配更多空间作为工作空间。

  • REORGANIZE是联机操作，使用一些额外的工作空间的页面进行碎片整理在群集或非聚集索引页的叶页。

  • REBUILD是企业版的联机操作，在其他版本中脱机，并且使用与索引大小一样多的额外工作空间。它创建索引的新副本，然后删除旧索引，从而消除碎片。作为此操作的一部分，统计信息默认重新计算，但可以禁用。

查看Reorganizing and Rebuilding Indexes了解更多信息。

不要使用SHRINK除与TRUNCATEONLY选项，即使这样，如果该文件将再次增长，那么你应该很难想象它是否是必要的：

sqlservercentral_SHRINKFILE

答

重组和重建是不同的事情。

重组：它是一个索引碎片整理。采用现有的索引并对现有页面进行碎片整理。但是，如果页面不是连续的，他们就像以前一样。只有页面的内容正在改变。

重建：实际上它会删除索引并从头重建它。这意味着你将得到一个全新的索引，具有碎片整理和连续的页面。

此外，通过重建，您可以更改分区或文件组，但通过重新组织，您不仅可以碎片整理整个索引，还可以碎片整理索引的一个分区。

更新统计信息在聚簇索引上是自动的，但不是在非聚簇索引上。

+1

对，但是在同一维护子计划中同时进行重组和重建有任何用处吗？ – codeulike 2011-08-22 10:28:56

+2

实际上，根据联机丛书 http://msdn.microsoft.com/en-us/library/ms189858.aspx Reorg DOES重组页面以使它们物理上连续。以下是确切的报价： “通过物理重新排序叶级页面以匹配叶节点的逻辑顺序（从左到右），重新组织索引对表和视图上的聚簇和非聚簇索引的叶级别进行碎片整理。以提高索引扫描性能，索引在已分配给它的现有页面内重新组织，不会分配新页面。“ – 2012-03-02 18:49:03

答

究竟是什么Biri说。这里是我会重新索引整个数据库：

答

在做索引的REORG，如果索引跨两个或多个物理文件中的数据将只在数据文件中进行磁盘碎片整理传播。页面不会从一个数据文件移动到另一个数据文件。

当索引位于单个文件中时，reorg和reindex具有相同的最终结果。

某些时候，reorg会更快，有时redex会更快，这取决于索引是多么分散。索引越不分散，reorg越快，reorg越慢，但重建索引速度越快。

答

更妙的是：

EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REINDEX'

或

EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REORGANIZE'

答

我用这个SP

CREATE PROCEDURE dbo.[IndexRebuild] 
AS 
DECLARE @TableName NVARCHAR(500); 
DECLARE @SQLIndex NVARCHAR(MAX); 
DECLARE @RowCount INT; 
DECLARE @Counter INT; 

DECLARE @IndexAnalysis TABLE 
    (
     AnalysisID INT IDENTITY(1, 1) 
        NOT NULL 
        PRIMARY KEY , 
     TableName NVARCHAR(500) , 
     SQLText NVARCHAR(MAX) , 
     IndexDepth INT , 
     AvgFragmentationInPercent FLOAT , 
     FragmentCount BIGINT , 
     AvgFragmentSizeInPages FLOAT , 
     PageCount BIGINT 
    ) 

BEGIN 
    INSERT INTO @IndexAnalysis 
      SELECT [objects].name , 
        'ALTER INDEX [' + [indexes].name + '] ON [' 
        + [schemas].name + '].[' + [objects].name + '] ' 
        + (CASE WHEN ( [dm_db_index_physical_stats].avg_fragmentation_in_percent >= 20 
            AND [dm_db_index_physical_stats].avg_fragmentation_in_percent < 40 
           ) THEN 'REORGANIZE' 
          WHEN [dm_db_index_physical_stats].avg_fragmentation_in_percent > = 40 
          THEN 'REBUILD' 
         END) AS zSQL , 
        [dm_db_index_physical_stats].index_depth , 
        [dm_db_index_physical_stats].avg_fragmentation_in_percent , 
        [dm_db_index_physical_stats].fragment_count , 
        [dm_db_index_physical_stats].avg_fragment_size_in_pages , 
        [dm_db_index_physical_stats].page_count 
      FROM [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, 
                 NULL, 'LIMITED') AS [dm_db_index_physical_stats] 
        INNER JOIN [sys].[objects] AS [objects] ON ( [dm_db_index_physical_stats].[object_id] = [objects].[object_id]) 
        INNER JOIN [sys].[schemas] AS [schemas] ON ([objects].[schema_id] = [schemas].[schema_id]) 
        INNER JOIN [sys].[indexes] AS [indexes] ON ( [dm_db_index_physical_stats].[object_id] = [indexes].[object_id] 
                  AND [dm_db_index_physical_stats].index_id = [indexes].index_id 
                 ) 
      WHERE index_type_desc <> 'HEAP' 
        AND [dm_db_index_physical_stats].avg_fragmentation_in_percent > 20 
END 

SELECT @RowCount = COUNT(AnalysisID) 
FROM @IndexAnalysis 

SET @Counter = 1 
WHILE @Counter <= @RowCount 
    BEGIN 

     SELECT @SQLIndex = SQLText 
     FROM @IndexAnalysis 
     WHERE AnalysisID = @Counter 

     EXECUTE sp_executesql @SQLIndex 

     SET @Counter = @Counter + 1 

    END 
GO

，并创建一个作业每周都会执行该SP。

答

我两毛钱......这种方法如下概述了高科技网络规范：http://technet.microsoft.com/en-us/library/ms189858(v=sql.105).aspx

USE [MyDbName] 
GO 

SET ANSI_NULLS OFF 
GO 

SET QUOTED_IDENTIFIER OFF 
GO 

CREATE PROCEDURE [maintenance].[IndexFragmentationCleanup] 
AS 
DECLARE @reIndexRequest VARCHAR(1000) 

DECLARE reIndexList CURSOR 
FOR 
SELECT INDEX_PROCESS 
FROM (
    SELECT CASE 
      WHEN avg_fragmentation_in_percent BETWEEN 5 
        AND 30 
       THEN 'ALTER INDEX [' + i.NAME + '] ON [' + t.NAME + '] REORGANIZE;' 
      WHEN avg_fragmentation_in_percent > 30 
       THEN 'ALTER INDEX [' + i.NAME + '] ON [' + t.NAME + '] REBUILD with(ONLINE=ON);' 
      END AS INDEX_PROCESS 
     ,avg_fragmentation_in_percent 
     ,t.NAME 
    FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL) AS a 
    INNER JOIN sys.indexes AS i ON a.object_id = i.object_id 
     AND a.index_id = i.index_id 
    INNER JOIN sys.tables t ON t.object_id = i.object_id 
    WHERE i.NAME IS NOT NULL 
    ) PROCESS 
WHERE PROCESS.INDEX_PROCESS IS NOT NULL 
ORDER BY avg_fragmentation_in_percent DESC 

OPEN reIndexList 

FETCH NEXT 
FROM reIndexList 
INTO @reIndexRequest 

WHILE @@FETCH_STATUS = 0 
BEGIN 
    BEGIN TRY 

     PRINT @reIndexRequest; 

     EXEC (@reIndexRequest); 

    END TRY 

    BEGIN CATCH 
     DECLARE @ErrorMessage NVARCHAR(4000); 
     DECLARE @ErrorSeverity INT; 
     DECLARE @ErrorState INT; 

     SELECT @ErrorMessage = 'UNABLE TO CLEAN UP INDEX WITH: ' + @reIndexRequest + ': MESSAGE GIVEN: ' + ERROR_MESSAGE() 
      ,@ErrorSeverity = 9 
      ,@ErrorState = ERROR_STATE(); 

    END CATCH; 

    FETCH NEXT 
    FROM reIndexList 
    INTO @reIndexRequest 
END 

CLOSE reIndexList; 

DEALLOCATE reIndexList; 

RETURN 0 

GO

答

我研究了网页，发现了一些好文章的。在和我写下面的函数和脚本是重新组织，重新创建或重建数据库中的所有索引。

首先，您可能需要阅读this article以了解为什么我们不只是重新创建所有索引。

其次，我们需要一个函数来为索引构建创建脚本。所以this article可能会有所帮助。我也在下面分享工作职能。

最后一步做一个while循环来查找和组织数据库中的所有索引。 This video是很好的例子。

功能：

create function GetIndexCreateScript(
    @index_name nvarchar(100) 
) 
returns nvarchar(max) 
as 
begin 

declare @Return varchar(max) 

SELECT @Return = ' CREATE ' + 
    CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END + 
    I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' + 
    I.name + ' ON ' + 
    Schema_name(T.Schema_id)+'.'+T.name + ' (' + 
    KeyColumns + ') ' + 
    ISNULL(' INCLUDE ('+IncludedColumns+') ','') + 
    ISNULL(' WHERE '+I.Filter_definition,'') + ' WITH (' + 
    CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ',' + 
    'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ',' + 
    -- default value 
    'SORT_IN_TEMPDB = OFF ' + ',' + 
    CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ',' + 
    CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ',' + 
    -- default value 
    ' DROP_EXISTING = ON ' + ',' + 
    -- default value 
    ' ONLINE = OFF ' + ',' + 
    CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ',' + 
    CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END + ') ON [' + 
    DS.name + ' ] ' 
FROM sys.indexes I 
JOIN sys.tables T ON T.Object_id = I.Object_id  
JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid 
JOIN (SELECT * FROM ( 
    SELECT IC2.object_id , IC2.index_id , 
     STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END 
    FROM sys.index_columns IC1 
    JOIN Sys.columns C 
     ON C.object_id = IC1.object_id 
     AND C.column_id = IC1.column_id 
     AND IC1.is_included_column = 0 
    WHERE IC1.object_id = IC2.object_id 
     AND IC1.index_id = IC2.index_id 
    GROUP BY IC1.object_id,C.name,index_id 
    ORDER BY MAX(IC1.key_ordinal) 
     FOR XML PATH('')), 1, 2, '') KeyColumns 
    FROM sys.index_columns IC2 
    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables 
    GROUP BY IC2.object_id ,IC2.index_id) tmp3)tmp4 
    ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id 
JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id 
JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id 
JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id 
LEFT JOIN (SELECT * FROM ( 
    SELECT IC2.object_id , IC2.index_id , 
     STUFF((SELECT ' , ' + C.name 
    FROM sys.index_columns IC1 
    JOIN Sys.columns C  
     ON C.object_id = IC1.object_id  
     AND C.column_id = IC1.column_id  
     AND IC1.is_included_column = 1 
    WHERE IC1.object_id = IC2.object_id  
     AND IC1.index_id = IC2.index_id  
    GROUP BY IC1.object_id,C.name,index_id 
     FOR XML PATH('')), 1, 2, '') IncludedColumns  
    FROM sys.index_columns IC2  
    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables 
    GROUP BY IC2.object_id ,IC2.index_id) tmp1 
    WHERE IncludedColumns IS NOT NULL) tmp2  
ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id 
WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0 
AND I.[name] = @index_name 

return @Return 

end

SQL进行同时：

declare @RebuildIndex Table(
    IndexId int identity(1,1), 
    IndexName varchar(100), 
    TableSchema varchar(50), 
    TableName varchar(100), 
    Fragmentation decimal(18,2) 
) 


insert into @RebuildIndex (IndexName,TableSchema,TableName,Fragmentation) 
SELECT 
    B.[name] as 'IndexName', 
    Schema_Name(O.[schema_id]) as 'TableSchema', 
    OBJECT_NAME(A.[object_id]) as 'TableName', 
    A.[avg_fragmentation_in_percent] Fragmentation 
FROM sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED') A 
INNER JOIN sys.indexes B ON A.[object_id] = B.[object_id] and A.index_id = B.index_id 
INNER JOIN sys.objects O ON O.[object_id] = B.[object_id] 
where B.[name] is not null and B.is_primary_key = 0 AND B.is_unique_constraint = 0 and A.[avg_fragmentation_in_percent] >= 5 

--select * from @RebuildIndex 

declare @begin int = 1 
declare @max int 
select @max = Max(IndexId) from @RebuildIndex 
declare @IndexName varchar(100), @TableSchema varchar(50), @TableName varchar(100) , @Fragmentation decimal(18,2) 

while @begin <= @max 
begin 

    Select @IndexName = IndexName from @RebuildIndex where IndexId = @begin 
    select @TableSchema = TableSchema from @RebuildIndex where IndexId = @begin 
    select @TableName = TableName from @RebuildIndex where IndexId = @begin 
    select @Fragmentation = Fragmentation from @RebuildIndex where IndexId = @begin 

    declare @sql nvarchar(max) 
    if @Fragmentation < 31 
    begin 
     set @sql = 'ALTER INDEX ['[email protected]+'] ON ['[email protected]+'].['[email protected]+'] REORGANIZE WITH (LOB_COMPACTION = ON)' 
     print 'Reorganized Index ' + @IndexName + ' for ' + @TableName + ' Fragmentation was ' + convert(nvarchar(18),@Fragmentation) 
    end 
    else 
    begin 
     set @sql = (select dbo.GetIndexCreateScript(@IndexName)) 
     if(@sql is not null) 
     begin 
      print 'Recreated Index ' + @IndexName + ' for ' + @TableName + ' Fragmentation was ' + convert(nvarchar(18),@Fragmentation) 
     end 
     else 
     begin 
      set @sql = 'ALTER INDEX ['[email protected]+'] ON ['[email protected]+'].['[email protected]+'] REBUILD PARTITION = ALL WITH (ONLINE = ON)' 
      print 'Rebuilded Index ' + @IndexName + ' for ' + @TableName + ' Fragmentation was ' + convert(nvarchar(18),@Fragmentation) 
     end 
    end 

    execute(@sql) 


    set @begin = @begin+1 

end

答

在考虑用索引的维护，重要的是要回答两个主要问题：

  1. 什么程度不成？
  2. 什么是适当的操作？重组或重建？

正如本文http://solutioncenter.apexsql.com/why-when-and-how-to-rebuild-and-reorganize-sql-server-indexes/描述，并帮助您确定是否应该执行索引重建或索引重组，请了解以下内容：

  • 索引重组是一个过程，其中的SQL Server出现通过现有的索引，并清理它。索引重建是一个繁重的过程，索引被删除，然后从全新的结构重新创建，从所有堆积的碎片和空白页面中解放出来。

  • 虽然索引重组是一种纯粹的清理操作，它保留了系统状态而不锁定受影响的表和视图，但重建过程会锁定整个重建期间的受影响的表，这可能会导致较长的停机时间在某些环境中不可接受。 考虑到这一点，显而易见，索引重建是一个具有“更强”解决方案的过程，但它带有价格 - 可能会影响索引表的长锁。

另一方面，索引重组是一个“轻量级”的过程，这将解决碎片在一个不太有效的方式 - 因为清洁指数永远是第二从头完全取得了新的。但是从效率角度来看，重组索引要好得多，因为它在操作过程中并未锁定受影响的索引表。

上述文章还解释了如何使用SSMS，T-SQL（重新组织/重建表中的索引）和名为ApexSQL Backup的第三方工具重新组织和重建索引。

相关推荐