SQL Server 重新组织生成索引

概述  

无论何时对基础数据执行插入、更新或删除操作，SQL Server 数据库引擎都会自动维护索引。随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中（含有碎片）。当索引包含的页中的逻辑排序（基于键值）与数据文件中的物理排序不匹配时，就存在碎片。碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢，所以在日常的维护工作当中就需要对索引进行检查对那些填充度很低碎片量大的索引进行重新生成或重新组织，但是在这个过程也需要注意一些小的细节，否则会产生错误。

正文

语法内容载自SQL Server联机丛书，标记出了需要注意的内容，最后分享自己平时用的维护索引的语句供参考。

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [ [PARTITION = ALL]
          [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
          | [ PARTITION = partition_number 
                [ WITH ( <single_partition_rebuild_index_option>
                        [ ,...n ] )
                ] 
            ]
        ]
    | DISABLE
    | REORGANIZE 
        [ PARTITION = partition_number ]
        [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
  | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
        table_or_view_name
}

<rebuild_index_option > ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor 
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE } 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE } }
}

<set_index_option>::=
{
    ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

PARTITION

指定只重新生成或重新组织索引的一个分区。如果 index_name 不是已分区索引，则不能指定 PARTITION。

PARTITION = ALL 重新生成所有分区。当指定PARTITION = ALL时不能使用ONLINE = ON

partition_number

要重新生成或重新组织已分区索引的分区数。partition_number 是可以引用变量的常量表达式。其中包括用户定义类型变量或函数以及用户定义函数，但不能引用 Transact-SQL 语句。partition_number 必须存在，否则，该语句将失败。

WITH (<single_partition_rebuild_index_option>)

SORT_IN_TEMPDB、MAXDOP 和 DATA_COMPRESSION 是在重新生成单个分区 (PARTITION = n) 时可以指定的选项。不能在单个分区重新生成操作中指定 XML 索引。

不能联机重新生成分区索引。在此操作过程中将锁定整个表。

DISABLE

将索引标记为已禁用，从而不能由 数据库引擎使用。任何索引均可被禁用。已禁用的索引的索引定义保留在没有基础索引数据的系统目录中。禁用聚集索引将阻止用户访问基础表数据。若要启用索引，请使用 ALTER INDEX REBUILD 或 CREATE INDEX WITH DROP_EXISTING。

REORGANIZE

指定将重新组织的索引叶级。此子句等同于 DBCC INDEXDEFRAG。ALTER INDEX REORGANIZE 语句始终联机执行。这意味着不保留长期阻塞的表锁，且对基础表的查询或更新可以在 ALTER INDEX REORGANIZE 事务处理期间继续。不能为已禁用的索引或 ALLOW_PAGE_LOCKS 设置为 OFF 的索引指定 REORGANIZE。

WITH ( LOB_COMPACTION = { ON | OFF } )

指定压缩所有包含大型对象 (LOB) 数据的页。LOB 数据类型包括 image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 和 xml。压缩此数据可以改善磁盘空间使用情况。默认值为 ON。

ON

压缩所有包含大型对象数据的页。

重新组织指定的聚集索引将压缩聚集索引中包含的所有 LOB 列。重新组织非聚集索引将压缩作为索引中非键（已包括）列的所有 LOB 列。有关详细信息，请参阅创建带有包含列的索引。

指定 ALL 时，将重新组织与指定表或视图相关联的所有索引，并且压缩与聚集索引、基础表或具有包含列的非聚集索引相关联的所有 LOB 列。

OFF

不压缩包含大型对象数据的页。

OFF 对堆没有影响。

如果 LOB 列不存在，则忽略 LOB_COMPACTION 子句。

SET ( <set_index option> [ ,...n] )

指定不重新生成或重新组织索引的索引选项。不能为已禁用的索引指定 SET。

PAD_INDEX = { ON | OFF }

指定索引填充。默认值为 OFF。

ON

FILLFACTOR 指定的可用空间百分比应用于索引的中间级页。如果在 PAD_INDEX 设置为 ON 的同时不指定 FILLFACTOR，则使用 sys.indexes 中存储的填充因子值。

OFF 或不指定 fillfactor

中间级页已填充到接近容量限制。这样将至少为索引可以基于中间页中的键集拥有的最大大小的一行留出足够的空间。

FILLFACTOR = fillfactor

指定一个百分比，指示在创建或更改索引期间，数据库引擎对各索引页的叶级填充的程度。fillfactor 必须为介于 1 至 100 之间的整数值。默认值为 0。

填充因子的值 0 和 100 在所有方面都是相同的。

 

显式的 FILLFACTOR 设置只是在索引首次创建或重新生成时应用。数据库引擎并不会在页中动态保持指定的可用空间百分比。有关详细信息，请参阅 CREATE INDEX (Transact-SQL)。

若要查看填充因子设置，请使用 sys.indexes。

使用 FILLFACTOR 值创建或更改聚集索引会影响数据占用的存储空间量，因为数据库引擎在创建聚集索引时会再分发数据。

 

SORT_IN_TEMPDB = { ON | OFF }

指定是否在 tempdb 中存储排序结果。默认值为 OFF。

ON

在 tempdb 中存储用于生成索引的中间排序结果。如果 tempdb 位于不同于用户数据库的磁盘集中，这样可能会缩短创建索引所需的时间。但是，这会增加索引生成期间所使用的磁盘空间量。

OFF

中间排序结果与索引存储在同一数据库中。

如果不需要执行排序操作，或者可以在内存中进行排序，则忽略 SORT_IN_TEMPDB 选项。

 

IGNORE_DUP_KEY = { ON | OFF }

指定在插入操作尝试向唯一索引插入重复键值时的错误响应。IGNORE_DUP_KEY 选项仅适用于创建或重新生成索引后发生的插入操作。当执行 CREATE INDEX、ALTER INDEX 或 UPDATE 时，该选项无效。默认值为 OFF。

ON

向唯一索引插入重复键值时将出现警告消息。只有违反唯一性约束的行才会失败。

OFF

向唯一索引插入重复键值时将出现错误消息。整个 INSERT 操作将被回滚。

对于对视图创建的索引、非唯一索引、XML 索引、空间索引以及筛选的索引，IGNORE_DUP_KEY 不能设置为 ON。

若要查看 IGNORE_DUP_KEY，请使用 sys.indexes。

在向后兼容的语法中，WITH IGNORE_DUP_KEY 等效于 WITH IGNORE_DUP_KEY = ON。

STATISTICS_NORECOMPUTE = { ON | OFF }

指定是否重新计算分发统计信息。默认值为 OFF。

ON

不会自动重新计算过时的统计信息。

OFF

启用统计信息自动更新功能。

若要恢复统计信息自动更新，请将 STATISTICS_NORECOMPUTE 设置为 OFF，或执行 UPDATE STATISTICS 但不包含 NORECOMPUTE 子句。

如果禁用分发统计信息的自动重新计算，可能会阻止查询优化器为涉及该表的查询挑选最佳执行计划。

ONLINE = { ON | OFF }

指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。默认值为 OFF。

对于 XML 索引或空间索引，仅支持 ONLINE = OFF。如果 ONLINE 设置为 ON，则会引发错误。

联机索引操作仅在 SQL Server Enterprise Edition、Developer Edition 和 Evaluation Edition 中可用。

ON

在索引操作期间不持有长期表锁。在索引操作的主要阶段，源表上只使用意向共享 (IS) 锁。这样，即可继续对基础表和索引进行查询或更新。操作开始时，将对源对象保持极短时间的共享 (S) 锁。操作结束时，如果创建非聚集索引，将对源持有极短时间的 S 锁；当联机创建或删除聚集索引时，或者重新生成聚集或非聚集索引时，将获取 SCH-M（架构修改）锁。对本地临时表创建索引时，ONLINE 不能设置为 ON。

OFF

在索引操作期间应用表锁。创建、重新生成或删除聚集索引、空间索引或 XML 索引或者重新生成或删除非聚集索引的脱机索引操作将获得对表的架构修改 (Sch-M) 锁。这样可以防止所有用户在操作期间访问基础表。创建非聚集索引的脱机索引操作将对表获取共享 (S) 锁。这样可以防止更新基础表，但允许读操作（如 SELECT 语句）。

有关详细信息，请参阅联机索引操作的工作方式。有关锁的详细信息，请参阅锁模式。

索引（包括全局临时表中的索引）可以联机重新生成，但以下索引除外：

• 禁用的索引
  
  
• XML 索引
  
  
• 本地临时表中的索引
  
  
• 分区索引
  
  
• 聚集索引（如果基础表包含 LOB 数据类型）。
  
  
• 使用 LOB 数据类型列定义的非聚集索引
  
  

如果表包含 LOB 数据类型，但这些列中没有任何列在索引定义中用作键列或非键列，则可以联机重新生成非聚集索引。

ALLOW_ROW_LOCKS = { ON | OFF }

指定是否允许行锁。默认值为 ON。

ON

在访问索引时允许使用行锁。数据库引擎确定何时使用行锁。

OFF

不使用行锁。

ALLOW_PAGE_LOCKS = { ON | OFF }

指定是否允许使用页锁。默认值为 ON。

ON

访问索引时允许使用页锁。数据库引擎确定何时使用页锁。

OFF

不使用页锁。

ALLOW_PAGE_LOCKS 设置为 OFF 时，无法重新组织索引。

MAXDOP = max_degree_of_parallelism

在索引操作期间覆盖“最大并行度”配置选项。有关详细信息，请参阅 max degree of parallelism 选项。使用 MAXDOP 可以限制在执行并行计划的过程中使用的处理器数量。最大数量为 64 个处理器。

 虽然从语法上讲所有 XML 索引都支持 MAXDOP 选项，但对于空间索引或主 XML 索引，ALTER INDEX 当前只使用一个处理器。

max_degree_of_parallelism 可以是：

1

取消生成并行计划。

>1

将并行索引操作中使用的最大处理器数量限制为指定数量。

0（默认值）

根据当前系统工作负荷使用实际的处理器数量或更少数量的处理器。

 并行索引操作仅在 SQL Server Enterprise Edition、Developer Edition 和 Evaluation Edition 中可用。

DATA_COMPRESSION

为指定的索引、分区号或分区范围指定数据压缩选项。选项如下所示：

NONE

不压缩索引或指定的分区。

ROW

使用行压缩来压缩索引或指定的分区。

PAGE

使用页压缩来压缩索引或指定的分区。

有关压缩的详细信息，请参阅创建压缩表和索引。

ON PARTITIONS ( { <partition_number_expression> | <range> } [,...n] )

指定对其应用 DATA_COMPRESSION 设置的分区。如果索引未分区，则 ON PARTITIONS 参数将产生错误。如果不提供 ON PARTITIONS 子句，则 DATA_COMPRESSION 选项将应用于分区索引的所有分区。

可以按以下方式指定 <partition_number_expression>：

• 提供一个分区号，例如：ON PARTITIONS (2)。
  
  
• 提供若干单独分区的分区号并用逗号将它们隔开，例如：ON PARTITIONS (1, 5)。
  
  
• 同时提供范围和单独的分区：ON PARTITIONS (2, 4, 6 TO 8)。
  
  

<range> 可以指定为以单词 TO 隔开的分区号，例如：ON PARTITIONS (6 TO 8)。

若要为不同分区设置不同的数据压缩类型，请多次指定 DATA_COMPRESSION 选项，例如：

REBUILD WITH 
(
DATA_COMPRESSION = NONE ON PARTITIONS (1), 
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), 
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
)

 

大型对象数据类型压缩

SET NOCOUNT ON
DECLARE @Objectid INT, @Indexid INT,@schemaname VARCHAR(100),@tablename VARCHAR(300),@ixname VARCHAR(500),@avg_fip float,@command VARCHAR(4000)
DECLARE IX_Cursor CURSOR FOR
SELECT  A.object_id,A.index_id,QUOTENAME(SS.NAME) AS schemaname,QUOTENAME(OBJECT_NAME(B.object_id,B.database_id))as tablename ,QUOTENAME(A.name) AS ixname,B.avg_fragmentation_in_percent AS avg_fip FROM sys.indexes A inner join  sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,‘LIMITED‘) AS B 
ON A.object_id=B.object_id and A.index_id=B.index_id 
INNER JOIN SYS.OBJECTS OS ON A.object_id=OS.object_id
INNER JOIN sys.schemas SS ON OS.schema_id=SS.schema_id
WHERE B.avg_fragmentation_in_percent>10 and B.page_count>20    AND A.index_id>0 AND A.IS_DISABLED<>1
ORDER BY tablename,ixname
OPEN IX_Cursor
FETCH NEXT FROM IX_Cursor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fip
WHILE @@FETCH_STATUS=0
BEGIN
    
    IF @avg_fip<30.0
    SET @command=N‘ALTER  INDEX ‘+@ixname+N‘ ON ‘+@schemaname+N‘.‘+ @tablename+N‘ REORGANIZE ‘;
    IF @avg_fip>=30.0 AND @Indexid=1
        BEGIN
        IF EXISTS (SELECT * FROM SYS.columns WHERE OBJECT_ID=@Objectid AND max_length in(-1,16))
        SET @command=N‘ALTER  INDEX ‘+@ixname+N‘ ON ‘+@schemaname+N‘.‘+ @tablename+N‘ REBUILD ‘;
        ELSE
        SET @command=N‘ALTER  INDEX ‘+@ixname+N‘ ON ‘+@schemaname+N‘.‘+ @tablename+N‘ REBUILD ‘+N‘ WITH (ONLINE = ON)‘;
        END
    IF @avg_fip>=30.0 AND @Indexid>1
        BEGIN    
        IF EXISTS (SELECT * FROM  SYS.index_columns IC INNER JOIN SYS.columns CS ON CS.OBJECT_ID=IC.OBJECT_ID AND CS.column_id=IC.column_id
                   WHERE  IC.OBJECT_ID=@Objectid AND IC.index_id=@Indexid AND CS.max_length in(-1,16) )
        SET @command=N‘ALTER  INDEX ‘+@ixname+N‘ ON ‘+@schemaname+N‘.‘+ @tablename+N‘ REBUILD ‘;
        ELSE
        SET @command=N‘ALTER  INDEX ‘+@ixname+N‘ ON ‘+@schemaname+N‘.‘+ @tablename+N‘ REBUILD ‘+N‘ WITH (ONLINE = ON)‘;
        END
    --PRINT @command
    EXEC(@command)
 
 FETCH NEXT FROM IX_Cursor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fip
END

CLOSE IX_Cursor
DEALLOCATE IX_Cursor

总结

 文章里面有很多细的知识点需要注意，也是很容易被忽略的地方。