Moving Non Cluetered Indexes

Question

Moving Non Cluetered Indexes

Jeetendra

SSCrazy

Points: 2076
More actions
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

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 2

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

Jeetendra SSCrazy Points: 2076 More actions · Answer 3

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

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

Jeetendra SSCrazy Points: 2076 More actions · Answer 6

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 8

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

Jeetendra SSCrazy Points: 2076 More actions · Answer 9

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

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

Jeetendra SSCrazy Points: 2076 More actions · Answer 11

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

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

Jeetendra SSCrazy Points: 2076 More actions · Answer 13

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

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

Kindest Regards,

Jeetendra

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

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.