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.

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  )  
                          growth_setting,
    is_percent_growth,
    growth,
    max_size
 sys.master_files
  database_id, type;










    cpu_count,
    scheduler_count,
    numa_node_count,            
    socket_count,               
    cores_per_socket,           
    sql_memory_model_desc       
 sys.dm_os_sys_info;



    DB_NAME(s.database_id)   database_name,
    (l.database_id)     vlf_count
 sys.databases  s
 APPLY sys.dm_db_log_info(s.database_id)  l
  s.database_id
  vlf_count ;


 name, total_vlf_count
 sys.databases  s
 APPLY sys.dm_db_log_stats(s.database_id)
  total_vlf_count ;


 servicename, instant_file_initialization_enabled
 sys.dm_server_services
 servicename  ;





 sp.name, sp.type_desc,
       IS_SRVROLEMEMBER(, sp.name)  is_sysadmin
 sys.server_principals  sp
 sp.name  
  sp.name;

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	config_value = 0 (not set)	—
B7 — Min Server Memory	config_value > 0	—
B12 — Compatibility level	< current SQL version × 10	—
B19 — VLF count	> 1000 per database	> 5000 per database
B20/B21 — Percent auto-growth	any percent growth on log or data	—
B23 — TempDB file count	< MIN(scheduler_count, 8)	—

---

Checks

B1 — MAXDOP = 0 on Multi-NUMA Instance

• Trigger: sp_configure 'max degree of parallelism' config_value = 0 AND sys.dm_os_sys_info.numa_node_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', <value>; 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', <value>; 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 < (SERVERPROPERTY('ProductMajorVersion') * 10) for any user database
• Severity: Warning
• Fix: Running an older compatibility level prevents the Query Optimizer from using newer cardinality estimator improvements, IQP features, and modern T-SQL syntax. Test workload at current level, then: ALTER DATABASE [dbname] SET COMPATIBILITY_LEVEL = 160; (for SQL 2022). Valid values: 80, 90, 100, 110, 120, 130, 140, 150, 160, 170 [Unverified — 170 pending future SQL Server release; SQL 2022 currently has level 160 as highest].

B13 — RCSI Not Enabled

• Trigger: sys.databases.is_read_committed_snapshot_on = 0 on user databases with READ_COMMITTED isolation level workloads
• Severity: Warning
• Fix: Without RCSI, READ COMMITTED readers block on writers and vice versa. Enable RCSI to eliminate most reader-writer blocking at the cost of tempdb version store space: ALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT ON; (requires brief exclusive access to the database).

B14 — Page Verification Not CHECKSUM

• Trigger: sys.databases.page_verify_option_desc ≠ 'CHECKSUM' on any database
• Severity: Warning
• Fix: CHECKSUM page verification detects storage corruption before it causes data loss. NONE and TORN_PAGE_DETECTION provide weaker or no protection. ALTER DATABASE [dbname] SET PAGE_VERIFY CHECKSUM;

B15 — Auto-Create Statistics Disabled

• Trigger: sys.databases.is_auto_create_stats_on = 0
• Severity: Warning
• Fix: Without auto-create statistics, the Query Optimizer may use poor cardinality estimates on unindexed columns. Re-enable unless a controlled manual statistics strategy is documented: ALTER DATABASE [dbname] SET AUTO_CREATE_STATISTICS ON;

B16 — Auto-Update Statistics Disabled

• Trigger: sys.databases.is_auto_update_stats_on = 0
• Severity: Warning
• Fix: Stale statistics cause cardinality estimation errors that produce bad query plans. Re-enable: ALTER DATABASE [dbname] SET AUTO_UPDATE_STATISTICS ON; If disabled deliberately for large tables, implement a manual statistics update job.

B17 — Trustworthy Enabled on User Database

• Trigger: sys.databases.is_trustworthy_on = 1 on any database except msdb (where it is expected ON by SQL Server)
• Severity: Warning
• Fix: Trustworthy allows modules in the database to impersonate server-level principals if the database owner is a sysadmin. Disable unless Service Broker cross-database messaging or EXTERNAL_ACCESS assemblies require it: ALTER DATABASE [dbname] SET TRUSTWORTHY OFF;

B18 — Cross-Database Ownership Chaining at Database Level

• Trigger: sys.databases.is_db_chaining_on = 1 on user databases
• Severity: Warning
• Fix: Per-database chaining allows ownership chain traversal across databases when both have chaining enabled. Disable unless cross-database views or procedures explicitly require it: ALTER DATABASE [dbname] SET DB_CHAINING OFF;

B19 — Excessive VLF Count

• Trigger: High VLF count per database (via sys.dm_db_log_info or DBCC LOGINFO). MS Learn's own sys.dm_db_log_info example flags > 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. Add files up to MIN(scheduler_count, 8), all equal in size and with equal fixed MB growth: ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'D:\tempdb\tempdev2.ndf', SIZE = 4096MB, FILEGROWTH = 512MB); All TempDB files must be the same size to enable proportional fill.

B24 — CLR Enabled

• Trigger: sp_configure 'clr enabled' config_value = 1
• Severity: Info
• Fix: CLR integration allows .NET assemblies to run inside SQL Server. If no CLR objects exist (SELECT COUNT(*) FROM sys.assemblies WHERE is_user_defined = 1 = 0), disable: EXEC sp_configure 'clr enabled', 0; RECONFIGURE;

B25 — OLE Automation Procedures Enabled

