Without monitoring, a database failure becomes a surprise party nobody wants — angry users, lost sales, and frantic late-night calls. For the DP-300 exam, understanding how to monitor database performance with Azure tools is the difference between passing and failing because it directly tests your ability to keep a database healthy under load. You will learn what metrics matter, which tools to use, and how to interpret the dashboard numbers that tell the real story.
Jump to a section
A simple way to picture Monitoring Database Performance with Azure Tools
You are driving a delivery van with 200 packages in the back. Your route has 47 stops across a city. You have a dashboard showing three things: a fuel gauge, a speedometer, and a temperature gauge for the engine.
Halfway through the route, you notice the fuel gauge drops from half a tank to near empty in just 10 minutes. You also see the engine temperature rising into the red zone. You pull over, check the fuel line, and find a slow leak. You also spot the radiator fan is broken. If you had ignored those gauges, the van would have stalled on a highway, missing all 20 remaining deliveries.
Azure monitoring tools for databases are exactly that dashboard. They show you metrics like CPU usage (like the speedometer), memory pressure (like the fuel gauge), and disk latency (like the temperature gauge). You can set alerts, just like a warning light, that tell you when a number goes outside safe limits. Without these tools, a database can silently slow down, run out of space, or crash — and you only find out when customers complain. That dashboard turns invisible problems into visible numbers you can act on.
Monitoring database performance is about watching key numbers that tell you how well your database is running. Think of it like checking vital signs: pulse, blood pressure, and oxygen level for a person. For a database, the vital signs include CPU usage, memory consumption, disk input/output (I/O) latency, and query execution time.
Azure provides a set of tools designed specifically for this. The main tool is Azure Monitor, a centralised service that collects metrics and logs from all your Azure resources, including databases. Metrics are numerical values measured over time, like 'average CPU percentage over the last 5 minutes'. Logs are text records of events, such as 'user login failed' or 'query timed out'.
When you set up a database — for example, an Azure SQL Database — Azure Monitor automatically collects basic metrics. You can view these in the Azure portal, a web-based dashboard. The key metrics are:
DTU (Database Transaction Unit): a bundled measure of compute, storage, and I/O for Azure SQL Database. Higher DTU usage means the database is working harder.
CPU percentage: how much of the allocated processor power is being used. Consistently high CPU (above 80%) means the database might need more power or optimised queries.
Data I/O percentage: how much of your disk read/write capacity is used. High I/O can indicate slow queries scanning too much data.
Log I/O percentage: how much disk write capacity is used for transaction logs (records of every change). High log I/O means many writes happening.
Memory grant percentage: how much memory queries are requesting. Running out of memory causes queries to spill to disk, which is slow.
Beyond metrics, Azure Monitor also integrates with Azure SQL Analytics, a dedicated monitoring dashboard for databases. It shows top queries by CPU, by duration, and by wait type (what the database is waiting for, like locks or disk I/O). You can drill down into a specific slow query to see its execution plan — the step-by-step roadmap the database uses to run that query. A bad execution plan might scan an entire table instead of using an index, causing slowness.
Alerts are another crucial part of monitoring. You can set rules: 'if CPU percentage goes above 90% for 5 minutes, send an email to the admin'. You can also set alerts on log events, like 'if a query takes longer than 10 seconds, log it'. Alerts use action groups — a list of people to notify and how (email, SMS, phone call, or automated action like scaling up the database).
Why does monitoring matter? Databases handle transactions — every order placed, every login, every search query. If the database is slow, users experience lag or errors. In extreme cases, the database can stop responding entirely, causing an outage. Monitoring catches the early warning signs: a slow query might not crash the system, but if ignored, it can block other queries, creating a snowball effect.
Before modern monitoring tools, administrators had to manually check performance counters on a server, which was slow and reactive. Azure Monitor and Azure SQL Analytics provide real-time, automated, and centralised visibility. You can see trends over days or weeks, not just the current moment. This helps you plan ahead: 'Disk space is growing 10% per week, so we'll run out in 10 weeks' — you can take action before disaster strikes.
In summary, monitoring is about collecting, visualising, and acting on data about your database's health. The tools make invisible problems visible and give you time to fix them before users notice.
Identify the performance symptom
When a user reports slowness, start by confirming the symptom using Azure SQL Analytics. Check the 'overview' dashboard for high DTU, CPU, or wait times. This narrows down the problem area (e.g., CPU-bound or I/O-bound).
Pinpoint the top problematic queries
Open Query Performance Insight within Azure SQL Analytics. Sort queries by CPU, duration, or execution count. The query at the top is the most likely cause of the slowdown. Note its query ID and text.
Examine the execution plan
Click on the slow query to view its execution plan. Look for large icons like 'Table Scan' (reading all rows) or 'Index Scan' (reading many index pages). If you see a scan, the query likely needs a missing index or a rewrite.
Apply a fix and verify
Based on the plan, create an index or optimise the query. After the change, go back to Query Performance Insight and refresh the data. The query should drop in CPU/duration. If not, check again for other slow queries.
Set up proactive alerts
Once verified, create a metric alert for a relevant threshold (like DTU > 80% for 5 minutes) with an action group that emails your team. Also set a log alert for error events to catch future issues early.
Imagine you are a database administrator at a mid-sized e-commerce company called 'ShopFast'. It is a busy Monday morning, and your team is launching a flash sale. Within minutes, the website starts slowing down. Customers are complaining on social media. Your manager calls you in a panic.
You open the Azure portal and navigate to the Azure SQL Database monitoring blade. You look at the metric chart for CPU percentage. It is pinned at 95% for the last 15 minutes — the database is maxed out. You also check the 'top queries by CPU' report in Azure SQL Analytics. The number one query is a product search that scans the entire product table instead of using an index. It is called 'spSearchProducts'.
You click on that query to see its execution plan. The plan shows a 'Table Scan' icon — that means the database is reading every single row of the product table (500,000 rows) for every customer search. That is slow. You notice an index is missing. You quickly create an index on the 'category' column, which the query uses to filter. Within two minutes, CPU drops to 40%. The website speeds up. Customers stop complaining.
What actually happened step by step:
You identified the problem using Azure SQL Analytics top queries view.
You diagnosed the root cause by examining the execution plan.
You resolved the issue by creating a missing index.
You set up an alert: 'If CPU stays above 80% for 10 minutes, notify the on-call team via SMS'.
You also configured a log alert for 'queries that take longer than 5 seconds' so you catch future slow queries early.
In another scenario, you might see disk space usage climbing. You set a metric alert that triggers when the database size reaches 80% of its maximum. The alert runs an Azure Automation runbook — a script — that automatically increases the storage size by 10%. That fix happens without human intervention, even at 2 AM.
Monitoring also helps with capacity planning. At monthly reviews, you export the last 30 days of metrics to an Excel report. You see that every weekday between 9 AM and 11 AM, DTU usage spikes to 70%. You predict that in three months, the spike will hit 100% and cause slowdowns. You upgrade the service tier from Standard S2 to S3 before it becomes a problem.
The tools also support proactive maintenance. You set up a schedule for an index maintenance job that runs automatically every night, rebuilding indexes that have become fragmented (broken into pieces). You monitor the fragmentation percentage in Azure SQL Analytics to confirm the job is working.
In all these cases, the real-world job of a database administrator is to be a detective, finding clues in the monitoring data and fixing issues before users feel the pain. The exam tests whether you know which tool to use in each situation.
The DP-300 exam tests your ability to choose and use the correct monitoring tool for a given scenario. You will see multiple-choice questions that describe a performance problem—like slow queries or high CPU—and ask you to pick the right approach from a list of Azure tools. They love to set traps by offering tools that sound similar but do different jobs.
Specific concepts that appear frequently:
Know the difference between Azure Monitor, Azure SQL Analytics, and Query Performance Insight. Azure Monitor is the umbrella tool that collects all metrics. Azure SQL Analytics is a pre-built dashboard for Azure SQL Database (and Azure SQL Managed Instance) that shows top queries, wait stats, and trends. Query Performance Insight is a blade within Azure SQL Analytics that focuses specifically on the top queries by CPU, duration, and execution count. You might see a question: 'A user reports a slow query. Which tool shows the query's execution plan?' The answer is Query Performance Insight.
Understand metric alerts versus log alerts. Metric alerts fire based on a numerical value crossing a threshold (like 'DTU > 70%'). Log alerts fire based on a search in log data (like 'find all error events in the last 30 minutes'). A trap question might ask: 'You need to be notified when a specific error message appears in the database error log. Which alert type?' The correct answer is log alert, because it searches text, not numbers.
Know the important metrics: DTU, CPU percentage, Data I/O percentage, Log I/O percentage, Memory grant percentage, Sessions count, Blocking count. For a given symptom, you must pick the metric to check. For example, 'Users report slow INSERT operations' — check Log I/O percentage, because inserts write to the transaction log.
Understand Intelligent Insights, an Azure feature that automatically analyses performance and gives recommendations. It uses built-in intelligence to detect anomalies and suggest fixes like creating indexes or changing query structure. The exam may present a scenario where you need an automated root cause analysis without manual work. The answer is Intelligent Insights.
Common traps:
Choosing 'Azure Monitor' when the question specifically asks for a tool that shows top queries. Use Query Performance Insight or Azure SQL Analytics instead.
Confusing metric alert with a log alert. If the trigger is a numeric threshold (like 'CPU > 90%'), it is a metric alert. If the trigger is a text search (like 'event ID 1234'), it is a log alert.
Thinking that query store is the same as query performance insight. Query Store is a feature of SQL Server (including Azure SQL) that captures query plans and runtime stats. Query Performance Insight is the Azure tool that visualises that data. The exam might ask: 'Where is historical query plan data stored?' The answer is Query Store. 'Which tool visualises that data?' Query Performance Insight.
Forgetting action groups. An alert without an action group does nothing. A question might say: 'You created an alert. Nobody is notified. Why?' The reason is the action group is missing or misconfigured.
You should also memorise the steps to set up a monitoring dashboard: Create a Log Analytics workspace (a container for your log data), connect your database to it (via diagnostic settings), then configure the Azure SQL Analytics solution (a pre-made workbook) for that workspace. That workflow appears in scenario-based questions.
Finally, the exam tests retention policies. Logs are kept for a period you define (default 30 days), but metrics can be stored longer. Questions might ask if you can query a metric from 3 months ago. The answer depends on whether you configured longer retention for metrics.
Azure Monitor is the centralised platform that collects all metrics and logs from Azure resources, including databases.
Azure SQL Analytics is a pre-built dashboard that shows top queries, wait statistics, and performance trends for Azure SQL Database.
Query Performance Insight shows the top queries by CPU, duration, and execution count, and lets you view execution plans.
Metric alerts fire when a numerical value crosses a threshold; log alerts fire when a specific text pattern appears in log data.
Action groups define who gets notified (email, SMS, phone) and what automated actions (runbooks) to take when an alert fires.
Intelligent Insights provides automated root cause analysis and performance recommendations without manual setup.
These come up on the exam all the time. Here's how to tell them apart.
Azure Monitor
Collects metrics and logs from all Azure resources
Central platform for all monitoring data
Supports alerts on any metric or log
Azure SQL Analytics
Pre-built dashboard for Azure SQL Database only
Visualises top queries, wait stats, trends
Requires Azure Monitor as data source
Metric Alerts
Fire when a numerical value crosses a threshold
Example: CPU > 80% for 5 minutes
Low latency (under 1 minute)
Log Alerts
Fire when a text pattern appears in log data
Example: 'error 1234' in event logs
Higher latency (up to 5 minutes)
Query Store
Feature inside SQL Server (including Azure SQL)
Stores query plans and runtime stats
Retains data for up to 400 days
Query Performance Insight
Azure tool that reads from Query Store
Shows top queries in a visual dashboard
Integrates with Azure SQL Analytics
DTU Metrics
Combined measure of CPU, I/O, memory
Shows overall resource consumption
Good for capacity planning
Wait Statistics
Shows what queries are waiting for
Identifies specific bottlenecks (locks, disk, network)
Used for detailed performance debugging
Mistake
Monitoring is only useful after a problem occurs — it is reactive.
Correct
Monitoring is primarily proactive. You set baselines, detect trends, and fix issues before they cause outages. Alerts catch early warning signs, not just failures.
Many beginners think monitoring is for troubleshooting after users complain, but in practice, good monitoring prevents complaints by surfacing issues minutes or hours early.
Mistake
CPU percentage is the most important metric to watch.
Correct
CPU is important, but wait statistics (what the database is waiting for, like disk I/O or locks) often reveal the real bottleneck. High CPU can be a symptom, not the cause.
CPU is the most visible and commonly understood metric, so beginners fixate on it. The exam tests deeper understanding of blocking and I/O waits.
Mistake
Azure Monitor and Azure SQL Analytics are two separate products that do not connect.
Correct
Azure SQL Analytics is a solution that runs on top of Azure Monitor. You configure Azure Monitor to collect database logs and metrics, and then Azure SQL Analytics visualises them in pre-built dashboards.
Microsoft has many overlapping tool names, so beginners assume they are independent. The exam shows they are layered.
Mistake
Setting up a metric alert automatically fixes the problem.
Correct
An alert only sends a notification. To take automatic action, you must configure an action group that triggers a runbook (an automated script) or another Azure service. Alerts alone do nothing.
The word 'alert' sounds like 'alarm' that triggers action, but in Azure it is just a signal. Beginners miss the step of attaching an action group.
Mistake
Query Performance Insight and Query Store are the same thing.
Correct
Query Store is a SQL Server feature that stores query plans and runtime stats inside the database. Query Performance Insight is an Azure tool that reads from Query Store and displays the data in a user-friendly dashboard.
They are closely related (one stores, one shows), so beginners conflate them. The exam explicitly distinguishes them.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
Use Query Performance Insight, which is part of Azure SQL Analytics. It lists queries sorted by CPU, duration, or execution count and shows their execution plans.
Azure Monitor is the general service that collects all metrics and logs. Azure SQL Analytics is a pre-built monitoring dashboard specifically for Azure SQL Database that uses data from Azure Monitor.
Create a metric alert in Azure Monitor, set the condition to 'CPU percentage greater than 80%', and configure an action group with your email address. The action group tells Azure who to notify.
Wait statistics measure how long queries spend waiting for resources like disk, locks, or memory. They show the bottleneck (e.g., 'PAGEIOLATCH' means waiting on disk I/O) so you know what to fix.
Yes, by installing the Microsoft Monitoring Agent and connecting it to a Log Analytics workspace. This sends logs and metrics to Azure Monitor, where you can use Azure SQL Analytics for on-premises databases.
It is an automated performance analysis feature that detects anomalies, identifies root causes (like missing indexes or query regressions), and provides actionable recommendations without manual setup.
You've finished Monitoring Database Performance with Azure Tools. Continue through the DP-300 study guide to build a complete picture of the exam.
Done with this chapter?