Post reply

Moving Non Cluetered Indexes

  • June 18, 2007 at 6:08 am

    #372236

    Hi All,

    I have a rather Strange Task to complete.

    I wanna move all the Non Clustered Indexes in a particular database on all the Tables to a New Secondary Filegroup just created.

    Is their any simple way of doing it?

    Thanks for your Suggestions in Advance.

     

    Kindest Regards,

    Jeetendra

  • June 18, 2007 at 6:28 am

    #713536

    Jeetendra

    You can either do them one by one in Enterprise Manager (let me know if you need me to explain how), or you can script out all your non-clustered indexes, drop them, and then run the script to create them on the new filegroup.  You may find that you need to drop some foreign key constraints first (and recreate them afterwards, of course).  It can be a bit messy.  There may be a third party tool that does all this for you, but if there is I've never used it.

    John

  • June 18, 2007 at 6:35 am

    #713539

    Better its to drop and recreate the non-clustered indexes. Script all these indexes and recreate them on secondary filegroup. If possible place them on a different drive to enhance performance.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • June 18, 2007 at 6:40 am

    #713544

    Hi All,

    I wanna a Script that will script out all the Non Clustered Indexes on all the Tables in a Particular database.

    Also if available Please send me the script of ReBuilding all the Non clustered Indexes on the secondary Filegroups.

    Thanks

     

     

    Kindest Regards,

    Jeetendra

  • June 18, 2007 at 9:22 am

    #713585

    Jeetendra

    I tried once to write a script that would generate scripts for all indexes in a database, but I gave up.  I'm not saying it isn't possible - search the internet and you may find something.  However, you have to be sure you can trust it to script out indexes exactly as they are: does it deal appropriately with indexes with more than one column, included columns (if you're using SQL Server 2005), fill factor, pad_index and so on?

    You may come to the conclusion, as I did, that it's safer to go through the painstaking process of scripting each index individually, scripting constraints that need to be dropped and recreated, and then putting it all into order.

    Good luck

    John

  • June 18, 2007 at 2:32 pm

    #713655

    If you run the script builder of enterprise manager, you can select all tables, then in the 2nd page, uncheck build create and drop scripts, then in the last page, check the index option.  Hit the preview button or run the script builder.  When you open the built file you will see that at the very end, you will have the whole list of non clustered index create DDL.  You can alter that section to change the filegroup, then you can add at the end of every line : with drop existing.  This will aleviate the need to build the drop commands.  As for unique constraints and PKs, I have not seen any easy way out yet, but maybe that'll give you some ideas...

  • June 25, 2007 at 5:47 am

    #714890

    Hi All,

    Thanks for the Replies........

    I have moved the Non Clustered ndexes apart from Unique Constraint and PKs......to another Filegroup

    Now I have a Problem .....whether moving Non Clustered Unique Constraint and PKs to another Filegroup will Move the Underlying Data also to another Filegroup.....OR Data will remain on the same Filegroup.....

     

     

    Kindest Regards,

    Jeetendra

  • June 25, 2007 at 5:55 am

    #714891

    The data will remain where the clustered index is.  Just make sure you don't move this index.

  • June 25, 2007 at 6:00 am

    #714892

    Data will be moved with th index only when its clustered else the data pages and index pages will remain separated.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • June 25, 2007 at 6:01 am

    #714894

    Thanks for your reply Ninja...........

    So does this mean that moving the Non Clustered Indexes created on PK's,Unique Constarints,FK to another Filegroup won't actually move the Data....The Data will remain on the existing Filegroup itself...........

     

     

    Kindest Regards,

    Jeetendra

  • June 25, 2007 at 6:02 am

    #714895

    Exactly, assuming that the clustered index is different from the PK or the said unique constraint.

  • June 25, 2007 at 6:16 am

    #714898

    How to check whether the data is on which FileGroup.......

    Is their any way of finding this out..........

    If Suppose I move the Non Clustered Indexes on PK's to another Filegroup..how will i be able to check that the DATA has not moved.......

    Is there a Practical way of finding it out......

    Kindest Regards,

    Jeetendra

  • June 25, 2007 at 6:34 am

    #714900

    This should get you started :

     

    SELECT I.Name AS IndexName, OBJECT_NAME(id) AS TableName, INDEXPROPERTY(I.id, I.Name, 'IsClustered') AS IndexIsClustered, FG.GroupName from dbo.sysfilegroups FG INNER JOIN dbo.SysIndexes I ON I.GroupID = FG.GroupID WHERE OBJECTPROPERTY(I.id, 'IsUserTable') = 1 AND INDEXPROPERTY(I.id, I.Name, 'IsAutoStatistics') = 0 ORDER BY TableName, IndexIsClustered DESC, IndexName

  • June 25, 2007 at 10:26 am

    #714969

    Thanks a Ton for your Help....!!!!!!!!!

    I was able to successfully move the Non Clustered Indexes to another FileGroup.

    Kindest Regards,

    Jeetendra

  • June 25, 2007 at 10:30 am

    #714972

    Can you take the time to describe the procedure you used and the scripts required to do this?  This will surely help someone else in the future who has the same need as you do.

     

    Thanks in advance.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply