Backround
The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data.
Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.
Rebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or Existing fill factor setting, and the index rows are reordered in contiguous pages (allocating new pages as needed). This can improve disk performance by reducing the number of page reads required to obtain the requested data.
*The following methods can be used to rebuild clustered and nonclustered indexes: *
ALTER INDEX with the REBUILD clause.
This statement replaces the DBCC DBREINDEX statement.
CREATE INDEX with the DROP_EXISTING clause.
If you do a reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.
By using the Rebuild Index task, a package can rebuild indexes in a single database or multiple databases. If the task rebuilds only the indexes in a single database, you can choose the views and tables whose indexes the task rebuilds.
This task encapsulates an ALTER INDEX REBUILD statement with the following index rebuild options:
1) Specify a FILLFACTOR percentage or use the original FILLFACTOR amount.
2) Set PAD_INDEX = ON to allocate the free space specified by FILLFACTOR to the intermediate-level pages of the index.
3) Set SORTINTEMPDB = ON to store the intermediate sort result used to rebuild the index in tempdb. When the intermediate sort result is set to OFF, the result is stored in the same database as the index.
4) Set IGNOREDUPKEY = ON to allow a multirow insert operation that includes records that violate unique constraints to insert the records that do not violate the unique constraints.
5) Set ONLINE = ON to not hold table locks so that queries or updates to the underlying table can proceed during re-indexing.
One way to describe a non-clustered index is whether it is a covering index or not. A covering index is one which has all the table columns necessary to satisfy a query, and so there is no need to go back to the underlying table (which I’ll call the base table) to fetch additional columns. Most non-clustered indexes are not covering indexes and so it is necessary for each non-clustered index record (even in covering indexes) to include a link back to the corresponding base table record.
Now let's discuss what happens to non-clustered indexes when various actions are performed to the base table.
SQL Server 2000
• Going from a heap to a clustered index: Changing the fundamental structure of a table from a heap to a clustered index means you're changing the way that records can be looked up in the table, from physical to logical.
This means that all the physical RIDs that are stored in the non-clustered index records need to be changed to logical RIDs. So - in this case, all non-clustered indexes are rebuilt after the clustered index has been built.
• Going from a clustered index to a heap: This is the opposite of the case above - you're changing the way records are looked up from logical to physical.
This means that all the logical RIDs that are stored in the non-clustered index records need to be changed to physical RIDs. So - in this case, all non-clustered indexes are rebuilt after the clustered index has been dropped.
• Rebuilding a unique clustered index: This operation has no effect on the cluster key, so there is no need for the non-clustered indexes to be rebuilt.
Various early Service Packs of SQL Server 2000 had bugs that caused this behavior to be broken - and this is the cause of much of the confusion around this behavior.
• Rebuilding a non-unique clustered index: In SQL Server 2000, when a non-unique clustered index (which contains uniquifier columns) is rebuilt, all the uniquifier values are regenerated.
This essentially means that the cluster keys have changed and so all the non-clustered indexes are rebuilt after the clustered index rebuild has completed.
• Changing the clustered index schema: This is simple - any change to the clustered index schema that changes the cluster keys will cause all the non-clustered indexes to be rebuilt.
SQL Server 2005
• Going from a heap to a clustered index: This is the same as SQL Server 2000 - all non-clustered indexes are rebuilt, regardless of whether the clustered index was created online or offline.
• Going from a clustered index to a heap: Again, all non-clustered indexes are rebuilt, regardless of whether the clustered index is dropped online or offline.
• Rebuilding a unique clustered index: This is the same as SQL Server 2000 - the cluster keys aren't changing and so the non-clustered indexes are not rebuilt.
• Rebuilding a non-unique clustered index: Aha! This is different from SQL Server 2000. SQL Server 2005 will RE-USE the old uniquifier values so the cluster keys don't change. This means that non-clustered indexes are NOT rebuilt in this case - that's very cool!
• Changing the clustered index schema:
◦ Changing the cluster key: This behavior has to be the same as SQL Server 2000 - any change to the cluster keys forces all the non-clustered indexes to be rebuilt after the new clustered index has been created.
◦ Changing anything else (e.g. partitioning it or moving it): This is one of the cases that confuses people in SQL Server 2005. Applying a partitioning scheme to a clustered index, or moving it to a different filegroup, doesn't change the cluster key at all - so there's no need to rebuild the non-clustered indexes.
Database reorganizations can be done using the Maintenance Wizard, or by running your own custom script via the SQL Server Agent (see below).
The DBCC DBREINDEX command will not automatically rebuild all of the indexes on all the tables in a database; it can only work on one table at a time. But if you run the following script,
you can index all the tables in a database with ease:
–Script to automatically reindex all tables in a database
USE DatabaseName –Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = ‘base table’
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,’ ‘,90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
The script will automatically reindex every index in every table of any database you select, and provide a fill factor of 90%. You can substitute any number appropriate for the fill factor in the above script.
When DBCC DBREINDEX is used to rebuild indexes, keep in mind that as the indexes on a specific table are being rebuilt, that the table becomes unavailable for use by your users.
For example, when a non-clustered index is rebuilt, a shared table lock is put on the table, preventing all but SELECT operations to be performed on it. When a clustered index is rebuilt,an exclusive table lock is put on the table, preventing any table access by your users. Because of this, you should only run this command when users don’t need access to the tables being reorganized.
Refernce Click:: http://www.sql-server-performance.com/2007/rebuilding-indexes
Refernce Click:: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2006/11/09/index-build-strategy-in-sql-server-introduction-i.aspx
Refernce Click:: http://www.sqlskills.com/blogs/paul/post/Indexes-From-Every-Angle-What-happens-to-non-clustered-indexes-when-the-table-structure-is-changed.aspx
Refernce Click:: http://blog.sqlauthority.com/2010/05/12/sql-server-understanding-alter-index-all-rebuild-with-disabled-clustered-index/