Sql Server死锁阻塞查询语句原创
28人赞赏了该文章
987次浏览
编辑于2023年10月13日 10:27:37
在处理星空提单时经常遇到各种Sql Server性能问题,这时经常要查询出Sql死锁或阻塞语句,客户在自行查询相关性能报告时可参照此帖子。注本文例举的所有Sql语句最初来至星空DBA或运维人员。
查询阻塞脚本Sql如下:
SELECT t1.resource_type AS [锁类型], DB_NAME(resource_database_id) AS [数据库名], t1.resource_associated_entity_id AS [阻塞资源对象], t1.resource_description as [资源描述信息], t1.request_mode AS [请求的锁], t1.request_session_id AS [等待会话], t2.wait_duration_ms AS [等待时间], ( SELECT [text] FROM sys.dm_exec_requests AS r WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) WHERE r.session_id = t1.request_session_id ) AS [等待会话执行的批SQL], ( SELECT SUBSTRING(qt.[text], r.statement_start_offset / 2, (CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 ELSE r.statement_end_offset END) / 2) FROM sys.dm_exec_requests AS r WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt WHERE r.session_id = t1.request_session_id ) AS [等待会话执行的SQL], t2.blocking_session_id AS [阻塞会话], ( 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 [阻塞会话执行的批SQL] FROM sys.dm_tran_locks AS t1 WITH (NOLOCK) INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK) ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE);
查询死锁信息语句(针对SQL Server 2008 R2):
SELECT XEventData.XEvent.value('@timestamp', 'datetime2(3)'), cast(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) FROM ( SELECT CAST (target_data AS XML) AS TargetData FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE [name] = 'system_health' AND st.target_name = N'ring_buffer' ) AS Data CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent) WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';
查询死锁信息语句(针对高版本SQL Server):
DECLARE @SessionName SysName SELECT @SessionName = 'system_health' IF OBJECT_ID('tempdb..#Events') IS NOT NULL BEGIN DROP TABLE #Events END DECLARE @Target_File NVarChar(1000), @Target_Dir NVarChar(1000), @Target_File_WildCard NVarChar(1000) SELECT @Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'NVARCHAR(256)') FROM sys.dm_xe_session_targets t INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address WHERE s.name = @SessionName AND t.target_name = 'event_file' SELECT @Target_Dir = LEFT(@Target_File, Len(@Target_File) - CHARINDEX('\', REVERSE(@Target_File))) SELECT @Target_File_WildCard = @Target_Dir + '\' + @SessionName + '_*.xel' --Keep this as a separate table because it's called twice in the next query. You don't want this running twice. SELECT DeadlockGraph = CAST(event_data AS XML), DeadlockID = Row_Number() OVER(ORDER BY file_name, file_offset) INTO #Events FROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F WHERE event_data like '<event name="xml_deadlock_report%' ;WITH Victims AS ( SELECT VictimID = Deadlock.Victims.value('@id', 'varchar(50)'), e.DeadlockID FROM #Events e CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/victim-list/victimProcess') as Deadlock(Victims) ) , DeadlockObjects AS ( SELECT DISTINCT e.DeadlockID, ObjectName = Deadlock.Resources.value('@objectname', 'nvarchar(256)') FROM #Events e CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/resource-list/*') as Deadlock(Resources) ) SELECT * FROM ( SELECT e.DeadlockID, TransactionTime = Deadlock.Process.value('@lasttranstarted', 'datetime'), DeadlockGraph, DeadlockObjects = substring((SELECT (', ' + o.ObjectName) FROM DeadlockObjects o WHERE o.DeadlockID = e.DeadlockID ORDER BY o.ObjectName FOR XML PATH ('')), 3, 4000), Victim = CASE WHEN v.VictimID IS NOT NULL THEN 1 ELSE 0 END, SPID = Deadlock.Process.value('@spid', 'int'), ProcedureName = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'), LockMode = Deadlock.Process.value('@lockMode', 'char(1)'), Code = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'), ClientApp = CASE LEFT(Deadlock.Process.value('@clientapp', 'varchar(100)'), 29) WHEN 'SQLAgent - TSQL JobStep (Job ' THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(Deadlock.Process.value('@clientapp', 'varchar(100)'), 32, 32) = (substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(Deadlock.Process.value('@clientapp', 'varchar(100)'), 67, len(Deadlock.Process.value('@clientapp', 'varchar(100)')) - 67) ELSE Deadlock.Process.value('@clientapp', 'varchar(100)') END, HostName = Deadlock.Process.value('@hostname', 'varchar(20)'), LoginName = Deadlock.Process.value('@loginname', 'varchar(20)'), InputBuffer = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)') FROM #Events e CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/process-list/process') as Deadlock(Process) LEFT JOIN Victims v ON v.DeadlockID = e.DeadlockID AND v.VictimID = Deadlock.Process.value('@id', 'varchar(50)') ) X ORDER BY DeadlockID DESC
查询慢语句:
SELECT top 10 s.[session_id], r.[start_time], DATEDIFF(SECOND,r.start_time,GETDATE()) AS elapsed_seconds, r.[status] AS RequestStatus, DB_NAME(r.database_id) AS DatabaseName, --r.[wait_type], --r.[wait_resource], --r.[wait_time], r.[reads], r.[writes], r.[logical_reads], r.cpu_time, r.total_elapsed_time, s.[status] AS SessionStatus, --s.[host_name], --s.[original_login_name], --s.[nt_user_name], --s.[program_name], --s.[client_interface_name], --c.[client_net_address], SUBSTRING (qt.text, r.statement_start_offset/2, (CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset) / 2) AS ExecutingSQL, qp.query_plan FROM sys.dm_exec_requests (nolock) r INNER JOIN sys.dm_exec_sessions (nolock) s ON r.session_id=s.session_id LEFT JOIN sys.dm_exec_connections (nolock) c ON c.session_id=s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) as qp
查询索引碎片信息/重建索引:
DBCC SHOWCONTIG(T_AR_RECEIVABLE) --碎片信息 DECLARE @DBCCStringNVARCHAR(1000) DECLARE @TableNameVARCHAR(100) DECLARE Cur_IndexCURSOR FOR SELECT Name AS TblName FROM sysobjects WHERE xType = 'U' AND (NAME LIKE '%T_SAL_ORDERENTRY_R%') AND (name NOT LIKE 'TMP%') ORDER BY TblName FOR READ ONLY OPEN Cur_Index FETCH NEXT FROM Cur_Index INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @DBCCString = 'DBCC DBREINDEX(@TblName,'''')WITH NO_INFOMSGS' EXEC SP_EXECUTESQL @DBCCString, N'@TblName VARCHAR(100)', @TableName PRINT '重建表' + @TableName + '的索引........OK!' FETCH NEXT FROM Cur_Index INTO @TableName END CLOSE Cur_Index DEALLOCATE Cur_Index PRINT '操作完成!' --2 更新统计信息 DECLARE @SqlStr1 NVARCHAR(MAX) SET @SqlStr1 = '' SELECT @SqlStr1 = @SqlStr1 + 'UPDATE STATISTICS '+ name + ';' FROM sysobjects WHERE xtype = 'U' AND (name LIKE 'T_SAL_ORDERENTRY_R%') AND (name NOT LIKE 'TMP%') EXEC (@SqlStr1)
推荐阅读