SQLSERVR.EXE used 80 to 98% of CPU.

Question

Post reply

SQLSERVR.EXE used 80 to 98% of CPU.

Arnold Magpantay

Old Hand

Points: 343
More actions
July 19, 2005 at 9:58 pm

#110056

Hi to all,
I have a sql server 2000, and now we are suffuring a slow performance of the server, I have sql server 2000 with 2 processors only (Win2k advance server). I checked the task manager, and found out that the sqlservr.exe used 80 to 98% cpu usage. What sould i do to improve the sql perfomance?
Any advise, tips and comments are highly appreciated.
Thank you in advance.
Regards,
Arnold

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

BarryOC SSC Veteran Points: 299 More actions · Answer 1

Hello,

I checked my notes for this and here is what I had

Processor Queue Length

System: Processor Queue Length.
The optimum Queue Length is 2 x (number of processors)

So run a Perfmon and get System: Processor Queue Length. If you maintain a 4 or greater then you need to decrease the workload or increase the number or size of the processors. You can decrease the workload by streamlining your SQL and by checking for errors. That is not a quick fix. The easy solution is beef up the hardware. You could always run a trace for your system and see how it looks. But that is better described elsewhere.

Best wishes,

Barry O'Connell

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

You may find increasing memory will drop cpu .. things I've done which have reduced cpu load ( depends upon your apps of course ):-

Added memory

Added secondary indexes to stop table scans/large selects on queries ( not my apps < grin> )

Placed clustered indexes on tables without clustered idnexes

Used Teratrax performance monitor to record high cpu sql statements/procs and applied indexes.

Reduced use of temporary tables / replaced with table vars

Removed select into statements

Defrag of ntfs file system / adequate disk space on volumes / rebuild of indexes / update of stats

You really need to isolate if you have high cpu queries etc. and fix them - if not add more procs!!

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

manojsoni2001 SSC Enthusiast Points: 145 More actions · Answer 3

Hi,

I read your problem.

I think that this problem may be becuase of Memory,

you should check Memory Counters and then add the required

memory if needed

Jeff Gray SSChampion Points: 10667 More actions · Answer 4

I would suggest using SQL profiler to look for processes that take large amounts of CPU time to complete. If you can find some, optimizing the SQL code that is causing lots of CPU may be the fastest way to alleviate your pain. This is commonly referred to as finding the "low-hanging fruit".

There are tools that will make this search easier. One such tool is Speed Coefficient by Imceda software. I use that tool quite a bit when looking for performance bottlenecks. The tool summarizes the information from traces, making it easier to find the badness.

While you may find that a lack of memory will increase CPU load, it is not the LIKELY culprit. Watch (using the O/S performance monitor) the Page Life Expectancy object. If the value is consistently high (defined by Microsoft as over 300) then adding memory probably won't help much, if at all.

Also note that having a CPU load above 80% doesn't automatically mean that there's a problem. It is a fair indication, coupled with the anecdotal evidence (response is slow) in many cases but not always.

hth jg