Performance counter quest

Hi,
SQL2000 Standard sp3.
Windows 2000 server, 2 physical processors, Intel hyper threading tech.
I am running Performance monitor from another machine which is windows 2000 terminal , to monitor the above mentioned sqlserver. The output of the csv file showing total processor time > 300. I dont understand why it is showing instead of values < 100. I
n that machine i can see .NET CLR counters also. Is this behaviour because of .NET?
(PDH-CSV 4.0) (Pacific Daylight Time)(420)\\AB01\Process(_Total)\% Privileged Time\\AB01\Process(_Total)\% Processor Time
4/13/04 5:00 AM349.7912317399.6868476
4/13/04 5:00 AM350399.8958333
4/13/04 5:00 AM399.7918835399.8959417
4/13/04 5:01 AM343.8541667399.6875
How can i take the processor time to calculate cpu usage? Any help appreciated
thanks
kris
Look at the Processor(_Total)\% Processor Time for values that you expect.
Some of the counters are explained at :
http://www.extremeexperts.com/sql/ar...LCounters.aspx
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"kris" <anonymous@.discussions.microsoft.com> wrote in message
news:BF219D19-16FC-40DD-8728-31A2E8E2580A@.microsoft.com...
> Hi,
> SQL2000 Standard sp3.
> Windows 2000 server, 2 physical processors, Intel hyper threading tech.
> I am running Performance monitor from another machine which is windows
2000 terminal , to monitor the above mentioned sqlserver. The output of the
csv file showing total processor time > 300. I dont understand why it is
showing instead of values < 100. In that machine i can see .NET CLR
counters also. Is this behaviour because of .NET?
> (PDH-CSV 4.0) (Pacific Daylight Time)(420) \\AB01\Process(_Total)\%
Privileged Time \\AB01\Process(_Total)\% Processor Time
> 4/13/04 5:00 AM 349.7912317 399.6868476
> 4/13/04 5:00 AM 350 399.8958333
> 4/13/04 5:00 AM 399.7918835 399.8959417
> 4/13/04 5:01 AM 343.8541667 399.6875
> How can i take the processor time to calculate cpu usage? Any help
appreciated
> thanks
> kris
>
>

Performance counter quest

Hi
SQL2000 Standard sp3.
Windows 2000 server, 2 physical processors, Intel hyper threading tech
I am running Performance monitor from another machine which is windows 2000 terminal , to monitor the above mentioned sqlserver. The output of the csv file showing total processor time > 300. I dont understand why it is showing instead of values < 100. In that machine i can see .NET CLR counters also. Is this behaviour because of .NET
(PDH-CSV 4.0) (Pacific Daylight Time)(420) \\AB01\Process(_Total)\% Privileged Time \\AB01\Process(_Total)\% Processor Tim
4/13/04 5:00 AM 349.7912317 399.686847
4/13/04 5:00 AM 350 399.895833
4/13/04 5:00 AM 399.7918835 399.895941
4/13/04 5:01 AM 343.8541667 399.687
How can i take the processor time to calculate cpu usage? Any help appreciate
thank
kriLook at the Processor(_Total)\% Processor Time for values that you expect.
Some of the counters are explained at :
http://www.extremeexperts.com/sql/articles/SQLCounters.aspx
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"kris" <anonymous@.discussions.microsoft.com> wrote in message
news:BF219D19-16FC-40DD-8728-31A2E8E2580A@.microsoft.com...
> Hi,
> SQL2000 Standard sp3.
> Windows 2000 server, 2 physical processors, Intel hyper threading tech.
> I am running Performance monitor from another machine which is windows
2000 terminal , to monitor the above mentioned sqlserver. The output of the
csv file showing total processor time > 300. I dont understand why it is
showing instead of values < 100. In that machine i can see .NET CLR
counters also. Is this behaviour because of .NET?
> (PDH-CSV 4.0) (Pacific Daylight Time)(420) \\AB01\Process(_Total)\%
Privileged Time \\AB01\Process(_Total)\% Processor Time
> 4/13/04 5:00 AM 349.7912317 399.6868476
> 4/13/04 5:00 AM 350 399.8958333
> 4/13/04 5:00 AM 399.7918835 399.8959417
> 4/13/04 5:01 AM 343.8541667 399.6875
> How can i take the processor time to calculate cpu usage? Any help
appreciated
> thanks
> kris
>
>

