SQL Server Review
Review SQL Server for execution plans, index tuning, tempdb, Always On AG, Query Store, and security.
Key Checks
- Actual execution plans — flag Key Lookup, Hash Match spills, Implicit Conversion warnings
- Clustered index: narrow, unique, ever-increasing; nonclustered columnstore for analytics
- sys.dm_db_missing_index_details hints, dm_db_index_usage_stats for unused indexes
- tempdb: multiple data files (up to 8), pre-sized to avoid auto-growth
- Always On: synchronous-commit same DC only, async for cross-region
- Query Store enabled, regressed query identification, plan forcing for parameter sniffing
- Parameter sniffing: OPTION (OPTIMIZE FOR UNKNOWN) or statement-level RECOMPILE
- AUTO_UPDATE_STATISTICS ON, FULLSCAN after large data loads
- DMV monitoring: dm_exec_requests, dm_os_wait_stats, dm_io_virtual_file_stats
- sa disabled, TRUSTWORTHY OFF, Always Encrypted for PII columns
Output
Severity-sorted: CRITICAL, HIGH, MEDIUM, LOW, PASSED.
1---2name: sqlserver-review3description: Review SQL Server schemas, queries, and configuration for performance and production readiness4agent: database-reviewer5argument-hint: "<file-or-query-or-schema>"6tags: [database, sqlserver, mssql, sql, performance, indexing]7user-invocable: true8---910# SQL Server Review1112Review SQL Server for execution plans, index tuning, tempdb, Always On AG, Query Store, and security.1314## Key Checks15- Actual execution plans — flag Key Lookup, Hash Match spills, Implicit Conversion warnings16- Clustered index: narrow, unique, ever-increasing; nonclustered columnstore for analytics17- sys.dm_db_missing_index_details hints, dm_db_index_usage_stats for unused indexes18- tempdb: multiple data files (up to 8), pre-sized to avoid auto-growth19- Always On: synchronous-commit same DC only, async for cross-region20- Query Store enabled, regressed query identification, plan forcing for parameter sniffing21- Parameter sniffing: OPTION (OPTIMIZE FOR UNKNOWN) or statement-level RECOMPILE22- AUTO_UPDATE_STATISTICS ON, FULLSCAN after large data loads23- DMV monitoring: dm_exec_requests, dm_os_wait_stats, dm_io_virtual_file_stats24- sa disabled, TRUSTWORTHY OFF, Always Encrypted for PII columns2526## Output27Severity-sorted: CRITICAL, HIGH, MEDIUM, LOW, PASSED.
ERP-CORE-DEV