Non-clustered indexes on 1 separate fileGroup?

Hi Guys,

I am reviewing the storage structure of my growing db.

I am going to create multiple FileGroups and db files.
Most of the filegroups will be used to allow table partitioning and put the heavily used partitions on different storage than the read-only archives. TempDB will also have its own filegroup/storage.

For Indexes, I have a doubt. I am sure I can have all my non-clustered indexes on a separate fileGroup and storage but what about primary keys and/or clustered indexes?

Should I move them to the same filegroup than the non-clustered indexes or should I leave them with the data? What would be best?

I am running a 100GB reporting datamart on SQL2005 enterprise, There are almost no transactions, just a daily and weekly refresh. I will soon have more storage, SAN or NetApp, not sure yet.

Thanks,

Philippe

Hi

Moving your indexes to a seperate filegroup can improve the parallism of some querries that need to work with indexes and then do a bookmark lookup afterwards. If you have a VLDB like you said you should consider doing it, since the indexes cant be kept in memory all the time. So when you access the indexes it will not block the retrieval of your data from the other partition.