November 13, 2008 at 6:33 am
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?
November 13, 2008 at 7:03 am
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."
November 13, 2008 at 7:12 am
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"
November 13, 2008 at 7:21 am
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
🙂
November 13, 2008 at 8:43 am
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?
November 14, 2008 at 7:23 am
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.
November 14, 2008 at 8:27 am
Another possibility is that maybe the indexes need to be re-organized....
November 14, 2008 at 8:42 am
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
Change is inevitable... Change for the better is not.
November 14, 2008 at 8:55 am
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?
November 14, 2008 at 8:59 am
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.
November 14, 2008 at 9:05 am
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?
November 14, 2008 at 9:10 am
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. 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply