解决性能问题中SQL Server警报:SQL Server警报基础

为什么SQL Server警报很重要? (Why is SQL Server alerting important?)

要了解系统的状况以及SQL Server的性能,必须进行监视。 但是,仅监视可能还不够。 每当特定SQL Server计数器达到阈值或发生特定的事件时,都会收到有关性能问题的通知,从而提高了监视的可用性。 这些通知使DBA能够在发生每一次重要的系统性能变化时立即知道它们,因此它们可以立即采取行动来解决问题并防止进一步的问题

Alerts fired when a specific metric reaches the pre-defined threshold allows DBAs to work on other tasks and be sure that no potentially threatening SQL Server issue will be unnoticed. With automatic alerts in SQL Server, there is no need to sit by the monitor, watch the graphs and numbers, or run reports every 5 minutes. A properly set SQL Server alerting will notify you about all important issues

当特定指标达到预定义阈值时触发的警报使DBA可以执行其他任务,并确保不会引起潜在威胁SQL Server问题的注意。 借助SQL Server中的自动警报,无需坐在监视器旁​​,观察图表和数字或每5分钟运行一次报告。 正确设置SQL Server警报将通知您所有重要问题

There are two monitoring features available in SQL Server Management Studio – SQL Server Activity Monitor and Data Collection. Activity Monitor provides no flexibility for specifying the monitored metrics and for creating reports. Data Collection provides creating custom collection sets where you can specify the metrics you want to audit, as well as using custom reports, but these custom collections and reports require coding. Another native resource for monitoring SQL Server counters is the sys. dm_os_performance_counters dynamic management view. Neither of the listed features provides specifying a threshold value and creating alerts when a specific condition is met

SQL Server Management Studio提供了两种监视功能-SQL Server 活动监视器数据收集活动监视器不提供灵活性来指定受监视的指标和创建报告。 数据收集提供了创建自定义收集集的功能,您可以在其中指定要审核的指标以及使用自定义报告,但是这些自定义收集和报告需要进行编码。 另一个用于监视SQL Server计数器的本机资源是sys。 dm_os_performance_counters动态管理视图。 列出的功能均未提供指定阈值并在满足特定条件时创建警报

SQL Server警报功能 (The SQL Server alerting feature)

The alerting feature in SQL Server is brought by Alerts in SQL Server Agent. It provides the capability to set thresholds and create alerts for the SQL Server counters, send messages to a number of operators, launches a built-in or custom application, or execute specific tasks

SQL Server中的警报功能由SQL Server代理中的警报带来。 它提供了设置阈值和为SQL Server计数器创建警报,向多个操作员发送消息,启动内置或自定义应用程序或执行特定任务的功能。

General recommendations for using SQL Server Alerts:

有关使用SQL Server警报的一般建议:

  • Alerting can increase processing load, so be careful when configuring it on resource intensive servers

    警报会增加处理负荷,因此在资源密集型服务器上配置警报时要格外小心
  • Alerting can increase network traffic, especially when alerting for multiple SQL Server instances is managed on a single SQL Server instance

    警报会增加网络流量,尤其是在单个SQL Server实例上管理多个SQL Server实例的警报时
  • Make sure you have all prerequisites properly configured (Database mail and SQL Server Agent properties) before creating an alert, as you will be able to create alerts without any error messages even when the necessary options are not available

    创建警报之前,请确保已正确配置了所有先决条件(数据库邮件和SQL Server代理属性),因为即使没有必要的选项,您也可以创建没有任何错误消息的警报。

警报类型 (Alert types)

Alerts can be created for SQL Server events, SQL Server performance conditions, and WMI events

可以为SQL Server事件,SQL Server性能条件和WMI事件创建警报

An alert created for a specific SQL Server event can be triggered when a specific error occurs (defined by the error number), any error of a specific severity occurs (defined by the severity level), or the message logged by the event contains a specific text. For example, an alert can be fired if the event message contains a specific database object or user name. In this article, we’ll focus on alerts triggered by a specific SQL Server performance condition

当发生特定错误(由错误号定义),发生特定严重性的任何错误(由严重性级别定义)或事件记录的消息包含特定错误时,可以触发为特定SQL Server事件创建的警报。文本。 例如,如果事件消息包含特定的数据库对象或用户名,则可以触发警报。 在本文中,我们将重点介绍由特定SQL Server性能条件触发的警报

