SQL Server Configuration & Tuning Reference

-- 1. Enable advanced options (required for most settings)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- 2. Change a setting
EXEC sp_configure 'max server memory (MB)', 24576;
RECONFIGURE WITH OVERRIDE;  -- OVERRIDE only needed for settings that warn

-- 3. Verify change
SELECT name, value, value_in_use, minimum, maximum, description
FROM sys.configurations
WHERE name = 'max server memory (MB)';

-- 4. Most settings take effect immediately; a few require restart
-- Check is_dynamic: 1 = immediate, 0 = requires restart
SELECT name, is_dynamic, is_advanced
FROM sys.configurations
ORDER BY name;

Reserved for OS = MAX(10% of total RAM, minimum floor)

-- Query current physical memory and suggest max server memory
DECLARE @TotalPhysGB DECIMAL(10,2);
SELECT @TotalPhysGB = physical_memory_kb / 1024.0 / 1024.0
FROM sys.dm_os_sys_info;

DECLARE @OsReserveGB DECIMAL(10,2) =
    CASE
        WHEN @TotalPhysGB <= 4   THEN 1.0
        WHEN @TotalPhysGB <= 16  THEN 2.0
        WHEN @TotalPhysGB <= 64  THEN 4.0
        WHEN @TotalPhysGB <= 256 THEN 8.0
        ELSE 16.0
    END;

SELECT
    @TotalPhysGB                                 AS total_ram_gb,
    @OsReserveGB                                 AS os_reserve_gb,
    @TotalPhysGB - @OsReserveGB                  AS suggested_max_server_memory_gb,
    (@TotalPhysGB - @OsReserveGB) * 1024         AS suggested_max_server_memory_mb;

-- Example: 32 GB server, reserve 4 GB for OS
-- max server memory = 28 GB = 28672 MB
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'max server memory (MB)', 28672;
RECONFIGURE;

Per NUMA node: MAXDOP ≤ number of logical CPUs per NUMA node

Global MAXDOP:
  - ≤ 8 logical CPUs total:  MAXDOP = 0 (all CPUs)
  - 8–16 logical CPUs:       MAXDOP = 8
  - > 16 logical CPUs:       MAXDOP = logical CPUs per NUMA node (max 16)
  - Hyperthreading enabled:  Consider halving the per-node value

-- Determine NUMA topology and suggest MAXDOP
SELECT
    numa_node_id,
    COUNT(*) AS logical_cpus_per_node
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
  AND status = 'VISIBLE ONLINE'
GROUP BY numa_node_id
ORDER BY numa_node_id;

-- Also check current MAXDOP
SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'max degree of parallelism';

-- Example: 4-core server with no NUMA, set MAXDOP = 4
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

-- Override per-query
SELECT * FROM Orders WITH (INDEX = IX_OrderDate)
OPTION (MAXDOP 1);  -- force serial

-- Override at database level (SQL Server 2016+)
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2;
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = 1;

-- Override in Resource Governor (see Resource Governor section)

-- Queries in cache near current threshold where parallelism might flip
SELECT TOP 50
    qs.total_worker_time / qs.execution_count  AS avg_cpu_microseconds,
    qs.total_elapsed_time / qs.execution_count AS avg_duration_microseconds,
    qs.execution_count,
    qp.query_plan,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_worker_time / qs.execution_count BETWEEN 5000 AND 100000  -- 5ms–100ms CPU
ORDER BY qs.execution_count DESC;

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

-- DO NOT ENABLE
EXEC sp_configure 'lightweight pooling', 0;  -- 0 = thread mode (correct)

SELECT locked_page_allocations_kb
FROM sys.dm_os_process_memory;
-- Non-zero means LPIM is active and pages are locked

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

-- Before enabling: check how much of plan cache is single-use
SELECT
    objtype,
    COUNT(*) AS plan_count,
    SUM(size_in_bytes) / 1024 / 1024 AS size_mb,
    SUM(CAST(usecounts AS BIGINT)) AS total_uses
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY size_mb DESC;

-- More detail: what % are used only once
SELECT
    COUNT(*) AS total_plans,
    SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS single_use_plans,
    CAST(SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS FLOAT)
        / COUNT(*) * 100 AS pct_single_use
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc';

EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;

BACKUP DATABASE MyDB TO DISK = '...'
WITH COMPRESSION;    -- override to force compression

BACKUP DATABASE MyDB TO DISK = '...'
WITH NO_COMPRESSION; -- override to disable compression

-- Enable Database Mail
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;

-- Enable CLR (required for CLR objects)
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

-- CLR strict security (2017+) - do not disable
-- Requires SAFE/EXTERNAL_ACCESS assemblies to be signed
-- Even with clr strict security = 1, use certificate signing rather than TRUSTWORTHY
EXEC sp_configure 'clr strict security', 1;
RECONFIGURE;

-- Ad hoc distributed queries (OPENROWSET/OPENDATASOURCE)
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

-- Ole Automation (sp_OACreate) - avoid
EXEC sp_configure 'Ole Automation Procedures', 0;  -- keep disabled

-- xp_cmdshell - use only with proxy account, disable when not in use
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
-- Note: always configure a proxy account for xp_cmdshell (sp_xp_cmdshell_proxy_account)
-- Otherwise it runs as the SQL Server service account

External Pool (2016+, for R/Python external scripts)
     ↑
Internal Pool (SQL Server itself)
     ↑
[Pool 1] OLTP       [Pool 2] Reporting    [default pool]
     ↑                    ↑
[WG: web-apps]    [WG: reports]    [default workload group]
     ↑
[Classifier Function] → routes incoming sessions to workload groups

-- Step 1: Create resource pools
CREATE RESOURCE POOL OltpPool
WITH (
    MIN_CPU_PERCENT = 10,      -- guaranteed minimum CPU %
    MAX_CPU_PERCENT = 80,      -- hard cap: cannot exceed this
    MIN_MEMORY_PERCENT = 5,    -- reserved memory % (from pool budget)
    MAX_MEMORY_PERCENT = 70    -- max memory from SQL Server's budget
);

CREATE RESOURCE POOL ReportPool
WITH (
    MIN_CPU_PERCENT = 0,
    MAX_CPU_PERCENT = 40,
    MIN_MEMORY_PERCENT = 0,
    MAX_MEMORY_PERCENT = 40
);

-- Step 2: Create workload groups
CREATE WORKLOAD GROUP OltpGroup
WITH (
    IMPORTANCE = HIGH,
    MAX_DOP = 4,
    GROUP_MAX_REQUESTS = 200    -- concurrent request limit; 0 = unlimited
)
USING OltpPool;

CREATE WORKLOAD GROUP ReportGroup
WITH (
    IMPORTANCE = LOW,
    MAX_DOP = 8,
    REQUEST_MAX_CPU_TIME_SEC = 300,   -- kill query after 5 minutes CPU time
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 25
)
USING ReportPool;

-- Step 3: Create classifier function
CREATE FUNCTION dbo.rg_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @wg sysname = 'default';

    -- Route by application name
    IF APP_NAME() LIKE '%ReportServer%'
        SET @wg = 'ReportGroup';
    ELSE IF APP_NAME() IN ('MyWebApp', 'MobileAPI')
        SET @wg = 'OltpGroup';

    -- Route by login
    IF SUSER_SNAME() = 'etl_service_account'
        SET @wg = 'OltpGroup';

    RETURN @wg;
END;
GO

-- Step 4: Register classifier function
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);

-- Step 5: Activate all changes
ALTER RESOURCE GOVERNOR RECONFIGURE;

CREATE EXTERNAL RESOURCE POOL DataSciencePool
WITH (
    MAX_CPU_PERCENT = 20,
    MAX_MEMORY_PERCENT = 20,
    MAX_PROCESSES = 4
);

ALTER EXTERNAL RESOURCE POOL [default]
WITH (MAX_CPU_PERCENT = 10);

