主页 / dba / 问题 / 208336
Accepted
Learning_DBAdmin
Asked: 2018-06-01 00:56:18 +0800 CST

监视 SQL Server 阻止的进程 [重复]

  • 772

我一直在使用sp_whoisactive，这对于查找运行会话和阻塞的详细信息非常有帮助。

我想检查是否有任何选项可以根据此过程安排作业，并且仅当数据库上存在活动阻塞并且阻塞保持超过可配置的分钟数时才提醒我。

此外，只有在达到超过可配置的数据库阻塞数时才应触发警报。

我使用了http://www.sqlserver-dba.com/2017/01/how-to-monitor-blocked-processes-with-sql-alert-and-email-sp_whoisactive-report.html提供的详细信息，但是它发送每次运行时都会发出警报，并且似乎没有检查阻塞情况。无论是只运行一个查询还是运行多个查询，无论是阻塞还是不阻塞，都发送电子邮件。不确定，如果我遗漏了什么。用于阻塞的 SQL 作业

sql-server sp-whoisactive

1 个回答

  1. Best Answer

    您链接到的脚本使用 SQL Server 的内置阻塞进程报告。它没有任何关于阻塞级别的配置选项——它只是按间隔（例如，每 5 秒）唤醒，检查阻塞，如果发现任何阻塞，则运行脚本。

    有一个阻塞进程阈值设置，但这只是确定阻塞进程报告运行的时间间隔——比如每 5 秒。（不要低于这个值。）

    如果您想构建自己的自定义阻塞进程报告并在其中放置关于发生多少阻塞以及在哪些数据库中的逻辑，这将是留给读者的练习。您可以从Darko 的构建自定义阻塞进程报告开始。

    根据我多年来的经验，我见过想要构建这类报告的人，然后他们要做的下一件事就是构建一个 Outlook 规则，将所有这些警报移动到一个文件夹中，然后他们阅读它们之后。如果您要这样做，请不要费心发送电子邮件。

    只需设置一个扩展事件会话来查找阻塞的进程和死锁 - 请注意，您需要更改服务器的文件位置和名称：

    CREATE EVENT SESSION [blocked_process] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name)) ,
ADD EVENT sqlserver.xml_deadlock_report (
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name))
ADD TARGET package0.asynchronous_file_target
(SET filename = N'c:\temp\XEventSessions\blocked_process.xel',
     metadatafile = N'c:\temp\XEventSessions\blocked_process.xem',
     max_file_size=(65536),
     max_rollover_files=5)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
GO

    启用 SQL Server 的阻塞进程报告：

    EXEC sp_configure ‘show advanced options’, 1 ;
GO
RECONFIGURE ;
GO
/* Enabled the blocked process report */
EXEC sp_configure 'blocked process threshold', '5';
RECONFIGURE
GO
/* Start the Extended Events session */
ALTER EVENT SESSION [blocked_process] ON SERVER
STATE = START;

    当你想检查阻塞时，这个查询会读取文件：

    WITH events_cte AS (
  SELECT
    xevents.event_data,
    DATEADD(mi,
    DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
    xevents.event_data.value(
      '(event/@timestamp)[1]', 'datetime2')) AS [event time] ,
    xevents.event_data.value(
      '(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)')
      AS [client app name],
    xevents.event_data.value(
      '(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)')
      AS [client host name],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="database_name"]/value)[1]', 'nvarchar(max)')
      AS [database name],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="database_id"]/value)[1]', 'int')
      AS [database_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="object_id"]/value)[1]', 'int')
      AS [object_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="index_id"]/value)[1]', 'int')
      AS [index_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="duration"]/value)[1]', 'bigint') / 1000
      AS [duration (ms)],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="lock_mode"]/text)[1]', 'varchar')
      AS [lock_mode],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="login_sid"]/value)[1]', 'int')
      AS [login_sid],
    xevents.event_data.query(
      '(event[@name="blocked_process_report"]/data[@name="blocked_process"]/value/blocked-process-report)[1]')
      AS blocked_process_report,
    xevents.event_data.query(
      '(event/data[@name="xml_report"]/value/deadlock)[1]')
      AS deadlock_graph
  FROM    sys.fn_xe_file_target_read_file
    ('C:\temp\XEventSessions\blocked_process*.xel',
     'C:\temp\XEventSessions\blocked_process*.xem',
     null, null)
    CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xevents
)
SELECT
  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
       THEN 'Deadlock'
       ELSE 'Blocked Process'
       END AS ReportType,
  [event time],
  CASE [client app name] WHEN '' THEN ' -- N/A -- '
                         ELSE [client app name]
                         END AS [client app _name],
  CASE [client host name] WHEN '' THEN ' -- N/A -- '
                          ELSE [client host name]
                          END AS [client host name],
  [database name],
  COALESCE(OBJECT_SCHEMA_NAME(object_id, database_id), ' -- N/A -- ') AS [schema],
  COALESCE(OBJECT_NAME(object_id, database_id), ' -- N/A -- ') AS [table],
  index_id,
  [duration (ms)],
  lock_mode,
  COALESCE(SUSER_NAME(login_sid), ' -- N/A -- ') AS username,
  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
       THEN deadlock_graph
       ELSE blocked_process_report
       END AS Report
FROM events_cte
ORDER BY [event time] DESC ;

    希望这有助于您入门。另外，仅供参考，我编辑了您的问题以删除有关 sp_WhoIsActive 属于我的部分 - 它不是。这是亚当·马哈尼奇的。如果你亲自见到他，你应该对他说声谢谢，并给他买他喜欢的饮料。

    • 6

相关问题