Menu
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).
But what does SQL do in OFFLINE mode?
345
SQL Server index reorganizes operation is always executed online. On the other hand, rebuilding an index can be executed online, without locking other queries when using SQL Server Enterprise edition, or offline, by holding locks on the database objects during the rebuild operation.
The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations. For example, while a clustered index is being rebuilt by one user, that user and others can continue to update and query the underlying data.
"Reorganize index" is a process of cleaning, organizing, and defragmenting of "leaf level" of the B-tree (really, data pages). Rebuilding of the index is changing the whole B-tree, recreating the index.
Web indexing, or internet indexing, comprises methods for indexing the contents of a website or of the Internet as a whole. Individual websites or intranets may use a back-of-the-book index, while search engines usually use keywords and metadata to provide a more useful vocabulary for Internet or onsite searching.
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.
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.
156
The main differences are:
1) OFFLINE index rebuild is faster than ONLINE rebuild.
2) Extra disk space required during SQL Server online index rebuilds.
3) SQL Server locks acquired with SQL Server online index rebuilds.
37
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."
http://msdn.microsoft.com/en-us/library/ms188388(v=sql.110).aspx
Additionally online index rebuilds are a enterprise (or developer) version only feature.
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
