Study guide
10.4 Compute/Storage Scaling and Intelligent Query Processing
DP-300: Administering Microsoft Azure SQL Solutions Exam
Track your progress as you read
Finished sections get a check mark.
1 Introduction & Exam Overview
1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources
2 Planning and Deploying Azure SQL Solutions
2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching
3 Configuring Resources for Scale and Performance
3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression
4 Planning and Implementing a Migration Strategy
4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting
5 Configuring Database Authentication and Authorization
5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management
6 Implementing Security for Data at Rest and in Transit
6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints
7 Implementing Compliance Controls for Sensitive Data
7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL
8 Monitoring Resource Activity and Performance
8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events
9 Monitoring and Optimizing Query Performance
9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights
10 Configuring Database Solutions for Optimal Performance
10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing
11 Creating and Managing SQL Server Agent Jobs
11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates
12 Automating Deployments and Database Tasks in Azure
12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks
13 Planning an HA/DR Strategy and Backup and Restore
13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations
14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups
14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups
15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting
15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions
10.4 Compute/Storage Scaling and Intelligent Query Processing
Key Takeaways Explain This Topic AI
Vertical scaling changes one database or instance's compute/storage; horizontal scaling adds replicas or pools; Hyperscale vCore scaling is constant-time regardless of database size.
Hyperscale named replicas provide independently-sized read-scale without affecting the primary and without a tier migration.
IQP features are gated by database compatibility level, not engine version; level 110 disables all IQP from 130/140 onward even on SQL Server 2022.
Key IQP features: adaptive join, batch mode on rowstore, memory grant feedback (batch and row mode), table variable deferred compilation, scalar UDF inlining, parameter sensitive plan optimization.
Post-upgrade process: keep compat level, enable Query Store, baseline, raise compat level one step at a time, use plan forcing for regressions.
Last updated: August 2026
Why Scaling and IQP Matter
Two of the most testable topics in Domain 3 are scaling (the levers you pull when a database outgrows its current compute or storage) and Intelligent Query Processing (IQP) — a family of optimizer improvements Microsoft ships in waves, gated by database compatibility level. Chapter 3 covers the platform-specific scaling mechanics in depth; here we summarize the scaling levers and focus on IQP, which is unique to this chapter.
Scaling Recap
Platform Vertical scale Horizontal scale Notes
Azure SQL Database DTU or vCore size (e.g. GP_Gen5_4 to GP_Gen5_8 ); online with brief switchover Read replicas (Business Critical, Premium); named replicas (Hyperscale); elastic pools Moving into/out of Hyperscale is a migration, not an in-place tier change
SQL Managed Instance vCores (4–80), storage, hardware generation Instance pools (2-vCore instances); readable secondary on Business Critical Tier change (GP to BC) is size-of-data; vCore/storage change ends in brief failover
SQL Server on Azure VMs Resize VM (memory-optimized E-series); disk types (Premium SSD, Premium SSD v2, Ultra Disk) Always On AGs, readable secondaries Watch the VM-level IOPS ceiling; stripe disks; tempdb on D:
Vertical scaling changes compute or storage capacity for a single database or instance; horizontal scaling adds replicas or pools. The decision pattern: if a workload saturates the primary, scale up; if it saturates only for reads or for a specific tenant, scale out.
Azure SQL Database Elastic Pools and Database Moves
Moving a database into an elastic pool ( ALTER DATABASE ... MODIFY (SERVICE_OBJECTIVE = ElasticPool) ) and out again is an online operation. Changing a database's service objective (DTU size or vCore size) is online with a brief connection drop at switchover. Changing between General Purpose and Business Critical is also online but involves data movement (size-of-data). On Hyperscale , scaling vCores is a constant-time metadata operation, because compute is decoupled from the page servers holding data — you can scale from 2 to 80 vCores in minutes regardless of database size, and you can add up to four named replicas for read scale-out.