question

pits avatar image
pits 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
performancecpu
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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)
0 Likes 0 ·
sp_lock avatar image
sp_lock answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SumitRana avatar image
SumitRana answered
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)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.