监控SQL Server正在执行的SQL语句和死锁情况

最新推荐文章于 2024-05-13 17:36:43 发布

_bujinton

最新推荐文章于 2024-05-13 17:36:43 发布

阅读量236

点赞数

本文链接：https://blog.csdn.net/qq_26709523/article/details/118297902

版权

SELECT [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,
   [Spid] = session_Id,
   ecid,
   [Database] = DB_NAME(sp.dbid),
   [User] = nt_username,
   [Status] = er.STATUS,
   [Wait] = wait_type,
   Program = program_name,
   Hostname,
   nt_domain,
   start_time
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)

--每秒死锁数量
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Number of Deadlocksc%';

--查询当前阻塞
WITH CTE_SID(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 CTE_SID 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 CTE_SID 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

--检查表的更新排他锁
DECLARE @t_lock AS TABLE (
   [spid] [smallint] NULL,
   [dbid] [smallint] NOT NULL,
   [ObjId] [int] NOT NULL,
   [IndId] [smallint] NOT NULL,
   [Type] [nvarchar](4) NULL,
   [Resource] [nvarchar](32) NULL,
   [Mode] [nvarchar](8) NULL,
   [Status] [nvarchar](5) NULL
   )

INSERT INTO @t_lock
EXEC sp_lock

SELECT *,
   [Database] = DB_NAME([dbid]),
   [Object] = OBJECT_NAME([ObjId], [dbid])
FROM @t_lock
WHERE [spid] > 50 /* Ignore system spids.*/
   AND [spid] NOT IN (@@SPID)
   AND [Type] = 'TAB'
   AND [Mode] IN (
       'U',
       'IU',
       'SIU',
       'UIX',
       'BU',
       'RangeS_U',
       'RangeI_U',
       'X',
       'IX',
       'SIX',
       'UIX',
       'RangeI_X',
       'RangeX_S',
       'RangeX_U',
       'RangeX_X'
       )