locked
Rebuilding Indexes RRS feed

  • Question

  • Hi,
    Is it good to Rebuild NonClustered Indexes on a table though I had already Rebuild the Unique Clustered Index on it?


    Kindly mark the reply as answer if they help


    • Edited by Sunil Gure Friday, June 8, 2012 7:28 AM
    Friday, June 8, 2012 7:28 AM

Answers

  • Hi Sunil,

    Rebuilding the clustered index does not affect the non clustered indexes (from SQL Server 2005 onwards). The clustering key that is stored in the leaf level of the non clustered index remains the same.

    With regard to is it good to rebuild non clustered indexes, for the most part, yes it is. The determining factor will be the level of fragmentation that is present within the non clustered index. There are a number of excellent and freely available scripts that will automate the process of performing this calculation and performing the optimization for you. For example:

    If you have any specific questions just let us know and I'm sure we can help.


    John Sansom | SQL Server DBA Blog | @SQLBrit on Twitter

    • Proposed as answer by Rama Udaya Sunday, June 10, 2012 1:15 PM
    • Marked as answer by Maggie Luo Monday, June 18, 2012 9:16 AM
    Friday, June 8, 2012 7:51 AM
  • Please read Ola's great utility http://ola.hallengren.com/

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Maggie Luo Monday, June 18, 2012 9:16 AM
    Sunday, June 10, 2012 8:29 AM
    Answerer

All replies

  • rebuild clustered index I believe won't automatically rebuild nonclustered index.

    so it is better to check the nonclustered index fragmentation ratio first from sys.dm_db_index_physical_stats, if the fragmentation rate is high say over 15 % or 25 % (based on your rule) then your rebuild them

    Friday, June 8, 2012 7:32 AM
  • Hi Sunil, yes it is, you need to rebuild NonClustered Indexes. becuase Rebuild the Clustered Index does not effect NonClustered Indexes.
    i hope the following links will help you .
    http://blogs.msdn.com/b/khen1234/archive/2007/02/27/does-rebuilding-a-clustered-index-rebuild-nonclustered-indexes.aspx
    http://technet.microsoft.com/en-us/library/ms189858%28v=sql.105%29.aspx

    thanks,
    nicofer


    • Edited by nicofer Friday, June 8, 2012 7:41 AM
    Friday, June 8, 2012 7:36 AM
  • Hi Sunil,

    Rebuilding the clustered index does not affect the non clustered indexes (from SQL Server 2005 onwards). The clustering key that is stored in the leaf level of the non clustered index remains the same.

    With regard to is it good to rebuild non clustered indexes, for the most part, yes it is. The determining factor will be the level of fragmentation that is present within the non clustered index. There are a number of excellent and freely available scripts that will automate the process of performing this calculation and performing the optimization for you. For example:

    If you have any specific questions just let us know and I'm sure we can help.


    John Sansom | SQL Server DBA Blog | @SQLBrit on Twitter

    • Proposed as answer by Rama Udaya Sunday, June 10, 2012 1:15 PM
    • Marked as answer by Maggie Luo Monday, June 18, 2012 9:16 AM
    Friday, June 8, 2012 7:51 AM
  • Thanks Guys. I believe order of rebuilding Indexes should be clustered first and followed by all non clustered on a particular table.

    Kindly mark the reply as answer if they help

    Friday, June 8, 2012 7:59 AM
  • please see the previous answers.  If there's no fragmentation, what's the point of rebuilding/defragging indexes?  Use one of the scripts posted by John which will only touch the indexes that need it (plus they'll do index statistics too, which reorganising doesn't).  This will reduce the duration of your maintenance job, and limit the impact on resources


    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you



    Friday, June 8, 2012 8:16 AM
  • Aim to schedule this job during a maintenance window - outside of normal usage times. It can be (depending on size of indexes and IO subsystem) a very resource intensive activity.


    Jack Vamvas sqlserver-dba.com

    Sunday, June 10, 2012 6:16 AM
  • Please read Ola's great utility http://ola.hallengren.com/

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Maggie Luo Monday, June 18, 2012 9:16 AM
    Sunday, June 10, 2012 8:29 AM
    Answerer