October 21, 2008 at 8:34 am
I am planning to move the huge database to its own disk with 3 different luns for ldf, mdf, and indexes for better performance and also to manage the db growth. Do I have to change the schema or anything? And also do I have to refer to the filegroup for the weekly maintenance sunch as to rebuild the indexes and update statistic? Experts please help me out. Thanks in advance!
October 21, 2008 at 9:52 am
Detach, move, attach ..... good to go.
October 21, 2008 at 10:02 am
By the way try to use FOR ATTACH option instead of sp_attach_db
* Noel
October 21, 2008 at 10:15 am
I am not asking for the process. All I am asking is when you move the nonclustered indexes to other disk array do you need the schema change or for rebuild indexes do you need to point it to the new location?
October 21, 2008 at 11:57 am
Here's a script that someone else helped me with (sorry I don't remember who to give credit to). This will generate all of the commands to move your nonclustered indexes:
SELECT CREATE_INDEX_SQL
FROM (
SELECT TBL_NAME = o.name,
INDEX_NAME = i.name,
CREATE_INDEX_SQL = 'CREATE '
+ ' INDEX ' + i.name
+ ' ON ' + s.name + '.' + o.name + ' ('
+ (STUFF(( SELECT ', ' + sc.name
FROM sys.indexes si
JOIN sys.tables st
ON si.object_id = st.object_id
JOIN sys.index_columns sic
ON st.object_id = sic.object_id
AND si.index_id = sic.index_id
JOIN sys.columns sc
ON st.object_id = sc.object_id
AND sc.column_id = sic.column_id
WHERE si.name = i.name
--AND st.name = i.name
ORDER BY sic.index_column_id
FOR XML PATH(''))
, 1,2,''))
+ ') WITH (DROP_EXISTING=ON) ON "Index_filegroup"'
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE o.type = 'U'
AND i.is_primary_key = 0
AND i.type_desc <> 'CLUSTERED'
AND i.is_unique_constraint = 0
AND ds.name = 'PRIMARY'
) o
GROUP BY CREATE_INDEX_SQL, TBL_NAME, INDEX_NAME
ORDER BY TBL_NAME, INDEX_NAME
once the indexes are in the new FileGroup, you won't need to wory about what filegroup they are in for your maintenance plans.
October 21, 2008 at 12:08 pm
hydbadrose (10/21/2008)
I am not asking for the process. All I am asking is when you move the nonclustered indexes to other disk array do you need the schema change or for rebuild indexes do you need to point it to the new location?
Did that last reply answer your question or are you still worried about your maintenance routine.
Changing the FileGroup that an index sits on has nothing to do with it's schema. Multiple schemas can reside on a single FileGroup and a single schema can have objects on multiple FileGroups.
So I guess what I'm saying is that if you are moving your non-clustered indexes to a new FileGroup, nothing has changes, and you still have the same schema for your object so your maintenance routine shouldn't be the wiser.
@SQLvariant
October 21, 2008 at 4:36 pm
Thank you somuch! I really appreciate your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply