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!

Jump to Performance Checks:

Server Configuration
Storage Performance
Database Configuration
Wait Statistics
Advanced Configuration Settings

Overview

sp_PerfCheck collects key health metrics and configuration settings at both the server and database level, providing actionable insights without overwhelming you with irrelevant information.

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

-- 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;

Server Configuration (4000-series)

Min and Max Server Memory (check_id 1001-1002)

Check for min server memory too close to max (≥90%) and identifies max server memory set too close to physical memory (≥95%).

Prevents SQL Server from dynamically adjusting memory usage in response to system demands.

High Stolen Memory (check_id 6002)

Identifies high percentage of memory stolen from buffer pool, calculates impact on data caching capability.

Suggests investigating memory usage by CLR, extended stored procedures, or linked servers that may be consuming too much memory.

For more detailed metrics, run sp_PressureDetector and look at the memory section.

Offline CPU Schedulers (check_id 4001)

Detects when CPU schedulers are offline, identifies potential affinity mask misconfigurations.

Checks for licensing or VM configuration issues limiting processor availability that could impact performance.

Most of the time I see this on Standard Edition when someone configured a VM with one core per socket for more than 4 sockets. Standard Edition can only see the first four of them.

Some of the time it happens to Enterprise Edition if someone installs the wrong SQL Server bits.

If neither of those things is the root cause, check the affinity settings.

MAXDOP Settings (check_id 1003)

Identifies default MAXDOP setting (0) on multi-processor systems, warns about potential excessive parallelism issues.

Provides recommendations based on logical processor count to help optimize query performance.

Cost Threshold for Parallelism (check_id 1004)

Detects low cost threshold settings that may cause excessive parallelism, analyzes impact on small query performance.

Recommends appropriate values based on workload characteristics to prevent unnecessary parallel execution plans.

Memory-Starved Stuff (check_id 4101)

Detects high buffer pool churn, memory grant issues, and external memory pressure.

The check provides specific thresholds based on server class and analyzes memory-related wait types to identify bottlenecks.

For more detailed metrics, run sp_PressureDetector and look at the memory section.

Memory Dumps Analysis (check_id 4102)

Detects SQL Server memory dumps indicating stability issues, calculates frequency of dumps relative to uptime, and provides guidance for dump analysis and resolution.

This query looks at sys.dm_server_memory_dumps — once you locate your dump files, you can use my friend Sean’s tool for some analysis — but you should probably also open a support ticket.

Deadlock Detection (check_id 4103)

Identifies deadlock frequency and patterns, tracks deadlocks per day since server startup, and indicates potential application concurrency issues.

For more deadlock information, you can use sp_HealthParser to look at the system health extended event for deadlocks.

Security Token Cache Size (check_id 4104)

Analyzes TokenAndPermUserStore cache size, identifies excessive security token cache which can consume significant memory, and provides recommendations for high-connection environments.

You can also use my ClearTokenPerm procedure to clear out that cache if it’s growing and causing problems.

Lock Pages in Memory Status (check_id 4105)

Identifies if SQL Server is using locked pages in memory.

This prevents the Windows operating system from paging out SQL Server’s buffer pool memory. It also allows SQL Server to allocate physical memory without messing with virtual memory.

Recommended for production environments with >32GB RAM to protect critical database memory from being swapped to disk.

Instant File Initialization (check_id 4106)

Verifies if IFI is enabled for file growth operations.

This feature allows SQL Server to skip zeroing out data files when creating or growing them, which is critical for fast file growths and database restores.

Starting with SQL Server 2022, you can get instant file initialization for transaction log files if the autogrowth increment is set to 64MB.

Resource Governor State (check_id 4107)

Detects if Resource Governor is enabled and provides scripts to examine resource pool and workload group settings.

This check helps you understand whether workloads are being managed effectively across the server.

I often recommend Resource Governor to clients on servers with large amounts of memory.

By default, any query can ask for 25% of your max server memory setting for a memory grant, and SQL Server is willing to give out 75% of your max server memory setting any queries running concurrently.

You can use Resource Governor to cap per-query memory grants to much more reasonable numbers to prevent unnecessary buffer pool theft.

CPU Scheduling Pressure (check_id 6101-6102)

Identifies high signal waits ratio (>25%) indicating CPU scheduler contention and excessive SOS_SCHEDULER_YIELD waits showing CPU pressure.

The check also analyzes worker threads vs. CPU cores and detects NUMA configuration issues affecting performance.

For more detailed metrics, run sp_PressureDetector and look at the CPU section.

SQL Server Edition and Configuration Options (server_info)

Documents product version, edition, and key server properties. Shows non-default global configuration settings and lists globally enabled trace flags. This information provides context for other performance findings.

tempdb Files and Settings (check_id 5000-5003)

Verifies proper number of tempdb data files based on CPU count.

Checks for equal file sizes and settings, identifies suboptimal growth settings, and analyzes tempdb contention indicators.

Proper tempdb configuration is critical for overall SQL Server performance. You may need to add (or even subtract) files if you’re still having contention issues.

Potentially Disruptive DBCC Commands (check_id 5003)

Detects execution of DBCC FREEPROCCACHE, FREESYSTEMCACHE, DROPCLEANBUFFERS.

Identifies DBCC SHRINKDATABASE and SHRINKFILE operations and warns about performance impact on production environments.

These commands can cause significant performance degradation when run during production hours.

Storage Performance (6000-series)

I/O Stall Statistics (check_id 6201)

Tracks read/write stalls per database, identifies databases experiencing I/O bottlenecks, calculates average stall times for reads and writes, and differentiates between data and log file performance issues.

This helps pinpoint storage subsystem problems that may be affecting specific databases.

For more detailed metrics, run sp_PressureDetector and look at the IO section.

Storage Performance by File (check_id 6202-6204)

Analyzes performance metrics for each database file, identifies specific files causing I/O bottlenecks, and correlates I/O stalls with storage layout decisions. File-level analysis helps identify performance problems that may be masked at the database level.

Auto-growth Events (server_info)

Captures slow auto-growth events for data and log files, reports frequency and average duration.

While you’re semi-immune to these issues if you have IFI enabled, a lot of folks still don’t turn it on, or have transaction log files growing by percentages that can really hurt.

Wait Statistics Analysis (6000-series)

High Impact Wait Types (check_id 6001)

Analyzes sys.dm_os_wait_stats to identify performance bottlenecks.

Calculates wait time as a percentage of SQL Server uptime, categorizes waits by resource type (CPU, I/O, Memory, etc.), identifies abnormal wait patterns indicating specific resource pressure, and focuses on wait stats that are performance-related.

For more detailed metrics, run sp_PressureDetector and look at the Wait Stats section.

Database Configuration (7000-series)

Auto-shrink (check_id 7001)

Identifies databases with auto-shrink enabled, which can cause performance problems due to cyclic shrink/grow operations.

This setting should be disabled for production databases where you care about performance.

Auto-close (check_id 7002)

Detects databases with auto-close enabled, which automatically closes a database when the last connection is closed.

This causes connection delays when the database needs to be reopened, and the plan cache to be cleared on close.

This should be disabled for production databases where you care about performance.

Restricted Access Mode (check_id 7003)

Identifies databases in non-multi-user modes (SINGLE_USER, RESTRICTED_USER), which limit who can connect to a database.

These modes should only be used temporarily for maintenance operations.

Statistics Settings (check_id 7004)

Checks for databases with auto create/update statistics disabled.

These features help the query optimizer make better execution plan choices, and disabling them can lead to suboptimal performance.

Most of the time, these are turned off by accident, or by someone fumbling around to solve performance problems. Worse, sometimes vendors tell you to turn them off.

Unless you’re managing statistics creation and updates really well on you own, these are bad settings to tinker with.

ANSI Settings (check_id 7005)

Identifies databases with non-standard ANSI settings that can affect query behavior and potentially cause performance issues or unexpected results.

Most interesting are the query errors you’ll get if you start trying to use more advanced features like filtered indexes, computed columns, and indexed views.

Query Store Status (check_id 7006)

Checks if Query Store is enabled for each database.

Query Store collects query performance data for monitoring and troubleshooting, and not having it enabled means missing valuable performance insights.

It’s a far better tool for query performance investigations than the plan cache, which is often unreliable because of how often it clears out, and queries recompile.

To dig into Query Store data, run sp_QuickieStore.

Recovery Time Target (check_id 7007)

Identifies databases with Target Recovery intervals not set to take advantage of Indirect Checkpoints.

This can impact both performance and recovery time. Indirect Checkpoints are usually far less stressful on servers.

To see if any of your databases can benefit from changing Target Recovery Interval, run sp_LogHunter and look for messages about FlushCache in your error log. In those messages, you’ll see a database id that you can use to figure out which one is having checkpoint problems.

Transaction Durability (check_id 7008)

Detects databases using delayed durability, which allows transactions to complete before being written to the transaction log.

This improves performance but increases the risk of data loss. I’m not telling you to turn it on or off here, I’m just letting you know when it’s on.

You may have it on, and have no queries using the feature. Again, this is just informational. It takes a lot of folks by surprise.

Accelerated Database Recovery (check_id 7009)

Identifies databases not using Accelerated Database Recovery (ADR) enabled.

