SQL Server disk sub-system (overhaul) performance questions

ajjvn

I 'inherited' a group of SQL Server server class machines. They are
true server technology but the disk sub-systems are lacking. There is
one hot-swap backplane that all the drives share (with one SCSI
channel) thusly even though there are three logical drives (composed
from 6 to 8 hard drives), they all go through one channel. This is
creating a performance issue that is noticable and can be seen in
various performance counters that Microsoft recommended one should
monitor in terms of disk I/O. For a cheaper 'fix', I can add a
seperate two drive bay (with its own SCSI channel) with mirrored
drives. I would then mostly likely place the transaction log files on
this new channel. Or I could place the indices filegroup files on
this new channel for DBs with mainly searching going on (not much
updating). If I went this route I would be leaning towards the
transaction log move since the second method would require me moving
DBs around quite a bit. Any input on this solution (besides spending
more money)?

What I would prefer to do is get a better server class machine or add
an external drive bay solution (not a SAN). I would try to get three
or four SCSI channels in the new hardware to split the different
file/filegroups out (i.e. transaction logs files, data filegroup,
indices filegroup, etc.). My only concern here is: would this more
expensive solution be worth the money? As far as replacing servers, I
have only two kinds of experience...replacing somewhat underpowered
servers with slightly less underpowered servers and replacing overkill
servers with even more overkill servers. In both cases, the disk
sub-systems were fairly equivalent from the old system to the new one.

Will going the three/four channel route really get data moving along?
We have one server in particular that hosts a database (one of many on
it) for a web application that gets decent traffic (it is a private
login based system for internal use and external use by our clients'
agents). Periodically throughout the day, there are 2-5 minute bursts
where performance slows to a crawl. I want to spend more time
profiling queries and such before recommending we spend more money,
but the folks I am working for want quick results and there is quite a
bit of stored procedure logic to profile and investigate. I know the
disk sub-system is definately in need of an overhaul, but I would like
to get an idea of peformance gains from adding either one additional
channel over the existing single channel as well as going the
three/four channel route over the existing single channel setup.

Any information would be greatly appreciated.

Regards,
Tony

Jul 20 '05 #1

Erland Sommarskog

ajjvn (aj***@hotmail.com) writes:

Will going the three/four channel route really get data moving along?
We have one server in particular that hosts a database (one of many on
it) for a web application that gets decent traffic (it is a private
login based system for internal use and external use by our clients'
agents). Periodically throughout the day, there are 2-5 minute bursts
where performance slows to a crawl. I want to spend more time
profiling queries and such before recommending we spend more money,
but the folks I am working for want quick results and there is quite a
bit of stored procedure logic to profile and investigate. I know the
disk sub-system is definately in need of an overhaul, but I would like
to get an idea of peformance gains from adding either one additional
channel over the existing single channel as well as going the
three/four channel route over the existing single channel setup.

I don't have much experience of dimensioning servers, but from what you
say it seems that upgrading the hardware is going to improve the system,
but the performance problems with remain. Whlie better hardware can
make some performance issues negligible, there are many ways to make
a database engine so slow, that no hardware in the world can save it.

So I predict that even if you buy all those disks, you will eventually
be asked to analyse the application and look for ways to improve it.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #2

ajjvn

What you say is true. We are doing a lot of profiling and analyzing
but we know a server replacement will be in order down the road to
handle a known increase in load. What we are wondering is if we move
the hardware replacement sooner, how much will we benefit from it
(based on others observations of somewhat similar changes in
hardware).

Anyone?

TIA,
Tony

--------------

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...

ajjvn (aj***@hotmail.com) writes:

Will going the three/four channel route really get data moving along?
We have one server in particular that hosts a database (one of many on
it) for a web application that gets decent traffic (it is a private
login based system for internal use and external use by our clients'
agents). Periodically throughout the day, there are 2-5 minute bursts
where performance slows to a crawl. I want to spend more time
profiling queries and such before recommending we spend more money,
but the folks I am working for want quick results and there is quite a
bit of stored procedure logic to profile and investigate. I know the
disk sub-system is definately in need of an overhaul, but I would like
to get an idea of peformance gains from adding either one additional
channel over the existing single channel as well as going the
three/four channel route over the existing single channel setup.

I don't have much experience of dimensioning servers, but from what you
say it seems that upgrading the hardware is going to improve the system,
but the performance problems with remain. Whlie better hardware can
make some performance issues negligible, there are many ways to make
a database engine so slow, that no hardware in the world can save it.

So I predict that even if you buy all those disks, you will eventually
be asked to analyse the application and look for ways to improve it.

Jul 20 '05 #3