Any DB admins here?

[|tera|]

We've got a server running SQL 2005 Enterprise SP2.

The problem we have is that our CPU usage is mostly above 90% for sqlserver.exe and the pagefile usage is huge, about 6.5GB.

It's a Quad Core CPU, 3.6 Ghz and the server's got 9GB of RAM.

It's currently serving just above 100 users and roughly 5-7 databases IIRC.

Due to the sensitive nature of the issue, I'd prefer answering questions via pm, since I can't say which databases or programs we run on the server, since it's confidential.

Would appreciate any help in the right direction.

Googling gives nada really.

Thanks,
tera

 

[Dark Agent]

CPU usage 90% on the main Server!!!

Thats normal. Mine always 100%. Users 40.
Depends if work is done and what program is running.
It's a Quad Core CPU, 2.6 Ghz and the server's got 4GB of RAM.

 

[|tera|]

It's definitely not normal.

The applications that run from the server are really slow, to the point that it's unbearable to use. This only started this week.

 

[The_Unbeliever]

@ tera - can be that the SQL database is fragmented.

Sent you a PM - confidential information as I don't feel like editing it out

 

[|tera|]

@ tera - can be that the SQL database is fragmented.

Sent you a PM - confidential information as I don't feel like editing it out

Cool. I haven't received any pm yet though

Edit: thanks, got it.

 

[gregmcc]

CPU usage 90% on the main Server!!!

Thats normal. Mine always 100%. Users 40.
Depends if work is done and what program is running.
It's a Quad Core CPU, 2.6 Ghz and the server's got 4GB of RAM.

That's definately not normal. If its always above 80% then you've got serious issues.

Besides the normal (memory, cpu, io) I'd look at running a reorg on the DB.

Also look at some trending - has it always been high? has it slowly been increasing?

 

[AjqNic]

The problem we have is that our CPU usage is mostly above 90% for sqlserver.exe and the pagefile usage is huge, about 6.5GB.

Poorly written SQL scripts / stored procs can cause excessive CPU and memory usage.

A simple case in point would be the following script which would cause 100% CPU usage on a single core until script execution is cancelled:

while 1=1
select * from dbo.systypes

Having just 4 users running the above script could use up all CPU power on all 4 cores.

One method for determining where the problem lies is to run SQL Profiler (comes standard with MSSQL). Using SQL Profiler, select 'File -> New -> Trace', create the necessary connection and then let it run. After a while take a look at the CPU and Duration columns, this should provide some insight as to where the problem lies.

 

[|tera|]

Thanks, will check it out.

Any more info would be appreciated.

 

[AjqNic]

Any more info would be appreciated.

Well from there on in things get a little more complicated. Assuming you've determined the culprit to be a poorly written SQL script (very often the case), you'll then need to optimise or re-engineer the script to fix the problem.

SQL scripting, stored procs and schema design are the heart of many applications (especially business applications) yet it's importance is often neglected. Many developers will boast about the complexity of their SQL code and the fact that it's so complex and ingenious that it takes minutes or even hours to run. Just because a database has millions of entries doesn't mean that the SQL process should take ages, in many instances (although not all) code can be optimised to provide far more efficient results. A common culprit is SQL scripts that make use of cursors, although cursors have a place they should be used sparingly if at all.

To give you an example of how dangerous poorly written code can be, I've re-engineered a script that took 3 hours to run and optimised it down to just 2 minutes (this script was in regular use by a major financial institute). More often is the case that a script may take a couple of minutes to run and in many cases these can be optimised down to no more than a few seconds.

SQL optimisation is not always easy, it's not simply a case of adding some indexes to the relevant tables but more often a redesign of the paradigms and methodology used to generate the required result. Optimisation of the code should preferably be done in collaboration with the creators of the application in question as there are often unforeseen circumstances that need to be catered for when the code is re-engineered.

 

[|tera|]

Thank you Gnite

 

[Dark Agent]

That's definately not normal. If its always above 80% then you've got serious issues.

Besides the normal (memory, cpu, io) I'd look at running a reorg on the DB.

Also look at some trending - has it always been high? has it slowly been increasing?

Only when our business is busy.
So it will always stay like that and I want it to be like that
btw if users are inactive it falls to 1-5%

So it is normal