Primary, Indexes and Foreign Key - Best Place for them

Hello
I have a database with two data files PRIMARY and INDEXES.
To beef up performance I would like to move as much as I
can out of PRIMARY into Index so I would like to know the
best place to keep my Primary, Foreign and Indexes.
For instance, is it better to keep my Primary Keys in the
PRIMARY filegroup or move it to the INDEXES filegroup ?
Thanks
JWhat makes you think that you would get much if any benefit out of doing
this?
Keeping data in different filegroups doesn't necessarily do anything for
performance unless those filegroups are on differnet spindles. (ie physical
disks). Even then... most databases rarely have a need for different
filegroups. Instead, it's normally just as good for performance to simply
create multiple files within a single filegroup. Generally, I don't use
seperate filegroups unless I want a different backup strategy for difference
data sets.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0e01c3a206$20fe0e10$a501280a@.phx.gbl...
> Hello
> I have a database with two data files PRIMARY and INDEXES.
> To beef up performance I would like to move as much as I
> can out of PRIMARY into Index so I would like to know the
> best place to keep my Primary, Foreign and Indexes.
> For instance, is it better to keep my Primary Keys in the
> PRIMARY filegroup or move it to the INDEXES filegroup ?
> Thanks
> J|||Thankyou for your post.
As I understand it, it is due to the read write heads of
SQL server only one head is allowed at one time per data
file.
Having more than one increases performance, though having
too many slows it.
According to the MCP course it is recommended that you
take your indexes out, and put them in a separate data
file, as then you will be able to ge immediatly from one
file to another.
Thanks
J
>--Original Message--
>What makes you think that you would get much if any
benefit out of doing
>this?
>Keeping data in different filegroups doesn't necessarily
do anything for
>performance unless those filegroups are on differnet
spindles. (ie physical
>disks). Even then... most databases rarely have a need
for different
>filegroups. Instead, it's normally just as good for
performance to simply
>create multiple files within a single filegroup.
Generally, I don't use
>seperate filegroups unless I want a different backup
strategy for difference
>data sets.
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a0e01c3a206$20fe0e10$a501280a@.phx.gbl...
>> Hello
>> I have a database with two data files PRIMARY and
INDEXES.
>> To beef up performance I would like to move as much as I
>> can out of PRIMARY into Index so I would like to know
the
>> best place to keep my Primary, Foreign and Indexes.
>> For instance, is it better to keep my Primary Keys in
the
>> PRIMARY filegroup or move it to the INDEXES filegroup ?
>> Thanks
>> J
>
>.
>|||Julie
Seems to be some confusion here. You say data files, but
it sounds like you are talking about file groups. I agree
with Brian, in that do not create multiple file groups
unless you know you need them.
If you are using multiple physical disks, SQL Server
usually does a good job of striping the tables across the
disks. If you do have one of more large tables that are
very active it can be a benefit to put the non-clustered
indexes in a seperate filegroup. Providing that filegroup
is on different physical drives. I would advise against
doing it as a matter of course, only do it if you can
prove it is an issue.
Hope this helps
John|||Thankyou both for your responses, it looks as if I have my
wires crossed somewhere.
J
>--Original Message--
>Julie
>Seems to be some confusion here. You say data files, but
>it sounds like you are talking about file groups. I agree
>with Brian, in that do not create multiple file groups
>unless you know you need them.
>If you are using multiple physical disks, SQL Server
>usually does a good job of striping the tables across the
>disks. If you do have one of more large tables that are
>very active it can be a benefit to put the non-clustered
>indexes in a seperate filegroup. Providing that filegroup
>is on different physical drives. I would advise against
>doing it as a matter of course, only do it if you can
>prove it is an issue.
>Hope this helps
>John
>.
>