The counters available are the counters available in the sys. dm_os_performance_counters dynamic management view, the object and counter names used in SQL Server alerting dialogs are the same

可用的计数器是系统中可用的计数器。 dm_os_performance_counters动态管理视图,SQL Server警报对话框中使用的对象和计数器名称相同

SQL Server alerting properties - Performance condition alert definition

Some of the alerts are defined for the SQL Server instance, while others can be defined on all or specific databases only. For example, the memory counters alerts are created on an instance, while database counter alerts can be created on a database level, so you can set an alert to be fired only if the Production database data file size reaches 1 GB. If any other database data file reaches this threshold, no alerts will be fired

一些警报是为SQL Server实例定义的,而其他警报则只能在所有数据库或特定数据库上定义。 例如,内存计数器警报是在实例上创建的,而数据库计数器警报可以在数据库级别上创建的,因此您可以将警报设置为仅在生产数据库数据文件大小达到1 GB时才触发。 如果任何其他数据库数据文件达到此阈值,则不会触发任何警报

When the alert is fired, an existing or newly created SQL Server job can be executed. This provides a wide range of actions that can automate steps needed to mitigate or fix the event that fired the alert. Besides executing a SQL Server job, an alert can send a notification at the same time. The available notifications are emails, net send and pager messages. Note that the latter two will be removed from the future versions of SQL Server. If you want to send an email, Database Mail has to be configured. It’s highly recommended to test this feature first. If you configure an alert without an existing Database Mail profile, you will not be notified about it and therefore unaware that emails will not be sent

触发警报后,可以执行现有或新创建SQL Server作业。 这提供了广泛的操作,这些操作可以自动执行缓解或修复引发警报的事件所需的步骤。 除执行SQL Server作业外,警报还可以同时发送通知。 可用的通知是电子邮件,网络发送和寻呼消息。 请注意,后两个将从将来SQL Server版本中删除。 如果要发送电子邮件,则必须配置数据库邮件。 强烈建议先测试此功能。 如果您在没有现有数据库邮件配置文件的情况下配置警报,则不会收到有关该警报的通知,因此不会知道不会发送电子邮件

SQL Server alerting properties - configuring SQL Server job and notification on alert firing

The benefits of using SQL Server Alerts are that DBAs are seamlessly up to date with all important and critical events on the SQL Server instance. The advantages of SQL Server Alerts are that alerts can be easily made for multiple counters – a custom combination of counters and values is easy to create and modify, and no coding is required. The feature is available in SQL Server Management Studio, so no third party tools are necessary, executing any SQL Server Agent job is possible, which enables fixing common error conditions automatically, without any manual work

使用SQL Server Alerts的好处是,DBA与SQL Server实例上的所有重要事件和关键事件无缝地保持同步。 SQL Server Alerts的优点是可以轻松地为多个计数器发出警报-轻松创建和修改计数器和值的自定义组合,并且不需要编码。 该功能在SQL Server Management Studio中可用,因此不需要第三方工具,可以执行任何SQL Server代理作业,从而可以自动修复常见错误条件,而无需进行任何手动操作

In case of notification frequency improperly set, there might come to increased network traffic and significantly slow down the system response. Also, sending too many email notifications can clog your email. The server load can also be increased

如果通知频率设置不正确,则可能会增加网络流量,并显着降低系统响应速度。 另外,发送过多的电子邮件通知可能会阻塞您的电子邮件。 服务器负载也可以增加

Although the feature provides a good solution for being alerted when a SQL Server counter reaches a specific value, the alerts are limited to the counters available in SQL Server. System performance counters are not supported. Another disadvantage is a single point of failure. If alerting on multiple SQL Server instances is managed from a single point and SQL Server Agent, SQL Server instance, or the whole server fail for any reason, alerts will not be fired for any of the managed SQL Server instances

尽管此功能提供了一个很好的解决方案,可以在SQL Server计数器达到特定值时发出警报,但警报仅限于SQL Server中可用的计数器。 不支持系统性能计数器。 另一个缺点是单点故障。 如果从单个点管理多个SQL Server实例的警报,并且SQL Server代理,SQL Server实例或整个服务器由于任何原因失败,则不会为任何托管SQL Server实例触发警报

In the next part of this series, we will give recommendations and detailed steps for creating alerts

在本系列的下一部分中,我们将提供有关创建警报的建议和详细步骤

翻译自: https://www.sqlshack.com/sql-server-alerts-troubleshooting-performance-issues-sql-server-alerting-basics/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值