Azure Monitor for Azure SQL: Complete Monitoring Guide for DBAs (DP-300 & Real-World)

If you’ve ever managed an on-premises SQL Server, you know the drill: Perfmon counters, SQL Server Profiler, a handful of DMV scripts you’ve copy-pasted since 2014, and maybe a third-party tool if your company paid for one. You built your own visibility.

Azure SQL takes that away from you but in a good way. You don’t get to RDP (Remote Desktop) into the box. There is no box. But Microsoft didn’t leave you blind; they replaced physical access with a monitoring platform that’s arguably far more powerful than anything most of us built ourselves: Azure Monitor.

This article is written for three kinds of readers at once, because in my experience they’re often the same person at different points in their career:

• DP-300 aspirants who need to know Azure Monitor cold for the exam
• Freshers who’ve never managed a database outside SSMS and want a mental model that actually makes sense
• Senior DBAs and Azure professionals who already know “some” Azure Monitor but want the gaps filled, especially around KQL, Smart Detection, and cost control

Read it top to bottom once, then keep it as a reference. Let’s get into it.

Introduction to Azure Monitor

Azure Monitor is not a single tool. It’s an umbrella platform that collects, analyzes, and acts on telemetry from every Azure resource you own, including Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs.

Think of it in three layers:

1. Collect — metrics and logs flow in automatically or via diagnostic settings you configure
2. Analyze — you query that data using metrics explorer or KQL (Kusto Query Language)
3. Act — alerts, action groups, and automation respond to what the data tells you

The single biggest mindset shift for a traditional DBA: monitoring is no longer something you bolt on after deployment. In Azure, monitoring is a first-class resource configuration decision you make while provisioning the database, because diagnostic settings, retention, and workspace routing are architecture choices, not afterthoughts.

Why Azure SQL Needs Monitoring

You might think “SQL Server is SQL Server, why does the cloud version need anything different?” Fair question & here’s the honest answer.

You’ve lost the machine. No RDP, no Task Manager, no Perfmon counters tied to a specific server. Every signal about CPU, memory, and I/O now has to come through a service-level API.

Elasticity cuts both ways. Azure SQL can scale automatically (serverless tiers) or you can scale manually, but either way, “normal” performance today might look different from “normal” performance after a scale event. Without monitoring, you won’t even know a scale event happened.

Shared infrastructure means noisy neighbor risk is real – especially on lower service tiers using shared elastic pools. DTU/vCore throttling can look exactly like a performance regression in your code, and only metrics will tell you the difference.

Billing is consumption-based. An unmonitored, badly-tuned database doesn’t just run slow. It costs you real money every single day via higher DTU/vCore tiers or storage growth.

Compliance and SLAs demand evidence. When a client asks “prove the database was available 99.99% of last month,” you need Resource Health and Azure Monitor Logs, not your memory of a bad Tuesday.

In short: on-prem monitoring was about catching problems. Azure monitoring is about catching problems, controlling cost, and proving service levels – three jobs instead of one.

Azure Monitor Architecture

Here’s the mental model I give every junior DBA I mentor:

Data Sources → Azure Monitor Platform → Analysis Tools → Action
(Azure SQL,     (Metrics DB, Log       (Metrics Explorer,  (Alerts, Action
 Activity Log,   Analytics workspace,   Log Analytics/KQL,   Groups, Logic
 App Insights)   Data Collector)        Workbooks)           Apps, Autoscale)

The architecture has a few core components you need to know by name for DP-300:

• Metrics database — a time-series database purpose-built for numeric data, retained for 93 days by default, queried through Metrics Explorer
• Azure Monitor Logs — powered by a Log Analytics workspace, stores structured log and event data queried through KQL, retention configurable up to 2 years (longer with Azure Data Explorer export)
• Activity Log — a subscription-level log of control-plane operations (who changed what, when). This is not the same as diagnostic logs
• Data collection rules (DCRs) — define what gets collected, from where, and where it’s sent
• Insights — curated, pre-built experiences (SQL Insights, VM Insights) layered on top of the raw platform

The exam-relevant distinction: Azure Monitor Metrics is platform-native and near-real-time (1-minute granularity), while Azure Monitor Logs requires you to explicitly route data via diagnostic settings and has slightly higher ingestion latency.

Data Collection Pipeline

Every piece of telemetry follows roughly the same journey:

1. Emission — the Azure SQL resource emits a metric or log event as part of normal operation (this happens automatically, you don’t configure it)
2. Platform metrics collection — basic metrics (CPU %, DTU %, storage, connections) are collected automatically the moment the resource exists, no configuration needed
3. Diagnostic settings routing — for anything beyond basic metrics (query store data, deadlocks, timeouts, blocking, errors, audit logs), you must create a diagnostic setting that routes this data to one or more destinations
4. Destination — Log Analytics workspace, Storage Account (for archival/compliance), or Event Hub (for streaming to third-party SIEM tools like Splunk)
5. Consumption — dashboards, workbooks, alerts, and KQL queries read from these destinations

The one line I want every fresher to underline: platform metrics are “free” and automatic; logs are opt-in and require a diagnostic setting. This single fact explains half of the “why can’t I see X” questions I get from new DBAs.

Metrics vs Logs vs Events

This trips people up constantly, including in interviews, so let’s be precise.

Aspect	Metrics	Logs	Activity Log (Events)
Data type	Numeric, time-series	Structured/semi-structured records	Structured JSON records
Example	CPU percentage, DTU consumption	Query Store data, deadlock XML, blocked process reports	“User X scaled database Y to S3”
Query tool	Metrics Explorer	KQL in Log Analytics	Activity Log blade / KQL
Latency	Seconds to ~1 minute	Minutes (depends on diagnostic setting)	Near real-time
Retention (default)	93 days	30 days (configurable to 2 years)	90 days
Cost model	Included/free for platform metrics	Charged per GB ingested + retention	Free
Best for	Dashboards, autoscale triggers, threshold alerts	Root-cause analysis, correlation, forensic queries	“Who changed the config” questions

Events are really a subset of logs, specifically, they’re discrete occurrences (a deadlock, a login failure, a scaling operation) rather than continuous numeric streams. When people say “events” loosely in an Azure SQL context they usually mean either Activity Log entries (control plane) or diagnostic log entries like Errors, DatabaseWaitStatistics, or Deadlocks (data plane).

Azure SQL Metrics Explained (Every Important Metric)

This is the section to bookmark. These are the metrics you should actually understand, not just recognize the name of.

DTU / vCore consumption

• dtu_consumption_percent (DTU model) or cpu_percent (vCore model) — sustained values above 80% mean you’re compute-bound; correlate with query duration before scaling
• dtu_limit / cpu_limit — your current ceiling, useful to confirm a scale operation actually took effect

CPU

• cpu_percent — average CPU percentage. A single spike is normal; a sustained plateau near 100% for 15+ minutes is your signal to investigate top queries

Memory

• memory_percent — Azure SQL manages buffer pool internally, but this metric tells you when memory pressure is contributing to plan evictions and PLE drops indirectly (Azure SQL doesn’t expose raw PLE the way on-prem does. You infer it from this and wait stats)

Storage

• storage — actual data file space used
• storage_percent — percentage of your max size used; this one silently kills applications when it hits 100% and every write starts failing
• xtp_storage_percent — relevant only if you’re using In-Memory OLTP (memory-optimized tables)

I/O

• physical_data_read_percent — read I/O saturation relative to your service tier’s limit
• log_write_percent — write I/O saturation on the transaction log; this is the metric most people forget, and it’s often the actual bottleneck on write-heavy OLTP workloads, not CPU

Connections

• connection_successful and connection_failed — a rising connection_failed trend usually means throttling, firewall misconfiguration, or the app hitting max connection limits for the tier
• blocked_by_firewall — self-explanatory but often overlooked as a monitoring signal rather than just an error message

Sessions and Workers

• sessions_percent and workers_percent — every service tier has a hard cap on concurrent sessions/workers; hitting 100% here causes new connections to be flatly rejected, and it’s a purely resource-tier problem, not a query-tuning problem

Deadlocks

• deadlock — count of deadlocks in the period; zero tolerance is unrealistic, but a rising trend on a previously quiet database needs investigation

Tempdb (Managed Instance and Elastic Pool specific)

