asked
cpu utilization
Good Morning , From task manager noticed CPU utilization for sqlserver.exe is 90%.We are using sql server 2008. How to understand which sql process is occupying that 90% of CPU volume? is there any query ? command? dmv? on which database it can be run to understand 90% of CPU utilized by which processess? Can you please advise Thank you
I use the query below in relation to using [Process explorer][1]. SELECT r.session_id, st.text, qp.query_plan, r.status, os_thread_id FROM sys.dm_os_threads AS ot JOIN sys.dm_os_tasks AS t ON t.worker_address = ot.worker_address JOIN sys.dm_exec_requests AS r ON t.session_id = r.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp A video from Jonathan that will walk you through it is [here][2] (saved me a few times - thanks!) I work with 2 monitors, so that you can have PE in one windows showing the tread that is consuming high CPU and the query in the other windows with the related SQL. [1]:
http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx [2]:
http://www.sqlskills.com/InsiderContent/201204/20120430Newsletter.pdf
You can use this DMV :) SELECT TOP 10 (total_worker_time * 1.0) / 1000000 AS CPU_Utilized_in_Seconds, text,* FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle)
9 People are following this question.
