October 15, 2008 at 10:31 am
Go back and reread Gail's post. She shows you how to measure which procs are taking the longest & using up CPU. Once you identify the query or queries that are causing the problem, you examine them to see if they're doing RBAR processing or have multi-statement table valued functions or any other of the myriad poor choices that can be made in TSQL and then you begin to fix them. You can also look at the execution plans for the queries that are behaving badly & clean them up.
You said this happened suddenly. Have you made any changes to the systems or introduced new code or new sets of users recently? That could be part of the underlying cause.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 15, 2008 at 2:15 pm
One other thought....
If you query sys.dm_os_wait_stats, what are the waits with the highest wait times (say the top 20)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2008 at 4:26 pm
SOS_SCHEDULER_YIELD is the another prominent wait event that shows up...
Oraculum
October 15, 2008 at 4:34 pm
If you are experiencing the problem now, can you try this query and show us the results?
Select * From
(Select DateDiff(ss, Login_Time, Getdate()) as ConnectTime,
Cast( Cast(CPU as Float)/1000000 as Numeric(12,3)) as CpuSec,
Cast( (Cast(CPU as Float)/10000)
/ (DateDiff(ss, Login_Time, Getdate())) as numeric(10,6)) as [Cpu%],
*
From master..sysprocesses
--dont bother if connected less than 10 seconds:
Where DateDiff(ss, Login_Time, Getdate()) > 10
) P
Order By [CPU%] DESC
Thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 15, 2008 at 4:39 pm
will see if occurs again tomoz.. i did just find this post which sounds very very similar though ... will post tomoz when i am back at work! thanks
http://www.sqlservercentral.com/Forums/FindPost472093.aspx
Oraculum
October 15, 2008 at 5:59 pm
we had a similar problem just last week. Couldn't explain it and still can't. All I know is that we stopped SQL Server Agent and restarted it. POOF! All the problems disappeared. It's like the Agent was shell shocked or something and needed a heavy duty reminder of what it was supposed to be doing.
The problem hasn't happened since, but then again, it's only been a week.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2008 at 1:27 am
oraculum (10/15/2008)
SOS_SCHEDULER_YIELD is the another prominent wait event that shows up...
What others show hight wait times?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2008 at 3:47 am
here are the results from the two querys...
am wondering whether to restart sql agent....umm..
have also just noticed something has changed with my tempdb config (i have been off work for a few months!) did have 3 datafile with fixed initial size of 2.5 gb - this morning i noticed it was 8MB and growing all the time!
Oraculum
October 16, 2008 at 7:28 am
Whoa, CXPACKET is big.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 16, 2008 at 7:30 am
Just checking: Do you have MAXDOP set to 1 already? And Hyperthreading is turned off, right?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 16, 2008 at 7:38 am
Another question: is this a 64-bit 2xQuad Core server running SQL 2005?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 16, 2008 at 7:47 am
Well, if someone's shrunk your TempDB database down, it's certainly going to have an effect. Fix that and see if it makes a difference.
There's a lot of paralleling going on (CXPacket), and a lot of latch waits. Can you look in the sys.dm_exec_requests DMV and see what the resources are for the latch waits. I'm willing to bet they're TempDB pages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2008 at 7:51 am
If restarting the SQL Agent causes the problem to go away, your load is most likely coming from a scheduled SQL Server Agent job. When CPU is at 100% you should determine which jobs are running and selectively stop the non-critical jobs to determine which exact job is causing the problem.
Once you pinpoint the job, look at what the job is running. Perhaps you are running a backup in the middle of the day or performing some re-indexing during the middle of the day. Re-indexing/defragmentation would explain the tempdb utilization and high CPU.
October 16, 2008 at 8:29 am
MAXDOP = 0
Hyperthreading...i'm not sure.. how can i tell?
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Manufacturer Model No. of CPUs Processor
HP ProLiant DL580 G3 4 Intel(R) Xeon(TM) MP CPU 3.00GHz
(believe they are dual core)
Oraculum
October 16, 2008 at 8:34 am
Since you've got parallelism on, what's the threshold set to?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply