Performance Monitoring & Tuning
Not sure which monitoring tool to use? See the Monitoring Tools Matrix for a complete comparison of Query Store, DMVs, Extended Events, and Azure-native tools.
The Monitoring Mental Model
Oracle DBA parallel: This is your OEM → AWR → ADDM → SQL Tuning Advisor flow, but in Azure. Portal Metrics = OEM dashboard. Query Store = AWR. Intelligent Insights = ADDM. Automatic Tuning = SQL Profiles applied automatically.
Key Monitoring Signals
| Metric | What It Tells You | Oracle Equivalent |
|---|---|---|
| CPU % | Query/index issues | CPU used by this session wait |
| Data IO % | Storage bottleneck, missing indexes | db file sequential/scattered read |
| Log IO % | Write-heavy workload, log rate limit | log file sync |
| DTU % | Overall resource saturation (DTU model) | Top-N AWR metrics combined |
| Sessions/Workers % | Connection pool exhaustion | v$session active count |
| Deadlocks | Concurrent access conflicts | ORA-00060 |
Query Store — Your Flight Recorder
The most important tool for DP-300. Think of it as AWR on steroids — it captures per-query plans and stats continuously.
What Query Store Captures
| Data | Retention | Oracle Equivalent |
|---|---|---|
| Query text | Until purged | V$SQL |
| Execution plans (all historical) | Configurable | DBA_HIST_SQL_PLAN |
| Runtime stats (CPU, duration, reads, rows) | Configurable | DBA_HIST_SQLSTAT |
| Wait statistics per query | Configurable | V$SESSION_WAIT + ASH |
| Plan forcing information | Until removed | SQL Profiles / SQL Baselines |
Query Store Architecture
Query Store exam facts: 1) Enabled by default in Azure SQL DB. 2) Must be manually enabled on SQL Server/VM. 3) You can force a specific plan (like Oracle SQL Profiles). 4) It detects plan regressions automatically. 5) Stores data in the user database (not system DB).
The "Regressed Queries" Pattern (Exam Favorite)
- A query runs fine for months with Plan A
- Statistics update or schema change → optimizer picks Plan B
- Plan B is slower → performance regression
- Query Store detects this and can auto-force Plan A back
This is FORCE_LAST_GOOD_PLAN in Automatic Tuning — Azure's equivalent of Oracle's SQL Plan Baseline.
Dynamic Management Views (DMVs) — Cheat Sheet
| What You Need | DMV | Oracle Equivalent |
|---|---|---|
| Currently running queries | sys.dm_exec_requests + sys.dm_exec_sql_text() | V$SESSION + V$SQL |
| Top queries by CPU | sys.dm_exec_query_stats | V$SQL ORDER BY CPU_TIME |
| Missing indexes | sys.dm_db_missing_index_* | DBA_ADVISOR_RECOMMENDATIONS |
| Index fragmentation | sys.dm_db_index_physical_stats() | INDEX_STATS / ANALYZE INDEX VALIDATE |
| Wait statistics | sys.dm_os_wait_stats | V$SESSION_WAIT / ASH |
| Resource usage (last 1 hr) | sys.dm_db_resource_stats | V$SYSMETRIC |
| Active sessions | sys.dm_exec_sessions | V$SESSION |
| Blocking chains | sys.dm_exec_requests (blocking_session_id) | V$LOCK + DBA_BLOCKERS |
Most-tested DMVs: sys.dm_db_resource_stats (resource utilization), sys.dm_exec_query_stats (query performance), sys.dm_db_missing_index_details (missing indexes), and sys.dm_db_index_physical_stats (fragmentation).
Index Tuning — Architecture & Strategy
Index Types
| Type | Architecture | Best For | Oracle Equivalent |
|---|---|---|---|
| Clustered | IS the table (data sorted by key) | Primary key, range queries | Index-Organized Table (IOT) |
| Non-clustered | Separate B-tree with pointers to data | Lookups, filtered queries | Regular B-tree index |
| Columnstore | Column-based storage, high compression | Analytics, aggregations | No direct equivalent (closest: bitmap) |
| Filtered | NC index with WHERE clause | Sparse data, status columns | Function-based index (partial) |
Index Maintenance Decision Tree
REBUILD locks the table by default (offline). Always use ALTER INDEX ... REBUILD WITH (ONLINE = ON) in production. This requires Enterprise edition on VMs, but is always available in Azure SQL DB and MI (which include Enterprise features).
Columnstore — The Analytics Superpower
Why columnstore is fast for analytics: SUM(Amount) only reads the Amount column (not entire rows). Compression can be 10x+, reducing I/O dramatically. Perfect for GROUP BY, SUM, AVG, COUNT on large datasets.
Automatic Tuning — Azure's Self-Healing
| Option | What It Does | Default |
|---|---|---|
| FORCE_LAST_GOOD_PLAN | Detects plan regression → forces previous good plan | ✅ On (Azure SQL DB) |
| CREATE_INDEX | Creates missing indexes based on DMV recommendations | ❌ Off (opt-in) |
| DROP_INDEX | Drops unused indexes wasting storage/write overhead | ❌ Off (opt-in) |
FORCE_LAST_GOOD_PLAN is the only automatic tuning option enabled by default. CREATE_INDEX and DROP_INDEX must be explicitly enabled. The exam tests whether you know which are on vs off by default.
Intelligent Insights
ML-based performance analysis that detects:
- Timeout increasing — queries timing out more frequently
- Waiting increasing — specific wait types growing
- Regression — queries getting slower over time
- Tempdb contention — excessive tempdb usage impacting performance
Results are written to a diagnostics log in JSON format → viewable in Azure Portal or Log Analytics.
Think of Intelligent Insights as ADDM that runs continuously. Oracle ADDM analyzes AWR snapshots hourly. Intelligent Insights analyzes Query Store + metrics continuously and proactively alerts on anomalies. No manual AWR report generation needed.