Database Watcher and Extended Events | Guided by A Guide to Cloud

Two monitoring powerhouses

Simple explanation

Database watcher is like a fitness tracker for your database — it continuously monitors vitals (CPU, queries, waits) and shows trends over time on a dashboard. Great for seeing the big picture.

Extended Events is like a body scanner — you tell it exactly what to look for (specific queries, deadlocks, errors), and it captures detailed diagnostic data. Great for diagnosing specific problems.

Use watcher for “is my database healthy?” Use Extended Events for “why is this specific query slow?”

Database watcher

What it monitors

Resource utilisation — CPU, memory, I/O, storage
Query performance — top queries by duration, CPU, logical reads
Wait statistics — what queries are waiting on
Session activity — active sessions, blocking
Index usage — missing indexes, unused indexes

How it works

Create a watcher resource in the Azure portal
Add targets — the SQL databases or Managed Instances to monitor
Data flows to an Azure Data Explorer cluster or free cluster
Pre-built Grafana-style dashboards in the Azure portal
Write custom KQL queries for deep analysis

Key benefits

Low overhead — uses read-only DMV polling, minimal impact on production
Cross-database visibility — monitor multiple databases from one dashboard
Historical data — retains months of performance history (unlike 1-hour DMVs)
Alert integration — set up alerts based on collected metrics

Database watcher vs Azure Monitor

Both monitor Azure SQL, but they serve different purposes:

Azure Monitor — resource-level metrics (CPU %, DTU %, storage), alerts, and diagnostic logs
Database watcher — deeper SQL-specific metrics (query-level performance, wait stats, index analysis) with richer dashboards

Think of Azure Monitor as the basic dashboard and database watcher as the advanced analytics tool. They complement each other.

Extended Events (XEvents)

Extended Events is the diagnostic microscope of SQL Server — capture exactly the events you need with minimal overhead.

Architecture

Component	Purpose
Events	What to capture (e.g., sql_statement_completed, deadlock_report)
Predicates	Filters — only capture events matching conditions
Actions	Additional data to collect (e.g., sql_text, query_plan_hash)
Targets	Where to store captured data (ring_buffer, event_file, histogram)
Session	A configured collection of events + predicates + actions + targets

Common XEvent sessions

-- Capture long-running queries (over 5 seconds)
CREATE EVENT SESSION LongQueries ON DATABASE
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (sqlserver.sql_text, sqlserver.query_plan_hash)
    WHERE duration > 5000000  -- microseconds
)
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY = 4096 KB);

ALTER EVENT SESSION LongQueries ON DATABASE STATE = START;

-- Capture deadlocks
CREATE EVENT SESSION DeadlockCapture ON DATABASE
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.ring_buffer;

ALTER EVENT SESSION DeadlockCapture ON DATABASE STATE = START;

Reading XEvent data

-- Read from ring buffer
SELECT
    event_data = CAST(target_data AS XML)
FROM sys.dm_xe_database_session_targets t
JOIN sys.dm_xe_database_sessions s ON t.event_session_address = s.address
WHERE s.name = 'LongQueries' AND t.target_name = 'ring_buffer';

Targets compared

Target	Best For	Data Retention
ring_buffer	Quick diagnostics, limited memory	In memory only (lost on restart)
event_file	Long-term capture, large volumes	Persisted to disk/blob storage
event_counter	Counting occurrences	In memory
histogram	Distribution analysis	In memory

XEvents on Azure SQL Database

Azure SQL Database supports Extended Events with some differences from on-prem:

Use ON DATABASE instead of ON SERVER
event_file target stores to Azure Blob Storage (not local disk)
Some server-level events aren’t available
SSMS’s XEvent viewer and Azure Data Studio both support browsing captured events

When to use which

Database Watcher vs Extended Events
Aspect	Database Watcher	Extended Events
Purpose	Continuous monitoring and trend analysis	Targeted diagnostic capture
Setup	Azure Portal (managed)	T-SQL or SSMS wizard
Overhead	Very low (DMV polling)	Low (but depends on events selected)
Data retention	Months (Azure Data Explorer)	Session lifetime or blob storage
Best for	Health dashboards, capacity planning	Deadlock analysis, slow query tracing, specific event diagnosis
Platforms	SQL DB, MI	SQL DB, MI, SQL on VMs
Custom queries	KQL (Kusto)	XQuery on XML event data

Question

What is the primary use case for database watcher?

Click or press Enter to reveal answer

Answer

Continuous, low-overhead monitoring of Azure SQL with pre-built dashboards. Shows trends in CPU, queries, waits, and index usage. Data stored in Azure Data Explorer for months of history.

Click to flip back

Question

What are the four main components of an Extended Events session?

Click or press Enter to reveal answer

Answer

Events (what to capture), Predicates (filter conditions), Actions (additional data to collect), and Targets (where to store: ring_buffer, event_file, histogram, etc.).

Click to flip back

Question

Where does the event_file target store data in Azure SQL Database?

Click or press Enter to reveal answer

Answer

Azure Blob Storage. Unlike on-prem SQL Server where files go to local disk, Azure SQL DB uses a Blob Storage container for event file targets.

Click to flip back

Knowledge Check

Tomas needs to investigate intermittent deadlocks that happen a few times per week. He needs to capture the full deadlock graph (victims, resources, queries) for analysis. What should he use?

Knowledge Check

Kenji wants a dashboard showing CPU trends, top queries, and wait statistics across all 15 of NorthStar's Azure SQL databases for the past 3 months. What should he set up?

🎬 Video coming soon

Next up: Query Store: Configure and Monitor — the built-in flight recorder for query performance.