Study guide
10.3 Server Settings and Resource Governor
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.3 Server Settings and Resource Governor
Key Takeaways Explain This Topic AI
Key server-level settings: MAXDOP (8 for OLTP), cost threshold for parallelism (raise for OLTP), min/max server memory, fill factor, and optimize for ad hoc workloads.
Azure SQL Database does not expose sp_configure; MAXDOP is per-database via ALTER DATABASE SCOPED CONFIGURATION; SQL MI exposes only a few server options.
Resource Governor has three components: resource pools, workload groups, and a classifier function (a scalar UDF) called on every new session.
Resource Governor is full on SQL Server on VMs, limited/preview on SQL MI, and not available on Azure SQL Database; use elastic pools for DB isolation there instead.
Use Resource Governor for multi-tenant isolation (guaranteed minimums, capped maximums) and runaway-query protection via request_max_memory_grant_percent.
Last updated: August 2026
Why Server Settings and Resource Governor Matter
On-prem SQL Server and SQL Server on Azure VMs expose a wide set of server-level configuration options that affect performance: parallelism, memory, threading, and disk layout. Resource Governor layers a workload-isolation mechanism on top, so a single instance can serve competing workloads without one starving the others. DP-300 tests the well-known server settings and the Resource Governor architecture, plus the important platform caveat: most server-level tuning and Resource Governor are not available on Azure SQL Database, and only limited on SQL Managed Instance.
Server-Level Performance Settings
Key options in sp_configure and their DP-300-relevant behavior:
Option Effect Notes
max degree of parallelism (MAXDOP) Caps parallelism per query Default 0 (use all CPUs); best practice 8 for OLTP, 0 for reporting
cost threshold for parallelism Minimum query cost before parallelism is considered Default 5; raise for OLTP to stop tiny queries going parallel
- max worker threads Caps the thread pool Default 0 (auto); rarely changed
- affinity Binds schedulers to CPUs Rarely used on Azure VMs
min/max server memory Bounds the buffer pool Set max memory to leave room for OS; leave a few GB for non-buffer use
fill factor (%) Leaves free space in index leaf pages Default 0 (full); 80–90 for high-churn insert tables
optimize for ad hoc workloads Caches only a small plan stub for one-shot ad hoc batches Reduces plan-cache bloat from non-parameterized queries
remote admin connections Allows DAC from remote Useful for troubleshooting locked instances
Best-practice MAXDOP guidance from Microsoft: for servers with a single NUMA node and 8 or more logical cores, set MAXDOP to 8; for OLTP-only servers, lower values are often appropriate. On Azure VMs, also respect vCore boundaries — do not set MAXDOP higher than the vCore count. A frequent scenario pattern: OLTP queries going parallel unnecessarily — raise cost threshold for parallelism first (the cheaper fix), and only then consider lowering MAXDOP.
Azure SQL DB / MI Server Config Differences
On Azure SQL Database , you cannot run sp_configure for these server-level settings. The platform controls them. MAXDOP is configurable per database via ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = ... , not via sp_configure . Server-level memory, worker threads, and affinity are not exposed. On SQL Managed Instance , server-level configuration is also mostly managed; you control MAXDOP through the instance properties (Azure Resource Manager) and via DSC, and a few sp_configure options are read-only. The general exam principle: on PaaS offerings, prefer database-scoped configuration and automatic tuning to server-level tuning.