By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,397 Members | 1,420 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,397 IT Pros & Developers. It's quick & easy.

Clustered versus Create table on high speed FILEGROUP

P: n/a
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

Nov 30 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 30 '05 #2

P: n/a
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

Nov 30 '05 #3

P: n/a
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
Nov 30 '05 #4

P: n/a
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

Nov 30 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.