Sqldbconfig Review

SQL Server Database Configuration Review Skill

Purpose

Detect instance and database configuration drift that degrades performance, causes instability, or creates security exposure. Applies 29 checks (B1–B29) across five categories:

• B1–B5 — Parallelism: MAXDOP alignment to NUMA topology, Cost Threshold for Parallelism at default, Optimize for Ad Hoc Workloads, query governor
• B6–B9 — Memory: Max Server Memory unconfigured, Min Server Memory, Lock Pages in Memory model, AWE (legacy 32-bit setting)
• B10–B18 — Database settings: auto-shrink, auto-close, compatibility level, RCSI, page verification, auto-statistics, Trustworthy, cross-DB chaining
• B19–B23 — File and storage: excessive VLF count, percent auto-growth on log and data files, Instant File Initialization, TempDB file count vs. scheduler count
• B24–B29 — Surface area: CLR, OLE Automation Procedures, Ad Hoc Distributed Queries, instance-level cross-DB chaining, remote admin connections, service-SID sysadmin membership (broken hardening)

Input

Accept any of:

• Output from EXEC sp_configure (all rows, or filtered to specific options)
• Output from SELECT … FROM sys.configurations (equivalent to sp_configure)
• Output from SELECT … FROM sys.databases (relevant columns — see capture query below)
• Output from SELECT … FROM sys.master_files (file growth columns)
• Output from SELECT … FROM sys.dm_os_sys_info (CPU, NUMA, scheduler counts)
• Output from SELECT … FROM sys.dm_db_log_info(db_id) or DBCC LOGINFO (VLF count)
• Output from SELECT … FROM sys.dm_server_services (Instant File Initialization status)
• Output from SELECT … FROM sys.server_principals (service-SID login presence and sysadmin membership — see capture query below)
• Combined paste of two or more of the above — apply all applicable checks
• A natural language description of symptoms ("auto-shrink keeps firing", "MAXDOP is 0 on a 4-NUMA server", "TempDB has 2 files on a 16-core box")

Recommended capture queries

-- 1. Instance configuration (sp_configure)
EXEC sp_configure;
-- Or via catalog view for scripting:
SELECT name, value, value_in_use, is_dynamic
FROM sys.configurations
ORDER BY name;

-- 2. Database settings
SELECT
    name,
    compatibility_level,
    is_auto_shrink_on,
    is_auto_close_on,
    is_read_committed_snapshot_on,
    page_verify_option_desc,
    is_auto_create_stats_on,
    is_auto_update_stats_on,
    is_trustworthy_on,
    is_db_chaining_on,
    recovery_model_desc,
    state_desc
FROM sys.databases
WHERE database_id > 4       -- exclude system databases from B10-B18 drift checks
   OR database_id IN (1,2,3,4);  -- include all for full picture

-- 3. File growth configuration
SELECT
    DB_NAME(database_id)    AS database_name,
    name                    AS logical_name,
    type_desc,
    size * 8 / 1024         AS size_mb,
    CASE is_percent_growth
        WHEN 1 THEN CAST(growth AS varchar) + '%'
        ELSE CAST(growth * 8 / 1024 AS varchar) + ' MB'
    END                     AS growth_setting,
    is_percent_growth,
    growth,
    max_size
FROM sys.master_files
ORDER BY database_id, type;

-- 4. CPU and NUMA topology
-- numa_node_count: number of NUMA nodes (physical CPU sockets + any soft-NUMA partitions)
-- scheduler_count: user schedulers = logical CPUs visible to SQL Server
-- SQL 2016+ MAXDOP guidance (multi-NUMA):
--   ≤ 16 logical processors per NUMA node → MAXDOP ≤ logical-per-NUMA-node
--   > 16 logical processors per NUMA node → MAXDOP = half(logical-per-NUMA-node), max 16
-- SQL 2014 and earlier: MAXDOP = logical-per-NUMA-node, max 8
-- On single-NUMA or single-socket systems B1/B3 do not fire
SELECT
    cpu_count,
    scheduler_count,
    numa_node_count,            -- SQL Server 2016 SP2+
    socket_count,               -- SQL Server 2016 SP2+
    cores_per_socket,           -- SQL Server 2016 SP2+
    sql_memory_model_desc       -- SQL Server 2012 SP4 / 2016 SP1+