• tempdb_data_size, tempdb_log_size — tempdb pressure is a top cause of “mystery slowness” that traditional DBAs used to chase with tempdb.sys.dm_db_file_space_usage; in Azure you watch it as a metric first

In-Memory OLTP

• xtp_storage_percent as above — relevant only if you’ve adopted memory-optimized tables/procedures

A practical tip for the exam and for real work: DP-300 loves scenario questions where the “obvious” answer (scale up) is wrong because the real bottleneck is log_write_percent or sessions_percent, not CPU. Always check all four resource dimensions, compute, storage I/O, log I/O, and connections/workers before recommending a scale action.

Diagnostic Settings

Diagnostic settings are the bridge between “the platform is generating this data somewhere” and “you can actually see it.”

To configure one (Portal path: your database → Monitoring → Diagnostic settings → Add diagnostic setting):

1. Choose which log categories to collect for Azure SQL Database, the key ones are:
   • SQLInsights — the aggregated performance data behind SQL Insights and Query Performance Insight
   • AutomaticTuning — records of automatic tuning actions taken
   • QueryStoreRuntimeStatistics and QueryStoreWaitStatistics — Query Store data exported for long-term KQL analysis
   • Errors — engine errors
   • DatabaseWaitStatistics — aggregated wait stats
   • Timeouts, Blocks, Deadlocks — exactly what they sound like
   • Basic, InstanceAndAppAdvanced, WorkloadManagement — for Managed Instance/elastic pool scenarios
   • SQLSecurityAuditEvents — if auditing is enabled
2. Choose destination(s): Log Analytics workspace (for KQL and alerting), Storage Account (cheap long-term archive, satisfies audit/compliance retention requirements), and/or Event Hub (streaming to a SIEM)
3. Save — data typically starts flowing within a few minutes

Common mistake: people enable diagnostic settings after an incident and then wonder why they have no historical data to analyze. Enable this on day one of provisioning, not day one of firefighting.

You can also deploy diagnostic settings via Azure Policy so every new Azure SQL database automatically gets a consistent diagnostic configuration. This is the enterprise-scale answer and a favorite DP-300 governance topic.

Log Analytics Workspace

The Log Analytics workspace is the data lake underneath Azure Monitor Logs. A few things every DBA should actually understand rather than just click through:

• One workspace per subscription is not a rule — you can have several, and design decisions (data residency, RBAC boundaries, cost allocation per team) usually drive how many you need
• Tables — each diagnostic category lands in its own table, e.g. AzureDiagnostics (legacy resource-specific mode disabled) or, in the newer resource-specific mode, dedicated tables like AzureDiagnostics, SQLInsights, AutomaticTuning, Errors, QueryStoreRuntimeStatistics. Always prefer resource-specific tables — they’re cheaper to query and easier to work with in KQL
• Retention — default 30 days included; extend up to 2 years per table, or archive further with Basic Logs / Archive tiers for cost control
• RBAC — access to workspace data can be scoped so a DBA sees only SQL-related tables, not, say, network security logs from the same workspace, relevant in larger organizations
• Data export — you can continuously export workspace data to a Storage Account or Event Hub for even longer retention or integration with external tools

For DP-300: know that diagnostic settings decide what goes into the workspace, but workspace-level retention and access control decide how long it stays and who can query it – these are two separate configuration surfaces, and exam questions like to test whether you know which setting lives where.

Azure Monitor Logs (KQL)

This is the section that separates DBAs who “use Azure Monitor” from DBAs who actually get value out of it. KQL (Kusto Query Language) is not optional knowledge anymore. Treat it the way you’d treat T-SQL.

The good news: if you know T-SQL, KQL feels familiar within a day. It’s still declarative, still has filtering, aggregation, and joins – just piped instead of clause-based.

Basic structure:

TableName
| where TimeGenerated > ago(1h)
| where ResourceId contains "mydatabase"
| project TimeGenerated, Category, ResultDescription
| order by TimeGenerated desc

Practical, DBA-relevant queries you’ll actually reuse:

Find recent deadlocks:

AzureDiagnostics
| where Category == "Deadlocks"
| where TimeGenerated > ago(24h)
| project TimeGenerated, deadlock_xml_s

Top wait stats over the last 6 hours:

AzureDiagnostics
| where Category == "DatabaseWaitStatistics"
| where TimeGenerated > ago(6h)
| summarize TotalWaitMs = sum(todouble(delta_wait_time_ms_d)) by wait_type_s
| order by TotalWaitMs desc
| take 10

Query Store — find the most expensive queries by CPU:

AzureDiagnostics
| where Category == "QueryStoreRuntimeStatistics"
| where TimeGenerated > ago(24h)
| summarize AvgCPU = avg(todouble(avg_cpu_time_d)) by query_hash_s
| order by AvgCPU desc
| take 20

Failed connections trend (useful for spotting throttling or firewall issues):

AzureMetrics
| where MetricName == "connection_failed"
| summarize FailedConns = sum(Total) by bin(TimeGenerated, 5m)
| order by TimeGenerated desc

Key KQL operators worth memorizing: where, project, summarize, join, bin(), ago(), render, extend, parse, mv-expand (for exploding nested JSON like deadlock graphs).

Interview-relevant point: be ready to explain the difference between summarize (aggregates, like GROUP BY) and extend (adds a computed column, like a derived column in a SELECT) — this comes up constantly.

Alerts

Alerts are how Azure Monitor stops being a dashboard you have to remember to check, and starts being a system that taps you on the shoulder.

Four alert types matter for Azure SQL:

1. Metric alerts — threshold-based on near-real-time metrics (e.g., “CPU > 85% for 15 minutes”). Fast to evaluate, cheapest, and the right tool for 80% of your alerting needs
2. Log alerts — based on a KQL query run on a schedule (e.g., “more than 5 deadlocks in 10 minutes”). More flexible but slightly higher latency
3. Activity Log alerts — fire on control-plane events (e.g., “alert me any time someone changes the firewall rules on this server”)
4. Smart Detection alerts — pre-built anomaly detection, covered in its own section below

Alert anatomy (know this for DP-300):

• Scope — which resource(s)
• Condition — the signal and threshold
• Actions — what happens (tied to an Action Group)
• Alert rule details — severity (0–4, 0 being critical), name, description

Practical guidance from experience: don’t alert on every metric — you’ll train your team to ignore notifications within a week. Alert on the handful that predict real pain: sustained CPU/DTU, storage approaching capacity, connection failures spiking, and deadlock/timeout counts crossing a baseline. Everything else belongs in a dashboard you glance at, not a page that wakes someone up.

Action Groups

An Action Group is simply the “what happens next” attached to an alert — and reusable across many alert rules so you configure it once.

Supported actions include:

• Email / SMS / voice call / push notification (Azure app)
• Webhook — call any HTTP endpoint, commonly used to integrate with PagerDuty, Opsgenie, or an internal ticketing system
• Logic App — trigger a full automated workflow (see the Automation section below)
• Azure Function — run custom remediation code
• ITSM connector — create a ticket directly in ServiceNow or similar
• Automation Runbook — kick off a PowerShell/Python runbook, e.g., to automatically kill a blocking session or scale a database

The pattern I recommend to every team: build tiered action groups — a low-severity group that just emails the DBA distribution list, and a high-severity group that pages on-call and fires a Logic App for auto-remediation, and opens a ticket. One alert can trigger multiple action groups.

Smart Alerts

“Smart Alerts” in the Azure SQL world mostly refers to two overlapping things, and it’s worth being precise for the exam:

1. Smart Detection (Application Insights lineage) — machine-learning-based anomaly detection that doesn’t require you to set a static threshold. It learns your database’s normal pattern and flags deviations — useful because a fixed “CPU > 80%” threshold means nothing for a database whose normal load swings from 20% to 95% throughout the day
2. Azure SQL’s built-in Intelligent Insights / Automatic Tuning alerts — Azure SQL Database itself has a machine-learning layer (Query Performance Insight and Intelligent Insights) that detects performance regressions — a query that suddenly got slower, excessive waits, missing indexes — and can even automatically apply fixes (Automatic Tuning: CREATE INDEX, DROP INDEX, FORCE LAST GOOD PLAN) if you opt in

The honest, practical view: Smart Detection is genuinely good at catching things a static threshold misses — like a slow, creeping degradation over three weeks that never crosses any single alert threshold on any single day, but adds up to a database that’s 40% slower than a month ago.

Workbooks

Workbooks are Azure Monitor’s answer to “I need a shareable, interactive report, not just a static chart.”

A few things that make them worth learning properly:

• Combine multiple data sources in one view — metrics, log queries, and even parameters (like a dropdown to pick which database to look at) in a single canvas
• Built-in Azure SQL workbook templates exist out of the box — start from a template rather than building from scratch
• Parameters let one workbook serve an entire fleet — build it once with a “select database” dropdown instead of duplicating a report per database
• Export to PDF / pin to dashboard for stakeholder reporting

A workbook I build for almost every client in the first week: a single page showing DTU/CPU trend, storage growth trend, top 10 waits, top 10 expensive queries, and connection failure count — all with a time-range picker at the top. That one workbook answers 70% of “how’s the database doing” questions without anyone writing a query.

Dashboards

Azure Dashboards are simpler and more static than Workbooks — think of them as a pinboard.

• You pin tiles from Metrics Explorer, Log Analytics query results, or Workbooks
• Dashboards can be shared across a team via Azure RBAC, so your whole DBA team sees the same landing page
• They’re great for a wall-mounted NOC screen or a morning-coffee glance — less great for deep investigation, which is what Workbooks and raw KQL are for

Rule of thumb I give students: Dashboards for “at a glance,” Workbooks for “let me dig in,” KQL for “let me find the exact root cause.”

Azure Advisor Integration

Azure Advisor is Azure’s built-in recommendation engine, and it has an entire category dedicated to your databases.

For Azure SQL specifically, Advisor surfaces:

• Performance recommendations — create/drop index suggestions, based on the same engine behind Automatic Tuning
• Cost recommendations — “this database has been under 10% DTU utilization for 30 days, consider downsizing,” or “consider reserved capacity for this steady-state workload”
• Reliability recommendations — missing geo-replication, backup configuration gaps
• Security recommendations — this overlaps heavily with Defender for Cloud, covered below

A habit worth building: check Advisor monthly, not just when something breaks. It’s one of the few places Azure proactively tells you “you’re overpaying” rather than “something is on fire.”

Azure Service Health

Service Health answers a very specific, very important question: “Is this Azure’s problem or my problem?”

It has three sub-blades:

• Service issues — active incidents affecting Azure services in your regions, with real-time status
• Planned maintenance — scheduled events, including SQL engine patching windows, that could affect your resources
• Health advisories — service-level changes, deprecations, and feature retirements relevant to services you use

The DBA-relevant workflow: when your application team says “the database is down,” your first move (after checking Resource Health, below) should be Service Health — because if it’s a regional Azure outage, no amount of index tuning or failover on your part will help, and you need that information within the first two minutes of an incident, not after 40 minutes of fruitless troubleshooting.

You can also configure Service Health alerts so you’re notified proactively about planned maintenance windows that touch your resources — genuinely useful for planning around patch cycles.

Resource Health

Where Service Health tells you about Azure broadly, Resource Health tells you about your specific database.

It reports one of four states: Available, Unavailable, Degraded, or Unknown — along with a health history timeline. This is the fastest way to distinguish “my database had a blip” from “my query is just slow,” and it’s often the first place I check during an incident, before touching a single DMV or KQL query.

Resource Health also lets you configure alerts specifically for state transitions — e.g., “notify me the moment this database becomes Unavailable” — which is a cleaner and faster signal than waiting for a connection-failure metric threshold to trip.

Azure Monitor Insights

“Insights” is Microsoft’s term for a curated, pre-configured monitoring experience built on top of the raw Azure Monitor platform, tailored to a specific resource type.

For Azure SQL, this shows up as:

• SQL Insights (preview-turned-mature feature) — a fleet-wide, cross-database view of performance, built from the SQLInsights diagnostic category, without you having to hand-build every chart
• Query Performance Insight — per-database view of top resource-consuming queries over time, directly in the portal, no KQL required — this is the single most useful “just show me what’s slow” tool for a fresher who isn’t confident in KQL yet

The relationship to remember: Insights are opinionated, ready-made views; Workbooks and dashboards are what you build when Insights don’t cover your specific need. Start with Insights, graduate to custom Workbooks as your requirements get specific.

