Indexes on separate Filegroups

Two questions. Let's suppose I have two file groups, Primary and Datagroup.
The Primary group is mirrored and has my system tables and transaction log
(Drive letter "D"). My tables and indexes are in the Datagroup on a separate
RAID 5 disk configuration (Drive letter "E"). Let us suppose I have available
a third RAID 5 disk configuration (Drive letter "F").
1. I just wanted to verify a thought: If all my tables have clustered indexes,
and since clustered indexes reside on the data pages themselves, then it
would NOT make sense to put my clustered indexes on a separate file or disk
from my data file. True or False? My guess is that the answer is true. Please
verify.
2. In the case of non-clustered indexes (where all the corresponding tables
have clustered indexes), there could possibly be a performance gain by
placing the non-clustered indexes on a separate file (say, Drive letter "F")
from my data. True or False? My guess is that the answer is true. Please
verify.
Message posted via http://www.droptable.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5e60b49711dd2@.uwe...
> Two questions. Let's suppose I have two file groups, Primary and
> Datagroup.
> The Primary group is mirrored and has my system tables and transaction log
> (Drive letter "D"). My tables and indexes are in the Datagroup on a
> separate
> RAID 5 disk configuration (Drive letter "E"). Let us suppose I have
> available
> a third RAID 5 disk configuration (Drive letter "F").
> 1. I just wanted to verify a thought: If all my tables have clustered
> indexes,
> and since clustered indexes reside on the data pages themselves, then it
> would NOT make sense to put my clustered indexes on a separate file or
> disk
> from my data file. True or False? My guess is that the answer is true.
> Please
> verify.
>
True. That would just move the tables to the other filegroup.

> 2. In the case of non-clustered indexes (where all the corresponding
> tables
> have clustered indexes), there could possibly be a performance gain by
> placing the non-clustered indexes on a separate file (say, Drive letter
> "F")
> from my data. True or False? My guess is that the answer is true. Please
> verify.
False. Seperating tables and indexes rarely helps, and is essentially an
obsolete micro-optimization of the physical database design. The large
memory size of modern servers makes physical IO on your non-clustered
indexes rare and unpredictable enough that you shouldn't dedicate a physical
IO channel to your indexes. A better idea would be to spread all your
objects across both disks by adding additional files to your data filegroup,
or monitor your physical IO and move objects to the other disk to roughly
balance the traffic.
David

Indexes on separate Filegroups

Two questions. Let's suppose I have two file groups, Primary and Datagroup.
The Primary group is mirrored and has my system tables and transaction log
(Drive letter "D"). My tables and indexes are in the Datagroup on a separate
RAID 5 disk configuration (Drive letter "E"). Let us suppose I have availabl
e
a third RAID 5 disk configuration (Drive letter "F").
1. I just wanted to verify a thought: If all my tables have clustered indexe
s,
and since clustered indexes reside on the data pages themselves, then it
would NOT make sense to put my clustered indexes on a separate file or disk
from my data file. True or False? My guess is that the answer is true. Pleas
e
verify.
2. In the case of non-clustered indexes (where all the corresponding tables
have clustered indexes), there could possibly be a performance gain by
placing the non-clustered indexes on a separate file (say, Drive letter "F")
from my data. True or False? My guess is that the answer is true. Please
verify.
Message posted via http://www.droptable.com"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5e60b49711dd2@.uwe...
> Two questions. Let's suppose I have two file groups, Primary and
> Datagroup.
> The Primary group is mirrored and has my system tables and transaction log
> (Drive letter "D"). My tables and indexes are in the Datagroup on a
> separate
> RAID 5 disk configuration (Drive letter "E"). Let us suppose I have
> available
> a third RAID 5 disk configuration (Drive letter "F").
> 1. I just wanted to verify a thought: If all my tables have clustered
> indexes,
> and since clustered indexes reside on the data pages themselves, then it
> would NOT make sense to put my clustered indexes on a separate file or
> disk
> from my data file. True or False? My guess is that the answer is true.
> Please
> verify.
>
True. That would just move the tables to the other filegroup.

> 2. In the case of non-clustered indexes (where all the corresponding
> tables
> have clustered indexes), there could possibly be a performance gain by
> placing the non-clustered indexes on a separate file (say, Drive letter
> "F")
> from my data. True or False? My guess is that the answer is true. Please
> verify.
False. Seperating tables and indexes rarely helps, and is essentially an
obsolete micro-optimization of the physical database design. The large
memory size of modern servers makes physical IO on your non-clustered
indexes rare and unpredictable enough that you shouldn't dedicate a physical
IO channel to your indexes. A better idea would be to spread all your
objects across both disks by adding additional files to your data filegroup,
or monitor your physical IO and move objects to the other disk to roughly
balance the traffic.
David