FROM sys.dm_os_sys_info;

-- 5. VLF count per database (SQL Server 2016 SP2+)
SELECT
    DB_NAME(s.database_id)  AS database_name,
    COUNT(l.database_id)    AS vlf_count
FROM sys.databases AS s
CROSS APPLY sys.dm_db_log_info(s.database_id) AS l
GROUP BY s.database_id
ORDER BY vlf_count DESC;

-- 6. VLF count alternative: sys.dm_db_log_stats (SQL Server 2016 SP2+)
SELECT name, total_vlf_count
FROM sys.databases AS s
CROSS APPLY sys.dm_db_log_stats(s.database_id)
ORDER BY total_vlf_count DESC;

-- 7. Instant File Initialization status
SELECT servicename, instant_file_initialization_enabled
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server (%';

-- 8. Service-SID logins and their sysadmin membership (B29)
-- Expected present AND is_sysadmin = 1 for every row SQL Server Setup provisions.
-- Default instance: NT SERVICE\MSSQLSERVER, NT SERVICE\SQLSERVERAGENT, NT SERVICE\SQLWriter, NT SERVICE\Winmgmt
-- Named instance X: NT SERVICE\MSSQL$X, NT SERVICE\SQLAgent$X (+ shared SQLWriter, Winmgmt)
SELECT sp.name, sp.type_desc,
       IS_SRVROLEMEMBER('sysadmin', sp.name) AS is_sysadmin
FROM sys.server_principals AS sp
WHERE sp.name LIKE 'NT SERVICE\%'
ORDER BY sp.name;
-- A required login missing entirely, or present with is_sysadmin = 0 → B29 fires.
-- Catalog-view alternative (avoids IS_SRVROLEMEMBER NULL edge cases):
--   SELECT sp.name,
--          CASE WHEN rm.member_principal_id IS NULL THEN 0 ELSE 1 END AS in_sysadmin
--   FROM sys.server_principals AS sp
--   LEFT JOIN sys.server_role_members AS rm
--          ON rm.member_principal_id = sp.principal_id
--         AND rm.role_principal_id = (SELECT principal_id FROM sys.server_principals
--                                     WHERE name = 'sysadmin' AND type = 'R')
--   WHERE sp.name LIKE 'NT SERVICE\%';

> Fallback for older instances (pre-2016 SP2): Replace queries 5/6 with DBCC LOGINFO per database. Replace query 7 with ERRORLOG search: look for Database Instant File Initialization: enabled or disabled near server startup.

---

Thresholds Reference

| Check | Warning threshold | Critical threshold | |-------|------------------|--------------------| | B2 — Cost Threshold for Parallelism | = 5 (default, unchanged) | — | | B6 — Max Server Memory | configvalue = 0 (not set) | — | | B7 — Min Server Memory | configvalue > 0 | — | | B12 — Compatibility level | 1000 per database | > 5000 per database | | B20/B21 — Percent auto-growth | any percent growth on log or data | — | | B23 — TempDB file count | 1`

• Severity: Warning
• Fix: NUMA (Non-Uniform Memory Access) is a hardware architecture where each CPU socket has its own local memory bank. Accessing memory on a remote NUMA node is 2–3× slower than local access. When MAXDOP = 0, a single query can span all NUMA nodes. Apply the SQL Server 2016+ guidance: if ≤ 16 logical processors per NUMA node, set MAXDOP ≤ logical-per-node; if > 16 per node, set MAXDOP = half that count, max 16. For SQL 2014 and earlier the cap is 8. Example: 4-NUMA, 64 schedulers → 16 per node → MAXDOP ≤ 16 (8 is a common starting point for OLTP). Example: 2-NUMA, 64 schedulers → 32 per node → MAXDOP = 16 (half of 32, max 16). EXEC sp_configure 'max degree of parallelism', ; RECONFIGURE;

B2 — Cost Threshold for Parallelism at Default

• Trigger: sp_configure 'cost threshold for parallelism' config_value = 5
• Severity: Warning
• Fix: Per MS Learn the default of 5 is "a starting point, not a recommendation" and Microsoft does not publish a specific target value — raising it helps keep CPU-light OLTP queries on serial plans. The 25–50 (OLTP) / 45–75 (mixed) ranges below are community/operational heuristics, not MS-documented values. Increase in small increments and observe a full business cycle; e.g. start at 50 and tune down if needed: EXEC sp_configure 'cost threshold for parallelism', 50; RECONFIGURE;. Confirm direction with waits — CXPACKET/CXCONSUMER dominating suggests it's too low; SOS_SCHEDULER_YIELD dominating with under-parallelized heavy queries suggests too high.

B3 — MAXDOP Exceeds Per-NUMA CPU Count

• Trigger: max degree of parallelism config_value > (scheduler_count / numa_node_count) when numa_node_count > 1
• Severity: Warning
• Fix: Each NUMA node owns a local memory pool. When a parallel query uses more threads than fit within one NUMA node, allocations spill across nodes — paying the remote-access latency penalty on every page touch. Recalculate using the SQL Server 2016+ formula: logical-per-NUMA = scheduler_count / numa_node_count. If ≤ 16: MAXDOP ≤ logical-per-NUMA. If > 16: MAXDOP = half(logical-per-NUMA), max 16. SQL 2014 and earlier: max 8. EXEC sp_configure 'max degree of parallelism', ; RECONFIGURE;

B4 — Optimize for Ad Hoc Workloads Disabled

• Trigger: sp_configure 'optimize for ad hoc workloads' config_value = 0
• Severity: Warning
• Fix: Enable to avoid caching single-use plans that bloat the plan cache. EXEC sp_configure 'optimize for ad hoc workloads', 1; RECONFIGURE; Low risk, immediate benefit on OLTP servers.

B5 — Query Governor Not Configured

• Trigger: sp_configure 'query governor cost limit' config_value = 0 on instances with reported runaway queries
• Severity: Info
• Fix: Consider setting a cost limit to cap runaway queries. EXEC sp_configure 'query governor cost limit', 3600; RECONFIGURE; Only apply if runaway queries are a documented concern.

B6 — Max Server Memory Not Configured

• Trigger: sp_configure 'max server memory (MB)' config_value = 0
• Severity: Critical
• Fix: Set Max Server Memory so the OS and other processes retain headroom. The "leave 10–15% of RAM (or at least 4 GB)" rule used here is a simplified operational heuristic, not MS-documented — MS Learn gives more detailed, tiered guidance (reserve ~1 GB per 4 GB up to 16 GB, then ~1 GB per 8 GB beyond, plus allowances for thread stacks, other instances/services, and any LPIM/columnstore/XTP footprint). Example on a 64 GB single-instance box: EXEC sp_configure 'max server memory (MB)', 57344; RECONFIGURE;. An unconfigured instance will consume essentially all available RAM, causing OS paging (error 17890).

B7 — Min Server Memory Greater Than Zero

• Trigger: sp_configure 'min server memory (MB)' config_value > 0
• Severity: Warning
• Fix: Min Server Memory forces SQL Server to hold a floor of RAM even during low-load periods, starving other processes. Set to 0 unless there is a documented reason. EXEC sp_configure 'min server memory (MB)', 0; RECONFIGURE;

B8 — Lock Pages in Memory Active

• Trigger: sys.dm_os_sys_info.sql_memory_model_desc = 'LOCK_PAGES' (applies SQL Server 2012 SP4 / 2016 SP1+)
• Severity: Info
• Fix: LPIM prevents the OS from paging the buffer pool but can cause OS memory starvation on busy servers. Verify this is intentional and that Max Server Memory (B6) is correctly set. If LPIM is unintentional, remove the SE_LOCK_MEMORY privilege from the SQL Server service account and restart.

B9 — AWE Enabled on 64-Bit Instance

• Trigger: sp_configure 'awe enabled' config_value = 1
• Severity: Warning
• Fix: The awe enabled configuration option is a SQL Server 2005/2008-era, 32-bit-only switch for addressing memory above 4 GB via Address Windowing Extensions; on 64-bit instances the option is ignored (no effect), and it was removed entirely in SQL Server 2012 (11.x) — the option is absent from the sp_configure list in all later versions. So config_value = 1 means the instance is SQL Server 2008 R2 or earlier — set it off and, more importantly, plan to upgrade off an out-of-support version: EXEC sp_configure 'awe enabled', 0; RECONFIGURE;. Note: do not confuse this option with the AWE API, which is still used by 64-bit SQL Server as the "locked pages" mechanism when Lock Pages in Memory is granted (see B8) — this check targets only the obsolete config switch.

B10 — Auto-Shrink Enabled

• Trigger: sys.databases.is_auto_shrink_on = 1 on any database
• Severity: Critical
• Fix: Auto-shrink causes severe index fragmentation, repeated file-growth events, and IO spikes. Disable immediately: ALTER DATABASE [dbname] SET AUTO_SHRINK OFF; Then reclaim space manually using DBCC SHRINKFILE only if disk space is critically low.

B11 — Auto-Close Enabled

• Trigger: sys.databases.is_auto_close_on = 1 on any database
• Severity: Critical
• Fix: Auto-close evicts database resources (buffer pool, plan cache, worker threads) after the last connection closes and re-initialises them on next connection — causing latency spikes. Disable: ALTER DATABASE [dbname] SET AUTO_CLOSE OFF;

B12 — Compatibility Level Below SQL Server Version

• Trigger: `compatibility_level 100 VLFs as worth investigating ("can affect database startup, restore, and recovery time"), and severe symptoms appear at "several hundred thousand." The 1,000 / 5,000 cutoffs below are operational severity heuristics, not MS-documented thresholds** — treat > 100 as the documented review point.
• Severity: Info/Warning — VLF count > 100 (MS Learn review point) rising to Warning > 1000; Critical — VLF count > 5000 (heuristic)
• Fix: Excessive VLFs slow log backups, database recovery, and replication log reader. Shrink and regrow the log in one large step: (1) Take a log backup, (2) DBCC SHRINKFILE (logfilename, 1), (3) Expand to the correct size in one operation using ALTER DATABASE … MODIFY FILE (SIZE = target_mb MB, FILEGROWTH = 512 MB). A single growth of 8 GB creates 16 VLFs of 512 MB each.

B20 — Log File Using Percent Auto-Growth

• Trigger: sys.master_files: type = 1 AND is_percent_growth = 1 AND growth > 0
• Severity: Warning
• Fix: Percent growth on log files produces increasingly large VLF bursts and unpredictable growth events. Switch to a fixed MB increment: ALTER DATABASE [dbname] MODIFY FILE (NAME = logfilename, FILEGROWTH = 512MB);

B21 — Data File Using Percent Auto-Growth

• Trigger: sys.master_files: type = 0 AND is_percent_growth = 1 AND growth > 0
• Severity: Warning
• Fix: Percent growth on large data files causes enormous auto-grow events (e.g., 10% of a 1 TB file = 100 GB growth) that block sessions. Switch to a fixed MB increment: ALTER DATABASE [dbname] MODIFY FILE (NAME = datafilename, FILEGROWTH = 1024MB);

B22 — Instant File Initialization Not Enabled

• Trigger: sys.dm_server_services.instant_file_initialization_enabled = 'N' for the SQL Server service (column is nvarchar(1): 'Y' = enabled, 'N' = disabled; applies SQL 2012 SP4, SQL 2014 SP3, SQL 2016 SP1+)
• Severity: Warning
• Fix: Without IFI, SQL Server must zero-initialise new data file space before use, causing multi-second or multi-minute stalls during auto-growth events and RESTORE DATABASE. Grant the SQL Server service account the SE_MANAGE_VOLUME_NAME Windows privilege ("Perform volume maintenance tasks" in Local Security Policy), then restart the SQL Server service. IFI applies to data files at any version; for transaction log files it historically did not apply (logs were always zeroed), but starting with SQL Server 2022 (16.x) — all editions, plus Azure SQL Database/MI — transaction log autogrowth events up to 64 MB also benefit from IFI (growth events larger than 64 MB still zero, and the 64 MB log benefit does not require the SE_MANAGE_VOLUME_NAME privilege). Verify after restart: SELECT instant_file_initialization_enabled FROM sys.dm_server_services WHERE servicename LIKE 'SQL Server (%';

B23 — TempDB File Count Below Recommended

• Trigger: Count of TempDB data files (database_id = 2, type = 0 in sys.master_files) < MIN(sys.dm_os_sys_info.scheduler_count, 8)
• Severity: Warning
• Fix: Too few TempDB data files causes PFS/GAM/SGAM allocation page contention under concurrent load

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: vanterx
• Source: vanterx/mssql-performance-skills
• License: MIT

Install and usage instructions live in the source repository linked above.