SQL Server 重新组织生成索引

标签：SQL SERVER/MSSQL SERVER/数据库/DBA/索引/统计信息

概述  

无论何时对基础数据执行插入、更新或删除操作，Server.aspx' target='_blank'>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 }}

如果表包含 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 个处理器。

 虽然从语法上讲所有

提供若干单独分区的分区号并用逗号将它们隔开，例如：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))

在同一个表中重新组织不同索引。

在同一个表中重新生成不重叠的索引时，重新组织不同的索引。

同一时间执行的所有其他联机索引操作都将失败。例如，您不能在同一个表中同时重新生成两个索引或更多索引，也不能在同一个表中重新生成现有索引时创建新的索引。

有关详细信息，请参阅联机执行索引操作。

ALTER INDEX <index> ...REBUILD PARTITION ... 语法可重新生成索引的指定分区。

ALTER INDEX <index> ...REBUILD WITH ... 语法可重新生成索引的所有分区。

大型对象数据类型压缩

重新组织索引时，除了重新组织一个或多个索引外，默认情况下还将压缩聚集索引或基础表中包含的大型对象数据类型 (LOB)。数据类型 image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 和  都是大型对象数据类型。压缩此数据可以改善磁盘空间使用情况：

• 重新组织指定的聚集索引将压缩该聚集索引的叶级别（数据行）包含的所有 LOB 列。
• 重新组织非聚集索引将压缩该索引中属于非键（包含性）列的所有 LOB 列。
• 如果指定 ALL，将重新组织与指定的表或视图相关联的所有索引，并压缩与聚集索引、基础表或带有包含列的非聚集索引相关联的所有 LOB 列。
• 如果 LOB 列不存在，则忽略 LOB_COMPACTION 子句。

语句

 

SET NOCOUNT ONDECLARE @Objectid INT, @Indexid INT,@schemaname VARCHAR(100),@tablename VARCHAR(300),@ixname VARCHAR(500),@avg_fip float,@command VARCHAR(4000)DECLARE IX_Cursor CURSOR FORSELECT 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_idINNER JOIN sys.schemas SS ON OS.schema_id=SS.schema_idWHERE B.avg_fragmentation_in_percent>10 and B.page_count>20  AND A.index_id>0 AND A.IS_DISABLED<>1ORDER BY tablename,ixnameOPEN IX_CursorFETCH NEXT FROM IX_Cursor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fipWHILE @@FETCH_STATUS=0BEGIN    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
总结
 文章里面有很多细的知识点需要注意，也是很容易被忽略的地方。
 
如果文章对大家有帮助，希望大家能给个推荐，谢谢！！！
 

 
         
        
        
原标题：SQL Server 重新组织生成索引
        
关键词：sql