Azure SQL Database — Configuration & Resource Governance
Unlike SQL Server on VMs, Azure SQL Database is PaaS — Microsoft manages the OS, patching, and infrastructure. But you still control critical configuration settings that directly impact performance, cost, and behavior.
Resource Governance — How Azure Controls Your Workload
Azure SQL Database enforces resource limits at multiple levels. Understanding these is crucial for troubleshooting performance.
Resource Limits by Tier
| Resource | General Purpose (8 vCore) | Business Critical (8 vCore) | Hyperscale (8 vCore) |
|---|---|---|---|
| Max IOPS | 6,400 | 48,000+ | Distributed (page servers) |
| Max log rate | 24 MB/s | 96 MB/s | 100 MB/s |
| Max concurrent workers | 800 | 800 | 800 |
| Max concurrent sessions | 30,000 | 30,000 | 30,000 |
| Max storage | 4 TB | 4 TB | 100 TB |
| tempdb size | Auto-managed | Auto-managed | Auto-managed |
| Read replicas | 0 | 1 free | Up to 4 HA + 30 named |
Log rate governance is one of the most common bottlenecks in Azure SQL DB. If your log_write_percent metric stays at 100%, your workload is hitting the log rate limit. Solutions: scale up to more vCores, use BC tier (higher log rate), or optimize write patterns (batch inserts).
How to Detect Resource Governance Throttling
| Symptom | Metric to Check | What It Means |
|---|---|---|
| Slow writes/inserts | log_write_percent = 100% | Hitting log rate limit |
| Slow reads | physical_data_read_percent = 100% | Hitting IOPS limit |
| Queries queueing | workers_percent > 80% | Running out of worker threads |
| Connections refused | sessions_percent = 100% | Session limit reached |
| General slowness | cpu_percent > 80% | Need more vCores |
Oracle DBA parallel: Resource governance = Oracle Resource Manager profiles. The difference: in Oracle, you configure it. In Azure SQL DB, it's enforced automatically by the platform based on your tier/vCore count. You can't disable it — you can only scale up.
MAXDOP in Azure SQL Database
Unlike SQL Server on VMs where you set MAXDOP as a server property, Azure SQL DB has specific MAXDOP behavior:
| Setting | Azure SQL DB | SQL MI | SQL on VM |
|---|---|---|---|
| Default MAXDOP | 0 (unlimited) | 0 | 0 |
| Scope | Database-level | Instance-level | Instance-level |
| Change method | ALTER DATABASE SCOPED CONFIGURATION | sp_configure | sp_configure |
| Query-level override | OPTION (MAXDOP n) | OPTION (MAXDOP n) | OPTION (MAXDOP n) |
Recommended MAXDOP for Azure SQL DB
| vCores | OLTP | Reporting/DW | Mixed |
|---|---|---|---|
| 2-4 | 1-2 | 0 | 2 |
| 8 | 4 | 0 or 8 | 4 |
| 16+ | 8 | 0 | 8 |
In Azure SQL DB, MAXDOP is set using ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = n; — NOT sp_configure. This is a frequently tested difference from SQL Server on VM/MI.
tempdb in Azure SQL Database
You cannot directly configure tempdb in Azure SQL DB. Microsoft manages it automatically:
| Aspect | Azure SQL DB | SQL MI |
|---|---|---|
| tempdb location | Local SSD (managed) | Local SSD (managed) |
| Number of files | Auto-configured by service | Configurable |
| Max tempdb size | Varies by tier/vCores | Configurable |
| DBA control | None (fully managed) | Full control |
| tempdb contention | Rare (well-tuned by platform) | You manage |
If you're troubleshooting tempdb contention in Azure SQL DB, you cannot add tempdb files like you would on a VM. Instead, optimize your queries to reduce tempdb usage (avoid large sorts, reduce temp table usage, use table variables cautiously).
Compatibility Level
The compatibility level determines which query optimizer behaviors are available:
| Compat Level | SQL Version | Key Features Enabled |
|---|---|---|
| 160 | SQL 2022 | Parameter Sensitivity Plan, CE 2022 feedback, DOP feedback |
| 150 | SQL 2019 | Batch mode on rowstore, memory grant feedback, adaptive joins, scalar inlining |
| 140 | SQL 2017 | Interleaved execution, adaptive query processing |
| 130 | SQL 2016 | Batch mode for columnstore, new CE improvements |
| 120 | SQL 2014 | New Cardinality Estimator (CE) |
| 110 | SQL 2012 | Legacy CE |
DP-300 critical: Changing compatibility level changes the query optimizer behavior, not the SQL engine version. A SQL 2022 database at compat 130 uses the SQL 2016 optimizer — it can run SQL 2022 T-SQL syntax but optimizes differently. When migrating, keep the old compat level first to avoid plan regressions, then upgrade gradually.
Safe Migration Pattern for Compatibility Level
- Keep old compat level after migration → queries use familiar optimizer
- Enable Query Store → captures baseline plans at old compat level
- Upgrade compat level → new optimizer may choose different plans
- Monitor Query Store for regressions → force old good plans if needed
- Use
FORCE_LAST_GOOD_PLAN→ automatic regression protection
Automatic Tuning
Azure SQL Database can automatically tune your workload:
| Feature | What It Does | Enabled by Default? |
|---|---|---|
| Force Last Good Plan | Detects plan regressions, auto-forces previous good plan | ✅ Yes (Azure SQL DB) |
| Create Index | Identifies missing indexes from DMVs, auto-creates | ❌ Off by default |
| Drop Index | Detects unused/duplicate indexes, auto-drops | ❌ Off by default |
Create Index and Drop Index auto-tuning should be tested carefully before enabling in production. Auto-created indexes can hurt write performance, and auto-dropped indexes might be needed for less-frequent queries. Always review the recommendations first in the Azure Portal before enabling auto-actions.
Intelligent Query Processing (IQP)
IQP features are activated by compatibility level, not by a setting you enable:
Key IQP Features (Compat 150+)
| Feature | What It Does | Exam Importance |
|---|---|---|
| Batch Mode on Rowstore | Uses batch processing even without columnstore indexes | High |
| Memory Grant Feedback | Adjusts memory grants based on actual vs estimated usage | High |
| Adaptive Joins | Switches between hash and nested loop at runtime | High |
| Table Variable Deferred Compilation | Correct row count for table variables (no longer assumes 1 row) | Medium |
| Scalar UDF Inlining | Inlines simple scalar functions into the query plan | Medium |
| Interleaved Execution | Executes multi-statement TVFs, then reoptimizes | Medium |
Key IQP Features (Compat 160 — SQL 2022)
| Feature | What It Does |
|---|---|
| Parameter Sensitivity Plan (PSP) | Creates multiple plan variants for different parameter values |
| Cardinality Estimation Feedback | Adjusts CE based on actual vs estimated rows |
| DOP Feedback | Optimizes parallelism degree based on runtime metrics |
| Optimized Plan Forcing | Reduces time to force plans from Query Store |
Batch Mode on Rowstore is the most heavily tested IQP feature on DP-300. It enables batch processing (processing 900 rows at a time instead of row-by-row) for rowstore tables — previously only available with columnstore indexes. Available at compat 150+.
Oracle DBA parallel: IQP features = Oracle Adaptive Query Optimization (adaptive plans, adaptive statistics, SQL plan directives). Both databases now automatically adjust execution strategies based on runtime feedback — Azure SQL calls it IQP, Oracle calls it Adaptive SQL Plan Management.
Serverless Compute Tier
Azure SQL DB Serverless auto-scales vCores and can auto-pause:
| Setting | Detail |
|---|---|
| Min vCores | 0.5 - 40+ (set your floor) |
| Max vCores | 1 - 80+ (set your ceiling) |
| Auto-pause delay | Minimum 60 minutes of inactivity |
| Wake-up time | ~1 minute on first connection after pause |
| Billing | Per-second for vCores used + storage |
| Storage | Billed even when paused |
When to use Serverless:
- Dev/test environments
- Databases with intermittent, unpredictable usage
- Applications tolerant of ~1 minute cold start
When NOT to use Serverless:
- Steady-state production OLTP
- Latency-sensitive applications
- Workloads that never go idle
Elastic Pools — Resource Sharing
Multiple databases sharing a pool of vCores or DTUs:
| Aspect | Detail |
|---|---|
| When to use | Multi-tenant SaaS, databases with complementary usage patterns |
| Pricing | Pay for pool resources, not per-database |
| Rule of thumb | 1.5x eDTUs × DB count < sum of individual DTUs = pool saves money |
| Per-DB limits | Set min/max resources per database within the pool |
| Monitoring | Pool-level and per-DB metrics in Azure Monitor |
Elastic Pools are cost-effective when databases have spiky, non-overlapping usage patterns. If all databases peak at the same time, a pool doesn't help — you need the same total peak capacity regardless.
Configuration Comparison: SQL DB vs MI vs VM
| Config Item | Azure SQL DB | SQL MI | SQL on VM |
|---|---|---|---|
| MAXDOP | DB scoped config | sp_configure | sp_configure |
| Max Memory | Managed | Managed | You configure |
| tempdb | Fully managed | You configure | You configure |
| Compatibility Level | You set | You set | You set |
| Query Store | ON by default | ON by default | You enable |
| Automated Backups | Automatic | Automatic | You configure |
| Patching | Automatic | Automatic | You manage |
| Storage | Managed | Managed | You configure |
| IFI | N/A | N/A | You enable |
| Lock Pages in Memory | N/A | N/A | You enable |
| Trace Flags | Not supported | Limited | Full control |