Performance counter quest

Hi,
SQL2000 Standard sp3.
Windows 2000 server, 2 physical processors, Intel hyper threading tech.
I am running Performance monitor from another machine which is Windows 2000
terminal , to monitor the above mentioned sqlserver. The output of the csv f
ile showing total processor time > 300. I dont understand why it is showing
instead of values < 100. I
n that machine i can see .NET CLR counters also. Is this behaviour because o
f .NET?
(PDH-CSV 4.0) (Pacific Daylight Time)(420) \\AB01\Process(_Total)\% Privileg
ed Time \\AB01\Process(_Total)\% Processor Time
4/13/04 5:00 AM 349.7912317 399.6868476
4/13/04 5:00 AM 350 399.8958333
4/13/04 5:00 AM 399.7918835 399.8959417
4/13/04 5:01 AM 343.8541667 399.6875
How can i take the processor time to calculate cpu usage? Any help appreciat
ed
thanks
krisLook at the Processor(_Total)\% Processor Time for values that you expect.
Some of the counters are explained at :
http://www.extremeexperts.com/sql/a...QLCounters.aspx
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"kris" <anonymous@.discussions.microsoft.com> wrote in message
news:BF219D19-16FC-40DD-8728-31A2E8E2580A@.microsoft.com...
> Hi,
> SQL2000 Standard sp3.
> Windows 2000 server, 2 physical processors, Intel hyper threading tech.
> I am running Performance monitor from another machine which is windows
2000 terminal , to monitor the above mentioned sqlserver. The output of the
csv file showing total processor time > 300. I dont understand why it is
showing instead of values < 100. In that machine i can see .NET CLR
counters also. Is this behaviour because of .NET?
> (PDH-CSV 4.0) (Pacific Daylight Time)(420) \\AB01\Process(_Total)\%
Privileged Time \\AB01\Process(_Total)\% Processor Time
> 4/13/04 5:00 AM 349.7912317 399.6868476
> 4/13/04 5:00 AM 350 399.8958333
> 4/13/04 5:00 AM 399.7918835 399.8959417
> 4/13/04 5:01 AM 343.8541667 399.6875
> How can i take the processor time to calculate cpu usage? Any help
appreciated
> thanks
> kris
>
>

Performance counter problem

I'm installing SQL2005 September CTP on a fresh W2K3 SP1 machine running on Virtual Server.

When I want to install SQL it says there is a performance counter.
The installation guide is clear about what I need to change in the registry to get this to work but the data type for the perflib/009 keys is REG_MULTI_SZ and the other one is REG_DWORD.

I have used the January 2005 MSDN disc to install the OS.

Can anyone help me with this please?

Thanks,

Jay

I assume you got this solved long ago. If it's still a problem for you with RTM / SP1 code, please repost.

Paul

Performance counter problem

I'm installing SQL2005 September CTP on a fresh W2K3 SP1 machine running on Virtual Server.

When I want to install SQL it says there is a performance counter.
The installation guide is clear about what I need to change in the registry to get this to work but the data type for the perflib/009 keys is REG_MULTI_SZ and the other one is REG_DWORD.

I have used the January 2005 MSDN disc to install the OS.

Can anyone help me with this please?

Thanks,

Jay

I assume you got this solved long ago. If it's still a problem for you with RTM / SP1 code, please repost.

Paul

Performance ?

If I set the performance monitor alert on the production machine so is there
any performance issue ?
Hi,
No issues, But select only required counters. See the below site, it defines
about lots of good monitoring counters
http://www.sql-server-performance.com
Thanks
Hari
SQL Server MVP
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:E4CA1ECE-8015-4186-8CAB-3315CBE32474@.microsoft.com...
> If I set the performance monitor alert on the production machine so is
> there
> any performance issue ?

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.

> Raid
> 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:

> Raid
> 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.
>
> 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...
> 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/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_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...
> you
> Let's assume that it is optimized. Where to next?
>

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 stre
ss
> 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 i
f
> 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.

> Raid
> 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 dea
l
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:

> Raid
> 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.
>
> 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...
> 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/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_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...
> you
> Let's assume that it is optimized. Where to next?
>

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?
>

Performance

I have a SQL server 7.0 which is installed on a dual
processors machine (NT4.0), when I try to do a query its
fairly slow, I have checked CPU utilization and memory
they are ok, is there anyway to find out what is wrong
here?
Also is there any tool I can check SQL server performance?
thanks in advance-RobCheck the query plan, also use profiler to see
the statistics of the query, such as reads, writes, etc.
>--Original Message--
>I have a SQL server 7.0 which is installed on a dual
>processors machine (NT4.0), when I try to do a query its
>fairly slow, I have checked CPU utilization and memory
>they are ok, is there anyway to find out what is wrong
>here?
>Also is there any tool I can check SQL server performance?
>thanks in advance-Rob
>.
>|||I am new this area, what is the query plan and how can I
use the profiler.
Thanks-rob
>--Original Message--
>Check the query plan, also use profiler to see
>the statistics of the query, such as reads, writes, etc.
>>--Original Message--
>>I have a SQL server 7.0 which is installed on a dual
>>processors machine (NT4.0), when I try to do a query its
>>fairly slow, I have checked CPU utilization and memory
>>they are ok, is there anyway to find out what is wrong
>>here?
>>Also is there any tool I can check SQL server
performance?
>>thanks in advance-Rob
>>.
>.
>|||Lookup Graphical Query Plan in Books on Line. Too hard to explain in a
newsgroup post.
The query plan will show you problems like Table Scans that can be solved
with more selective where clauses and/or applying indexes to table in
question.
There is obviously much more to it than that, but there is a start for you.
Also, visit www.sql-server-performance.com
Cheers,
Greg Jackson
Portland, OR|||Hi Rob,
You may also want to refer to these articles that may help you troubleshoot performance issues.
224587 HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/?id=224587
This step-by-step article describes how to troubleshoot SQL Server performance issues. Troubleshooting
performance issues involves the use of a series of steps to isolate and determine the cause of an application
slowdown.
~~~
243589 HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
This article describes how to handle a performance issue that applications may experience in conjunction with
Microsoft SQL Server: slow performance of a specific query or group of queries.
~~~
243588 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
http://support.microsoft.com/?id=243588
This article describes how to troubleshoot the slow performance of many concurrent ad-hoc queries.
~~~
224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/?id=224453
This article focuses on how to monitor SQL Server to capture pertinent system information and how to analyze
that information to successfully resolve blocking issues.
~~~
Sincerely,
William Wang
Microsoft Partner Online Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Jaxon" <GregoryAJackson@.hotmail.com>
> References: <045401c3a960$5eccf1f0$a301280a@.phx.gbl> <037501c3a964$e45d7660$a401280a@.phx.gbl>
<05b301c3a966$30440bb0$a101280a@.phx.gbl>
> Subject: Re: Performance
> Date: Wed, 12 Nov 2003 16:31:22 -0800
> Lines: 19
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
> Message-ID: <OKLL41XqDHA.2964@.tk2msftngp13.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> NNTP-Posting-Host: 66.203.99.51
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:316205
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Lookup Graphical Query Plan in Books on Line. Too hard to explain in a
> newsgroup post.
> The query plan will show you problems like Table Scans that can be solved
> with more selective where clauses and/or applying indexes to table in
> question.
> There is obviously much more to it than that, but there is a start for you.
> Also, visit www.sql-server-performance.com
>
> Cheers,
>
> Greg Jackson
> Portland, OR
>
>