09 - Datadog Alerting

7 min read

Datadog Alerting

Quote

“Nines don’t matter if users aren’t happy.”

— Charity Majors, charity.wtf (2019)

SQL Server DBA Monitors

Deadlock Alert Monitor

The most critical SQL Server monitor — triggers an email alert whenever a deadlock occurs.

Deadlock Alert Monitor Setup

  1. Go to Monitors > New Monitor > Metric
  2. Detection method: Change Alert
  3. Metric: sqlserver.deadlocks.total
  4. Alert condition: change over last 5 minutes is above 0
  5. Title: “SQL Server Deadlock Detected”
  6. Message:
{{#is_alert}}
Deadlock detected on {{host.name}}.

1. Check active locks: SELECT * FROM sys.dm_tran_locks WHERE request_status = 'WAIT'
2. Review Extended Events deadlock graph
3. Identify conflicting queries and fix access order
{{/is_alert}}

{{#is_recovery}}
Deadlock alert resolved on {{host.name}}.
{{/is_recovery}}

Notify: @oncall-team@example.com
  1. Tags: env:prod, service:data-pipeline-sql
  2. Click Create

The monitor triggers immediately when a deadlock occurs and sends an email with remediation steps. It auto-resolves when no new deadlocks are detected in the evaluation window. When a P1 or P2 alert fires, follow the response procedures in the on call guide to ensure consistent triage and escalation.

Testing the Deadlock Alert

To verify the full pipeline (deadlock → metric → monitor → email):

  1. Create a deadlock using the procedure in the SQL Server Tuning Guide, Section 5.8
  2. Wait 15-30 seconds for the Datadog agent to collect the updated counter
  3. The monitor should trigger and send an email within 1-2 minutes
  4. The Deadlock Count widget on the SQL Server DBA dashboard should increment

Why change alert

sqlserver.deadlocks.total is a monotonic_count — it resets to 0 each collection cycle when no new deadlocks occur. A Change Alert detects when the value increases from 0, which is more reliable than a threshold alert for delta-based metrics.

Monitor evaluation delay

Datadog evaluates monitors on a fixed interval (typically 60 seconds). A deadlock that occurs and resolves within one evaluation cycle may never trigger the alert. For critical monitors, set the evaluation window to the smallest supported interval and consider enabling require_full_window: false so partial data triggers the alert rather than waiting for a full window.

Minimize Missed Deadlock Alerts

Set the monitor’s evaluation window to last 1 minute (the minimum), enable require_full_window: false in the monitor’s advanced settings, and use a Change Alert on sqlserver.deadlocks.total rather than a threshold — this fires as soon as the counter increments, even within a single collection cycle.

Recovery notification flood

When a monitor recovers, Datadog sends a recovery notification to all channels. If a flapping metric (e.g., scheduler heartbeat on a slow VM) triggers and recovers repeatedly, the on-call engineer receives dozens of notifications. Use notify_no_data: true with no_data_timeframe: 10 (minutes) instead of a tight threshold to reduce noise for heartbeat-style monitors.

Suppress Flapping Notifications

For heartbeat-style monitors, set Alert condition to “no data for 5 minutes” rather than a threshold, enable notify_no_data: true with no_data_timeframe: 10, and add a renotification interval of at least 30 minutes. This ensures a single alert per incident rather than a flood for each flap cycle.

Airflow Orchestration Monitors

Create these in Monitors → New Monitor → Metric:

MonitorMetricConditionSeverity
Scheduler Downairflow.scheduler_heartbeatNo data for 5 minP1 (Critical) — see airflow scheduler down
DAG Parse Errorairflow.dag_processing.import_errors> 0 for 5 minP2 (High)
Task Failureairflow.ti.finish.*.failed> 0 within 15 minP2 (High)
Pool Starvationairflow.pool.starving_tasks.default_pool> 0 for 10 minP3 (Medium)
DAG Duration Anomalyairflow.dagrun.duration.success.pipeline_pulse.avg> 2x baseline for 3 consecutive runsP3 (Medium)
Queued Duration Spikeairflow.dag.*.queued_duration.95percentile> 60,000,000 (60s)P4 (Low)
Triggerer Downairflow.triggerer_heartbeatNo data for 5 minP3 (Medium)

Airflow Monitor Priority Guide

PriorityUse ForResponse Time
P1 (Critical)Scheduler down — pipeline is completely blocked (see also sql server disk full for disk-related critical alerts)Immediate
P2 (High)DAG parse errors, task failures — pipeline quality at riskWithin 30 min
P3 (Medium)Pool starvation, triggerer down, duration anomalyWithin 2 hours
P4 (Low)Queued duration spikes — performance degradation onlyNext business day

Airflow Duration Units

Airflow emits durations in microseconds. The Queued Duration Spike threshold of 60,000,000 equals 60 seconds. Adjust based on your typical DAG run times from the Airflow dashboard.

Dashboard Conditional Formatting

In addition to monitors (which send notifications), the SQL Server DBA dashboard uses conditional formatting on Query Value widgets for at-a-glance status:

Deadlock Count Widget

ConditionBackground
value = 0Green
value > 0Red

Buffer Cache Hit Ratio Widget

ConditionBackground
value > 99Green
value > 95Yellow
value ≤ 95Red

GCE Host Automuting in Datadog

When GCE Automuting is enabled in the GCP Integration, monitors are automatically muted when a VM is stopped in GCP. This prevents false alerts during intentional maintenance or off-hours cost reduction when the SQL or Airflow VM is stopped.

Enable in: Integrations > Google Cloud Platform > Edit > GCE Automuting = ON

Graph View

Backlinks