Integration with Defender for Cloud

Microsoft Defender for Cloud extends Azure Monitor’s “is it healthy” question into “is it under attack.”

For Azure SQL, enabling Microsoft Defender for SQL gets you:

• Vulnerability assessment — scans for misconfigurations, excessive permissions, unencrypted sensitive columns, missing auditing
• Advanced Threat Protection — real-time detection of SQL injection attempts, anomalous login behavior (e.g., a login from a country your app has never connected from), and potential data exfiltration patterns
• Alerts feed directly into Azure Monitor’s Action Groups — meaning a threat detection can page your on-call the same way a CPU alert would

The practical takeaway for DP-300: Defender for Cloud alerts and Azure Monitor alerts are not separate silos — they share the same alerting and action group infrastructure, which is exactly the kind of integration detail exam scenario questions test.

Automation Using Logic Apps

This is where monitoring stops being passive and starts doing something.

A Logic App triggered from an Action Group can:

• Automatically scale a database up during a detected sustained CPU spike, and scale back down afterward (a lightweight alternative to writing your own autoscale logic)
• Kill a blocking session identified from a KQL alert, using a runbook or Azure Automation
• Post to a Teams/Slack channel with the alert context, so the team sees it without checking email
• Open a ServiceNow/Jira ticket automatically with the alert payload pre-filled
• Trigger a backup or failover in specific pre-approved incident scenarios

A real example from my own work: a client had periodic tempdb pressure during month-end batch jobs. Instead of a human waking up at 2 AM, we built a Logic App triggered on a tempdb_log_size metric alert that automatically bumped the elastic pool’s eDTU ceiling for two hours, then reverted it — fully unattended, logged, and auditable.

Monitoring Multiple Azure SQL Databases

Fleet monitoring is where “just check the portal” completely falls apart, and where a lot of exam and interview questions live.

Approaches, roughly in order of increasing sophistication:

1. Azure Policy-enforced diagnostic settings — guarantee every database, present and future, routes to the same workspace automatically
2. A single shared Log Analytics workspace across all databases (with resource-specific tables), so one KQL query can union across your entire fleet
3. SQL Insights fleet view — purpose-built for exactly this, showing all monitored databases in one pane, sortable by health signal
4. Workbooks with a resource-selector parameter — build once, apply to any database or group of databases the viewer selects
5. Elastic pools — if you have many databases with unpredictable, non-overlapping usage patterns, pooling them changes what you even need to monitor (pool-level DTU/vCore becomes as important as per-database metrics)
6. Azure Arc-enabled SQL Server — if your fleet includes on-prem or multi-cloud SQL Server instances, Arc extends Azure Monitor’s reach to them too, unifying your view across hybrid environments

A KQL trick specific to fleet monitoring — query across every database routed to the workspace at once:

AzureDiagnostics
| where Category == "Errors"
| where TimeGenerated > ago(1h)
| summarize ErrorCount = count() by Resource
| order by ErrorCount desc

Cost Optimization

Azure Monitor is not free once you go beyond platform metrics, and I’ve seen monitoring bills quietly become a bigger surprise than the database bill itself. A few concrete levers:

• Ingestion volume is the main cost driver for Log Analytics — be deliberate about which diagnostic categories you actually enable. QueryStoreRuntimeStatistics can be chatty on a busy OLTP system; decide if you need it at full fidelity or can sample
• Use resource-specific tables, not legacy AzureDiagnostics mode — they store data more efficiently and are cheaper per query
• Set table-level retention deliberately — not everything needs 2 years; error logs might, routine performance counters probably don’t
• Basic Logs tier — for high-volume, rarely-queried tables (some audit categories fit this), Basic Logs cost significantly less to ingest, at the cost of reduced query capability and a 8-day retention floor before you must move to Analytics tier or export
• Commitment tiers — if you know your daily ingestion volume, a Log Analytics commitment tier is materially cheaper than pay-as-you-go once you cross roughly 100 GB/day
• Export cold data to Storage Account (cool/archive tier) instead of keeping everything hot in the workspace for years
• Right-size metric alert evaluation frequency — a 1-minute evaluation cadence on a low-priority metric costs more (and is noisier) than a 5-minute cadence for no real operational benefit

