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.

Same as above

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.

@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.

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.

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.dmtranlocks to see what is going on.

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.

@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?

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

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?