Performance

I have an application that allows for searches, but it appears when I stress
test it with 50+ users the CPU utilization on the MS SQL server machine is
hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
dual AMD Opteron cpu's. I can get a more powerful machine, but not sure if
that is the solution here.
What are some methods to scale this thing out? Replicate the database and
spread out the searches to multiple identical databases?It's pretty hard to say what the problem is without seeing exactly what you
are doing. But more than likely your queries and / or indexes are not
optimized. Try tuning them first.
--
Andrew J. Kelly SQL MVP
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:2dadnQoD5ty8u6jfRVn-3A@.adelphia.com...
>I have an application that allows for searches, but it appears when I
>stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure
> if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
> It's pretty hard to say what the problem is without seeing exactly what
you
> are doing. But more than likely your queries and / or indexes are not
> optimized. Try tuning them first.
Let's assume that it is optimized. Where to next?|||You did not specify any details so my recommendations:
- Check your DB and log files are on seperate arrays. (Raid 1 for Log, Raid
5 for db (Raid 10 is better))
- Did you enable /3GB switch, if possible.
- Use SQL Profiler, Index Tuning Wizard to find out problematic queries.
- Check your DB and Log file auto-growth, auto-shrink settings.
"Shabam" wrote:
> I have an application that allows for searches, but it appears when I stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>
>|||> - Check your DB and log files are on seperate arrays. (Raid 1 for Log,
Raid
> 5 for db (Raid 10 is better))
Strange, but I was told that Raid 1 is better for db as it performs best,
and that Raid 5 is bad for performance? This db will be writing quite a
bit.
> - Did you enable /3GB switch, if possible.
Where do I do this?
Ok, beyond the turning, optimizing, etc... what options are there next if I
outgrow it? Sure I can upgrade the server, but that gets to a limit. Then
what?|||If we are to assume that the code and DB model are optimal, and that we
should focus on answering your question only. Your specific question is how
does SQL scale OUT an OLTP. Scale out is generally a matter of adding more
SQL servers and distributing (usually via code but distributed partitions is
also an option) your database and/or functions across them. Sounds like
your asking about creating a reporting ODS to pull the searches off to
another server. By focusing only on scale out solutions you are confining
the answers to expensive solutions.
Lets start with the basics.
What other applications are running on the server if any?
Is this SQL 2000?
Is this SQL Standard or Enterprise edition?
What's driving your high CPU?
How many recompiles and procedure cache misses are occurring?
Does your code have an abundance of non set oriented logic?
Is this code owned by someone else and you don't have the right to change
it?
The more information you can provide the better advice the group can give.
Ray
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:2dadnQoD5ty8u6jfRVn-3A@.adelphia.com...
>I have an application that allows for searches, but it appears when I
>stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure
> if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>|||> > - Did you enable /3GB switch, if possible.
> Where do I do this?
From BOOT.INI, but you did not give any details about which edition of SQL
Server you are using and the OS.
> > - Check your DB and log files are on seperate arrays. (Raid 1 for Log,
> Raid
> > 5 for db (Raid 10 is better))
> Strange, but I was told that Raid 1 is better for db as it performs best,
> and that Raid 5 is bad for performance? This db will be writing quite a
> bit.
>
You can search RAID5 vs RAID 10 on internet for more information. Raid1 is
better for Log because log writes are sequential. Raid 10 is better for db
files because you spread io across multiple disks and it did not need to deal
with parity as Raid 5 does.
> Ok, beyond the turning, optimizing, etc... what options are there next if I
> outgrow it?
My point is to not use autogrowth-auto shrink which may cause problems.
"Shabam" wrote:
> > - Check your DB and log files are on seperate arrays. (Raid 1 for Log,
> Raid
> > 5 for db (Raid 10 is better))
> Strange, but I was told that Raid 1 is better for db as it performs best,
> and that Raid 5 is bad for performance? This db will be writing quite a
> bit.
> > - Did you enable /3GB switch, if possible.
> Where do I do this?
> Ok, beyond the turning, optimizing, etc... what options are there next if I
> outgrow it? Sure I can upgrade the server, but that gets to a limit. Then
> what?
>
>|||Run Profiler and find out what it is that takes time and then try to
optimize those parts.
Regards
Steen
Shabam wrote:
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
>> It's pretty hard to say what the problem is without seeing exactly
>> what you are doing. But more than likely your queries and / or
>> indexes are not optimized. Try tuning them first.
> Let's assume that it is optimized. Where to next?|||"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23BuYhsJKFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Run Profiler and find out what it is that takes time and then try to
> optimize those parts.
Another dead end...|||Have a look here:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:H8qdnR8CepdmpqjfRVn-qQ@.adelphia.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
>> It's pretty hard to say what the problem is without seeing exactly what
> you
>> are doing. But more than likely your queries and / or indexes are not
>> optimized. Try tuning them first.
> Let's assume that it is optimized. Where to next?
>