10.3 Server Settings and Resource Governor

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:

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.

Resource Governor Architecture

Resource Governor has three components:

1. Resource pools — define the resource budget. Each pool has MIN_CPU_PERCENT, MAX_CPU_PERCENT, CAP_CPU_PERCENT, MIN_MEMORY_PERCENT, MAX_MEMORY_PERCENT, and (on SQL Server 2019+ on VMs) MIN_IOPS_PER_VOLUME / MAX_IOPS_PER_VOLUME. There are two built-in pools: internal (system tasks, cannot be constrained) and default (user sessions without a classifier match).
2. Workload groups — logical groupings of sessions within a pool. A group can set request_max_memory_grant_percent (caps memory per request, preventing a single query from consuming the pool), max_dop, and importance. Built-in groups: internal and default.
3. Classifier function — a scalar UDF in master that Resource Governor calls for every new session. It returns the name of the workload group the session belongs to, based on SUSER_NAME(), APP_NAME(), HOST_NAME(), or connection properties.

The typical flow: create a pool for each workload class (for example, rp_Reporting, rp_TenantA), create a workload group inside each pool, write a classifier UDF that maps incoming sessions to the right group, and register the function with ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ClassifierFn).

Enabling and Configuring Resource Governor

Resource Governor is off by default. Enable with ALTER RESOURCE GOVERNOR RECONFIGURE. Changes to pools, groups, and the classifier function require a RECONFIGURE to take effect. To turn it off (and route all sessions to the default pool), ALTER RESOURCE GOVERNOR DISABLE. The classifier function runs on every login, so it must be fast and deterministic — a slow or non-deterministic classifier adds connection latency for every session.

MIN, MAX, and CAP_CPU_PERCENT

The three CPU settings differ in subtle ways the exam tests:

• MIN_CPU_PERCENT — guaranteed minimum CPU share when there is contention. The sum of all pools' minimums cannot exceed 100.
• MAX_CPU_PERCENT — the maximum share the pool can use when there is contention. With no contention, a pool can exceed its MAX (up to 100).
• CAP_CPU_PERCENT — a hard ceiling that applies even when there is no contention. Use CAP when you must guarantee a pool never exceeds a specific CPU share regardless of system load.

Memory works similarly with MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT, but there is no CAP equivalent for memory.

When to Use Resource Governor

The two canonical use cases:

• Tenant isolation — multi-tenant SaaS on a single SQL Server instance: each tenant's workload lands in its own pool with guaranteed minimums and capped maximums, so a busy tenant cannot starve the others.
• Runaway query protection — ad hoc or reporting workloads that occasionally produce a giant query: the workload group's request_max_memory_grant_percent caps the grant, and MAX_CPU_PERCENT limits the CPU share, protecting OLTP from being squeezed out.

Platform Limitations

Resource Governor is not available on Azure SQL Database; isolation there is done through elastic pool per-database min/max and service-tier sizing. On SQL Managed Instance, support is limited — CPU governance is available, but the classifier function has restrictions (no SQL CLR) and IO governance is not exposed. For full, production-grade workload isolation, the exam answer is SQL Server on Azure VMs.