Performance

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

Checks in this category
29
Weight in the score
14
Breakdown by severity
6 high · 10 medium · 4 low · 9 info

Checks in this category

  • LATCH003

    Non-yielding scheduler events

    High

    Detects non-yielding scheduler events via the SCHEDULER_MONITOR ring buffer, a sign of a serious stall.

    Versions : 2012-2025

  • PERF004

    High PAGEIOLATCH

    High

    High PAGEIOLATCH_* indicates disk I/O issues

    Versions : 2012-2025

  • PERF006

    Memory Pressure

    High

    Detection of pending memory grants

    Versions : 2012-2025

  • SCHED001

    Runnable tasks (CPU pressure)

    High

    Measures runnable_tasks_count on visible schedulers; a persistent queue means tasks wait for CPU (signal-wait pressure).

    Versions : 2012-2025

  • SCHED003

    THREADPOOL exhaustion

    High

    Compares active workers to the maximum and detects THREADPOOL waits, which mean SQL ran out of worker threads (the instance can appear hung).

    Versions : 2012-2025

  • TDB001

    TempDB allocation-page contention

    High

    Detects live contention on tempdb allocation pages (PFS/GAM/SGAM) via PAGELATCH_UP waits.

    Versions : 2012-2025

  • PERF001

    Page Life Expectancy

    Medium

    Memory-scaled PLE target ((RAM GB / 4) × 300). Weak signal on its own - corroborate with PAGEIOLATCH waits

    Versions : 2012-2025

  • PLAN001

    Single-use adhoc plan bloat

    Medium

    Detects significant plan-cache memory consumed by single-use adhoc plans.

    Versions : 2012-2025

  • PLAN002

    Plan cache flushed recently

    Medium

    Detects an oldest-cached-plan age far below uptime, signalling a recent cache flush.

    Versions : 2012-2025

  • PLAN003

    Compiles/recompiles ratio

    Medium

    Measures the compile and recompile ratio relative to batch requests (cumulative since restart).

    Versions : 2012-2025

  • SCHED002

    Scheduler / work-queue imbalance

    Medium

    Measures work_queue_count and load spread across schedulers; a non-zero work queue means tasks wait for an available worker (thread starvation).

    Versions : 2012-2025

  • SCHED004

    Offline schedulers / hidden cores

    Medium

    Detects VISIBLE OFFLINE schedulers, usually caused by an affinity mask or a core/licensing limit hiding CPUs from SQL Server.

    Versions : 2012-2025

  • TDB004

    TempDB metadata contention diagnostic

    Medium

    Measures all PAGELATCH waits on tempdb (allocation + metadata) as a contention indicator.

    Versions : 2012-2025

  • TDB005

    TempDB XTP (HkTempDB) growth

    Medium

    Measures XTP memory consumed in tempdb by memory-optimized temp tables/variables (2019+).

    Versions : 2019-2025

  • XTP003

    XTP not bound to resource pool

    Medium

    Detects databases with memory-optimized data using the default/internal pool instead of a dedicated pool.

    Versions : 2014-2025

  • XTP004

    XTP checkpoint file bloat

    Medium

    Measures checkpoint file pair (CFP) size for durable XTP; bloat signals merge lag or infrequent log backups.

    Versions : 2014-2025

  • LATCH002

    Spinlock contention

    Low

    Identifies the spinlock with the most backoffs (cumulative), a CPU-spinning contention signal.

    Versions : 2012-2025

  • SCHED005

    NUMA memory imbalance

    Low

    Measures foreign_committed_kb per memory node; a high value means a NUMA node is using another node's memory (cross-node latency).

    Versions : 2012-2025

  • TDB002

    Trace flags 1117/1118 vs version

    Low

    Audits trace flags 1117/1118; redundant on SQL 2016+ where this behaviour is the default.

    Versions : 2012-2025

  • TDB006

    TempDB autogrowth/size sanity

    Low

    Checks tempdb data files are equally sized and autogrowth is fixed-MB (not percent, not tiny).

    Versions : 2012-2025

  • PERF002

    Buffer Cache Hit Ratio

    Info

    Informational only: read-ahead keeps this ratio at ~99-100%, so it does not reliably indicate memory health. Prefer wait stats

    Versions : 2012-2025

  • PERF003

    Critical Waits

    Info

    Analyze top wait statistics

    Versions : 2012-2025

  • PERF005

    High CXPACKET

    Info

    High CXPACKET may indicate parallelism issues

    Versions : 2012-2025

  • PERF007

    Query Store Enabled

    Info

    Query Store helps diagnose performance issues

    Versions : 2016-2025

  • PERF008

    Resource Governor

    Info

    Verify Resource Governor configuration if used

    Versions : 2012-2025

  • PERF009

    Optimized locking not enabled (SQL Server 2025 - recommendation)

    Info

    Optimized locking (SQL Server 2025) reduces blocking, lock memory and lock escalation. It is OFF by default on SQL Server 2025. Informational check listing user databases where it is not enabled, prioritizing those already running ADR (direct candidates; full LAQ benefit also needs RCSI).

    Versions : 2025

  • TDB003

    TempDB metadata memory-optimized

    Info

    Checks whether tempdb system metadata is memory-optimized (2019+), removing the PAGELATCH bottleneck.

    Versions : 2019-2025

  • XTP001

    In-Memory OLTP inventory

    Info

    Inventories databases using In-Memory OLTP (memory-optimized filegroup) and memory-optimized table count.

    Versions : 2014-2025

  • XTP005

    SCHEMA_ONLY memory-optimized tables

    Info

    Inventories memory-optimized SCHEMA_ONLY tables, whose data is lost on restart/failover.

    Versions : 2014-2025

Other categories

All checks