AI SQL Tuner

SQL Server Health Check

Overview

AI SQL Tuner’s SQL Server Health Check provides a comprehensive analysis of your SQL Server instance to identify performance bottlenecks, configuration issues, security concerns, and operational risks.

To run it, use the ServerHealth tuning goal.

What It Analyzes

1. Wait Statistics

Analyzes the top 20 wait types consuming the most time on your SQL Server instance, helping identify:

  • I/O bottlenecks (PAGEIOLATCH_*, WRITELOG)
  • CPU pressure (SOS_SCHEDULER_YIELD, CXPACKET)
  • Memory issues (RESOURCE_SEMAPHORE)
  • Locking/blocking issues (LCK_*)

2. I/O Performance

Reviews I/O statistics for all database files, including:

  • Average read/write latency per file
  • Total I/O stall times
  • Data volumes (MB read/written)
  • File-level performance issues

3. System Configuration

Captures comprehensive server information:

  • CPU configuration (logical/physical cores, scheduler count)
  • Memory allocation (total physical, committed, target)
  • SQL Server version, edition, and update level
  • Uptime and availability features (HADR, clustering)
  • Collation and security settings

4. Configuration Settings (sp_configure)

Reviews all SQL Server configuration settings to identify:

  • Non-default or unusual settings
  • Memory configuration issues
  • Parallelism settings
  • Advanced configuration that may need adjustment

5. Query Performance

Analyzes the most resource-intensive queries from the plan cache:

  • CPU time and logical reads
  • Memory grants and spills (SQL Server 2019+)
  • Execution counts and patterns
  • Potentially problematic queries

6. High-Value Missing Indexes

Identifies missing indexes with highest potential impact:

  • Tables requiring indexes
  • Equality, inequality, and included columns
  • Impact score and performance metrics
  • Avg user cost and impact percentage
  • Total seeks and scans
  • Recommendation to run IndexTuning for comprehensive analysis

7. Query Store Settings

Reviews Query Store configuration for all user databases:

  • Query Store state (OFF, READ_ONLY, READ_WRITE)
  • Storage usage and limits
  • Capture and cleanup settings
  • Collection intervals and retention policies
  • Read-only reasons and configuration issues
  • Available on SQL Server 2016+ and Azure SQL Database

8. Database Integrity Checks (DBCC CHECKDB)

Reviews when DBCC CHECKDB was last run on each database:

  • Last CHECKDB execution time
  • Days since last integrity check
  • Status indicators (OK, WARNING, CRITICAL)
  • Identifies databases that have never been checked
  • Highlights databases overdue for integrity checks (>7 days)

9. Connection Encryption Status

Reviews connection’s security configuration:

  • Encryption status (TRUE/FALSE)
  • Authentication scheme (NTLM, Kerberos, SQL, etc.)
  • Client network address
  • Warnings for unencrypted connections
  • Recommendations for enabling encryption

10. Sysadmin Role Membership

Audits accounts with sysadmin privileges:

  • Complete list of sysadmin members
  • Total count of privileged accounts
  • Security warnings for excessive membership
  • Identification of risky accounts (sa, BUILTIN groups)
  • Best practice recommendations for least privilege

11. Recent Deadlocks (Past 7 Days)

Reviews deadlock occurrences on the server from the past 7 days:

  • Timestamp of each deadlock occurrence
  • Summary count with recommendations
  • Suggestion to run FixDeadlocks goal for detailed analysis
  • Platform-specific detection (On-premises, Azure SQL MI)
  • Not available on Azure SQL Database

Usage

Command Line Examples

Basic usage with Windows Authentication (database defaults to ‘master’):

aisqltuner -S localhost -goal ServerHealth -E

With Azure AD Interactive authentication:

aisqltuner -S myserver.database.windows.net -goal ServerHealth -aad

With SQL Server authentication:

aisqltuner -S localhost -goal ServerHealth -u sa -p MyPassword123

Output to HTML file:

aisqltuner -S localhost -goal ServerHealth -E -o serverhealth_report.html

Output language (optional):

aisqltuner -S localhost -goal ServerHealth -E -o serverhealth_es.html -l Spanish

What You Get

The AI will analyze all collected data and provide:

  1. Performance Improvements

    • CPU optimization recommendations
    • Memory configuration tuning
    • I/O performance enhancements

  2. Configuration Optimizations

    • sp_configure setting recommendations
    • Database-level settings review
    • Version-specific feature suggestions

  3. Security Enhancements

    • Security configuration review
    • Authentication mode recommendations
    • Best practices for your edition

  4. Operational Best Practices

    • Maintenance recommendations
    • Monitoring suggestions
    • Capacity planning insights

  5. Proactive Maintenance

    • Preventive measures
    • Health monitoring setup
    • Long-term optimization strategies

Key Differences from Other Goals

Goal Focus Use When Database Required
ServerHealth Overall server performance, configuration, and operations Regular health checks, troubleshooting general performance issues No (defaults to master)
IndexTuning Index optimization and query tuning Optimizing specific database query performance Yes
CodeReview T-SQL code quality and patterns Reviewing stored procedures, functions, and triggers Yes
FixDeadlocks Deadlock analysis and resolution Investigating specific deadlock issues No (defaults to master)

SQL Server Version Support

  • SQL Server 2019+: Full feature set including memory grant and spill statistics
  • SQL Server 2017 and earlier: Full analysis with estimated execution plans
  • Azure SQL Database: Supported with cloud-specific recommendations
  • Azure SQL Managed Instance: Fully supported

Best Practices

  1. Run during representative workload periods – Collect data when the server is under typical load for the most relevant insights

  2. Compare over time – Run ServerHealth checks regularly (monthly or quarterly) to track trends and validate improvements

  3. Review all sections – The AI provides holistic recommendations; don’t focus on just one area

  4. Test recommendations carefully – Always test configuration changes in a non-production environment first

  5. Combine with other goals – Use ServerHealth for overall assessment, then drill down with IndexTuning or CodeReview as needed

Technical Details

Data Collection Queries

The ServerHealth goal executes the following DMV queries:

  • sys.dm_os_wait_stats – Wait statistics
  • sys.dm_io_virtual_file_stats – I/O performance
  • sys.dm_os_sys_info – System information
  • sys.configurations – Configuration settings (sp_configure)
  • sys.dm_exec_query_stats – Query performance
  • sys.dm_exec_sql_text – Query text
  • sys.database_query_store_options – Query Store settings for all databases
  • DBCC DBINFO – Last CHECKDB execution time per database
  • sys.dm_exec_connections – Connection encryption status
  • sys.server_role_members – Sysadmin role membership
  • sys.dm_os_ring_buffers – Recent deadlock information (if available)

Permissions Required

The SQL Server login needs:

  • VIEW SERVER STATE permission
  • VIEW DATABASE STATE permission
  • Access to the target database (defaults to master)
  • For Query Store information: Access to user databases
  • For CHECKDB information: sysadmin or db_owner role on each database
  • For deadlock information: Access to system health extended events

Output Format

  • Console: Plain text recommendations (when -o is not specified)
  • HTML: Formatted HTML report with sections and styling (when -o is specified). The footer includes the model and reasoning effort used for the run.

Reasoning Effort

For large ServerHealth payloads, AI SQL Tuner uses low reasoning effort for this goal to improve reliability and reduce timeouts. Other goals use medium reasoning effort.

Troubleshooting

“No data found for analysis”

  • Ensure the SQL Server instance has been running with workload
  • Check that you have sufficient permissions

“Access denied” errors

  • Grant VIEW SERVER STATE permission to the login
  • Ensure you can connect to the server with the specified credentials

Empty wait statistics

  • The server may have been recently restarted
  • Consider resetting wait stats: DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR); (then run workload)

Related Resources

AI SQL Tuner

Thank You, we'll be in touch soon.

Features

Compare

Resources

© 2025 AI SQL Tuner LLC — AI-Powered SQL Server Optimization. All rights reserved.