This topic defines the structures that exist during an online index operation and shows the activities associated with these structures.
Online Index Structures
To allow for concurrent user activity during an index data definition language (DDL) operation, the following structures are used during the online index operation: source and preexisting indexes, target, and for clustered indexes, a temporary mapping index.
- Source and preexisting indexes
The source is the original table or clustered index data. Preexisting indexes are any nonclustered indexes that are associated with the source structure. For example, if the online index operation is rebuilding a clustered index that has four associated nonclustered indexes, the source is the existing clustered index and the preexisting indexes are the nonclustered indexes.
The preexisting indexes are available to concurrent users for select, insert, update, and delete operations. This includes bulk inserts (supported but not recommended) and implicit updates by triggers and referential integrity constraints. All preexisting indexes are available for queries and searches. This means they may be selected by the query optimizer and, if necessary, specified in index hints. - Target
The target or targets is the new index (or heap) or a set of new indexes that is being created or rebuilt. User insert, update, and delete operations to the source are applied by the SQL Server 2005 Database Engine to the target during the index operation. For example, if the online index operation is rebuilding a clustered index, the target is the rebuilt clustered index; the Database Engine does not rebuild nonclustered indexes when a clustered index is rebuilt.
The target index is not searched while processing SELECT statements until the index operation is committed. Internally, the index is marked as write-only. - Temporary mapping index
Online index operations that create, drop, or rebuild a clustered index also require a temporary mapping index. This temporary index is used by concurrent transactions to determine which records to delete in the new indexes that are being built when rows in the underlying table are updated or deleted. This nonclustered index is created in the same step as the new clustered index (or heap) and does not require a separate sort operation. Concurrent transactions also maintain the temporary mapping index in all their insert, update, and delete operations.
Online Index Activities
During a simple online index operation, such as creating a clustered index on a nonindexed table (heap), the source and target go through three phases: preparation, build, and final.
The following illustration shows the process for creating an initial clustered index online. The source object, the heap, has no other indexes. The source and target structure activities are shown for each phase; and concurrent user select, insert, update, and delete operations are also shown. The preparation, build, and final phases are indicated together with the lock modes used in each phase.
Source Structure Activities
The following table lists the activities involving the source structures during each phase of the index operation and the corresponding locking strategy.
* The index operation will wait for any uncommitted update transactions to complete before acquiring the S-lock or SCH-M lock on the table.
** The resource lock INDEX_BUILD_INTERNAL_RESOURCE prevents the execution of concurrent DDL operations on the source and preexisting structures while the index operation is in progress. For example, this lock prevents concurrent rebuild of two indexes on the same table. Although this resource lock is associated with the Sch-M lock, it does not prevent data manipulation statements.
The previous table shows a single Shared (S) lock acquired during the build phase of an online index operation that involves a single index. When clustered and nonclustered indexes are built, or rebuilt, in a single online index operation (for example, during the initial clustered index creation on a table that contains one or more nonclustered indexes) two short-term Shared (S) locks are acquired during the build phase followed by long-term IS locks. One S lock is acquired first for the clustered index creation and when creating the clustered index is completed, a second short-term S lock is acquired for creating the nonclustered indexes. After the nonclustered indexes are created, the S lock is downgraded to an IS lock until the final phase of the online index operation.
Target Structure Activities
The following table lists the activities that involve the target structure during each phase of the index operation and the corresponding locking strategy.
The target is not accessed by SELECT statements issued by the user until the index operation is completed.
After the preparation and final phase is completed, the query and update plans that are stored in the procedure cache are invalidated. Subsequent queries will use the new index.
The lifetime of a cursor declared on a table that is involved in an online index operation is limited by the online index phases. Update cursors are invalidated at each phase. Read-only cursors are invalidated only after the final phase.
The most matrix comparing some of the key functional differences between the rebuild and reorganize options:
| Functionality | REBUILD / DBREINDEX | REORGANIZE / DEFRAG |
| Online / Offline | Offline; Online possible with SQL 2005 & higher versions with Enterprise Edition only | Online |
| Faster when logical fragmentation is | High | Low |
| Parallel Processing | Yes | No |
| Compacts Pages | Yes | Yes |
| Can be stopped/started without losing completed work to that point | No | Yes |
| Able to untangle interleaved pages | Yes | No |
| Additional free space required in data file for process | Yes - 1.2x - 2x existing size | No |
| Faster on larger indexes | Yes | No |
| Rebuilds Statistics | Yes | No |
| Log Space Used | Full Recovery - High; Bulk/Simple Recovery - Low; | Depends on work performed |
| May skip pages on busy system | No | Yes |
| Can specify additional options (fill factor, etc.) rebuild pause\resume | Yes Yes (SQL 2017 & higher versions and less than SQL 2017 version not possible) | No No |
One other option to 'addressing' fragmentation could be to choose to not fix it. Hopefully you're not the type of engineer who simply rebuilds/defrags indexes every day just for the sake of doing so or because it makes you feel better - instead, try to work out a plan to understand which indexes in your schema actually matter the most, are impacted by the different kinds of fragmentation, etc. and those that aren't.
Let's take a look at the internals for defrag and reorganizing - reorganizing an index is performed in basically 2 stages:
- The page compaction stage - this stage is meant to try and make pages have a 'fullness' near the original fillfactor specification for the index. This operates at the leaf-level of the index only, and pages are compacted by shuffling rows towards the left-side of the B-tree and dropping ghosted records and freeing pages made empty.
- Page defrag-ing stage - this stage is meant to make the logical order of pages match the allocation order. Again this operates at the leaf-level only, by performing a logical-ordered scan and an allocation-ordered scan in lockstep with one another, re-establishing a scan position after every page. This is a totally online operation, aside from an eXclusive lock on each page for the duration of each given re-ordering. Remember that no new pages are allocated during a defrag operation (aside from the single page for temporary space), which means that logical re-ordering is achieved by shuffling pages that are already allocated to the index only - this is why a defrag operation can't solve interleaved pages, since only existing pages are shuffled.
Ref:https://www.mssqltips.com/sqlservertip/2268/sql-server-fragmentation-how-to-address-it-part-8-of-9/