• Trigger: sp_configure 'Ole Automation Procedures' config_value = 1
• Severity: Warning
• Fix: OLE Automation (sp_OACreate, sp_OAMethod) exposes COM objects to T-SQL and is a significant attack surface. Disable unless actively used: EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE;

B26 — Ad Hoc Distributed Queries Enabled

• Trigger: sp_configure 'Ad Hoc Distributed Queries' config_value = 1
• Severity: Warning
• Fix: Ad Hoc Distributed Queries enables OPENROWSET and OPENDATASOURCE for arbitrary remote data access. Disable if not actively used: EXEC sp_configure 'Ad Hoc Distributed Queries', 0; RECONFIGURE; Use linked servers with controlled permissions instead.

B27 — Instance-Level Cross-Database Ownership Chaining Enabled

• Trigger: sp_configure 'cross db ownership chaining' config_value = 1
• Severity: Warning
• Fix: Instance-level chaining enables ownership chain traversal across all databases on the server, including system databases. Disable and use per-database chaining only where required: EXEC sp_configure 'cross db ownership chaining', 0; RECONFIGURE;

B28 — Remote Admin Connection Disabled

• Trigger: sp_configure 'remote admin connections' config_value = 0
• Severity: Info
• Fix: Without remote admin connections enabled, the Dedicated Administrator Connection (DAC) is only accessible from the server console. On named instances or clustered/containerised deployments, enable to allow remote DAC access for emergency diagnostics: EXEC sp_configure 'remote admin connections', 1; RECONFIGURE;

B29 — Service-SID Login Missing from sysadmin

• Trigger: A per-service-SID login that SQL Server Setup provisions is absent from sys.server_principals, OR present with IS_SRVROLEMEMBER('sysadmin', name) = 0. Required set — 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 (note SQLAgent$, not SQLSERVERAGENT), plus the instance-unaware NT SERVICE\SQLWriter and NT SERVICE\Winmgmt. Applies SQL Server 2012+ (per-service-SID era), Windows only; N/A for Azure SQL Database / Managed Instance.
• Severity: Critical
• Fix: Inverse polarity from the other surface-area checks — this is a broken hardening, not excess exposure. These service SIDs are how the Database Engine and SQL Agent services connect to the instance itself; dropping the login or removing it from sysadmin breaks service startup, SQL Agent connectivity, and future Setup/patching. Do not "fix" a flagged row by removing the login — restore it. Recreate if missing and re-add to the role: CREATE LOGIN [NT SERVICE\MSSQLSERVER] FROM WINDOWS; then ALTER SERVER ROLE sysadmin ADD MEMBER [NT SERVICE\MSSQLSERVER]; (repeat for SQLSERVERAGENT/SQLWriter/Winmgmt, or MSSQL$X/SQLAgent$X on a named instance). These logins are created and set to sysadmin by Setup regardless of whether the service runs under a virtual account, domain account, or gMSA — the assigned startup account governs external resource access; the service SID governs the internal self-connection. Always change the startup account via SQL Server Configuration Manager (not the Windows Services applet), which maintains the service-SID ACLs and local-group membership. Since SQL Server 2016, a gMSA is a supported FCI startup account (virtual accounts are not, because their SID differs per node).

---

Output Format

## SQL Server Configuration Review

### Summary
- X Critical, Y Warnings, Z Info
- Highest-risk finding: [check name and ID]
- Databases affected: [list]

### Critical Issues   ([C1], [C2], ...)
**[C1] Auto-Shrink Enabled (B10)**
- Observed: is_auto_shrink_on = 1 on databases: SalesDB, ReportDB
- Impact: Repeated shrink-and-grow cycles fragment indexes and cause IO spikes
- Fix: ALTER DATABASE [SalesDB] SET AUTO_SHRINK OFF;

### Warnings          ([W1], [W2], ...)
**[W1] Max Server Memory Not Configured (B6)**
- Observed: config_value = 0 (value_in_use = 2147483647 MB)
- Impact: SQL Server will consume all available RAM, causing OS paging
- Fix: EXEC sp_configure 'max server memory (MB)', 57344; RECONFIGURE;

### Info              ([I1], [I2], ...)

### Configuration Summary Table
| Category | Setting | Current | Recommended | Check |
|----------|---------|---------|-------------|-------|

### Passed Checks
(List check IDs that were explicitly verified clean)

---
*Analyzed by: [state the AI model and version you are running as, e.g. "Claude Sonnet 4.6"] · [current date and time in the user's local timezone, or UTC if timezone is unknown]*

---

Companion Skills

Skill	Relationship
sqlmemory-review (O)	B6 Max Server Memory and B8 LPIM are root causes for O20 and O19 findings — run /sqlmemory-review to see the downstream memory pressure
sqlwait-review (V)	B19 excessive VLFs → V34 log write stalls; B23 TempDB files → V30–V32 TempDB contention waits
sqldiskio-review (Z)	B20/B21 percent auto-growth → Z7–Z9 auto-growth event stalls — run /sqldiskio-review to quantify the file growth impact
sqlplan-review (S/N)	B1–B3 MAXDOP misconfiguration → N44–N47 excessive parallelism in plans — cross-reference plan operators
sqlmigration-review (Y)	Dispatches configuration-drift findings here when comparing source/target instance settings (MAXDOP, compatibility level, TempDB layout) ahead of a migration
mssql-performance-review	Dispatches to this skill when artifact type is dbconfig