ALTER RESOURCE GOVERNOR RECONFIGURE;

-- Current pool statistics
SELECT
    pool_id, name,
    min_cpu_percent, max_cpu_percent,
    min_memory_percent, max_memory_percent,
    used_memory_kb, target_memory_kb,
    active_worker_count
FROM sys.dm_resource_governor_resource_pools;

-- Workload group statistics
SELECT
    g.name AS workload_group,
    p.name AS resource_pool,
    g.total_request_count,
    g.total_cpu_usage_ms,
    g.request_count,
    g.active_worker_count,
    g.total_queued_request_count
FROM sys.dm_resource_governor_workload_groups g
JOIN sys.dm_resource_governor_resource_pools p
    ON g.pool_id = p.pool_id;

-- Which sessions are in which workload group
SELECT
    s.session_id, s.login_name, s.program_name,
    g.name AS workload_group,
    p.name AS resource_pool,
    r.status, r.cpu_time, r.memory_usage * 8 AS memory_kb
FROM sys.dm_exec_sessions s
JOIN sys.dm_resource_governor_workload_groups g
    ON s.group_id = g.group_id
JOIN sys.dm_resource_governor_resource_pools p
    ON g.pool_id = p.pool_id
LEFT JOIN sys.dm_exec_requests r
    ON s.session_id = r.session_id
WHERE s.is_user_process = 1;

-- View current database-scoped settings
SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
ORDER BY name;

-- Common configurations
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = 2;

-- Legacy cardinality estimator for one database (all other DBs use new CE)
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

-- Disable parameter sniffing for this database
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;  -- use with caution

-- Disable Query Store hints for this database
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_STORE_QUERY_HINTS = OFF;

-- Accelerated database recovery (2019+ Enterprise)
ALTER DATABASE MyDB SET ACCELERATED_DATABASE_RECOVERY = ON;

-- Batch mode on rowstore (enable or disable IQP feature)
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;

-- DOP feedback (2022+)
ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON;

-- CE feedback (2022+)
ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = ON;

-- NUMA nodes
SELECT node_id, node_state_desc, memory_node_id,
       processor_group, cpu_count
FROM sys.dm_os_nodes
WHERE node_state_desc != 'ONLINE DAC';

-- Schedulers per node
SELECT scheduler_id, cpu_id, status, is_online, parent_node_id
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
ORDER BY parent_node_id, scheduler_id;

-- Memory per NUMA node
SELECT memory_node_id,
       virtual_address_space_reserved_kb / 1024 AS reserved_mb,
       virtual_address_space_committed_kb / 1024 AS committed_mb,
       locked_page_allocations_kb / 1024 AS locked_mb
FROM sys.dm_os_memory_nodes
WHERE memory_node_id < 64;  -- exclude internal nodes

-- Soft NUMA auto-configuration (2016+, ON by default for servers > 8 cores)
-- Check current state
SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'automatic soft-NUMA disabled';
-- 0 = auto soft-NUMA enabled (recommended)
-- 1 = disabled

-- Set MAXDOP = logical CPUs per soft-NUMA node for best parallelism control

-- Affinity mask is bitmask of CPUs SQL Server can use
-- Use SQL Server Configuration Manager (GUI) for large CPU counts
-- Only T-SQL for <= 64 CPUs:
EXEC sp_configure 'affinity mask', 255;  -- CPUs 0-7 only (binary 11111111)
RECONFIGURE;

-- View active trace flags
DBCC TRACESTATUS(-1);  -- all globally active flags

-- Enable trace flag globally (session)
DBCC TRACEON(4199, -1);   -- -1 means global scope

-- Disable trace flag globally
DBCC TRACEOFF(4199, -1);

-- All non-default configurations
SELECT
    name,
    value,
    value_in_use,
    minimum,
    maximum,
    description,
    is_dynamic,
    is_advanced
FROM sys.configurations
WHERE value != value_in_use  -- pending restart required
   OR (value != 0 AND name IN (
       'optimize for ad hoc workloads',
       'backup compression default',
       'backup checksum default',
       'max server memory (MB)',
       'max degree of parallelism',
       'cost threshold for parallelism'
   ))
