CPU Utilization is 100% !!!!

Question

CPU Utilization is 100% !!!!

SQL_DBA_3

SSCarpal Tunnel

Points: 4772
More actions
November 13, 2008 at 6:33 am

#125898

We use SQL Server 2005 SP1 ,on Windows 2003 Server R2,4 Intel XEON CPUs @2.66 GHz and 32 GB of RAM(PAE Enabled).Problem is at peak time for 4-6 hours the CPU Utilization is 100% in Performance Monitor and the users are reporting that the server and queries are damn slow.
Can you please suggest where can I check and what may be the causes?

Viewing 12 posts - 1 through 11 (of 11 total)

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

First you can check your CPU & MEMORY is sufficient to take a load.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

Use SQL Profiler to see which kind of activities brings server to it's knees.

N 56°04'39.16"
E 12°55'05.25"

CrazyMan SSChampion Points: 12560 More actions · Answer 3

Check the process that access the SQL and check the codes, if its a SP then try to improve performance by findnig bottleneck, as suggested i would use profiler to see what exactly is happening at the time, then start from there

🙂

SQL_DBA_3 SSCarpal Tunnel Points: 4772 More actions · Answer 4

Thanks for your posts.Only stored procedures are used by the application.

My question here is :Are all the 4 processors being used by sql server ?Can you explain me about parallelism and affinity mask?

AlanBarber Ten Centuries Points: 1118 More actions · Answer 5

check what your parallelism settings are and keep an eye on what's running and check for heavy processes that are using parallelism. That has been the major contributor the high cpu with us.

We fought this issue for months and finally figured out the best was to set the parallelism cost from the default of 5 to 25 and the max degree of parallelism from 0 (use all cpus) to 2.

If you system is anything like ours is the 4 xeons you have, have hyperthreading turned on so to the OS and sql server it looks like you have 8 cpus. when things get heavy sql spawns out extra threads and since the default max degree is to use all procs it will create 8 threads. get a few heavy queries at once and you can see that it will just choke the server to death.

pgagnon Grasshopper Points: 13 More actions · Answer 6

Another possibility is that maybe the indexes need to be re-organized....

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 7

Along with the other suggestions, I'd also take the time to upgrade to at least Service Pack 2. From what I saw on my own machine, SP2 fixed a lot of problems.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL_DBA_3 SSCarpal Tunnel Points: 4772 More actions · Answer 8

Thanks for your posts.

Yes...we are upgrading to SP2 in couple of weeks.

We have index rebuild job running every week.If the tables are more fragmented ,performance should be same in all the time.But in only peak hours the cpu shows 100%.

run_value when i run sp_configure

affinity I/O mask - 0

affinity mask - 0

cost threshold for parallelism -5

max. degree of parallelism -0

Is there anything to change among these?Does it require a server restart after the change?

AlanBarber Ten Centuries Points: 1118 More actions · Answer 9

try setting the max dop to 2 to start and see what happens. then if that doesn't help try slowly adjusting the cost threshhold up a bit at a time to try to find that magic zone.

SQL_DBA_3 SSCarpal Tunnel Points: 4772 More actions · Answer 10

Thank you...

But this is a production server and I cannot keep testing on it.I need to take downtime for this which needs many people's approval.I need to give the statistics of what Iam doing .

So I have to be sure in my answers.Please ..any other exact solution which reduces the CPU Utilization?

AlanBarber Ten Centuries Points: 1118 More actions · Answer 11

changing the max dop does not require a restart and is an instant change.

I know it's not fun playing with a production box but it's really the only way. it's an instant change so set it to 2 and let it sit for 10-15 to stabalize and if things start to runs smoother you're on the right track. if not flip it back.

as I always like to say to my boss, if the db is running like junk i can't make it any worse by trying to fix it. 🙂