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 Filegroups

I'm fishing for some advice here.

I'm porting an old Access 97 application to SQL Server 2000. The Access app uses 9 separate files for a series of linked tables (one table in each file to get around the 1Gb Access file size limit). The tables vary in size form 2Mb to 800Mb so the whole data set weighs in at around 6Gb.

I've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.

My next problem is the indexes. The Access tables don't have primary keys. My SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.

At present I have all the table indexes in the PRIMARY filegroup (which in tunr just has the default MDF file in it)

I've built a small version of the DB for testing various triggers and new views and the DTS import packages and the indexing for the 300Mb of data I have now is obviously fairly quick. I am wondering if I should split the indexes out into each of the separate MDF files that I am storing the table data or should I split the indexes into their own files?

I want this thing to be fast. The VBA app that will be plugging into the DB has a huge amount of code and currently struggles especially when several dozen people are all connected to the same tables.

So any advice? Indexes in separate files? Or in with the data? or all together in one index file? Any performance impacts I should be aware of?

The DB is running on its own dedicated box. Its not huge 1Gb Ram, 30Gb drive and a 3Ghz P4. But given that it isn't running anything else it should be up to the job. It should certainly be faster than the current shared drive that the access app runs from.

many thanks

SteveI've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.

if data set weighs in at around 6Gb assignation each table into its own filegroup not requeried

The Access tables don't have primary keys. MS SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.

add new field int type with identity and set it as primary key

if you wont make quick application - do not use access 97 oledb connection to Mssql server. rebuld it to accessXP ADP project