What is the first word that comes to mind whenever one mentions the need for optimizing search or for increasing the performance of a database? Index.
Indexes play a vital role in SQL Server performance tuning, but they too need periodic maintenance. An index is logically implemented as a B-tree.
Over time as data is accumulated by the table, fragmentation may set in and the distribution of data within the index may become unbalanced, with some pages becoming full (as governed by the fill factor) leading to “page splits” whereas others might be sparsely filled. If data is deleted from the table, it leaves behind a “hole” in the index page – this free space is not reclaimed, which contributes to the bloating of the index. This reduces the efficiency of the indexes, while possibly consuming more than required disk space. At this stage, administrators need to perform maintenance operations on the index.
Index Maintenance operations can be classified into two:
- Index Rebuild
- During a rebuild, the entire index (from the root to the leaf nodes) is rebuilt using the same columns, index type, uniqueness attribute, and sort order
- The rebuild of an index effectively recreates the entire B-tree structure
- During a rebuild, the entire index (from the root to the leaf nodes) is rebuilt using the same columns, index type, uniqueness attribute, and sort order
- Index Reorganize
- Index reorganization, on the other hand, only removes fragmentation at the leaf level
- Intermediate-level pages and the root page are not defragmented
- Index reorganization, on the other hand, only removes fragmentation at the leaf level
From an availability perspective, Reorganization is better than a rebuild, because reorganization is always an ONLINE process, i.e. it acquires only short-term locks whereas REBUILD prevents any changes to the underlying table until the operation completes, i.e. it acquires long-term locks. The question therefore is:
What types of locks do index REBUILD and index REORGANIZE operations occupy on the underlying table, by default (i.e. the “ONLINE” option is not specified)?
Bonus Exercise
As a bonus exercise, you may want to share any queries or tests that you may have conducted to arrive at the answer to the question above. Please note that the bonus exercise does not carry any rewards this time around – it is only as an exercise for the brain cells.
Rules
The rules are very simple:
- You can answer these questions till Wednesday 22:00hrs (IST)/Wednesday 12:30hrs (US-EDT) – August 15, 2012
- Answers will be shared and winners declared in my post on Thursday, August 16, 2012
- You can answer multiple times – however, at the end of the day, only one entry would be considered
- Follow the following on Twitter!
- Pluralsight (Handle is: @pluralsight)
- BeyondRelational.com (Handle: @beyondrel)
- Me! (Handle is: @nakulv_sql )
- Pluralsight (Handle is: @pluralsight)
- Winners will be chosen randomly
- Prizes will be given out through Twitter – hence, it is important that you follow me
Remember: Participation is always more important! Make sure you reply to the quiz even if it’s just a guess!
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 
If ONLINE option is not specified, when a **non-clustered index is rebuilt**, a **shared table lock** occupies the table, preventing all but SELECT operations. When a **clustered index is rebuilt, an exclusive table lock** occupies the table, preventing any table access to the table.
Index **REORGANIZE doesn’t lock the table** and it’s always online operation. This operation puts some temporary locks on the pages it works with, but they are removed as soon as the operation is complete.
LikeLike
Same as above
LikeLike
> REBUILD prevents any changes to the
> underlying table until the operation
> completes
No, this is not always true. When ONLINE = ON (Available with Enterprise Edition), “Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This allows queries or updates to the underlying table and indexes to continue”
http://msdn.microsoft.com/en-us/library/ms188388.aspx
> From an availability perspective,
> Reorganization is better than a
> rebuild, because reorganization is
> always an ONLINE process, i.e. it
> acquires only short-term locks whereas
> REBUILD prevents any changes to the
> underlying table until the operation
> completes, i.e. it acquires long-term
> locks
This is simply not true. From an availability perspective, if an index is fragmented more than 30%, REBUILD ONLINE = ON is the better solution. It will take less time and fewer server resources than doing a REORGANIZE.
LikeLike
@Marc: The whole question has an assumption that we are dealing with “default” behaviour, i.e. for REBUILDs, ONLINE = OFF; for REORGANIZE, ONLINE is always ON.
LikeLike
The statement “if an index is fragmented more than 30%, REBUILD is the better solution” stands. You’ll have to schedule a maintenance window as the underlying table will be unavailable. If this requires an unacceptable period of time, this is your motivation and cost-justification to move to Enterprise Edition, where REBUILD ONLINE=ON is available.
When one makes statements that include “better” or “best”, it makes sense to qualify that with WHY one is “better” or “best”. If I need something that happens quickly, then REBUILD is better than REORGANIZE, given a level of fragmentation.
If I need something that allows users to continue to submit queries, then REORGANIZE or REBUILD ONLINE = ON is better.
LikeLike
While Rebuilding is index
table is in S Lock and Sch-M Lock
Pages of the table are in X Lock mode
But why we can not access table if table is in S Lock mode , index is created drop and created for rebuild purpose so All pages are created Again
and IAM Pages is been put on XMode
While Reorganize index
Table is in IX Lock mode
Pages of the table in X Lock Mode
here index is not drop and recreated so pages are the same but it places gradually X Mode and Release that page from X Mode as it has been reorganized
and then it took other page to reorganize and put it in XMode.
so here only Those Pages are locked and release which are moved
sorry for could not attaching script cause it will take really long to make a script that can be nice
But one can use sys.dm_tran_locks to see what is going on.
LikeLike
As per my knowledge, For clustered as well as non-clustered index, **When the Total Fragmentation exceeds 40% or more**, it required index Rebuild and when u start this operation an exclusive lock apply on the entire table which prevents any changes to the table until Index rebuild complete as it recreates the entire index.
If the **Total Fragmentation is less than 40%** then Index Reorganize applied. This will apply shared lock to the table. Personally i would go with Index Reorganize as my database is frequently updated.
LikeLike
@Krish: 30% vs. 40%…. whatever. It’s a minor difference.
Is your database updated constantly throughout the 24 hour day? You have absolutely no maintenance windows at all? And unlimited transaction log space?
LikeLike
Below is the (incomplete) list of locks required to run a REORGANIZE on a Non Clustered index. Apart from the locks listed, there may be other METADATA lock requirement based on the security context of the executing user and features like Auditing and triggers.
1. IS Lock on the Table
2. Sch-S Lock on the Clustered Index
3. Sch-S lock on the NC Index
4. Sch-S lock on the statistics on th NC Index
5. IX Lock on the table
6. S Lock on the table
7. X lock on the HOBT
8. U lock on the Extents
9. X lock on the pages
10. X lock on the extents
11. S lock on the keys
Some these locks locks may be acquired and released several times.
The REORGANIZE process can be blocked by another process, participate in a blocking chain and block other processes, participate in a deadlock as a victim or winning process.
The key point about REORGANIZE is that it only acquires “short term” locks and works on a page by page manner. So the chances of other processes getting blocked by the REORGANIZE thread for a longer duration is much less.
The REBUILD process acquires a Sch-M lock in the beginning and keep it till the end, thus making the table inaccessible for other queries. Teh rEBUILD process also requires all the locks mentioned above pluse few additional METADATA locks.
Regards
Roji Thomas
http://sqlindian.com
LikeLike
ya its right.If we not specify ONLINE=ON by default SQL server lock table in the rebuilding index operation.
While in Reorganize process database is online.
IF fragmentation is more than 30% we need to rebuild the index otherwise reorganize is better solution.
I do not have much idea about types of table lock is there good article available on table locks?
LikeLike