Configuration

The 33 checks in the “Configuration” 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
16
Breakdown by severity
1 critical · 5 high · 8 medium · 8 low · 11 info

Checks in this category

  • CFG001

    Max Server Memory

    Critical

    Max server memory must be configured to leave RAM for the OS

    Versions : 2012-2025

  • CFG004

    MAXDOP

    High

    MAXDOP should be configured based on NUMA core count

    Versions : 2012-2025

  • CFG005

    Priority Boost

    High

    Priority Boost should be disabled (can cause deadlocks)

    Versions : 2012-2025

  • CFG006

    Lightweight Pooling

    High

    Lightweight Pooling should be disabled on modern systems

    Versions : 2012-2025

  • CFG013

    Lock Pages in Memory

    High

    Checks if Lock Pages in Memory is enabled for the SQL Server service account

    Versions : 2012-2025

  • CFG018

    Server name mismatch (@@SERVERNAME vs actual network name)

    High

    Compares @@SERVERNAME (local name in sys.servers server_id=0, not auto-updated) against the real network name SERVERPROPERTY('ServerName'). A mismatch reveals a cloned/renamed host never fixed with sp_dropserver + sp_addserver: silently breaks replication, SQL Agent jobs, linked servers and scheduled backups.

    Versions : 2012-2025

  • CFG003

    Cost Threshold for Parallelism

    Medium

    Default value (5) is often too low for OLTP workloads

    Versions : 2012-2025

  • CFG007

    Remote Admin Connections

    Medium

    Remote DAC (Dedicated Admin Connection) should be enabled

    Versions : 2012-2025

  • CFG010

    OLE Automation

    Medium

    OLE Automation should be disabled if not used

    Versions : 2012-2025

  • CFG012

    Optimize for Ad Hoc Workloads

    Medium

    Checks if optimize for ad hoc workloads is enabled to reduce plan cache bloat

    Versions : 2012-2025

  • CFG017

    Server configuration drift (value <> value_in_use)

    Medium

    Flags sp_configure options whose configured value differs from the running value (value_in_use): RECONFIGURE not run/failed, or a Database Engine restart pending. A silent gap between intended and effective configuration.

    Versions : 2012-2025

  • OPS005

    Risky / undocumented trace flags

    Medium

    Compares active global trace flags against a known list; unknown flags may alter engine behaviour unexpectedly.

    Versions : 2012-2025

  • QOPT001

    Legacy CE forced (DB scope)

    Medium

    Detects LEGACY_CARDINALITY_ESTIMATION = ON at database scope, masking the modern cardinality estimator.

    Versions : 2016-2025

  • QOPT003

    Parameter sniffing disabled

    Medium

    Detects PARAMETER_SNIFFING = OFF at DB scope (equivalent to TF4136), a blunt tool that can degrade good plans.

    Versions : 2016-2025

  • CFG002

    Min Server Memory

    Low

    Min server memory should be configured appropriately

    Versions : 2012-2025

  • CFG008

    Default Trace

    Low

    Default trace should be enabled for diagnostics

    Versions : 2012-2025

  • CFG016

    Default Fill Factor

    Low

    Checks the default fill factor value at the server level

    Versions : 2012-2025

  • CFG019

    blocked process threshold (blocked process report)

    Low

    Server-level option 'blocked process threshold (s)' (sys.configurations, value_in_use), in seconds, above which SQL Server emits a blocked process report (consumable by an Extended Events session or an Agent alert). Default = 0: no report produced. The lock monitor only wakes every 5 s, so a value of 1-4 is a misconfiguration (reports are never generated). Classified under 'configuration' as an instance-level setting, not per database.

    Versions : 2012-2025

  • OPS002

    Number of error logs retained

    Low

    Checks that SQL Server keeps at least 12 ErrorLog files, preserving enough history across recycles.

    Versions : 2012-2025

  • OPS003

    Default paths off system drive

    Low

    Checks that the default data/log/backup directories are not on the system drive (C:), to avoid filling the OS volume.

    Versions : 2012-2025

  • RG002

    Resource Governor classifier function

    Low

    Checks the presence and validity (SCHEMABINDING) of the classifier function that routes connections to groups.

    Versions : 2012-2025

  • RG003

    Resource Governor reconfigure pending

    Low

    Detects is_reconfiguration_pending = 1, signalling pool/group changes that were never applied.

    Versions : 2012-2025

  • CFG009

    CLR Enabled

    Info

    CLR should only be enabled if necessary

    Versions : 2012-2025

  • CFG011

    Trace Flags

    Info

    Verify active trace flags and their relevance

    Versions : 2012-2025

  • CFG014

    Max Worker Threads

    Info

    Checks if max worker threads is set to 0 (auto) or manually configured

    Versions : 2012-2025

  • CFG015

    Contained Database Authentication

    Info

    Checks if contained database authentication is enabled

    Versions : 2012-2025

  • OPS004

    Deprecated features in use

    Info

    Detects, via the 'Deprecated Features' counter, usage of features slated for removal, to fix before upgrading.

    Versions : 2012-2025

  • OPS006

    TF 4199 optimizer hotfixes

    Info

    Checks whether TF 4199 (post-RTM optimizer fixes) is enabled; on 2016+ the DB-scoped QUERY_OPTIMIZER_HOTFIXES option is an alternative.

    Versions : 2012-2025

  • QOPT002

    MAXDOP overridden at DB scope

    Info

    Inventories databases whose DB-scope MAXDOP overrides the instance setting.

    Versions : 2016-2025

  • QOPT004

    PSP optimization disabled (2022)

    Info

    Detects PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF on 2022 (compat 160), usually a regression workaround.

    Versions : 2022-2025

  • QOPT005

    Compat level blocking IQP

    Info

    Detects databases whose compatibility level is below the engine default, blocking Intelligent Query Processing features.

    Versions : 2012-2025

  • QOPT006

    DOP/CE feedback off (2022)

    Info

    Detects DOP_FEEDBACK or CE_FEEDBACK = OFF on 2022, forgoing self-tuning (requires Query Store read-write).

    Versions : 2022-2025

  • RG001

    Resource Governor pools/groups

    Info

    Inventories non-default pools and workload groups and how many impose CPU/memory limits.

    Versions : 2012-2025

Other categories

All checks