SQL Server 2000 and clustered index

vasoov

Active Member
My database is divided into 2 filegroups, PRIMARY and INDICES. Currently the clustered indices of the top 50 largest tables reside in the INDICES filegroup. INDICES is 45GB insize and PRIMARY is 5GB in size. All nonclustered indices are also in the INDICES filegroup

PRIMARY resides on a 65GB RAID 5 (18GB x5), and INDICES on a 65GB RAID 0+1 (18GB x8)

Question: Is it correct to place the clustered indices into the INDICES filegroup. Some people say that the clustered index should reside together with the data (PRIMARY) and nonclustered should be placed in the INDICES filegroup.

Has anyone got any specific index tuning tips for JDE. In my case I had to manually add a nonclustered index on F4211 on SDKCOO, SDTRDJ field as certain queries were taking 5 minutes. With the index this came down to 7 seconds!
 
I think your take on the clustered index is correct because the clustered index's leaf level is actually the table data. If you move the clustered index to the INDICES filegroup you essentially move the tables data as well. The tables data and the clustered index are essentially the same.

At least that's my interpretation.

I've never had a good reason to move indices to another filegroup. I have created additional indices on J.D. Edwards tables to improve performance, but I have no specific recommendations.

Excellent question.
 
Back
Top