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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
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_countFROM sys.dm_os_schedulersWHERE 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 thisSELECT 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 |
 |
|
|