sp_PerfCheck

sp_PerfCheck – SQL Server Performance Diagnostic Tool

A comprehensive SQL Server performance diagnostic tool that quickly identifies configuration issues, capacity problems, and performance bottlenecks at both server and database levels.

Download Me!

Priority System

All findings are assigned a priority level indicating severity and urgency:

Priority Label Meaning
10 Critical Server instability — crashes, offline resources, pending configuration changes
20 High Active performance degradation — severe I/O latency, memory pressure, high deadlock rates
30 Medium Moderate impact or risky configuration that will likely cause problems
40 Low Best practice recommendations that improve reliability
50 Informational Awareness items and non-default settings that may be intentional

Results are sorted by priority (lowest number first), then by category, finding, database name, and check_id.

Jump to Performance Checks

Server Configuration

Storage Performance

Wait Statistics

Database Configuration

Advanced Configuration Settings

Overview

Features

  • Fast, lightweight server-level health check
  • Database-specific checks for all accessible user databases
  • Identifies misconfigurations impacting performance
  • Detects resource pressure signals (CPU, memory, I/O)
  • Analyzes wait statistics to identify bottlenecks
  • Finds suboptimal database settings
  • Works with SQL Server 2016+ and Azure SQL options

Requirements

  • SQL Server 2016 SP2 or newer
  • VIEW SERVER STATE permissions for full functionality
  • Database access permissions for database-level checks

Usage Examples

-- Basic check on all databases
EXEC dbo.sp_PerfCheck;

-- Check a specific database only
EXEC dbo.sp_PerfCheck
    @database_name = 'YourDatabaseName';

-- Run with debug information
EXEC dbo.sp_PerfCheck
    @debug = 1;

Detailed Check Documentation



Min and Max Server Memory (check_id 1001-1002)

Priority: Low (40)

Detects min server memory set too high relative to max (≥90%) and identifies max server memory configured too close to physical RAM (≥95%). This prevents SQL Server from dynamically adjusting memory usage in response to system demands.

High Stolen Memory (check_id 6002)

Priority: High (20) if >30% stolen, Medium (30) if >15%, Low (40) otherwise

Identifies high percentage of memory stolen from buffer pool. Stolen memory reduces memory available for data caching. Suggests investigating memory consumption by CLR, extended stored procedures, linked servers, or other memory clerks. Run sp_PressureDetector for detailed memory metrics.

Offline CPU Schedulers (check_id 4001)

Priority: Critical (10)

Detects offline CPU schedulers, reducing available processing power. Most common causes: Standard Edition on multi-socket VM, incorrect SQL Server installation bits, or affinity mask misconfigurations.

MAXDOP Settings (check_id 1003)

Priority: Low (40)

Identifies default MAXDOP setting (0) on multi-processor systems. This can lead to excessive parallelism. Provides recommendations based on logical processor count.

Cost Threshold for Parallelism (check_id 1004)

Priority: Low (40)

Detects low cost threshold settings (≤5) that may cause excessive parallelism for small queries. Recommends appropriate values to prevent unnecessary parallel execution plans.

Memory-Starved Queries (check_id 4101, 4103)

Priority: High (20)

4101 — Forced Memory Grants: Queries are being forced to run with less memory than requested, causing spills to tempdb and poor performance.

4103 — Memory Grant Timeouts: Queries are waiting for memory grants and giving up. These timeouts indicate severe memory pressure. Run sp_PressureDetector for detailed memory analysis.

Memory Dumps Analysis (check_id 4102)

Priority: Critical (10)

Detects SQL Server memory dumps in the last 90 days indicating server stability issues. Examines sys.dm_server_memory_dumps and reports dump count and most recent occurrence. Check the SQL Server error log and Windows event logs for details.

Deadlock Detection (check_id 5103)

Priority: High (20) if >50/day, Medium (30) if >9/day

Tracks deadlocks per day since server startup to identify concurrency issues. High deadlock rates indicate application-level problems that should be investigated. Run sp_HealthParser for system health extended event deadlock analysis.

Security Token Cache Size (check_id 4104)

Priority: High (20) if >5 GB, Medium (30) if >2 GB, Low (40) if >1 GB

Analyzes TokenAndPermUserStore cache size. Only fires when the cache exceeds 1 GB. Large security token caches can consume significant memory and may indicate issues such as excessive application role usage or frequent permission changes. Consider using the ClearTokenPerm procedure to manage this issue.

Lock Pages in Memory Status (check_id 4105)

Priority: Low (40)

Identifies if SQL Server is using locked pages in memory (LPIM). This prevents Windows from paging out SQL Server’s buffer pool memory under OS memory pressure. Recommended for production environments with ≥32 GB RAM.

Instant File Initialization (check_id 4106)

Priority: Low (40)

Verifies if Instant File Initialization is enabled. IFI allows SQL Server to skip zeroing out data files during creation or growth operations, significantly reducing file operation times. Enable by granting “Perform Volume Maintenance Tasks” to the SQL Server service account.

Resource Governor State (check_id 4107)

Priority: Informational (50)

Detects if Resource Governor is enabled. Provides scripts to examine resource pool and workload group settings. Resource Governor affects workload resource allocation and may impact performance by limiting resources available to various workloads.

CPU Scheduling Pressure (check_id 6101-6102)

Priority: High (20) if severe, Medium (30) if moderate, Low (40) otherwise

6101 — High Signal Wait Ratio: Signal waits ≥25% indicate CPU scheduler contention. Processes are waiting to get scheduled on the CPU.

6102 — High SOS_SCHEDULER_YIELD Waits: Tasks frequently giving up their quantum of CPU time, caused by CPU-intensive queries. Run sp_PressureDetector for detailed CPU metrics.

SQL Server Edition and Configuration (check_id 1000, 1007)

1000 — Non-Default Configuration: Informational (50). Reports any sp_configure option changed from its default value.

1007 — Configuration Pending Reconfigure: Critical (10). A configuration option has been changed but requires RECONFIGURE to take effect. The server is not running the configuration the admin intended.

TempDB Files and Settings (check_id 2001-2010)

Check Finding Priority
2001 Single TempDB Data File Medium (30)
2002 Odd Number of TempDB Files Informational (50)
2003 More TempDB Files Than CPUs Informational (50)
2004 Uneven TempDB Data File Sizes Low (40)
2005 Mixed TempDB Autogrowth Settings Low (40)
2006 Percentage Auto-Growth in TempDB Low (40)
2010 TempDB Allocation Contention Detected Medium (30)

Verifies proper number of TempDB data files based on CPU count, checks for equal file sizes and consistent growth settings, identifies contention via pagelatch wait analysis. For best performance, TempDB data files should be the same size with consistent fixed-size growth settings.

Disruptive DBCC Commands (check_id 5003)

Priority: Medium (30) for destructive commands, Informational (50) for others

Detects execution of DBCC FREEPROCCACHE, FREESYSTEMCACHE, DROPCLEANBUFFERS, WRITEPAGE, SHRINKDATABASE, and SHRINKFILE operations from the default trace. These commands can cause performance degradation on production environments.



Storage Performance

Storage Performance by File (check_id 3001-3003)

Analyzes I/O latency at the individual database file level using sys.dm_io_virtual_file_stats. Default threshold: 500 ms (configurable via @slow_read_ms and @slow_write_ms parameters).

Check Finding Priority Condition
3001 Slow Read Latency High (20) / Medium (30) >1000 ms = High, >500 ms = Medium. Requires >1000 reads.
3002 Slow Write Latency High (20) / Medium (30) >1000 ms = High, >500 ms = Medium. Requires >1000 writes.
3003 Multiple Slow Files on Storage Location High (20) Multiple files on the same drive exceeding latency thresholds. Indicates a systemic storage problem.

Auto-Growth Events (check_id 5001)

Priority: Medium (30) for log file auto-grows, Low (40) for data file auto-grows

Captures slow auto-growth events (>1 second) from the default trace. Log file growths are higher priority because log files must be zeroed out during growth, even with Instant File Initialization enabled.

Auto-Shrink Events (check_id 5002)

Priority: Low (40)

Detects auto-shrink operations from the default trace. Auto-shrink causes file fragmentation and repeated grow/shrink cycles. Consider disabling auto-shrink on affected databases.



Wait Statistics Analysis

High Impact Wait Types (check_id 6001)

Priority: High (20) if >75% of uptime, Medium (30) if ≥50%, Low (40) otherwise

Analyzes sys.dm_os_wait_stats to identify the top 10 most significant performance bottlenecks. Calculates wait time as a percentage of SQL Server uptime and categorizes waits by resource type: I/O (PAGEIOLATCH), Latches (PAGELATCH), Logging (WRITELOG), Compilations, and others. Filters out benign or expected wait types. Run sp_PressureDetector for detailed wait stats analysis.



Database Configuration

Auto-Shrink (check_id 7001)

Priority: Medium (30)

Identifies databases with auto-shrink enabled. This is an actively harmful configuration that causes file fragmentation, repeated grow/shrink cycles, and significant performance degradation. Auto-shrink should be disabled on all production databases.

Auto-Close (check_id 7002)

Priority: Low (40)

Detects auto-close databases that automatically close when the last connection ends. This causes connection delays when the database needs to be reopened and clears the plan cache on close. Should be disabled on production systems.

Restricted Access Mode (check_id 7003)

Priority: High (20)

Identifies databases in non-MULTI_USER modes (SINGLE_USER, RESTRICTED_USER). This actively prevents applications from connecting and should only be used temporarily for maintenance operations.

Statistics Settings (check_id 7004)

Priority: Medium (30)

Checks for databases with auto create statistics and/or auto update statistics disabled. These features help the query optimizer make better execution plan choices. Most of the time, these are turned off by accident or by someone fumbling around to solve performance problems.

ANSI Settings (check_id 7005)

Priority: Informational (50)

Identifies databases with non-standard ANSI settings (ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NUMERIC_ROUNDABORT, QUOTED_IDENTIFIER). These settings affect query behavior and can impact filtered indexes, computed columns, and indexed views compatibility.

Query Store Status (check_id 7006)

Priority: Informational (50)

Checks if Query Store is enabled for each database. Query Store collects query performance data that is superior to what’s available in the plan cache. Run sp_QuickieStore for Query Store data analysis.

Recovery Time Target (check_id 7007)

Priority: Informational (50)

Identifies databases with non-default target recovery time settings. Indirect Checkpoints (the default since SQL Server 2016) usually create less server stress than traditional checkpoints. Run sp_LogHunter for checkpoint issue analysis.

Transaction Durability (check_id 7008)

Priority: Medium (30)

Detects databases using delayed durability (ALLOWED or FORCED). Delayed durability allows transactions to complete before being fully written to the transaction log, improving performance but increasing data loss risk on crash. This is a risky configuration that should be used with full understanding of the trade-offs.

Accelerated Database Recovery (check_id 7009)

Priority: Low (40)

Identifies databases using snapshot isolation (SI or RCSI) that don’t have Accelerated Database Recovery enabled. ADR speeds up database recovery and transaction rollbacks and is particularly beneficial for databases using snapshot isolation. One of the best SQL Server features in recent memory.

Ledger Feature (check_id 7010)

Priority: Informational (50)

Checks for databases using the Ledger feature providing blockchain-style immutability. Should only be used when data immutability is required, as it creates significant disk space overhead.

Query Store Health (check_id 7011-7012)

7011 — Query Store State Mismatch: Medium (30). Query Store desired state doesn’t match actual state (OFF or READ_ONLY when it should be ON). Common causes: max size reached, database in single-user or emergency mode.

7012 — Query Store Suboptimal Configuration: Low (40). Identifies settings that may limit Query Store effectiveness: max size too small (<1 GB), capture mode NONE, size-based cleanup disabled, short stale query threshold, or low max plans per query.

Database Scoped Configurations (check_id 7020)

Priority: Informational (50)

Identifies non-default Database Scoped Configuration (DSC) settings. Reports each non-default setting for awareness. These may be intentionally configured for workload-specific tuning.

Percentage Growth for Data Files (check_id 7101)

Priority: Low (40)

Checks for data files using percentage-based auto-growth. Reports the growth percentage and current file size in GB. Even with IFI enabled, growing a database file by a percentage can lead to disk space surprises as the file grows larger. Consider using a fixed size instead for more predictable growth.

Percentage Growth for Log Files (check_id 7102)

Priority: Medium (30)

Identifies transaction log files using percentage-based auto-growth. Reports the growth percentage and current file size in GB. Higher priority than data file settings because log files must be zeroed out during growth operations, causing stalls. Always use fixed-size growth for log files.

Non-Optimal Log Growth Increments (check_id 7103)

Priority: Low (40)

Detects log files not using the 64 MB growth increment on SQL Server 2022+, Azure SQL DB, or Azure Managed Instance. The 64 MB increment enables Instant File Initialization for log files and helps with virtual log file management.

Extremely Large Growth Increments (check_id 7104)

Priority: Low (40)

Identifies database files with very large fixed growth increments (>10 GB). Extremely large growth events can cause extended stalls during auto-growth operations. Use moderate increments to balance growth frequency with stall minimization.



Advanced Configuration Settings

Priority Boost (check_id 1005)

Priority: High (20)

Detects priority boost enablement. This is generally not recommended for modern SQL Server versions and can cause issues with Windows scheduling priorities.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'priority boost', 0;
RECONFIGURE;

Note: Requires SQL Server service restart.

Lightweight Pooling (check_id 1006)

Priority: Low (40)

Identifies fiber mode enablement. This setting often causes more problems than it solves and can cause issues with OLEDB providers and other components.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'lightweight pooling', 0;
RECONFIGURE;

Note: Requires SQL Server service restart.

Affinity Mask (check_id 1008)

Priority: Informational (50)

Determines which CPUs can be used by SQL Server threads. Can prevent Windows from moving SQL Server threads between processors. Only configure when there is a specific need to restrict SQL Server’s CPU usage, such as in multi-instance environments.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'affinity mask', 0;
RECONFIGURE;

Note: Service restart recommended.

Affinity I/O Mask (check_id 1009)

Priority: Informational (50)

Binds SQL Server disk I/O operations to specific CPUs. Can improve high-end OLTP performance for I/O-issuing threads.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'affinity I/O mask', 0;
RECONFIGURE;

Important: Never enable the same CPU in both the affinity I/O mask and regular affinity mask.

Affinity64 Mask (check_id 1010)

Priority: Informational (50)

For servers with >32 processors (64-bit systems), controls CPU affinity for processors 33–64. Works with the standard affinity mask. Only applicable on 64-bit systems with >32 processors.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'affinity64 mask', 0;
RECONFIGURE;

Affinity64 I/O Mask (check_id 1011)

Priority: Informational (50)

Controls which processors (33–64) handle disk I/O on >32 processor servers. Works with the affinity I/O mask.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'affinity I/O mask64', 0;
RECONFIGURE;

Important: Never assign the same CPU to both affinity64 mask and affinity64 I/O mask. Service restart recommended.

Microsoft generally recommends using the default configuration (all zeros) for all affinity settings. Only configure these when there is a specific need to restrict SQL Server’s CPU usage, such as in multi-instance environments or when isolating workloads.



Understanding Results

Results are organized by check_id ranges:

  • 1000-series: Server configuration settings
  • 2000-series: TempDB configuration
  • 3000-series: Storage performance (file-level I/O)
  • 4000-series: Server health (memory, CPU, stability)
  • 5000-series: Trace events (auto-growth, deadlocks, DBCC)
  • 6000-series: Resource performance (waits, I/O, memory)
  • 7000-series: Database configuration

Results are returned in two result sets:

  1. Server Information: General server metrics and configuration details
  2. Performance Check Results: Specific findings sorted by priority, with a priority_label column (Critical, High, Medium, Low, Informational) for readability

Download Me!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.