The single biggest cost mistake I see: teams enable every diagnostic category “just in case” on day one, never revisit it, and six months later are paying to ingest and store data nobody has queried once.

Best Practices

A condensed list I’d hand to any team standing up Azure SQL monitoring from scratch:

• Enable diagnostic settings at provisioning time, via Azure Policy, not manually per-database after the fact
• Route to a shared workspace per environment (prod/non-prod separated, not per-database)
• Use resource-specific mode tables, always
• Build tiered alerts — a handful of severity-0/1 alerts that page someone, everything else as dashboard-visible only
• Pair every critical alert with an Action Group that includes context, not just a bare notification
• Automate the boring remediations (transient blocking, predictable scale needs) via Logic Apps, and save humans for judgment calls
• Review Advisor recommendations monthly, not reactively
• Keep a standard Workbook template per environment so every team’s “dashboard” looks and behaves the same way
• Revisit retention and ingestion volume quarterly — this is a cost review, not a one-time setup task
• Document runbooks tied to specific alerts — when the “CPU sustained > 85%” alert fires, there should be a written, known next step, not improvisation

DP-300 Exam Notes

Things I’ve seen tested repeatedly, worth memorizing precisely:

• Platform metrics are automatic; diagnostic logs require explicit diagnostic settings — this distinction shows up in multiple question forms
• Know the default retention numbers: 93 days for metrics, 30 days default for Logs (extendable to 2 years)
• Know which destination fits which use case: Log Analytics workspace for querying/alerting, Storage Account for compliance archival, Event Hub for external SIEM streaming
• Automatic Tuning options (CREATE INDEX, DROP INDEX, FORCE LAST GOOD PLAN) and which are enabled by default at the Azure SQL Database vs Managed Instance level differ — check current defaults, they’ve shifted over time
• Resource Health vs Service Health — expect a scenario question asking you to pick the right blade for a described symptom
• Understand that Query Performance Insight requires Query Store to be active on the database — if Query Store is off, QPI has nothing to show
• Elastic pool metrics vs single database metrics are reported and interpreted differently — know that pool-level eDTU/vCore consumption doesn’t map 1:1 to any single database’s reported percentage
• Diagnostic settings can route to multiple destinations simultaneously — this is often the “best answer” in a multi-select scenario question about compliance + alerting requirements together

Interview Questions

A set I’d actually ask (or expect to be asked) at mid-to-senior level:

1. What’s the practical difference between Azure Monitor Metrics and Azure Monitor Logs, and when would you use each?
2. A client says a database “feels slow” but no metric threshold has been breached. Walk me through your investigation using Azure Monitor.
3. How would you design diagnostic settings and alerting for a fleet of 200 Azure SQL databases without generating alert fatigue?
4. Explain how you’d use KQL to find the top 5 most expensive queries in the last 24 hours.
5. What’s the difference between a metric alert and a log alert, and what’s the cost/latency trade-off?
6. How does Automatic Tuning relate to Intelligent Insights, and would you enable it in a highly regulated environment? Why or why not?
7. Describe a real (or hypothetical) automated remediation you’d build with a Logic App triggered from an Azure Monitor alert.
8. How would you control Log Analytics ingestion costs on a busy OLTP system without losing the ability to diagnose incidents?
9. What’s the difference between Resource Health and Service Health, and which would you check first during an outage?
10. How does Defender for SQL integrate with the alerting you’d already built in Azure Monitor?

If you can answer all ten conversationally, without notes, you’re in genuinely strong shape for a senior Azure DBA interview.

Hands-on Lab

A self-guided lab you can complete in under an hour with a free/trial Azure subscription:

1. Provision a basic-tier Azure SQL Database (single database, not pool) in a new resource group
2. Enable diagnostic settings on it — route SQLInsights, QueryStoreRuntimeStatistics, Errors, Deadlocks, and Blocks to a new Log Analytics workspace
3. Generate load — run a simple script (even a loop of SELECT * FROM sys.objects CROSS JOIN sys.objects a few hundred times, or use an OSTRESS/lightweight load tool) to push CPU up
4. Watch Metrics Explorer in near-real-time as cpu_percent climbs
5. Write a KQL query against AzureDiagnostics or the resource-specific tables to find the query responsible, using the QueryStoreRuntimeStatistics category
6. Create a metric alert — “CPU > 70% for 5 minutes” — with an Action Group that emails you
7. Deliberately trigger it, confirm the email arrives, and note the latency between breach and notification
8. Build a one-page Workbook combining a CPU chart, storage chart, and your top-queries KQL query
9. Check Azure Advisor for the database — even a small test database usually gets at least one recommendation within a day
10. Tear down the resource group when done to avoid ongoing charges

Doing this once, hands-on, will teach you more than reading this entire article twice.

Common Mistakes

Patterns I see repeatedly, from freshers and senior folks alike:

• Enabling diagnostic settings only after an incident — then having zero historical data to actually investigate the incident that just happened
• Alerting on everything — leading to alert fatigue, where real alerts get ignored because they’re buried in noise
• Using legacy AzureDiagnostics mode instead of resource-specific tables — works, but more expensive and clunkier in KQL
• Never revisiting retention settings — paying to store years of data nobody queries
• Treating Azure Advisor as a one-time setup checklist instead of an ongoing monthly review
• Confusing Resource Health with Service Health during an incident and wasting the first critical minutes checking the wrong blade
• Assuming Automatic Tuning is “set and forget” — it’s powerful, but you should still review what it’s applying, especially DROP INDEX actions, before fully trusting it unattended in a critical production database
• Building dashboards but never building runbooks — a dashboard tells you something’s wrong; it doesn’t tell the on-call engineer what to do about it
• Not testing alerts — configuring an alert rule and assuming it works, without ever deliberately triggering it once to confirm the notification actually arrives

Troubleshooting Scenarios

A few realistic scenarios, worked through the way you’d actually approach them:

Scenario 1: “The database is randomly slow, but only sometimes, and I can’t reproduce it.” Check log_write_percent and physical_data_read_percent over the same window as the reported slowness — intermittent I/O throttling near your service tier’s limit produces exactly this symptom and won’t show up if you’re only watching CPU. Cross-reference with DatabaseWaitStatistics for LOG_RATE_GOVERNOR or IO_QUEUE_LIMIT type waits.

Scenario 2: “Connections are being refused, but CPU and DTU both look fine.” Check sessions_percent and workers_percent — these are hard tier limits independent of CPU. A database can be at 20% CPU and still reject every new connection if it’s hit its session cap, often from a connection leak in the application layer, not a server-side resource problem.

Scenario 3: “Storage alert fired at 90%, but we just added a small table last week.” Check for recent index rebuild operations or a runaway transaction log if the database is in Full recovery without regular log backups — storage growth is rarely just “table data,” and a KQL query against Activity Log plus the storage metric trend over 30 days usually reveals the actual growth driver.

Scenario 4: “A deadlock alert fired, but the application team says nothing changed.” Pull the deadlock XML from the Deadlocks diagnostic category via KQL, and cross-reference the timestamp against Query Store data — often a statistics update, an auto-index change from Automatic Tuning, or a parameter-sniffing plan flip changed query behavior even though the application code itself didn’t change.

Scenario 5: “Resource Health shows Available, but users report the database is down.” This usually means the platform-level resource is healthy but something above it — application connection string, firewall rule change, Azure AD auth token expiry, or a DNS/private endpoint issue — is the actual cause. Resource Health tells you the database itself is fine; it doesn’t tell you the path to it is fine.

Summary

Azure Monitor turns Azure SQL from a black box into a fully instrumented system — but only if you configure it deliberately rather than relying on defaults. The core ideas to carry forward:

• Platform metrics are automatic and free; logs require diagnostic settings and cost money to ingest and store
• KQL is not optional anymore — it’s the modern equivalent of your DMV script library
• Alerts should be few, meaningful, and tied to action groups that actually do something useful
• Resource Health and Service Health answer different questions — know which one to check first
• Automation (Logic Apps, Automatic Tuning) turns monitoring from passive observation into active remediation
• Cost control is part of the monitoring job now, not a separate concern

Whether you’re prepping for DP-300, walking into a senior Azure DBA interview, or just trying to sleep better at night knowing your production database will tell you before it fails — Azure Monitor, configured properly, is what makes that possible.