Resource Governor and Server Configuration

48.1 Key Instance Settings (sp_configure)

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 28672;        -- leave memory for the OS
EXEC sp_configure 'min server memory (MB)', 4096;
EXEC sp_configure 'max degree of parallelism', 8;
EXEC sp_configure 'cost threshold for parallelism', 50;   -- default 5 is too low
EXEC sp_configure 'optimize for ad hoc workloads', 1;
EXEC sp_configure 'backup compression default', 1;
EXEC sp_configure 'backup checksum default', 1;
EXEC sp_configure 'remote admin connections', 1;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
SELECT * FROM sys.configurations;

48.2 OS and Instance Best Practices

Instant File Initialization (grant "Perform volume maintenance tasks" to the service account)

Lock Pages in Memory (for large servers) Power plan set to High Performance

64 KB NTFS allocation unit size for data and log volumes Antivirus exclusions for data, log, and backup files

Trace flags (mostly built in since 2016: 1117/1118 behavior is the default)

48.3 Resource Governor (Enterprise)

Also: MIN/MAX_IOPS_PER_VOLUME, CAP_CPU_PERCENT, GROUP_MAX_REQUESTS, external resource pools (ML Services), tempdb space governance (2025).

CREATE RESOURCE POOL ReportPool WITH (MAX_CPU_PERCENT = 30, MAX_MEMORY_PERCENT = 30);
CREATE WORKLOAD GROUP ReportGroup WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 20) USING ReportPool;
GO
CREATE FUNCTION dbo.fn_RGClassifier() RETURNS SYSNAME WITH SCHEMABINDING
AS BEGIN
    RETURN CASE WHEN APP_NAME() LIKE '%Report%' THEN 'ReportGroup' ELSE 'default' END;
END
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_RGClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

48.4 Database-Scoped Configuration

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;
SELECT * FROM sys.database_scoped_configurations;