Monday, February 20, 2012

Moving indexes to their own disk drive

I am trying to find out if it is possible to move indexes to a separate filegroup/disk drive during database restore. I am trying this to see if it improves performance. Also if I cannot move the indexes during restore, how would I move them afterwards to a different filegroup/disk drive? Thanks in advance for all the help.Just recreate indexes:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ] --<<<<<<<<<<<<<<<<<<<<<<|||Look up ALTER TABLE

I think you can ALTER the contraint (The indexes) and change the file group

Never done it though...let me look into it...|||I was meesing with

USE Northwind
GO

CREATE TABLE myTable99 (Col1 int IDENTITY(1,1),Col2 char(1))
GO

CREATE INDEX myTable99_IX1 ON myTable99(Col1)
GO

ALTER TABLE myTable99 NOCHECK CONSTRAINT myTable99_IX1 ON Secondary
GO

DROP TABLE myTable99
GO

But can't get it to work...

Seems like you have to drop and recreate...I would have thought there might have been a move or alter or something...

How big is the database?|||Will that SQL allow me to move the existing index to a different drive, and can you show me an example. Thanks.|||One thing to remember, you can not move a clustered index to a drive (filegroup) that is different from the filegroup the data is on. In short, a clustered index is the data. As for an example, suppose you have two filegroups named UserData and IndexData. For table Customers you would have:

create (unique) index indexname on Customers (fields) on IndexData

alter table Customers add constraint pk_customers primary key (keyfield(s)) on UserData|||I can't find a way to move the index across filegroups...

Even EM does a drop and recreate...|||if this is going to be a one time thing then i would invest the time and do it right and completely recreate the index on the new disk.
this would give you a fresh index on the new disk

and kids
dont forget your fill factor.

No comments:

Post a Comment