January 28, 2016 at 10:35 am
There are a few times I saw queries that the status is RUNNABLE, but CPUTime and DiskIO remain unchanged. There is no blocking, and the LastBatch is several hours ago. I killed the process without further investigate.
Does anyone know what could cause this?
Thanks,
Lijun
January 28, 2016 at 12:10 pm
A session can be held for an extended period of time in a wait state without being blocked by another session. For example, it could be a complex and long running query doing cxpacket exchanges. I'd suggest something like the below which uses sys.dm_exec_requests to get current statement text, last wait type, and last wait time for all sessions.
SELECT
er.session_id
, er.request_id
, DB_NAME(er.database_id) AS database_name
, er.command
, substring(char(13)+SUBSTRING (qt.text,(er.statement_start_offset/2) + 1,
((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1),1,8000) AS statement_text
, er.blocking_session_id
, er.start_time
, convert(varchar,case when getdate() <= er.start_time then 0 else getdate()-er.start_time end,108)elapsed_hhmmss
, er.last_wait_type
, convert(varchar,dateadd(ms,er.wait_time,getdate())-getdate(),114)last_wait_hhmmsssss
, er.row_count
, er.percent_complete
, case er.transaction_isolation_level
when 0 then 'Unspecified'
when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted'
when 3 then 'Repeatable'
when 4 then 'Serializable'
when 5 then 'Snapshot'
else ''
end isolation_level
, er.open_transaction_count
, cast((er.reads * 8192.0) / (1024.0 * 1024.0) as int) reads_mb
, cast((er.writes * 8192.0) / (1024.0 * 1024.0) as int) writes_mb
, cast((er.granted_query_memory * 8192.0) / (1024.0 * 1024.0) as int) memory_mb
, convert(varchar,dateadd(ms,er.cpu_time,getdate())-getdate(),114) cpu_hhmmsssss
from sys.dm_exec_requests AS er
outer apply sys.dm_exec_sql_text( er.sql_handle) AS qt
where er.session_id >= 50
order by er.start_time asc;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply