October 15, 2008 at 4:29 am
Can anyone offer any advice to why over the last 1.5 days server is flatlining at 100% CPU..
I have tried to find out which query /session etc is hogging all cpus, but i am not getting much luck..the below i was told would help, but all seem to be within normal limits...
-- Check SQL Server Schedulers to see if they are waiting on CPU
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
scheduler_id current_tasks_count runnable_tasks_count
------------ ------------------- --------------------
0 14 6
1 14 7
2 9 5
3 14 8
SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)
,'%signal (cpu) waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
,resource_wait_time_ms=SUM(wait_time_ms - signal_wait_time_ms)
,'%resource waits'= CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
FROM sys.dm_os_wait_stats
signal_wait_time_ms %signal (cpu) waits resource_wait_time_ms %resource waits
-------------------- --------------------------------------- --------------------- ---------------------------------------
1084371806 7.04 14311003186 92.96
How can i see what is causing the CPU issue? the server overview report shows adhoc queries using 90% CPU..
Oraculum
October 15, 2008 at 5:13 am
Repeated Post
October 15, 2008 at 8:38 am
Please don't cross post. It just wastes people's time and fragments replies.
Replies to the following thread please
http://qa.sqlservercentral.com/Forums/Topic586071-360-1.aspx
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply