Automatic Tuning and Performance Settings

Advanced performance configuration

Server settings for performance

MAXDOP (Max Degree of Parallelism)

Controls how many CPU cores a single query can use in parallel:

Setting	Effect
MAXDOP = 0	Use all available cores (default, but often too aggressive)
MAXDOP = 1	No parallelism — every query runs single-threaded
MAXDOP = 4	Each query can use up to 4 cores in parallel

Recommendations:

• OLTP workloads: MAXDOP 1-4 (short queries don’t benefit from high parallelism)
• OLAP/analytics: MAXDOP 4-8 (large scans and aggregations benefit from parallelism)
• Azure SQL Database: Configure per-database via database-scoped configuration

-- Set server-level MAXDOP (SQL on VMs / MI)
EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

-- Set database-level MAXDOP (all platforms)
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;

Cost threshold for parallelism

Controls the minimum query cost (in optimizer cost units) before parallel execution is considered:

-- Default is 5 (too low for most OLTP workloads)
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

Sam’s advice to Tomas: “If you see lots of CXPACKET waits, increase the cost threshold before reducing MAXDOP. Many small queries going parallel unnecessarily is usually the problem.”

Max server memory

For SQL Server on VMs, always set a memory cap to leave room for the OS:

-- Leave 4 GB for the OS on a 32 GB VM
EXEC sp_configure 'max server memory', 28672;  -- MB
RECONFIGURE;

Resource Governor

Resource Governor lets you control how CPU, memory, and I/O are distributed among workloads — essential when OLTP and reporting share the same instance.

Architecture

Component	Purpose
Resource pools	Define resource limits (CPU %, memory %)
Workload groups	Map to resource pools, set per-request limits
Classifier function	Routes sessions to workload groups based on criteria

-- Create resource pools
CREATE RESOURCE POOL OLTPPool WITH (MAX_CPU_PERCENT = 80);
CREATE RESOURCE POOL ReportingPool WITH (MAX_CPU_PERCENT = 30);

-- Create workload groups
CREATE WORKLOAD GROUP OLTPGroup USING OLTPPool;
CREATE WORKLOAD GROUP ReportingGroup USING ReportingPool;

-- Classifier function routes sessions
CREATE FUNCTION dbo.ClassifierFunction()
RETURNS SYSNAME WITH SCHEMABINDING
AS BEGIN
    RETURN CASE
        WHEN APP_NAME() LIKE '%SSRS%' THEN 'ReportingGroup'
        WHEN APP_NAME() LIKE '%Report%' THEN 'ReportingGroup'
        ELSE 'OLTPGroup'
    END;
END;

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ClassifierFunction);
ALTER RESOURCE GOVERNOR RECONFIGURE;

ℹ️ Resource Governor availability

• SQL Server on VMs: Full Resource Governor support (Enterprise edition)
• Azure SQL Managed Instance: Resource Governor available
• Azure SQL Database: Does NOT support user-defined Resource Governor. Azure manages resources internally. Use elastic pools for resource sharing between databases.

The exam may ask about Resource Governor for a scenario on MI or VMs — remember it’s not available on SQL Database.

Database-scoped configuration

Settings that apply per-database (override server settings for that database):

Setting	Purpose	Default
MAXDOP	Per-database parallelism	Inherits server
LEGACY_CARDINALITY_ESTIMATION	Use old CE model	OFF
PARAMETER_SNIFFING	Enable/disable parameter sniffing	ON
QUERY_OPTIMIZER_HOTFIXES	Enable optimizer hotfixes	OFF
LAST_QUERY_PLAN_STATS	Store actual plan stats	OFF

-- Set per-database MAXDOP
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2;

-- Set for secondary replicas differently
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = 8;

-- Disable parameter sniffing for a problematic database
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

Intelligent Query Processing (IQP)

IQP is a family of features that automatically improve query performance without code changes. They’re activated by compatibility level:

Feature	Compat Level	What It Does
Adaptive joins	140+	Switches between nested loop and hash join at runtime based on actual rows
Memory grant feedback	140+	Adjusts memory grants based on previous executions (reduces spills and waste)
Batch mode on rowstore	150+	Uses batch processing for analytics queries on regular tables
Scalar UDF inlining	150+	Inlines scalar functions into the query plan (huge improvement)
Table variable deferred compilation	150+	Compiles table variable queries after the variable is populated (better cardinality)
Approximate count distinct	150+	APPROX_COUNT_DISTINCT for fast approximate analytics
Parameter sensitivity plan (PSP)	160	Multiple plans for the same query based on parameter values
Cardinality estimation feedback	160	Adjusts CE based on actual vs estimated row mismatches
DOP feedback	160	Automatically tunes parallelism per query
Optimized plan forcing	160	Faster plan forcing with reduced compilation overhead

-- Check current compatibility level
SELECT name, compatibility_level FROM sys.databases WHERE name = DB_NAME();

-- Upgrade to enable latest IQP features
ALTER DATABASE NorthStarERP SET COMPATIBILITY_LEVEL = 160;

💡 Exam tip: compatibility level is the IQP switch

IQP features are controlled by compatibility level, NOT the SQL Server version. A SQL Server 2022 database running at compatibility level 130 won’t use IQP features from 150 or 160.

The exam may ask: “How do you enable adaptive joins?” → Set compatibility level to 140 or higher.

Azure SQL Database defaults to the latest compatibility level for new databases. MI and SQL VMs may run databases at older levels after migration.

Scaling compute and storage

Quick reference for when workload outgrows current resources:

Platform	How to Scale	Notes
SQL Database	Change tier (GP/BC/HS), adjust vCores, enable serverless	Brief connectivity blip during scale
Managed Instance	Change vCores or tier	Can take 30+ minutes
SQL on VMs	Resize VM, add/resize disks	VM restart required

Question

What does MAXDOP control?

Click or press Enter to reveal answer

Answer

Max Degree of Parallelism — the maximum number of CPU cores a single query can use in parallel. Set per-server (sp_configure) or per-database (ALTER DATABASE SCOPED CONFIGURATION).

Click to flip back

Question

What is the purpose of Resource Governor?

Click or press Enter to reveal answer

Answer

It controls how CPU, memory, and I/O are distributed among workloads. Uses resource pools, workload groups, and a classifier function to prevent one workload (like reporting) from starving another (like OLTP).

Click to flip back

Question

How do you enable Intelligent Query Processing features?

Click or press Enter to reveal answer

Answer

Set the database compatibility level to the required level: 140+ for adaptive joins and memory grant feedback, 150+ for batch mode on rowstore and scalar UDF inlining, 160 for PSP and DOP feedback.

Click to flip back

Knowledge Check

Kenji's SQL Server VM hosts both OLTP and reporting workloads. Heavy reports are consuming 90% CPU, causing OLTP transactions to time out. What should he configure?

AIncrease MAXDOP to allow reports to finish fasterBConfigure Resource Governor with separate pools for OLTP and reportingCMove reports to a different database on the same serverDEnable automatic tuning to force last good plans

Check Answer

Knowledge Check

After migrating to Azure SQL MI, Priya notices that adaptive joins and memory grant feedback aren't being used. The database compatibility level is 130. What should she do?

AEnable automatic tuningBSet compatibility level to 140 or higherCConfigure Resource GovernorDEnable Query Store

Check Answer

🎬 Video coming soon

You’ve completed Domain 3! You can now baseline, monitor, diagnose, and optimise Azure SQL performance.

Next up: Automation Landscape: What Runs Where — understand the automation tools available across Azure SQL platforms.