CPU 100% and critical

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » CPU 100% and critical

36 posts, Page 1 of 4

1 2 3 4 »»»

CPU 100% and critical

Rate Topic

Display Mode

Topic Options

Author

Message

oraculum

Posted Wednesday, October 15, 2008 4:34 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 03, 2012 6:35 AM
Points: 97, Visits: 501

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

Post #586071

vyas

Posted Wednesday, October 15, 2008 4:48 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:02 AM
Points: 3,131, Visits: 1,056

Check with dbcc opetran will give you any longest executing query.

Post #586075

Kishore.P

Posted Wednesday, October 15, 2008 5:21 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, November 26, 2012 3:25 AM
Points: 1,389, Visits: 596

use sp_who2 and dbcc commands, to know the issue.

Post #586090

Grant Fritchey

Posted Wednesday, October 15, 2008 6:19 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:50 AM
Points: 13,371, Visits: 25,143

Since your server is stressed, you'll want to be judicious about using this, but I'd suggest placing a trace on the server to capture the queries that are being called. Yes, doing spot checks with sp_who2 will likely show you which process is causing all the blocks, but that can change over time. Getting a good set of actual queries run against your server, the order they're called in, their cost in time, cpu and I/O... Knowing this information will better enable you to troubleshoot the root problems.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #586120

Steve Jones - SSC Editor

Posted Wednesday, October 15, 2008 7:13 AM

SSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 1:47 PM
Points: 31,406, Visits: 13,722

You could set a minimum duration in Profiler/Trace, grabbing everything that takes longer than say, 10 sec. That would help.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #586163

oraculum

Posted Wednesday, October 15, 2008 7:16 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 03, 2012 6:35 AM
Points: 97, Visits: 501

something even stranger... when i run a sql profiler as soon as i press 'run' on start trace the cpu on the server drops down to around 30-40% - but then all application are totally unusable. When i then pause or stop the trace the CPU instantly jumps back up to 100%...

any ideas?

Oraculum

Post #586169

Grant Fritchey

Posted Wednesday, October 15, 2008 8:29 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:50 AM
Points: 13,371, Visits: 25,143

Yikes. That doesn't sound good.

First, don't use Profiler on the production server. Instead, set up a server side trace. Second, use Steve's advice and put a filter on the trace so that it captures only events that are of a certain duration or use a certain amount of cpu or something else. Third, this is general advice for the trace anyway, minimize the data collected, only capture the events you need for this, usually statement complete and rpc complete are enough, and only collect the columns you need. Finally, again, general trace advice, output to a file, not to a table. Roll over the file every 50mb or so.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #586260

GilaMonster

Posted Wednesday, October 15, 2008 8:48 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 12:59 PM
Points: 37,640, Visits: 29,895

If you're having problems with trace, you can try querying the procedure cache. It should pick up the most expensive queries.

select total_worker_time/execution_count/1000 as AverageCPUTime, total_elapsed_time/execution_count/1000 as AvgDuration,  st.text
	from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) st
	where (total_worker_time/execution_count) > 10000000 -- 10 seconds in microseconds
	order by total_worker_time/execution_count

Change the where clause if you want to see queries that used less than 10 sec cpu time.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #586285

Grant Fritchey

Posted Wednesday, October 15, 2008 8:48 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:50 AM
Points: 13,371, Visits: 25,143

I'm so stupid...

This is 2005 right? Toss trace. Run a query against sys.dm_exec_query_stats. This is a dynamic management view that contains information about the queries currently in cache. You can get everything you need, real time, without having to use a trace (trace is still useful, but in this instance, it isn't).

Here's an example script to get you started, but you can look up the various DMV & DMF's used to get more:

SELECT sql_handle, plan_handle, execution_count

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text (qs.plan_handle) AS sql

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #586286

Grant Fritchey

Posted Wednesday, October 15, 2008 8:50 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:50 AM
Points: 13,371, Visits: 25,143

OK. That's twice. It should stop now.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #586290

« Prev Topic | Next Topic »

36 posts, Page 1 of 4

1 2 3 4 »»»

Permissions