Placement of datafiles and log files

This is my first sql server app so I'm pretty ignorant.
In my production environment I have a server with 4
processors and 4 drives and is part of a SANS network. 2
drives are mirrorred to be c: and two drives are mirrorred
to be d:, both RAID1. I also have another drive (T:)
available for my app. I have my datafiles for this app on
the t: drive which is RAID5 and I have my log files on
drive d:. Is this the recommended practice. I know that
I read that I need to separate my data files and log
files. Please advise.
Thanks,
EdieThat seems like a reasonable setup. To be able to give more precise
suggestions, we'd need I/O metrics for the current config. I suggest you
keep it like it is unless you have performance problems and are willing to
spend some time on the issue.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Edie Richardson" <anonymous@.discussions.microsoft.com> wrote in message
news:034e01c3be9f$6c680620$a401280a@.phx.gbl...
> This is my first sql server app so I'm pretty ignorant.
> In my production environment I have a server with 4
> processors and 4 drives and is part of a SANS network. 2
> drives are mirrorred to be c: and two drives are mirrorred
> to be d:, both RAID1. I also have another drive (T:)
> available for my app. I have my datafiles for this app on
> the t: drive which is RAID5 and I have my log files on
> drive d:. Is this the recommended practice. I know that
> I read that I need to separate my data files and log
> files. Please advise.
> Thanks,
> Edie|||Thanks. If I were to add more databases to that instances
of sql server, would it be a good practice to put all my
log files on that one D Drive?
>--Original Message--
>That seems like a reasonable setup. To be able to give
more precise
>suggestions, we'd need I/O metrics for the current
config. I suggest you
>keep it like it is unless you have performance problems
and are willing to
>spend some time on the issue.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Edie Richardson" <anonymous@.discussions.microsoft.com>
wrote in message
>news:034e01c3be9f$6c680620$a401280a@.phx.gbl...
>> This is my first sql server app so I'm pretty ignorant.
>> In my production environment I have a server with 4
>> processors and 4 drives and is part of a SANS network.
2
>> drives are mirrorred to be c: and two drives are
mirrorred
>> to be d:, both RAID1. I also have another drive (T:)
>> available for my app. I have my datafiles for this app
on
>> the t: drive which is RAID5 and I have my log files on
>> drive d:. Is this the recommended practice. I know
that
>> I read that I need to separate my data files and log
>> files. Please advise.
>> Thanks,
>> Edie
>
>.
>|||Yes, I think so. Ideally, each database should have it's on drive for the
log, but sometimes that is not practical. Isolating other stuff from the
drive where you have a number of databases log files is still better than
mix 'n match, IMO.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:000301c3c011$770047f0$a501280a@.phx.gbl...
> Thanks. If I were to add more databases to that instances
> of sql server, would it be a good practice to put all my
> log files on that one D Drive?
> >--Original Message--
> >That seems like a reasonable setup. To be able to give
> more precise
> >suggestions, we'd need I/O metrics for the current
> config. I suggest you
> >keep it like it is unless you have performance problems
> and are willing to
> >spend some time on the issue.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Edie Richardson" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:034e01c3be9f$6c680620$a401280a@.phx.gbl...
> >> This is my first sql server app so I'm pretty ignorant.
> >> In my production environment I have a server with 4
> >> processors and 4 drives and is part of a SANS network.
> 2
> >> drives are mirrorred to be c: and two drives are
> mirrorred
> >> to be d:, both RAID1. I also have another drive (T:)
> >> available for my app. I have my datafiles for this app
> on
> >> the t: drive which is RAID5 and I have my log files on
> >> drive d:. Is this the recommended practice. I know
> that
> >> I read that I need to separate my data files and log
> >> files. Please advise.
> >>
> >> Thanks,
> >> Edie
> >
> >
> >.
> >

PK design in clustered environment

We are designing a large transactional database that will end up being deployed in a clustered SQL Server 2000 environment. We have initially designed all of our primary key fields as GUID type to eliminate any potential future replication issues. Will
this design impact the performance of the db negatively versus using an auto-increment PK design using big int type fields and following a ranged identities design approach?
Primary keys selection and performance is the same regardless of whether you
are in a clustered environment or not.
GUIDs as PKs are not a good idea. First, they are effectively random, so
there will be lots of page splits and table/index fragementation if you
allow the default of clustered order on the PK. Second, they are totally
artificial, which I think is a very bad idea from a data integrity
standpoint. Finally, they are wide. Remember, every non-clustered index
uses the clustered index to look up date rows. GUIDs are very wide and will
slow down index intersection as a possible query resolution path.
Here is the (very condensed) version of how I prefer to layout index
structures.
First, I create a RowID field Int (or BigInt) Identity(1,1) on each table.
I then create a unique, clustered index on this column. Note it is NOT the
Primary Key. Primary keys are data-centric, not artificial. If you cannot
identify a data-centric key, then you need to rethink your design since it
is by definition not third normal form. With data-centric keys, I don't
worry about new RowIDs if I need to replicate the data.
My PK index is non-clustered. Any additional indexes are also
non-clustered.
Since the data is now in insert order, the cache manager now has a bit of
help. Most databases query newly inserted data more often than older data,
especially data in large tables. Inserts are intentionally hot-spotted,
also helping out the lazy writer and checkpoint processes. Index
intersection is highly optimized.
I have been using this technique for a few years now and it makes a HUGE
difference in performance.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"toml" <anonymous@.discussions.microsoft.com> wrote in message
news:A2293202-5C5F-4240-9413-628E5913FF58@.microsoft.com...
> We are designing a large transactional database that will end up being
deployed in a clustered SQL Server 2000 environment. We have initially
designed all of our primary key fields as GUID type to eliminate any
potential future replication issues. Will this design impact the
performance of the db negatively versus using an auto-increment PK design
using big int type fields and following a ranged identities design approach?