进程死锁运行状态、进程阻塞、死锁监控
捕获死锁:https://www.cnblogs.com/gered/p/9504791.html
实时死锁查看:
--死锁检测DMV
sys.dm_exec_requests
sys.dm_tran_locks
sys.dm_os_waiting_tasks
sys.dm_tran_database_transactions
sp_who
--DBCC INPUTBUFFER (spid) 查看spid中的t-sql
--是 sp_who 系统存储过程的输出中所显示的用户连接系统进程 ID (SPID)。
sp_lock
--死锁监控 SELECT t1.resource_type AS [信息锁定类型] , DB_NAME(resource_database_id) AS [数据库名] , t1.resource_associated_entity_id AS [锁定的ID] , OBJECT_NAME(resource_associated_entity_id) AS [锁定的对象] , t1.request_mode AS [等待者需求的锁定类型] , t1.request_session_id AS [等待者sid] , t2.wait_duration_ms AS [等待时间] , GETDATE() AS [StartTimeed] , ( SELECT text FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) WHERE r.session_id = t1.request_session_id ) AS [等待者要运行的批处理] , ( SELECT SUBSTRING(qt.text, r.statement_start_offset / 2 + 1, ( CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH(qt.text) ELSE r.statement_end_offset END - r.statement_start_offset ) / 2 + 1) FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt WHERE r.session_id = t1.request_session_id ) AS [等待者正要运行的语法] , t2.blocking_session_id AS [锁定者sid] , ( SELECT text FROM sys.sysprocesses AS p CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) WHERE p.spid = t2.blocking_session_id ) AS [锁定者的语法] FROM sys.dm_tran_locks AS t1 , sys.dm_os_waiting_tasks AS t2 WHERE t1.lock_owner_address = t2.resource_address IF EXISTS ( SELECT * FROM master.sys.sysprocesses WHERE spid IN ( SELECT blocked FROM master.sys.sysprocesses ) ) --确定有进程被其他的进程锁住 SELECT spid AS 进程 , status AS 状态 , 登入帐号 = SUBSTRING(SUSER_SNAME(sid), 1, 30) , 使用者机器名称 = SUBSTRING(hostname, 1, 12) , 是否被锁住 = CONVERT(CHAR(3), blocked) , 数据库名称 = SUBSTRING(DB_NAME(dbid), 1, 20) , cmd AS 命令 , waittype AS 等待型态 FROM master.sys.sysprocesses --列出锁住别人(在别的进程中blocked字段出现的值),但自己未被锁住(blocked=0) WHERE spid IN ( SELECT blocked FROM master.sys.sysprocesses ) AND blocked = 0 ELSE SELECT '没有进程被锁住'
--把死锁信息记录到错误日志 dbcc traceon(1222,1204,3605,-1)
--看查调用语句与父语句以及来源情况
SELECT [Spid] = session_id ,
start_time,
[Database] = DB_NAME(sp.dbid) ,
command,
[User] = nt_username ,
[Status] = er.status ,
[Wait] = wait_type ,
[Individual Query] = SUBSTRING(qt.text,
er.statement_start_offset / 2,
( 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) ,
[Parent Query] = qt.text ,
Program = program_name ,
hostname ,
nt_domain ,
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE session_id > 50 -- Ignore system spids.
AND session_id NOT IN ( @@SPID ) -- Ignore this current statement.
ORDER BY 1
--查看表锁 SELECT request_session_id spid,OBJECT_NAME(resource_associated_entity_id)tableName FROM sys.dm_tran_locks WHERE resource_type='OBJECT '
--查看进程状态 select start_time,command,percent_complete,wait_type,text, session_id,blocking_session_id from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle) s
--查看阻塞
DBCC INPUTBUFFER(76) 查看阻塞spid 对应的SQL
WITH temp ( BSID, SID, sql_handle ) AS ( SELECT blocking_session_id , session_id , sql_handle FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 UNION ALL SELECT A.blocking_session_id , A.session_id , A.sql_handle FROM sys.dm_exec_requests A JOIN temp B ON A.SESSION_ID = B.BSID ) SELECT C.BSID , C.SID , S.login_name , S.host_name , S.status , S.cpu_time , S.memory_usage , S.last_request_start_time , S.last_request_end_time , S.logical_reads , S.row_count , q.text FROM temp C JOIN sys.dm_exec_sessions S ON C.sid = s.session_id CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q ORDER BY sid
--找到死锁与阻塞的原因的方法: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_who_lock]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_who_lock] GO --说明 : 查看数据库里阻塞和死锁情况 use master go create procedure sp_who_lock as begin declare @spid int,@bl int, @intTransactionCountOnEntry int, @intRowcount int, @intCountProperties int, @intCounter int create table #tmp_lock_who ( id int identity(1,1), spid smallint, bl smallint) IF @@ERROR<>0 RETURN @@ERROR insert into #tmp_lock_who(spid,bl) select 0 ,blocked from (select * from sysprocesses where blocked>0 ) a where not exists(select * from (select * from sysprocesses where blocked>0 ) b where a.blocked=spid) union select spid,blocked from sysprocesses where blocked>0 IF @@ERROR<>0 RETURN @@ERROR -- 找到临时表的记录数 select @intCountProperties = Count(*),@intCounter = 1 from #tmp_lock_who IF @@ERROR<>0 RETURN @@ERROR if @intCountProperties=0 select '现在没有阻塞和死锁信息' as message -- 循环开始 while @intCounter <= @intCountProperties begin -- 取第一条记录 select @spid = spid,@bl = bl from #tmp_lock_who where Id = @intCounter begin if @spid =0 select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' else select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl ) end -- 循环指针下移 set @intCounter = @intCounter + 1 end drop table #tmp_lock_who return 0 end --需要的时候直接调用,就可以查出引起死锁的进程和SQL语句. exec sp_who_lock 发现问题后:就进行语句的优化,避免来再次出现上面现象。Profiler跟踪时主要是看:TextData,Applicationname,Username,Loginname,CpU,Read and write Duration(这个很重要),spid