monitoring when cpu goes high - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

monitoring when cpu goes high

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-03-28 : 08:37:16

I have a computer that is used just for sql server
it is sometimes spiking the cpu to 100% though usually is much lower - how can i monitor it to find out which query is causing it to go to 100% (as when it does it causes other queries to fail)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-28 : 12:26:33

See what queries have a very high CPU value. You've likely got missing indexes causing the high CPU.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-03-28 : 12:32:14

how can I see which queries have a high CPU value - that's what I want to get a list of queries so that I can optimize them.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-28 : 12:33:49

Don't you have a CPU column in your trace?

SELECT TOP 1000 * FROM Blah ORDER BY CPU DESC

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-03-28 : 12:46:16

so the best way is to save a complete trace and then evaluate that way?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-28 : 12:57:51

Yes. Make sure you save it to a trace file and not to a trace table. After you are done with the trace, you can then import it as a trace table for queries. If you save directly to a trace table from Profiler, you can cause severe performance issues. Trace files are recommended instead, and better yet use a server-side trace instead of Profiler.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-03-29 : 02:15:30

better yet use a server-side trace instead of Profiler.

what is a server side trace?

is there anyway I can know at what point the cpu was 100% what the trace was?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-03-29 : 05:30:36

what's considered a high number for cpu usage?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-03-29 : 05:45:04

if i am finding a specific query is an issue but it's a query that needs to run - can I specify to that specific query how much of the cpu it could use? I would rather that query take longer then it make other quick queries time out

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-29 : 11:33:16

You can look into the resource governor.

What's a high CPU is dependent upon your environment. You need to know what your baseline is in order to know what's abnormal. You have to answer that. All we can say is that 90%+ is considered a hardware bottleneck if that's your baseline.

A query that causes high CPU utilization typically is missing indexes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-03-29 : 11:49:49

I have a query that parses an xml file and checks a bunch of tables in the database. -- this is spiking the cpu up to 100% - usually it's much less.

but i need this stored procedure

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-29 : 13:40:01

Well you'll need to thoroughly analyze it to see what can be improved.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-03-31 : 05:01:27

Measure CPU Pressure
(CPU pressure is a state wherein the CPU is fully occupied with currently assigned tasks
and there are more tasks in the queue that have not yet started.)

SELECT scheduler_id,
cpu_id,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count,
pending_disk_io_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255 ;

--------------------------
http://connectsql.blogspot.com/

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-03-31 : 05:16:46

so i run this when it peaks?
how does it help me point to the culprit?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-03-31 : 05:23:40

yeah use it to detect CPU bottelneck. if work_queue_count contains values greater then 0 continously then its sure then CPU bottelneck creating in delayed query response.

--------------------------
http://connectsql.blogspot.com/

fanzhouqi
Starting Member

4 Posts

Posted - 2011-04-01 : 03:57:46

try this
SELECT TOP 10 total_worker_time/execution_count AS avg_cpu_cost, plan_handle,execution_count, (SELECT SUBSTRING(text,statement_start_offset/2+1, (CASE WHEN statement_end_offset=-1 THEN LEN(CONVERT(NVARCHAR(max),text))*2 ELSE statement_end_offset END -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle) ) AS query_textFROM sys.dm_exec_query_stats ORDER BY [avg_cpu_cost] DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources