CPU and memory usage

Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
Unfortunatly there is not a straight answer for your query.
My first question is are you sure its SQL Server that causing it, i.e. have
you looked into TASK MANAGER to see what process has the most CPU time ?
You could also set up a Performance check under Administration Tool, you can
set up CPU as one of the things it check.
Could you give an indication of the size of the database and the version of
SQL Server you using.
If you like although its not really recommended you can change the CPU's SQL
Server uses. You go to the properties of the Server under Enterprise Manager
then select Processors, just read up on BOL when you get there.
You could also check which jobs are running at the time, both continually
and at certain times, then have a look at optimising them.
However according to SQL Server 2000 Performance Tuning if the CPU usage is
greater than 80% then generally you need new processors.
"CC&JM" wrote:

> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>
|||Maybe these will help:
ut while DB expanding
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
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any
> way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>
|||The biggest problem you have is to diagnose whether or not SQL Server is
performing legitimate work or not. If it is, then, as the others have
suggested, you are probably over capacity. If not, I would be suspecious
that you are being hit by a "slammer" like attack.
Here are some tests.
Block UDP port 1434 from the IPSec policies on the connecting
switch/firewall or SQL Server host itself. If the acitivity drops, you were
either being attacked, or legitimate clients have now all been locked out.
Take a look at SQLServer:Databases Transactions per Second and
SQLServer:Statistics Batch Requests per Second. This will tell you how much
activity is being thrown at the server.
Check the ProcessorTime% and PageFaults/sec for the sqlservr process. If
SQL Server is page faulting, you are probably shy on memory. You say you
have 4 GB of ram, but how are you using it? Have you enabled 4 GB Tuning?
Do you have SS2K Standard or Enterprise Edition. If EE, have you enabled
AWE or considered going multi-instanced?
What build level are you running? There was a MS security patch for SP2,
which was rolled up into SP3. There is also a post SP3a (build 760)
security patch, MS03-031 (builds 818 and 819). Finally, the most recent
publicly available hot fix is build 878.
First make sure you are not "infected," then I would concentrate on the
performance tuning and capacity planning.
Sincerely,
Anthony Thomas

"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.