SQL Server 中的 OFFLINE 和 ONLINE 索引重建有什么区别？

What is the difference between OFFLINE and ONLINE index rebuild in SQL Server?

提问by esac

When rebuilding an index, there is an option for ONLINE=OFF and ONLINE=ON. I know that when ONLINE mode is on, it makes a copy of the index, switches new queries to utilizing it, and then rebuilds the original index, tracking changes using versioning to both (correct me if I am wrong).

重建索引时，有一个 ONLINE=OFF 和 ONLINE=ON 选项。我知道当 ONLINE 模式打开时，它会复制索引，切换新查询以使用它，然后重建原始索引，使用版本控制跟踪两者的更改（如果我错了，请纠正我）。

But what does SQL do in OFFLINE mode?

但是 SQL 在离线模式下会做什么呢？

回答by Remus Rusanu

In ONLINE mode the new index is built while the old index is accessible to reads and writes. any update on the old index will also get applied to the new index. An antimatter column is used to track possible conflicts between the updates and the rebuild (ie. delete of a row which was not yet copied). See Online Index Operations. When the process is completed the table is locked for a brief period and the new index replaces the old index. If the index contains LOB columns, ONLINE operations are not supported in SQL Server 2005/2008/R2.

在 ONLINE 模式下，会构建新索引，而旧索引可供读取和写入访问。对旧索引的任何更新也将应用于新索引。反物质列用于跟踪更新和重建之间可能的冲突（即删除尚未复制的行）。请参阅在线索引操作。该过程完成后，表会被锁定一小段时间，新索引将替换旧索引。如果索引包含 LOB 列，则 SQL Server 2005/2008/R2 不支持 ONLINE 操作。

In OFFLINE mode the table is locked upfront for any read or write, and then the new index gets built from the old index, while holding a lock on the table. No read or write operation is permitted on the table while the index is being rebuilt. Only when the operation is done is the lock on the table released and reads and writes are allowed again.

在离线模式下，表被预先锁定以进行任何读取或写入，然后从旧索引构建新索引，同时在表上保持锁定。重建索引时不允许对表进行读或写操作。只有当操作完成时，表上的锁才被释放，再次允许读写。

Note that in SQL Server 2012 the restriction on LOBs was lifted, see Online Index Operations for indexes containing LOB columns.

请注意，在 SQL Server 2012 中取消了对 LOB 的限制，请参阅包含 LOB 列的索引的在线索引操作。

回答by dunos

Online index rebuilds are less intrusive when it comes to locking tables. Offline rebuilds cause heavy locking of tables which can cause significant blocking issues for things that are trying to access the database while the rebuild takes place.

当涉及到锁定表时，在线索引重建的侵入性较小。离线重建会导致表的重锁定，这可能会导致在重建发生时尝试访问数据库的事物出现严重阻塞问题。

"Table locks are applied for the duration of the index operation [during an offline rebuild]. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements."

“在索引操作的持续时间内应用表锁 [在离线重建期间]。创建、重建或删除聚集索引、空间索引或 XML 索引，或者重建或删除非聚集索引的离线索引操作，获取架构表上的修改 (Sch-M) 锁。这会阻止所有用户在操作期间访问基础表。创建非聚集索引的离线索引操作会获取表上的共享 (S) 锁。这会阻止更新到基础表，但允许读取操作，例如 SELECT 语句。”

http://msdn.microsoft.com/en-us/library/ms188388(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms188388(v=sql.110).aspx

Additionally online index rebuilds are a enterprise (or developer) version only feature.

此外，在线索引重建是企业（或开发人员）版本独有的功能。

回答by Bhavin Katrodiya

The main differences are:

主要区别是：

1) OFFLINE index rebuild is faster than ONLINE rebuild.

1) 离线索引重建比在线重建快。

2) Extra disk space required during SQL Server online index rebuilds.

2) SQL Server 联机索引重建期间需要额外的磁盘空间。

3) SQL Server locks acquired with SQL Server online index rebuilds.

3) 通过 SQL Server 联机索引重建获得的 SQL Server 锁。

• This schema modification lock blocks all other concurrent access to the table, but it is only held for a very short period of time while the old index is dropped and the statistics updated.

• 此模式修改锁会阻止对表的所有其他并发访问，但只会在删除旧索引和更新统计信息时保持很短的时间。