ORDER BY name;

-- Settings changed but not yet in use (requires restart)
SELECT name, value AS configured_value, value_in_use AS current_value
FROM sys.configurations
WHERE value <> value_in_use
ORDER BY name;

-- Current RG classifier function
SELECT classifier_function_id, is_enabled
FROM sys.resource_governor_configuration;

-- Full RG topology
SELECT
    p.name AS pool,
    g.name AS workload_group,
    g.importance,
    g.max_dop,
    g.request_max_cpu_time_sec,
    g.request_max_memory_grant_percent,
    p.min_cpu_percent, p.max_cpu_percent,
    p.min_memory_percent, p.max_memory_percent
FROM sys.resource_governor_workload_groups g
JOIN sys.resource_governor_resource_pools p
    ON g.pool_id = p.pool_id
ORDER BY p.name, g.name;

-- Memory grant pending (queries waiting for memory)
SELECT * FROM sys.dm_exec_query_resource_semaphores;

-- Buffer pool usage by database
SELECT
    DB_NAME(database_id) AS db_name,
    COUNT(*) * 8 / 1024 AS buffer_pool_mb,
    COUNT(*) AS page_count
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4
GROUP BY database_id
ORDER BY page_count DESC;

-- Current memory clerks (top consumers)
SELECT TOP 20
    type,
    name,
    memory_node_id,
    pages_kb / 1024 AS mb_allocated
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC;

-- LPIM and page fault rates
SELECT
    physical_memory_in_use_kb / 1024 AS phys_mem_in_use_mb,
    locked_page_allocations_kb / 1024 AS lpim_locked_mb,
    page_fault_count
FROM sys.dm_os_process_memory;

-- Recommended initial configuration for a new SQL Server instance
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- 1. Memory: adjust MB value based on RAM calculation above
EXEC sp_configure 'max server memory (MB)', 28672;  -- example: 28 GB on 32 GB server

-- 2. MAXDOP: adjust based on CPU topology
EXEC sp_configure 'max degree of parallelism', 8;   -- example: 8-core server

-- 3. Parallelism threshold
EXEC sp_configure 'cost threshold for parallelism', 50;

-- 4. Plan cache efficiency
EXEC sp_configure 'optimize for ad hoc workloads', 1;

-- 5. Backup quality
EXEC sp_configure 'backup compression default', 1;
EXEC sp_configure 'backup checksum default', 1;

-- 6. Reduce error log noise
DBCC TRACEON(3226, -1);

RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

-- Prevent report queries from taking > 50% CPU
CREATE RESOURCE POOL ReportPool
WITH (MAX_CPU_PERCENT = 50, MAX_MEMORY_PERCENT = 40);

CREATE WORKLOAD GROUP ReportGroup
WITH (
    IMPORTANCE = LOW,
    MAX_DOP = 4,
    REQUEST_MAX_CPU_TIME_SEC = 120  -- kill any report query running > 2 min CPU
)
USING ReportPool;

CREATE FUNCTION dbo.rg_classifier()
RETURNS sysname WITH SCHEMABINDING
AS
BEGIN
    IF APP_NAME() LIKE '%SSRS%' OR APP_NAME() LIKE '%ReportServer%'
        RETURN 'ReportGroup';
    RETURN 'default';
END;
GO

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

-- Compare running config against expected values
DECLARE @expected TABLE (name sysname, expected_value sql_variant);
INSERT @expected VALUES
    ('max server memory (MB)', 28672),
    ('max degree of parallelism', 8),
    ('cost threshold for parallelism', 50),
    ('optimize for ad hoc workloads', 1),
    ('backup compression default', 1);

SELECT
    e.name,
    e.expected_value,
    c.value_in_use AS current_value,
    CASE WHEN e.expected_value = c.value_in_use THEN 'OK' ELSE 'DRIFT' END AS status
FROM @expected e
JOIN sys.configurations c ON c.name = e.name;