/sqldbconfig-review

name: sqldbconfig-review
description: Analyze SQL Server instance and database configuration drift against proven DBA best practices. Applies 29 checks (B1–B29) across five categories: parallelism tuning (MAXDOP, Cost Threshold for Parallelism, Optimize for Ad Hoc Workloads), memory configuration (Max Server Memory, Lock Pages in Memory), database-level settings (auto-shrink, auto-close, compatibility level, RCSI, page verification, statistics, Trustworthy, cross-DB chaining), file and storage configuration (VLF count, percent auto-growth, Instant File Initialization, TempDB file count), and surface area exposure (CLR, OLE Automation, Ad Hoc Distributed Queries, service-SID sysadmin membership). Use this skill when the server behaves erratically after changes, a new instance needs a configuration audit, or silent misconfiguration is suspected as a root cause of performance or stability problems. Trigger when pasting output from sp_configure, sys.databases, sys.master_files, sys.dm_os_sys_info, sys.dm_db_log_info, or sys.server_principals.
triggers:
  - /sqldbconfig-review
  - /dbconfig-review
  - /config-audit

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\S