ADR speeds up database recovery and transaction rollbacks and is particularly beneficial for databases using snapshot isolation.

It’s one of the best SQL Server features in recent memory, and goes hand-in-hand with optimistic isolation levels and optimized locking as well.

Ledger Feature (check_id 7010)

Checks for databases using the Ledger feature, which provides blockchain-style immutability for tables but introduces performance overhead.

This feature should only be used when data immutability is required. And when you have infinite disk space.

Query Store Health (check_id 7011-7012)

Examines Query Store health issues including state mismatches (desired state doesn’t match actual state) and suboptimal configurations that limit its effectiveness.

I see a lot of folks with Query Store enabled (or so they think) that are surprised by it being in an unhealthy state when we go to actually look at the data in there.

You can have it enabled, but because of errors or space limitations, it may not be logging new query performance data.

Database Scoped Configurations (check_id 7020)

Identifies non-default Database Scoped Configuration (DSC) settings, explains the performance impact of each setting, and analyzes version-specific optimal settings.

DSC settings can significantly affect database performance, and the defaults should only be messed with in specific circumstances.

Percentage Growth for Data Files (check_id 7101)

Checks for data files using percentage-based growth, which can lead to increasingly larger growth events as the database grows.

Fixed-size growth increments are generally more predictable and manageable.

Even if you’re using IFI and the growths themselves are basically free, growing a database file by a percentage can lead to disk space surprises.

Like, hey, surprise, you’re out of disk space.

Percentage Growth for Log Files (check_id 7102)

Identifies transaction log files using percentage-based growth, which is particularly problematic due to the zeroing impact on log files.

This issue has higher priority than data file growth settings.

Non-optimal Log Growth Increments (check_id 7103)

Detects log files not using the required 64MB growth increment setting for IFI to work on SQL Server 2022+.

Optimal log growth increments help with virtual log file (VLF) management and instant growths that don’t make your queries sit around twiddling thumbs.

Extremely Large Growth Increments (check_id 7104)

Identifies files with very large growth increments (>10GB) that can cause extended stalls during growth operations.

Moderate growth increments balance reducing growth frequency with minimizing stall duration.

Advanced Configuration Settings

Priority Boost (check_id 1005)

Detects when priority boost is enabled, warns about potential issues with Windows scheduling priorities.

This is generally not recommended for modern SQL Server versions.

To reset to default (disabled):

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

Note: Changing this setting requires a SQL Server service restart to take effect.

Lightweight Pooling (check_id 1006)

Identifies when lightweight pooling (fiber mode) is enabled, warns about potential compatibility issues with OLEDB providers.

This setting often causes more problems than it solves.

To reset to default (disabled):

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

Note: Changing this setting requires a SQL Server service restart to take effect.

Affinity Mask (check_id 1007)

Determines which CPUs can be used by SQL Server threads. This can prevent Windows from moving SQL Server threads between processors, potentially improving performance under heavy loads.

To reset to default (all CPUs available):

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

Note: Changing this setting requires a SQL Server service restart to take full effect, especially for permanent system tasks.

Affinity I/O Mask (check_id 1008)

Binds SQL Server disk I/O operations to specific CPUs. In high-end OLTP environments, this can improve the performance of threads issuing I/O operations.

To reset to default (no CPU affinity for I/O operations):

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

Note: Never enable the same CPU in both the affinity I/O mask and the regular affinity mask. Each CPU should be assigned to at most one of these masks.

Affinity64 Mask (check_id 1009)

For servers with more than 32 processors (64-bit systems only), this setting controls CPU affinity for processors 33-64. Works in conjunction with the standard affinity mask.

To reset to default (all higher CPUs available):

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

Note: Only applicable on systems with more than 32 processors running a 64-bit version of SQL Server.

Affinity64 I/O Mask (check_id 1010)

Controls which processors (33-64) can be used for disk I/O operations on servers with more than 32 processors. Works with affinity I/O mask to cover all processors.

To reset to default (no I/O affinity for higher CPUs):

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

Note: As with the regular affinity masks, never assign the same CPU to both the affinity64 mask and affinity64 I/O mask. A restart is recommended when changing these settings.

Important: For all affinity mask settings, Microsoft generally recommends using the default configuration (all zeros) in most scenarios. Only configure these settings 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 to help prioritize focus areas:

  • 1000-series: Advanced configuration settings
  • 4000-series: Server configuration issues (memory, CPU, stability)
  • 5000-series: tempdb configuration problems
  • 6000-series: Resource-specific performance issues (waits, I/O, CPU)
  • 7000-series: Database configuration issues

Results are returned in two sections:

  1. Server Information: General server metrics and configuration details
  2. Performance Check Results: Specific findings from all checks, sorted by priority

Download Me!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.