无论何时对基础数据执行插入、更新或删除操作,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 }
}
已分区表和已分区索引。
ALLOW_PAGE_LOCKS 设置为 OFF 时,无法重新组织索引。
ALTER INDEX 不能用于对索引重新分区或将索引移到其他文件组。此语句不能用于修改索引定义,如添加或删除列,或更改列的顺序。使用带有 DROP_EXISTING 子句的 CREATE INDEX 执行这些操作。
未显式指定选项时,则应用当前设置。例如,如果未在 REBUILD 子句中指定 FILLFACTOR 设置,将在重新生成过程中使用系统目录中存储的填充因子值。若要查看当前索引选项设置,请使用 sys.indexes。
系统目录中不存储 ONLINE、MAXDOP 和 SORT_IN_TEMPDB 的值。除非在索引语句中指定,否则,将使用选项的默认值。
在多处理器计算机中,就像其他查询那样,ALTER INDEX REBUILD 自动使用更多处理器来执行与修改索引相关联的扫描和排序操作。运行 ALTER INDEX REORGANIZE 时,无论是否有 LOB_COMPACTION,“max degree of parallelism”值均为单个线程化操作。有关详细信息,请参阅配置并行索引操作。
如果索引所在的文件组脱机或设置为只读,则无法重新组织或重新生成索引。如果指定了关键字 ALL,但有一个或多个索引位于脱机文件组或只读文件组中,该语句将失败。
重新生成索引将会删除并重新创建索引。这将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间,然后对连续页中的索引行重新排序。如果指定 ALL,将删除表中的所有索引,然后在单个事务中重新生成。不必预先删除 FOREIGN KEY 约束。重新生成具有 128 个区或更多区的索引时,数据库引擎延迟实际的页释放及其关联的锁,直到事务提交。有关详细信息,请参阅删除并重新生成大型对象。
重新生成或重新组织小索引不会减少碎片。小索引的页面存储在混合区中。混合区最多可由八个对象共享,因此在重新组织或重新生成小索引之后可能不会减少小索引中的碎片。
在早期版本的 SQL Server 中,您有时可以重新生成非聚集索引来更正由硬件故障导致的不一致。在 SQL Server 2008 中,您仍然可以通过脱机重新生成非聚集索引来纠正索引和聚集索引之间的这种不一致。但是,您不能通过联机重新生成索引来纠正非聚集索引的不一致,因为联机重新生成机制将会使用现有的非聚集索引作为重新生成的基础,因此仍存在不一致。相反,脱机重新生成索引将会强制扫描聚集索引(或堆),因此会删除不一致。与早期版本一样,建议通过从备份还原受影响的数据来从不一致状态进行恢复;但是,您可以通过脱机重新生成非聚集索引来纠正索引的不一致。
使用最少系统资源重新组织索引。通过对叶级页以物理方式重新排序,使之与叶节点的从左到右的逻辑顺序相匹配,进而对表和视图中的聚集索引和非聚集索引的叶级进行碎片整理。重新组织还会压缩索引页。压缩基于现有的填充因子值。
如果指定 ALL,将重新组织表中的关系索引(包括聚集索引和非聚集索引)和 XML 索引。指定 ALL 时应用某些限制,请参阅“参数”部分的 ALL 定义。
禁用索引可防止用户访问该索引,对于聚集索引,还可防止用户访问基础表数据。索引定义保留在系统目录中。对视图禁用非聚集索引或聚集索引会以物理方式删除索引数据。禁用聚集索引将阻止对数据的访问,但在删除或重新生成索引之前,数据在 B 树中一直保持未维护的状态。
如果表位于事务复制发布中,则无法禁用任何与主键列关联的索引。复制需要使用这些索引。若要禁用索引,必须先从发布中删除该表。
使用 ALTER INDEX REBUILD 语句或 CREATE INDEX WITH DROP_EXISTING 语句启用索引。重新生成已禁用聚集索引不能在 ONLINE 选项设置为 ON 时执行。
您可以为指定的索引设置选项 ALLOW_ROW_LOCKS、ALLOW_PAGE_LOCKS、IGNORE_DUP_KEY 和 STATISTICS_NORECOMPUTE,而不重新生成或重新组织该索引。修改的值立即应用于索引。
如果 ALLOW_ROW_LOCKS = ON 并且 ALLOW_PAGE_LOCK = ON,则当访问索引时将允许行级别、页级别和表级别的锁。数据库引擎将选择相应的锁,并且可以将锁从行锁或页锁升级到表锁。
如果 ALLOW_ROW_LOCKS = OFF 并且 ALLOW_PAGE_LOCK = OFF,则当访问索引时只允许表级锁。有关为索引配置锁定粒度的详细信息,请参阅自定义索引的锁定。
设置行锁或页锁选项时,如果指定 ALL,这些设置将应用于所有索引。基础表为堆时,通过以下方式应用这些设置:
锁升级(数据库引擎)。
重新生成索引且 ONLINE 选项设置为 ON 时,基础对象、表和关联的索引均可用于查询和数据修改。更改过程中,排他表锁只保留非常短的时间。
重新组织索引始终联机执行。该进程不长期保留锁,因此,不阻塞正在运行的查询或更新。
只有在执行以下操作时,才能对同一个表执行并发联机索引操作:
同一时间执行的所有其他联机索引操作都将失败。例如,您不能在同一个表中同时重新生成两个索引或更多索引,也不能在同一个表中重新生成现有索引时创建新的索引。
有关详细信息,请参阅联机执行索引操作。
重新生成空间索引时,基础用户表在索引操作持续期间不可用,因为空间索引持有架构锁。
对用户表的某一列定义了空间索引时,无法修改该表中的 PRIMARY KEY 约束。若要更改 PRIMARY KEY 约束,首先要删除该表的每个空间索引。修改 PRIMARY KEY 约束后,您可以重新创建每个空间索引。
在单个分区重新生成操作中,无法指定任何空间索引。但是,您可以在完整的分区重新生成过程中指定空间索引。
若要更改特定于某个空间索引的选项(例如 BOUNDING_BOX 或 GRID),您可以使用 CREATE SPATIAL INDEX 语句指定 DROP_EXISTING = ON,或删除该空间索引并创建一个新的空间索引。有关示例,请参阅 CREATE SPATIAL INDEX (Transact-SQL)。
若要评估更改压缩状态将对表、索引或分区有何影响,请使用 sp_estimate_data_compression_savings 存储过程。
以下限制适用于已分区索引:
重新组织索引时,除了重新组织一个或多个索引外,默认情况下还将压缩聚集索引或基础表中包含的大型对象数据类型 (LOB)。数据类型 image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 都是大型对象数据类型。压缩此数据可以改善磁盘空间使用情况:
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
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_fipENDCLOSE IX_CursorDEALLOCATE IX_Cursor
注意:该语句不适合所有人,大家根据自己的需求进行修改。
文章里面有很多细的知识点需要注意,也是很容易被忽略的地方。