Maintenance

The 33 checks in the “Maintenance” category of a SQL Server audit: what each one verifies, its severity and the versions covered.

Checks in this category
33
Weight in the score
14
Breakdown by severity
1 critical · 4 high · 11 medium · 7 low · 10 info

Checks in this category

  • MAINT001

    Recent DBCC CHECKDB

    Critical

    CHECKDB should be run regularly (< 7 days)

    Versions : 2012-2025

  • MAINT007

    SHRINK Operations

    High

    SHRINK operations cause fragmentation

    Versions : 2012-2025

  • MS001

    Ola Hallengren solution installed

    High

    Checks for the Ola Hallengren procedures (IndexOptimize, DatabaseBackup, DatabaseIntegrityCheck, CommandExecute) and the CommandLog table in master.

    Versions : 2012-2025

  • MS002

    Ola jobs scheduled & successful

    High

    Checks that Ola SQL Agent jobs are enabled, scheduled, and that at least one succeeded within the last 14 days.

    Versions : 2012-2025

  • MS003

    Maintenance solution present

    High

    Checks that at least one maintenance solution exists (Ola procedures or msdb maintenance plans); none means no automated index/CHECKDB/backup maintenance.

    Versions : 2012-2025

  • CCI001

    Columnstore deleted-row fragmentation

    Medium

    Detects compressed columnstore rowgroups with more than 20% deleted rows (rebuild/reorg candidates).

    Versions : 2014-2025

  • IDX001

    Duplicate / overlapping indexes

    Medium

    Detects indexes sharing identical leading key columns, wasting storage and slowing writes.

    Versions : 2012-2025

  • IDX003

    Write-only indexes

    Medium

    Detects indexes with no reads but heavy updates since the last restart, which are pure overhead.

    Versions : 2012-2025

  • IDX004

    FK without supporting index

    Medium

    Detects foreign keys whose parent columns lack a leading index, causing scans and blocking on parent updates/deletes.

    Versions : 2012-2025

  • IDX005

    Heaps with forwarded records

    Medium

    Detects clustered-index-less tables (heaps) accumulating forwarded records, which cause extra I/O per lookup (top 5 databases).

    Versions : 2012-2025

  • IDX007

    Untrusted foreign keys

    Medium

    Detects enabled FKs with is_not_trusted=1 that the optimizer ignores and that no longer guarantee referential integrity.

    Versions : 2012-2025

  • IDX010

    High-impact missing indexes

    Medium

    Aggregates high-impact missing-index suggestions (cost x impact x scans/seeks); these are candidates to review, not literal CREATE INDEX statements.

    Versions : 2012-2025

  • MAINT002

    Index Fragmentation

    Medium

    Fragmented indexes (>30%) degrade performance

    Versions : 2012-2025

  • MAINT009

    Recent risky DBCC commands (default trace)

    Medium

    Read-only inspection of the default trace (fn_trace_gettable, EventClass 116 = Audit DBCC) for cache-flushing DBCC (FREEPROCCACHE, DROPCLEANBUFFERS, FREESYSTEMCACHE) and direct-write DBCC (WRITEPAGE). DBCC command names are invariant T-SQL keywords, so matching TextData stays language-neutral.

    Versions : 2012-2025

  • STAT002

    Low sample-rate statistics

    Medium

    Detects large-table statistics sampled below 25%, producing skewed histograms.

    Versions : 2012-2025

  • STAT003

    Stale statistics (modification counter)

    Medium

    Detects large-table statistics where more than 20% of rows changed since the last update.

    Versions : 2012-2025

  • CCI002

    Too many small rowgroups

    Low

    Detects many under-filled compressed rowgroups (< 100K rows, ideal ~1,048,576).

    Versions : 2014-2025

  • CCI003

    Uncompressed delta rowgroups

    Low

    Detects delta rowgroups (OPEN/CLOSED) not yet compressed, scanned as row-store row-by-row.

    Versions : 2014-2025

  • IDX008

    Untrusted CHECK constraints

    Low

    Detects enabled CHECK constraints with is_not_trusted=1 that the optimizer cannot use for plan simplification.

    Versions : 2012-2025

  • IDX009

    Disabled indexes present

    Low

    Detects disabled indexes (is_disabled=1) that consume metadata and often indicate forgotten maintenance.

    Versions : 2012-2025

  • MAINT006

    Heap Tables

    Low

    Tables without clustered index can cause issues

    Versions : 2012-2025

  • OPS007

    msdb backup history purged

    Low

    Measures the age of the oldest msdb.dbo.backupset entry; unpurged history bloats msdb and slows backups.

    Versions : 2012-2025

  • STAT004

    NORECOMPUTE statistics

    Low

    Detects statistics flagged NORECOMPUTE, which lose automatic updates and go stale.

    Versions : 2012-2025

  • CCI004

    Columnstore on small tables

    Info

    Detects columnstore indexes on tables under 1M rows, where the benefit is marginal.

    Versions : 2012-2025

  • IDX002

    Wide indexes (key columns)

    Info

    Lists indexes with more than 5 key columns, which are large and rarely fully seekable.

    Versions : 2012-2025

  • IDX006

    Low fill factor indexes

    Info

    Detects indexes with a fill factor between 1 and 80, which waste disk space and buffer pool memory.

    Versions : 2012-2025

  • MAINT003

    Stale Statistics

    Info

    Statistics should be updated regularly

    Versions : 2012-2025

  • MAINT004

    Unused Indexes

    Info

    Unused indexes consume space and slow down writes

    Versions : 2012-2025

  • MAINT005

    Missing Indexes

    Info

    Missing indexes can significantly impact performance

    Versions : 2012-2025

  • MAINT008

    Maintenance Plans

    Info

    Verify maintenance plans execute correctly

    Versions : 2012-2025

  • MS004

    CHECKDB coverage per database

    Info

    Evaluates per database the last clean DBCC CHECKDB date (dbi_dbccLastKnownGood) and flags databases never checked or not checked in over 14 days.

    Versions : 2012-2025

  • STAT001

    Auto Update Stats Async off (large DBs)

    Info

    Detects large databases (>= 50GB) where auto stats update is synchronous, causing latency spikes.

    Versions : 2012-2025

  • STAT005

    Trace flags 2371/2453 audit

    Info

    Audits TF2371 (dynamic stats-update threshold, default at compat 130+) and TF2453 (table-variable recompile).

    Versions : 2012-2025

Other categories

All checks