Not assigned to a worker thread took up node 0 the process on the new query, query is blocked or run for a long period of time will lead to appear this situation, and could extend the client response time, please use the "maximum number of worker threads (Max worker threads)" configuration option to increase to allow the number of threads, or query optimization is currently running,

SQL, even not on service didn't stop, don't know what the specific reason, check the log, prompt the above question, could you tell me what's the problem?

CodePudding user response:

A deadlock occurs, the track or check the lock

CodePudding user response:

 -=============================================
Author: yng 
- the Create date: 2014-11-18 
- Description: congestion warning 
-=============================================
CREATE PROCEDURE [dbo] [Proc_DBA_BlockingWarning] 
@ BlockingWarning INT=100 -- the session number is greater than the @ BlockingWarning of blocked warning 
AS 
The BEGIN 
SET NOCOUNT ON 
- 1. The table variables and will block and block of data in a table variable 
DECLARE @ t TABLE (
SPID SMALLINT, 
DBName NVARCHAR (128), 
Remark NCHAR (3), 
[ProgramName] nchar (128), 
[LoginName] nchar (128), 
The HostName nchar (128), 
[Status] nchar (30), 
BlockedBy SMALLINT, 
LoginTime DATETIME, 
QUERY nvarchar (Max) 
) 
INSERT INTO @ t (
SPID, 
DBName, 
Remark, 
[ProgramName], 
[LoginName], 
The HostName, 
[Status], 
BlockedBy, 
LoginTime, 
QUERY 
) 
SELECT 
SPID=p. pid, 
DBName=the convert (VARCHAR (20), d.n ame), 
Remark=CASE WHEN p.b locked> 0 THEN 'blocked' else 'blocking the source end, 
ProgramName=program_name, 
LoginName=the convert (CHAR (20), l.n ame), 
The HostName=the convert (CHAR (20), the HostName), 
Status=p. tatus, 
BlockedBy=p.b locked, 
LoginTime=login_time, 
QUERY=TEXT 
FROM the MASTER. The dbo. Sysprocesses p 
LEFT the JOIN MASTER. The dbo. Sysdatabases d 
ON conviction yourself bid=d.d bid 
LEFT the JOIN MASTER. The dbo. Syslogins l 
ON p. id=l.s id 
CROSS the APPLY sys. Dm_exec_sql_text (sql_handle) 
The WHERE (p.b locked=0 
AND the EXISTS (SELECT 1 
FROM the MASTER. The dbo. Sysprocesses p1 
WHERE p1. Blocked=p. pid)) 
OR (p.b locked> 0) 

- 2. If the number of blocked is greater than the set parameters, 
DECLARE @ Warning INT 
IF (SELECT COUNT (1) the FROM @ t WHERE BlockedBy> 0) & gt;=@ BlockingWarning 
The BEGIN 
The SET @ Warning=1 
END 
The ELSE 
The BEGIN 
The SET @ Warning=0 
END 

SELECT @ Warning AS Warning, 
SPID, 
DBName, 
Remark, 
[ProgramName], 
[LoginName], 
The HostName, 
[Status], 
BlockedBy, 
LoginTime, 
QUERY 
The FROM @ t 
SET NOCOUNT OFF 
END 
GO 

Performs a see,

CodePudding user response:

View a situation and cause of deadlock specific
Then take out
The create proc _proc_ view the deadlock and IP
As
- 1. New stored procedure
- create proc prtest
- @ spid int
- as
- DBCC inputbuffer (@ spid)
- go
- 2. Save the results into a temporary variable # TMP
SELECT the Session ID AS the Session ID,
[Login] AS the user name,
The Database AS the Database,
[Task State] AS State,
The Command AS a Command,
Application AS Application software,
[Wait Time (ms)] AS waiting Time,
[Wait Type] AS waiting Type,
Host Name AS the client Name,
[.net Address] AS the IP Address INTO # TMP

The FROM (SELECT [] the Session ID=s.s ession_id,
[the User Process]=the CONVERT (CHAR (1), s.i s_user_process),
[Login]=s.l ogin_name,
[the Database]=ISNULL (DB_NAME (conviction yourself the bid), N '),
[Task State]=ISNULL (t.t ask_state, N '),
[Command]=ISNULL (r.com mand, N '),
[Application]=ISNULL (supachai panitchpakdi rogram_name, N '),
[Wait Time (ms)]=ISNULL (w.w ait_duration_ms, 0),
[Wait Type]=ISNULL (w.w ait_type, N '),
[Wait Resource]=ISNULL (w.r esource_description, N '),
[Blocked By]=ISNULL (CONVERT (VARCHAR, w.b locking_session_id),
"'),
[Head Blocker]=CASE
The WHEN r2. Session_id IS NOT NULL
AND (r. locking_session_id=0
The OR r.s ession_id IS NULL
) THEN '1'
The ELSE '
END,
[Total CPU (ms)]=s.c pu_time,
[Total Physical I/O (MB)]=(s.r eads + s. rites) * 8
/1024,
[the Memory Use (KB)]=s.m emory_usage * 8192/1024,
[Open the Transactions]=ISNULL (r.o pen_transaction_count, 0),
[the Login Time]=s.l ogin_time,
[the Last Request Start Time]=s.l ast_request_start_time,
[Host Name]=ISNULL (Sheldon horowitz ost_name, N '),
[the.net Address]=ISNULL (Arthur c. lient_net_address, N '),
[Execution Context ID]=ISNULL (t.e xec_context_id, 0),
[Request ID]=ISNULL (r.r equest_id, 0),
[Workload Group]=ISNULL (g.n ame, N ')
The FROM sys. Dm_exec_sessions s
The LEFT OUTER JOIN sys. Dm_exec_connections ON c (s.s ession_id c.s. ession_id)=
ON the LEFT OUTER JOIN sys. Dm_exec_requests r (s.s ession_id=r.s ession_id)
ON the LEFT OUTER JOIN sys. Dm_os_tasks t (r.s ession_id=t.s ession_id
AND r.r equest_id=t.r equest_id
)
The LEFT OUTER JOIN (
SELECT *,
ROW_NUMBER () OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
The FROM sys. Dm_os_waiting_tasks
ON) w (t.t ask_address=w.w aiting_task_address)
AND w.r ow_num=1
The LEFT OUTER JOIN sys. Dm_exec_requests r2 ON (s.s ession_id=r2. Blocking_session_id)
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull