Analyze SQL Server instance and database configuration drift against proven DBA best practices. Applies 28 checks (B1–B28) 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). 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, or sys.dm_db_log_info.
How this skill is triggered — by the user, by Claude, or both
Slash command
/mssql-performance-skills:sqldbconfig-reviewThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Detect instance and database configuration drift that degrades performance, causes instability, or creates security exposure. Applies 28 checks (B1–B28) across five categories:
Detect instance and database configuration drift that degrades performance, causes instability, or creates security exposure. Applies 28 checks (B1–B28) across five categories:
Accept any of:
EXEC sp_configure (all rows, or filtered to specific options)SELECT … FROM sys.configurations (equivalent to sp_configure)SELECT … FROM sys.databases (relevant columns — see capture query below)SELECT … FROM sys.master_files (file growth columns)SELECT … FROM sys.dm_os_sys_info (CPU, NUMA, scheduler counts)SELECT … FROM sys.dm_db_log_info(db_id) or DBCC LOGINFO (VLF count)SELECT … FROM sys.dm_server_services (Instant File Initialization status)-- 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 (%';
Fallback for older instances (pre-2016 SP2): Replace queries 5/6 with
DBCC LOGINFOper database. Replace query 7 with ERRORLOG search: look forDatabase Instant File Initialization: enabledordisablednear server startup.
| 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) | — |
sp_configure 'max degree of parallelism' config_value = 0 AND sys.dm_os_sys_info.numa_node_count > 1EXEC sp_configure 'max degree of parallelism', <value>; RECONFIGURE;sp_configure 'cost threshold for parallelism' config_value = 5EXEC sp_configure 'cost threshold for parallelism', 50; RECONFIGURE;max degree of parallelism config_value > (scheduler_count / numa_node_count) when numa_node_count > 1scheduler_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;sp_configure 'optimize for ad hoc workloads' config_value = 0EXEC sp_configure 'optimize for ad hoc workloads', 1; RECONFIGURE; Low risk, immediate benefit on OLTP servers.sp_configure 'query governor cost limit' config_value = 0 on instances with reported runaway queriesEXEC sp_configure 'query governor cost limit', 3600; RECONFIGURE; Only apply if runaway queries are a documented concern.sp_configure 'max server memory (MB)' config_value = 0EXEC sp_configure 'max server memory (MB)', 57344; RECONFIGURE; An unconfigured instance will consume all available RAM, causing OS paging.sp_configure 'min server memory (MB)' config_value > 0EXEC sp_configure 'min server memory (MB)', 0; RECONFIGURE;sys.dm_os_sys_info.sql_memory_model_desc = 'LOCK_PAGES' (applies SQL Server 2012 SP4 / 2016 SP1+)SE_LOCK_MEMORY privilege from the SQL Server service account and restart.sp_configure 'awe enabled' config_value = 1EXEC sp_configure 'awe enabled', 0; RECONFIGURE;sys.databases.is_auto_shrink_on = 1 on any databaseALTER DATABASE [dbname] SET AUTO_SHRINK OFF; Then reclaim space manually using DBCC SHRINKFILE only if disk space is critically low.sys.databases.is_auto_close_on = 1 on any databaseALTER DATABASE [dbname] SET AUTO_CLOSE OFF;compatibility_level < (SERVERPROPERTY('ProductMajorVersion') * 10) for any user databaseALTER 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].sys.databases.is_read_committed_snapshot_on = 0 on user databases with READ_COMMITTED isolation level workloadsALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT ON; (requires brief exclusive access to the database).sys.databases.page_verify_option_desc ≠ 'CHECKSUM' on any databaseALTER DATABASE [dbname] SET PAGE_VERIFY CHECKSUM;sys.databases.is_auto_create_stats_on = 0ALTER DATABASE [dbname] SET AUTO_CREATE_STATISTICS ON;sys.databases.is_auto_update_stats_on = 0ALTER DATABASE [dbname] SET AUTO_UPDATE_STATISTICS ON; If disabled deliberately for large tables, implement a manual statistics update job.sys.databases.is_trustworthy_on = 1 on any database except msdb (where it is expected ON by SQL Server)ALTER DATABASE [dbname] SET TRUSTWORTHY OFF;sys.databases.is_db_chaining_on = 1 on user databasesALTER DATABASE [dbname] SET DB_CHAINING OFF;sys.dm_db_log_info or DBCC LOGINFO)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.sys.master_files: type = 1 AND is_percent_growth = 1 AND growth > 0ALTER DATABASE [dbname] MODIFY FILE (NAME = logfilename, FILEGROWTH = 512MB);sys.master_files: type = 0 AND is_percent_growth = 1 AND growth > 0ALTER DATABASE [dbname] MODIFY FILE (NAME = datafilename, FILEGROWTH = 1024MB);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+)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 does not apply to log files (they always require zeroing). Verify after restart: SELECT instant_file_initialization_enabled FROM sys.dm_server_services WHERE servicename LIKE 'SQL Server (%';database_id = 2, type = 0 in sys.master_files) < MIN(sys.dm_os_sys_info.scheduler_count, 8)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.sp_configure 'clr enabled' config_value = 1SELECT COUNT(*) FROM sys.assemblies WHERE is_user_defined = 1 = 0), disable: EXEC sp_configure 'clr enabled', 0; RECONFIGURE;sp_configure 'Ole Automation Procedures' config_value = 1sp_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;sp_configure 'Ad Hoc Distributed Queries' config_value = 1OPENROWSET 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.sp_configure 'cross db ownership chaining' config_value = 1EXEC sp_configure 'cross db ownership chaining', 0; RECONFIGURE;sp_configure 'remote admin connections' config_value = 0EXEC sp_configure 'remote admin connections', 1; RECONFIGURE;## 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]*
| 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 |
mssql-performance-review | Dispatches to this skill when artifact type is dbconfig |
npx claudepluginhub vanterx/mssql-performance-skills --plugin mssql-performance-skillsProvides CDSS development patterns for drug interaction checking, dose validation, clinical scoring (NEWS2, qSOFA), and alert classification integrated into EMR workflows.