10.4 Compute/Storage Scaling and Intelligent Query Processing

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

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.

Intelligent Query Processing (IQP)

Intelligent Query Processing is a family of query optimizer and execution improvements introduced in waves starting with SQL Server 2017 (compatibility level 140) and continuing through SQL Server 2022 (level 160). IQP features are gated by database compatibility level, not engine version — running SQL Server 2022 with a database at compatibility level 110 disables most IQP features even though the engine is new. This is the most common exam trap in this area.

The IQP family, by the compatibility level that introduced each:

Each feature is designed to fix a class of plan-quality problem the optimizer previously could not: adaptive joins fix bad plan choices based on stale cardinality; memory grant feedback fixes spill-heavy or memory-wasting plans; batch mode on rowstore brings columnar-engine speed to rowstore tables without requiring a columnstore index.

IQP Feature Deep Dive

A few features deserve attention because they appear in scenario questions:

• Adaptive join: the optimizer builds both a hash-join index and prepares a nested-loop path; at runtime, after scanning the build input, it picks the cheaper path. Use it for cases where row counts vary widely between executions. The tradeoff: adaptive joins use more memory than a static plan.
• Memory grant feedback: on the second execution of a plan, the engine checks whether the first execution spilled or wasted memory and adjusts the grant. It can fix plans that consistently spill to tempdb because of over-estimation. Row-mode feedback extends this beyond the original batch-mode scope.
• Batch mode on rowstore: lets a query against a rowstore index use batch-mode operators. The engine can apply batch-mode operators to rowstore scans without requiring a columnstore index on the table, accelerating scan-heavy reporting workloads.
• Scalar UDF inlining: turns a T-SQL scalar function into an inline expression, eliminating the row-by-row context switch. Not all UDFs are inlinable — ones with side effects, non-deterministic calls, or certain constructs remain non-inlined.
• Parameter sensitive plan optimization (PSPO): solves the "parameter sniffing on steroids" problem where the same plan is good for one parameter value and catastrophic for another. The optimizer can now keep multiple plans and dispatch based on parameter value ranges. This is the post-upgrade answer when parameter sniffing regresses a workload.
• Approximate count distinct: returns a count of distinct values using HyperLogLog with a small error margin, dramatically faster and lower-memory than COUNT(DISTINCT ...) for billion-row distinct sets. Ideal for dashboards where an exact count is not required.

Compatibility Level Gating

The hard rule: IQP features are enabled by database compatibility level, not engine version. A database at compatibility level 110 (SQL Server 2012) running on SQL Server 2022 disables all IQP features from 130/140 onward. The recommended post-upgrade process:

1. Upgrade the engine, keep the database compatibility level at its prior value.
2. Enable Query Store (ALTER DATABASE ... SET QUERY_STORE = ON).
3. Wait for a baseline of workload data (Microsoft suggests at least a full business cycle).
4. Raise the compatibility level one step at a time (110 to 130 to 140 to 150 to 160), watching Query Store for regressions at each step.
5. If a query regresses at a new level, use Query Store plan forcing to keep the prior plan, or disable a specific IQP feature via a QUERY_HINT or database-scoped configuration like LEGACY_CARDINALITY_ESTIMATION = ON.

ALTER DATABASE SCOPED CONFIGURATION provides escape hatches — for example, you can run at compatibility level 150 (for most IQP features) but turn off QUERY_OPTIMIZER_HOTFIXES or set LEGACY_CARDINALITY_ESTIMATION = ON if a specific workload regresses. The Data Migration Assistant (DMA) and Query Store's regressed-queries report are the tools to validate each step.