| Severity | Category | Finding | Current | Recommended | Action |
|---|---|---|---|---|---|
| Warning | Database | Query Store disabled on some databases Query Store is essential for performance troubleshooting and regression analysis. |
16 of 21 database(s) affected | Enabled | Enable Query Store on production databases (READ_WRITE). DBA_DB FabrikamWorker GlobalCallCenterDashboard T-DealerPortal T-FieldData T-FleetDB T-FormsMT T-HelpdeskDB T-LedgerDB t-HelpdeskAcq t-HelpdeskPF t-fabrikamPayAcq t-fabrikamPayAcqKyc t-fabrikamPayCMS t-fabrikamPayMonitoring t-telemetry |
| Info | Database | ADR disabled on some databases ADR speeds up recovery and long-running transaction rollback, but changes version-store behavior. |
21 of 21 database(s) affected | Enabled | Evaluate and test before enabling, especially on large databases. AdventureWorksLT DBA_DB FabrikamWorker GlobalCallCenterDashboard T-DealerPortal T-FieldData T-FleetDB T-FormsMT T-HelpdeskDB T-LedgerDB WideWorldImporters sample_test_db t-HelpdeskAcq t-HelpdeskPF t-fabrikamPayAcq t-fabrikamPayAcqKyc t-fabrikamPayCMS t-fabrikamPayMonitoring t-fabrikamPayMrc t-fabrikamPayPayFac t-telemetry |
| Info | Database | RCSI disabled on some databases RCSI can dramatically reduce reader/writer blocking for OLTP workloads. |
18 of 21 database(s) affected | Enabled | Evaluate enabling RCSI; validate application compatibility first. AdventureWorksLT DBA_DB FabrikamWorker GlobalCallCenterDashboard T-DealerPortal T-FieldData T-FleetDB T-FormsMT T-HelpdeskDB T-LedgerDB WideWorldImporters t-HelpdeskAcq t-HelpdeskPF t-fabrikamPayAcq t-fabrikamPayAcqKyc t-fabrikamPayCMS t-fabrikamPayMonitoring t-telemetry |
| Info | Database | Recovery model overview Confirm each database's recovery model (FULL / SIMPLE / BULK_LOGGED) matches its backup strategy and RPO. |
FULL×8, SIMPLE×13 | Align with business RPO | Databases in SIMPLE cannot do point-in-time restore; ensure that is intentional. FULL: 8 (DBA_DB, T-DealerPortal, T-FieldData, T-HelpdeskDB, t-HelpdeskAcq, t-HelpdeskPF...) SIMPLE: 13 (AdventureWorksLT, FabrikamWorker, GlobalCallCenterDashboard, T-FleetDB, T-FormsMT, T-LedgerDB...) |
| Info | Instance | Backup Compression not enabled by default Uncompressed backups use more storage and I/O. |
Disabled | Enabled | Enable unless CPU resources are severely constrained. |
| Info | Instance | Lock Pages in Memory not enabled The OS can trim SQL Server's working set under memory pressure. |
Conventional | LOCK_PAGES (dedicated instances) | Review enabling LPIM for dedicated production instances (grant 'Lock pages in memory' to the service account). |
| Info | Instance | Optimize for Ad Hoc Workloads disabled Single-use ad hoc plans can bloat the plan cache. |
Disabled | Enabled | Consider enabling if you see many single-use plans in the cache. |
| Info | Instance | Remote Dedicated Admin Connection (DAC) disabled Without remote DAC you cannot connect for emergency troubleshooting when the instance is unresponsive. |
Disabled | Enabled | Enabling aids emergency access; weigh against your hardening policy (it widens the admin surface area). |
| Info | Storage | Items requiring manual (OS-level) verification A few checklist items cannot be reliably read through SQL Server and must be checked on the host. |
Not verifiable from T-SQL | Verify on the server OS. Power Plan = High Performance on dedicated SQL Server hosts Volume Allocation Unit Size = 64 KB for SQL data and log volumes |
|
| Pass | Database | AUTO_CREATE_STATISTICS enabled everywhere Auto Create Statistics is configured as recommended on all user databases. |
All 21 database(s) | ||
| Pass | Database | AUTO_UPDATE_STATISTICS enabled everywhere Auto Update Statistics is configured as recommended on all user databases. |
All 21 database(s) | ||
| Pass | Database | PAGE_VERIFY = CHECKSUM everywhere All user databases use CHECKSUM page verification. |
CHECKSUM | ||
| Pass | Database | Query Store is capturing on all enabled databases Every database with Query Store switched on is actively recording and has headroom below its size limit. |
5 database(s) READ_WRITE | ||
| Pass | Instance | Cost Threshold for Parallelism Cost threshold has been raised above the default. |
50 | ||
| Pass | Instance | Instant File Initialization enabled Data file growth and restores skip zero-initialization. |
Enabled | ||
| Pass | Instance | MAXDOP MAXDOP is within the topology-based upper bound (4). |
4 | ||
| Pass | Instance | Max Server Memory is capped A memory cap is configured, leaving headroom for the OS. |
10,240 MB | ||
| Pass | Monitoring | Deadlock capture (system_health) is running The always-on system_health Extended Events session is active and records deadlock graphs by default. |
system_health session running | ||
| Pass | Storage | File growth settings User database files use sensible fixed-size growth increments. |
Fixed, adequate increments | ||
| Pass | Storage | TempDB data file count TempDB data file count is reasonable for the core count. |
4 data file(s) |