Clustered versus Create table on high speed FILEGROUP

[Dr Warehouse]

Hi,

I am expanding our data warehouse solution with new filegroups on
several subsystems.

I want to know which idea is better!

- create clustered indexes on tables to 'move' them to new filegroups
- create these tables on the new filegroups.

The background of this question is as follows:

- we want the whole data on the new filegroups
- we want to know if there is any difference in performance between the
2 solutions

Thanks in advance,
Danny

[Stu]

Hey Danny,

In both cases your data will physically reside in the space where there
is either a clustered index OR (in the case of a heap) on the drive
where the table was created. What you didn't ask about was
nonclustered indexes; if you move the clustered index, the nonclustered
indexes will stay where they are. If the filegroups are one seperate
disk structures, you may see some disk i/o benefits.

Does that help?

Stu

[Dr Warehouse]

Hi Stu,

Thanks. Nonclustered indexes are already on seperate filegroups. Do you
know wat the difference in locking / performance gain is if the table
structure stays on the 'old' filegroup (in case of an clustered index
to a new filegroup) or the table structure is on the new filegroup.

Danny

[Erland Sommarskog]

Dr Warehouse (d.*******@reddion.com) writes:

Thanks. Nonclustered indexes are already on seperate filegroups. Do you
know wat the difference in locking / performance gain is if the table
structure stays on the 'old' filegroup (in case of an clustered index
to a new filegroup) or the table structure is on the new filegroup.

The clustered index is the the table structure, so if you move the
clustered index, there will be nothing left on the old filegroup.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Stu]

Where your clustered index is, your table is. When you move a
clustered index to a new filegroup, you are moving the entire table to
that filegroup, so there is no performance gain in moving the index
(unless you're moving to better hardware or a better configuration).

Just to be clear, in order to get a performance boost using filegroups,
those filegroups must be on seperate physical disk structures; however,
when you move a clustered index, you're moving the whole table.

HTH,
Stu