Indexes on separate Filegroups

Two questions. Let's suppose I have two file groups, Primary and Datagroup.
The Primary group is mirrored and has my system tables and transaction log
(Drive letter "D"). My tables and indexes are in the Datagroup on a separate
RAID 5 disk configuration (Drive letter "E"). Let us suppose I have available
a third RAID 5 disk configuration (Drive letter "F").
1. I just wanted to verify a thought: If all my tables have clustered indexes,
and since clustered indexes reside on the data pages themselves, then it
would NOT make sense to put my clustered indexes on a separate file or disk
from my data file. True or False? My guess is that the answer is true. Please
verify.
2. In the case of non-clustered indexes (where all the corresponding tables
have clustered indexes), there could possibly be a performance gain by
placing the non-clustered indexes on a separate file (say, Drive letter "F")
from my data. True or False? My guess is that the answer is true. Please
verify.
--
Message posted via http://www.sqlmonster.com"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5e60b49711dd2@.uwe...
> Two questions. Let's suppose I have two file groups, Primary and
> Datagroup.
> The Primary group is mirrored and has my system tables and transaction log
> (Drive letter "D"). My tables and indexes are in the Datagroup on a
> separate
> RAID 5 disk configuration (Drive letter "E"). Let us suppose I have
> available
> a third RAID 5 disk configuration (Drive letter "F").
> 1. I just wanted to verify a thought: If all my tables have clustered
> indexes,
> and since clustered indexes reside on the data pages themselves, then it
> would NOT make sense to put my clustered indexes on a separate file or
> disk
> from my data file. True or False? My guess is that the answer is true.
> Please
> verify.
>
True. That would just move the tables to the other filegroup.
> 2. In the case of non-clustered indexes (where all the corresponding
> tables
> have clustered indexes), there could possibly be a performance gain by
> placing the non-clustered indexes on a separate file (say, Drive letter
> "F")
> from my data. True or False? My guess is that the answer is true. Please
> verify.
False. Seperating tables and indexes rarely helps, and is essentially an
obsolete micro-optimization of the physical database design. The large
memory size of modern servers makes physical IO on your non-clustered
indexes rare and unpredictable enough that you shouldn't dedicate a physical
IO channel to your indexes. A better idea would be to spread all your
objects across both disks by adding additional files to your data filegroup,
or monitor your physical IO and move objects to the other disk to roughly
balance the traffic.
David

Indexes and File groups

Something strange.

I have a database(SQL2000) with two file group(on seperate physical
drives).
One is meant for table data[PRIMARY] and one for indexes [INDEX].

So i create a table on the [PRIMARY] file group, and fill in
data.

Next I build a clustered index on the table, on the [INDEX] filegroup.

Once the index is built, the database now indicates that the filegroup
for the table [INDEX]! and not [PRIMARY] as i originally set it up for!

My question it then: Has the table been moved or is this somehow an
error in SQL server?
I would really appreciate any thought anyone might have on this?Jens

A clustered index is the table (Well not quiet, but close enough). It
is impossible to have a clustered index on a different filegroup from
the data. You can build non-clustered on a seperate filegroup.

I suggest you rebuild your clustered index on your primary filegroup.
Regards

John|||Aha! Solved some mysteries for me :-). Thank you very much.
I guess i didnt quite understand how clustered indexes worked.

Actually i have a bunch of tables with clustered indexes which
currently reside my file group for indexes. The good news is,if I
understand you correctly,
the if I simply rebuild the clustered index on my data file group
the table data will be moved back.|||Jens

Yes, rebuilding the clustered index will move the table. You can also
do it through enterprise manager, using design table, this rebuilds the
index for you.

Regards

John|||Im planning to recreate the clustered index like this:

CREATE
CLUSTERED INDEX [idx-clusteredindex]
ON
[dbo].[TABLE_NAME]([COLOUMN_NANE])
WITH
DROP_EXISTING,
FILLFACTOR = 90
ON
[PRIMARY]

As I understand this will alse cause all non-clustered index
on the table to be rebuilt/recalculated as well.
Is this infact the case of do I have to
do i have to do it explicitly afterwards like:

DBCC DBREINDEX ([dbo].[TABLE_NAME],[idx-nonclustered],90)

johnbandettini@.yahoo.co.uk wrote:
> Jens
> Yes, rebuilding the clustered index will move the table. You can also
> do it through enterprise manager, using design table, this rebuilds
the
> index for you.
>
> Regards
>
> John