July 7, 2009 at 9:48 am
I am using the following query to help identify causes of high CPU on my SQL server:
SELECT r.session_id, status, SUBSTRING(qt.text,r.statement_start_offset/2,
(CASE
WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2),
qt.dbid, db_name(qt.dbid), qt.objectid, '',
r.cpu_time, r.total_elapsed_time, r.reads, r.writes, r.logical_reads
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
WHERE r.session_id > 50
AND qt.dbid > 1
Quite often, the Resource DB shows 'Suspended'. Can anyone tell me why that is?
July 9, 2009 at 12:25 pm
Suspended = The session is waiting for an event, such as I/O, to complete.
http://msdn.microsoft.com/en-us/library/ms179881.aspx
MJ
July 22, 2009 at 2:20 pm
I know what 'suspended' means, but the Resource DB is almost constantly showing this status. I have 2 production servers, but this only happens on one of them. What I'm trying to find out is what could cause the Resource DB to show this status so often.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply