Error: New queries assigned to process on Node 0 have not been picked up by a worker
thread in the last 60 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time.
thread in the last 60 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time.
2 engine thread s failed to stop after 30 seconds of waiting
To
find the deadlock
Exec XP_ReadErrorLog 0, 1, 'deadlock'
To
check the dump file
SELECT * FROM
sys.dm_server_memory_dumps
Difference between deadlock
scheduler and Deadlock (lock manager)
Deadlock (lock manager) defines
process being blocked by each other’s as a chains.
Deadlock scheduler is no thread
available available for the new request since all the threads are being
assigned to wait for the lock type. I.e. no more threads available.
If the deadlock schedulers
happens on Numa on one node where as other node will just work fine. However if
that happens on single node as all the worker threads are exhausted and unable
to assign the worker threads for the new request.
Mostly it could happen due to
blocking queries or queries holds the lock.
The below query is used to find
out how many new request task been in queue against the current worker threads.
SELECT
scheduler_id,
cpu_id,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255;
Scheduler_id =255 would be equal
to DAC connection or any of them below <=255 mostly relates to the user
request.
Note: All
schedulers that are used to run regular queries have ID numbers less than
1048576. Those schedulers that have IDs greater than or equal to 1048576 are used
internally by SQL Server, such as the dedicated administrator connection
scheduler. Is not null able.
runnable_tasks_count:
Number of workers, with tasks assigned to them that are waiting to be scheduled
on the runnable queue. Is not null able.
Lower
runnable count implies that multiple tasks are waiting for a resource.
current_workers_count: Number of workers assigned to the
task.
work_queue_count: Number of tasks tasks are waiting for an assignment to a worker
https://msdn.microsoft.com/en-gb/library/ms177526.aspx
To find out what happened during sql
server restart or what has caused sql to go offline that has contributed with
deadlock schedulers.
SELECT [dd hh:mm:ss.mss]
,[session_id]
,[sql_text]
,[login_name]
,[wait_info]
,[tran_log_writes]
,[CPU]
,[start_time]
,[login_time]
,[request_id]
,[collection_time]
FROM [DBA].[dbo].[WhoIsActive_20160427]
where login_time between '2016-04-27
08:10:01.407' and '2016-04-27 08:18:01.407'
and wait_info like '%lck%' and login_name='user-id'
and database_name='dbname'
order by [dd hh:mm:ss.mss] desc
No comments:
Post a Comment