February 20, 2009 at 3:08 am
Hi all,
One of my SQL Servers stuck due to performance problems. I then checked out what was going on using the Activity Monitor. I found a long list of sleeping and dormant processes. How can I deal with this list? Kill or what to do if this stays on like this for sometime.
Specifically what to do with sleeping and dormant ones.
Thanks for your hint.
Niyala
February 20, 2009 at 7:42 am
Nothing. Sleeping & dormant processes are ones that are still working. The real thing to look for is blocked or blocking processes. If none of those, then you need to drill down more. See which of the processes, other than system processes, has been running the longest and see if something is up with that. Or, try gathering wait state information to see what the processes inside your system are waiting on. Here's a good article from MS to get you started.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 19, 2009 at 8:21 am
I have the same problem, but the machines/code has been closed for hours and the server still has the ProcessID in a Sleeping mode. How can I clear these. After 2-3 hours they are released, but it is killing the user threads
March 19, 2009 at 8:39 am
Use this query to check the TOP10 waits
SELECT TOP 10
wait_type,
waiting_tasks_count AS tasks,
wait_time_ms,
max_wait_time_ms AS max_wait,
signal_wait_time_ms AS signal
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
And this one to check which are holding locks and blocked:
SELECT
t1.resource_type,
'database' = DB_NAME(resource_database_id),
'blk object' = t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id,
t2.wait_duration_ms,
(SELECT SUBSTRING(text, t3.statement_start_offset/2 + 1,
(CASE WHEN t3.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE t3.statement_end_offset
END - t3.statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
t2.resource_description
FROM
sys.dm_tran_locks AS t1,
sys.dm_os_waiting_tasks AS t2,
sys.dm_exec_requests AS t3
WHERE
t1.lock_owner_address = t2.resource_address AND
t1.request_request_id = t3.request_id AND
t2.session_id = t3.session_id
March 19, 2009 at 8:40 am
By the way these codes are from Kalen daleney's Inside SQL server, I ahve used them and they are quite handy.
March 19, 2009 at 8:53 am
Thanks, I'll give them a try. Thanks for the quick response
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply