【转】SQL SERVER死锁查询,死锁分析,解锁,查询占用
简单点的处理方法:
1、查询死锁的表
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT'
2、解锁
declare @spid int Set @spid = 79 --锁表进程 declare @sql varchar(1000) set @sql='kill '+cast(@spid as varchar) exec(@sql)
专业点的处理方法:
1.查询死锁的表:
SELECT request_session_id spid, OBJECT_NAME( resource_associated_entity_id ) tableName FROM sys.dm_tran_locks WHERE resource_type = 'OBJECT'
2.分析被锁死的原因:
select t1.resource_type [资源锁定类型] , DB_NAME(resource_database_id) as 数据库名 , t1.resource_associated_entity_id 锁定对象 , t1.request_mode as 等待者请求的锁定模式 , t1.request_session_id 等待者SID , t2.wait_duration_ms 等待时间 , (select TEXT from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle) where r.session_id = t1.request_session_id) as 等待者要执行的SQL , t2.blocking_session_id [锁定者SID] , (select TEXT from sys.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) where p.spid = t2.blocking_session_id ) 锁定者执行语句 from sys.dm_tran_locks t1, sys.dm_os_waiting_tasks t2 where t1.lock_owner_address = t2.resource_address
3.解锁:
create Proc Sp_KillAllProcessInDB @DbName VarChar(100) as if db_id(@DbName) = Null begin Print 'DataBase dose not Exist' end else Begin Declare @spId Varchar(30) DECLARE TmpCursor CURSOR FOR Select 'Kill ' + convert(Varchar, spid) as spId from master..SysProcesses where db_Name(dbID) = @DbName and spId <> @@SpId and dbID <> 0 OPEN TmpCursor FETCH NEXT FROM TmpCursor INTO @spId WHILE @@FETCH_STATUS = 0 BEGIN Exec (@spId) FETCH NEXT FROM TmpCursor INTO @spId END CLOSE TmpCursor DEALLOCATE TmpCursor end
4、查询SQL占用资源情况:
SELECT TOP 20 total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数], qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)], last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)], SUBSTRING(qt.text,qs.statement_start_offset/2+1, (CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) AS [使用CPU的语法], qt.text [完整语法], dbname=db_name(qt.dbid), object_name(qt.objectid,qt.dbid) ObjectName FROM sys.dm_exec_query_stats qs WITH(nolock) CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE execution_count>1 ORDER BY total_worker_time DESC