Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Performance tuning expert often encounter situation where it is difficult to figure out why certain

  • Performance tuning expert often encounter situation where it is difficult to figure out why certain activities are happening behind the scene in SQL Server Engine. Every action has some root cause and understanding the same is the key to be successful performance tuning expert. Here is one question which often puzzles many - When are the non-Clustered Indexes automatically rebuilt?

    Posted on 01-11-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

10  Answers  

Subscribe to Notifications
  • Score
    6

    *Rule of Thumb* - if the physical or logical RID contained in the non-clustered index records needs to change then all the non-clustered indexes need to be rebuilt.

    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 and above

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

    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.

    Replied on Jan 11 2012 12:14AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    7

    The rule of thumb to remember is: if the physical or logical RID contained in the non-clustered index records needs to change then all the non-clustered indexes need to be rebuilt.

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

    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.

    http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/06/07/what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed.aspx

    SQL Server 2008

    1. ALTER TABLE ... REBUILD will remove forwarding pointers, but for some strange reason it will also rebuild all non-clustered indexes on that table.

    2. Rebuilding a clustered index does rebuild associated nonclustered indexes if the keyword ALL is specified When you use keyword ALL you do not specify particular index name instead you just specify table name e.g.

      ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80, SORTINTEMPDB = ON, STATISTICS_NORECOMPUTE = ON); GO

      Also, in that case (when ALL is used) first clustered index will be rebuilt after that all non clustered index will be rebuilt..

    3. Rebuilding a clustered index does rebuild associated nonclustered indexes if the index definition changed.
      CREATE INDEX with the DROP_EXISTING clause.

    4. Add maintenance jobs in SQL Server Management Studio to rebuild indexes

    http://sqlblog.com/blogs/tiborkaraszi/archive/2009/08/28/heaps-forwarding-pointers-alter-table-rebuild-and-non-clustered-indexes.aspx

    if you're going to make schema changes in ANY tool - then work through what those changes are going to do. Most important - consider re-arranging the SQL (especially wrt indexes). If you're ever changing the CL index you need to do the following:

    Drop nonclustered indexes first Drop the clustered next IMPORTANT NOTES: If you're completely changing it you definitely need to drop the nonclustered and then drop the CL. BUT, if you're keeping the name and only changing the definition (and it's NOT a constraint-based index) then you can use WITH DROPEXISTING. If you use DROPEXISTING you do NOT need to drop the nonclustered indexes first. Create the new clustered Recreate the nonclustered indexes Some of the weird things that we've seen:

    1. Some changes to table structure (changing from a heap to a clustered or a clustered to a heap) cause disabled nonclustered to get rebuilt (and re-enabled). That's not even SSMS - that's a general SQL Server problem. In my opinion this means that disabling indexes is somewhat broken. So... gotta use drop/create (which is a bummer!).
    2. Changes to the clustering key in SSMS are sometimes done by dropping the clustered and then recreating the clustered. This will cause your nonclustered indexes to be rebuilt TWICE!
    3. If you have col1 that's currently clustered and col2 is currently nonclustered but you want to swap them... you can do that in the UI and then save but here's what they do:

      .  They drop the nonclustered  
      .  They drop the clustered (this causes all of the remaining nonclustered to get rebuilt)  
      .  They create the nonclustered  
      .  They create the clustered (this causes all of the remaining AND the new nonclustered to get rebuilt... again)
      

      http://sqlskills.com/BLOGS/KIMBERLY/post/Trusting-the-tools!.aspx#ixzz1j93Kez7t

    Replied on Jan 11 2012 4:26AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    8

    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/

    Replied on Jan 11 2012 8:05AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    4

    Paul has the best explanation for this -

    http://www.sqlskills.com/blogs/paul/post/Indexes-From-Every-Angle-What-happens-to-non-clustered-indexes-when-the-table-structure-is-changed.aspx

    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.

    Replied on Jan 11 2012 6:37PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    1

    Non clustered indexes rebuilt automatically when 1. Existing Clustered Index is dropped 2. Clustered Index is created. 3. Any data got change in column which is part of non clustered index.

    Replied on Jan 12 2012 7:14AM  . 
    sk2000
    515 · 0% · 73
  • Score
    4

    An automatic rebuild of non-clustered indexes will be initiated whenever there is a change to, creation of, or deletion of, the clustered index on a table that causes a change in the unique row identifiers.

    There are some slight differences between the different versions of SQL Server that dictate when a change to the unique row identifier will change.

    Rebuilding a unique clustered index does not cause an automatic rebuild because the unique row identifier does not change.

    Rebuilding a non-unique clustered index in SQL Server prior to version 2005 WILL cause an automatic rebuild of non-clustered indexes because starting with version 2005, the uniquifier on non-unique clustered indexes will be reused when the index is rebuilt. Prior to 2005 the uniquifier changes when a non-clustered index is rebuilt.

    In case anyone cares, a uniquifier is a column that SQL Server adds to a non-unique clustered index in order to ensure that each row in the table can be uniquely identified and located.

    Replied on Jan 12 2012 9:11AM  . 
    Cris
    194 · 1% · 242
  • Score
    3

    Following are the cases when NON-Clustered indexes are rebuild automatically;

    • When ever a Clustered index is dropped
    • When ever a Clustered index is created
    • When the clustered index in rebuild (Unique or NON-nique)
    Replied on Jan 17 2012 10:36PM  . 
    ATif-ullah Sheikh
    133 · 1% · 391
  • Score
    3

    Index should be rebuild automatically when index fragmentation is great than 40%.

    Replied on Jan 19 2012 2:18AM  . 
    ajiteshmalhotra
    1769 · 0% · 11
  • Score
    5

    When can this happen automatically?

    Creating or dropping clustered indexes requires rewriting the entire table. If the table has nonclustered indexes, all the nonclustered indexes must all be recreated whenever the clustered index is changed. Therefore, changing from a heap to a clustered index structure or back can take a lot of time and require disk space for reordering data in tempdb.

    so it is very importance that having an idea on how and when to use the heap

    Heaps (Tables without Clustered Indexes)

    A heap is a table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap. Data is stored in the heap without specifying an order. Usually data is initially stored in the order in which is the rows are inserted into the table, but the Database Engine can move data around in the heap to store the rows efficiently; so the data order cannot be predicted. To guarantee the order of rows returned from a heap, you must use the ORDER BY clause. To specify the order for storage of the rows, create a clustered index on the table, so that the table is not a heap.

    When to Use a Heap

    If a table is a heap and does not have any nonclustered indexes, then the entire table must be examined (a table scan) to find any row. This can be acceptable when the table is tiny, such as a list of the 12 regional offices of a company.

    When a table is stored as a heap, individual rows are identified by reference to a row identifier (RID) consisting of the file number, data page number, and slot on the page. The row id is a small and efficient structure. Sometimes data architects use heaps when data is always accessed through nonclustered indexes and the RID is smaller than a clustered index key.

    When Not to Use a Heap

    Do not use a heap when the data is frequently returned in a sorted order. A clustered index on the sorting column could avoid the sorting operation.

    Do not use a heap when the data is frequently grouped together. Data must be sorted before it is grouped, and a clustered index on the sorting column could avoid the sorting operation.

    Do not use a heap when ranges of data are frequently queried from the table. A clustered index on the range column will avoid sorting the entire heap.

    Do not use a heap when there are no nonclustered indexes and the table is large. In a heap, all rows of the heap must be read to find any row.

    Managing Heaps

    To create a heap, create a table without a clustered index. If a table already has a clustered index, drop the clustered index to return the table to a heap.

    To remove a heap, create a clustered index on the heap.

    To rebuild a heap to reclaim wasted space, create a clustered index on the heap, and then drop that clustered index.

    Replied on Jan 24 2012 11:43PM  . 
    indika saminda kannangara
    188 · 1% · 251
  • Score
    6

    The rule of thumb to remember is: if the physical or logical RID contained in the non-clustered index records needs to change then all the non-clustered indexes need to be rebuilt.

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

    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.

    http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/06/07/what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed.aspx

    Replied on Feb 3 2012 10:18AM  . 
    Mitesh Modi
    18 · 10% · 3080

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.