The DP-300 exam's monitoring section asks which tool diagnoses which problem. Azure SQL offers several overlapping diagnostic tools, and exam questions are designed to blur their boundaries. Think of it this way: a car dashboard shows you fuel and RPM right now, a black box preserves what happened before the crash, and an AI mechanic flags abnormalities before you even notice them — each tool covers a different time horizon and layer.
Azure Monitor — The Control Tower Watching Everything at Once
An airport control tower does not monitor a single runway — it watches the entire airfield. Azure Monitor works the same way. It is a unified observability platform that collects metrics and logs from all three Azure SQL products: Azure SQL Database, Managed Instance, and SQL Server on Azure VM.
Metrics are collected automatically with no extra configuration. CPU percentage, DTU consumption, Workers/Sessions counts, and storage used all flow in by default. The default retention is 93 days; for longer-term analysis you route metrics to a Log Analytics Workspace.
Diagnostic logs behave differently. They are off by default. You must configure first, choosing which log categories to collect and where to send them — Log Analytics Workspace, Azure Storage, or Event Hubs. Alert Rules let you trigger emails or webhooks the moment CPU crosses 80%, keeping your team informed without constant manual checks.
Query Store — The Black Box for Query Performance
A flight recorder preserves hours of data before a crash, so investigators can reconstruct exactly what happened. Query Store does the same for queries. It continuously saves execution plans and runtime statistics in internal tables, so you can look up which plan a slow query was using last night at 11 p.m. — even if you investigate at 9 a.m. the next day.
Query Store is enabled by default on Azure SQL Database and Managed Instance. Its most important scenario is detecting — when a statistics update or index change causes the engine to pick a worse execution plan and performance suddenly degrades. You compare the before and after plans in Query Store and use to lock in the better one.
Wait statistics per query are also stored in Query Store, letting you determine whether a slow query is waiting on CPU, locks, or I/O. Retention period and capture policy are configurable via or the Azure Portal.
DMVs — The Real-Time Gauges Inside the Engine
An emergency room doctor watches a patient's oxygen saturation and heart rate live, not from records made yesterday. Dynamic Management Views (DMVs) give you that real-time window into the SQL Server engine.
shows cached query execution statistics. lists queries running right now. aggregates wait types per session. Unlike Query Store, DMV data is volatile — it disappears on a service restart or plan cache flush. The rule of thumb: need history? Query Store. Need the current state right now? DMVs.
Extended Events — Lightweight Event Tracing
To track when and where defective parts came off a factory line, you install sensors that fire only when a defect occurs, not continuously. Extended Events (XE) follow the same philosophy — they capture data only when a specified SQL Server event fires, keeping server overhead minimal.
Extended Events replace the older SQL Trace and SQL Server Profiler. You define a session, select exactly the events you care about, and choose a target. The target stores data in memory (lost on restart), while persists data to Azure Blob Storage. On Azure SQL Database you can create XE sessions from the Azure Portal or T-SQL.
SQL Insights and Intelligent Insights — Unified Dashboard and AI Diagnostics
A good concierge notices a guest's discomfort before the guest has to say anything. SQL Insights and Intelligent Insights split that role between them.
is Azure Monitor's integrated monitoring dashboard for all Azure SQL products. When you want a single pane of glass comparing Azure SQL Database, Managed Instance, and SQL Server on Azure VM side by side, SQL Insights is the answer. It collects DMV data without installing an agent, stores everything in a Log Analytics Workspace, and surfaces it through pre-built workbooks.
is AI-powered anomaly detection. It learns the normal performance pattern of Azure SQL Database and Managed Instance, automatically detects sudden degradation, and writes a root-cause diagnosis to diagnostic logs — no manual query writing required.
Tool Selection at a Glance
They look similar but serve distinct purposes.
| Tool | Layer | History Retained | Key Scenario | |-|-|-|-| | Azure Monitor | Infrastructure (CPU, DTU, storage) | 93 days (metrics) | Threshold alerts, multi-product overview | | Query Store | Execution plans + runtime stats | Persistent (configurable) | Plan regression detection, Force Plan | | DMV | Engine real-time state | Cleared on restart | Active queries, current locks | | Extended Events | Specific event capture | ring_buffer (volatile) / event_file (persistent) | Lightweight tracing, Deadlock capture | | SQL Insights | DMV-based unified dashboard | Log Analytics retention | Multi-instance comparison | | Intelligent Insights | AI anomaly detection | Diagnostic logs | Automatic performance degradation alert |
Exam Pitfall — Forgetting Diagnostic Settings Makes Logs Invisible
A team sets up a new Azure SQL Database and expects to see query statistics in Log Analytics, but the logs never appear. Metrics look fine. The cause is simple: nobody configured Diagnostic Settings.
In the Azure Portal, go to the resource, then Diagnostic Settings, then Add Diagnostic Setting. Select log categories such as , , and , and set the destination to a Log Analytics Workspace. Data will start arriving within a few minutes. Skip this step, and no engine-level logs will ever leave the Azure SQL service — no matter what else you configure.
The other common trap is confusing Intelligent Insights with Query Store. Both surface performance problems, but Intelligent Insights finds them automatically through AI while Query Store requires a DBA to run analyses manually. When the exam says "automatically detected," think Intelligent Insights.
Exam Key Takeaways
"I need the execution plan a slow query used last night" -- Query Store "A plan regression occurred and I want to force the old plan" -- Query Store Force Plan "I need a list of queries running right now" -- DMV (sys.dm_exec_requests) "Alert me when CPU exceeds 80%" -- Azure Monitor Alert Rule "Send diagnostic logs to Log Analytics" -- Configure Diagnostic Settings first "Single dashboard for all Azure SQL products" -- SQL Insights "AI automatically detects performance degradation" -- Intelligent Insights "Capture only Deadlock events with minimal overhead" -- Extended Events "Query stats must survive a service restart" -- Query Store (not DMVs) "ring_buffer vs event_file" -- ring_buffer is in-memory (volatile), event_file goes to Blob (persistent)
Query Store = query black box, DMV = real-time gauge, Azure Monitor = infrastructure control tower, Intelligent Insights = AI auto-diagnostics