November 8, 2014 at 11:50 am
I ran sp_who and found no sessions in running state, only runnable, suspended or sleeping? My question is how its possible? How to find what is holding sessions in runnable state?
Thank you,
Marina
November 8, 2014 at 10:30 pm
Runnable = waiting for a slot on the CPU. Suspended = waiting for a resource. Sleeping = have nothing to do.
High CPU usage outside of SQL?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2014 at 3:41 am
I see
Reporting Service 16 %
Sqlsrv 59 %
System Idle 13%
What I don't get, if no running processes why sqlsrv CPU use is 59/%
Thanks for your reply.
November 9, 2014 at 11:11 am
What I am missing?
I thought that status from sys.dm_exec_requests should match to status from sp_who2, and they are not?
SELECT
a.scheduler_id ,
b.session_id,
c.status,
start_time,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
cpu_time,
blocking_session_id,
wait_type,
wait_time,
wait_resource,
open_transaction_count
FROM sys.dm_os_schedulers a
INNER JOIN sys.dm_os_tasks b on a.active_worker_address = b.worker_address
INNER JOIN sys.dm_exec_requests c on b.task_address = c.task_address
CROSS APPLY sys.dm_exec_sql_text(c.sql_handle) AS s2
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply