Ask for Best Server Configuration

Dear All,
This is my SQL Server 2000 hardware Configuration:
-RAID 5
-4 HD, each 20 GB
* 1 HD for Drive C
* 3 HD for Raid5 with Fault Tolerance 20 GB
I have OLTP Database that has been running for 1.5 years. In the
database, there is a heavily accessed table that contain more than 1
million rows with several non-cluster indexes belonging to that table.
My question are:
-Is it neccessary to place log files and data files on distinct drive?
Why?
-Is it neccessary to place heavily accessed table and non-cluster
indexes belonging to that table on difference filegroups? And How'
Thanks
Robert LieSee Inline responses:

> -Is it neccessary to place log files and data files on distinct drive?
> Why?
Necessary, No....A good Idea, Yes.
By segregating specific IO Tasks to different logical drives, you redue IO
Saturation (allowing multiple disks, controllers, etc to work
simultaneously).

> -Is it neccessary to place heavily accessed table and non-cluster indexes
> belonging to that table on difference filegroups? And How'
Again, NOT Necessary, but often a good idea.
You would use the "ON [FILEGROUP]" clause in your DDL Commands
Greg Jackson
PDX, Oregon|||Robert Lie wrote:
> Dear All,
> This is my SQL Server 2000 hardware Configuration:
> -RAID 5
> -4 HD, each 20 GB
> * 1 HD for Drive C
> * 3 HD for Raid5 with Fault Tolerance 20 GB
>
> I have OLTP Database that has been running for 1.5 years. In the
> database, there is a heavily accessed table that contain more than 1
> million rows with several non-cluster indexes belonging to that table.
> My question are:
> -Is it neccessary to place log files and data files on distinct drive?
> Why?
It helps, especially for an OLTP system. RAID 5 is the worst performing
raid option for writing. When you consider that log files and tempdb
deal primarily with writing, further burdeoning the data drive doesn't
make sense. It would probably be better for your system to use a
mirrored array instead of a raid 5 array w/ 3 HDs for the log. You
really need to mirror the OS drive as well. You lose that drive and your
entire server is down for days.

> -Is it neccessary to place heavily accessed table and non-cluster
> indexes belonging to that table on difference filegroups? And How'
You don't have that option really given your current design. If you
think about writing to a table and to an index, you can get better
performance if those objects are on two different arrays because they
can be written to simultaneously. There is a filegroup option when
creating the index that determines its data file location.
I might consider making your main data raid 5 array 5 disks, use a
mirrored array for the logs and tempdb, and add a drive to mirror the
OS.
OTOH, if you're not having any performance or capacity issues, then you
might want to leave the design as is.
David Gugick
Imceda Software
www.imceda.com|||Ok Thanks fo your sugestion.
Do you know any web site that explain about RAID on Windows 2000 Server
in more detail?
Thanks
David Gugick wrote:
> Robert Lie wrote:
>
>
> It helps, especially for an OLTP system. RAID 5 is the worst performing
> raid option for writing. When you consider that log files and tempdb
> deal primarily with writing, further burdeoning the data drive doesn't
> make sense. It would probably be better for your system to use a
> mirrored array instead of a raid 5 array w/ 3 HDs for the log. You
> really need to mirror the OS drive as well. You lose that drive and your
> entire server is down for days.
>
>
> You don't have that option really given your current design. If you
> think about writing to a table and to an index, you can get better
> performance if those objects are on two different arrays because they
> can be written to simultaneously. There is a filegroup option when
> creating the index that determines its data file location.
> I might consider making your main data raid 5 array 5 disks, use a
> mirrored array for the logs and tempdb, and add a drive to mirror the OS.
> OTOH, if you're not having any performance or capacity issues, then you
> might want to leave the design as is.
>|||Robert Lie wrote:
> Ok Thanks fo your sugestion.
> Do you know any web site that explain about RAID on Windows 2000
> Server in more detail?
>
RAID is a harware implementation, separate from the OS. There is a web
site that advocates not to use RAID 5, but I don't remember the address.
See if SQLServerPerformance.com has some answers.
David Gugick
Imceda Software
www.imceda.com|||Hello Robert,
Performance Tuning Guide from MS press shud be a good start.
Create the objects on that filegroup. If the table has a Clustered Index,
drop and recreate the Clustered Index on the desired File Group.
[Is it neccessary to place heavily accessed table and non-cluster
indexes belonging to that table on difference filegroups? And How']
Log file is write intensive - "Sequentially". It is best kept on its own
drive (RAID 1)
[Is it neccessary to place log files and data files on distinct drive? >
Why?]
Are all the Indexes being used. Sometimes, we create Indexes and over a
period of time
we loose track of its usage and as a consequence this reduces the
performance during
writes with respect to any unused Index.
[with several non-cluster indexes belonging to that table.]
Gopi
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23$Nv$csSFHA.580@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> This is my SQL Server 2000 hardware Configuration:
> -RAID 5
> -4 HD, each 20 GB
> * 1 HD for Drive C
> * 3 HD for Raid5 with Fault Tolerance 20 GB
>
> I have OLTP Database that has been running for 1.5 years. In the database,
> there is a heavily accessed table that contain more than 1 million rows
> with several non-cluster indexes belonging to that table.
> My question are:
> -Is it neccessary to place log files and data files on distinct drive?
> Why?
> -Is it neccessary to place heavily accessed table and non-cluster indexes
> belonging to that table on difference filegroups? And How'
> Thanks
> Robert Lie|||Try to google for RAID - that will give you a number of hits. You can also
start with this one http://www.acnc.com/raid.html that gives you a brief
introduction to the various RAID types.
Regards
Steen
Robert Lie wrote:[vbcol=seagreen]
> Ok Thanks fo your sugestion.
> Do you know any web site that explain about RAID on Windows 2000
> Server in more detail?
> Thanks
>
> David Gugick wrote: