AI-assisted SQL Server performance triage framework that detects production issues using Query Store and DMVs, then generates structured AI-driven analysis and Markdown reports.
SQL Server AI Triage Framework is designed to accelerate SQL Server performance investigations by combining:
- Deterministic SQL Server diagnostics
- Structured JSON outputs
- PowerShell orchestration
- AI-assisted root cause analysis
- Markdown-based triage reporting
The framework focuses on identifying high-value production issues such as:
- Query Store plan regressions
- Blocking chains
- RESOURCE_SEMAPHORE memory pressure
- High CPU workloads
- Wait statistic anomalies
- Query performance hotspots
Rather than replacing DBA expertise, the framework is intended to help standardize and accelerate the initial triage process.
SQL Server (Query Store + DMVs)
↓
T-SQL Detector Scripts (.sql)
↓
Structured JSON Output
↓
PowerShell Orchestration Layer
↓
OpenAI API Analysis
↓
Markdown Triage Reports
Detects plan instability and compares best vs worst execution plans.
- 40x slower plan regressions
- Excessive logical reads
- CPU spikes caused by plan changes
- Parameter sniffing indicators
Detects active blocking chains and long-running waits.
- Head blockers
- Long-running transactions
- Lock escalation scenarios
- Session wait analysis
Detects memory pressure and inefficient query memory usage.
- Excessive memory grants
- Wasted grant memory
- RESOURCE_SEMAPHORE waits
- Spill indicators
Identifies workload hotspots and CPU-intensive queries.
- High cumulative CPU queries
- Sudden CPU spikes
- Expensive procedures
- Repeated high-cost executions
Surfaces server-wide wait patterns affecting performance.
- PAGEIOLATCH waits
- CXPACKET / CXCONSUMER
- WRITELOG
- SOS_SCHEDULER_YIELD
- ASYNC_NETWORK_IO
- Query Store regressions
- Blocking chains
- Memory grant pressure
- High CPU workloads
- Performance bottlenecks
- Wait statistic anomalies
- Query instability
- Production workload hotspots
PowerShell executes all detector scripts against the target SQL Server instance.
Each detector:
- Returns structured JSON findings
- Returns nothing if no issue is detected
- Focuses on deterministic SQL-based analysis
The PowerShell orchestration layer:
- Generates IssueKey values
- Calculates SeverityScore values
- Suppresses duplicate alert noise
- Tracks issue history over time
High-value findings are submitted to the OpenAI API.
AI-generated analysis may include:
- Root cause summaries
- Triage recommendations
- Performance interpretation
- Potential remediation steps
- Investigation guidance
Readable Markdown triage reports are generated for:
- Incident review
- DBA triage
- Operational visibility
- Historical tracking
- Knowledge sharing
/sql
01-QueryStorePlanRegression.sql
02-Blocking.sql
03-MemoryGrants.sql
04-TopCpuQueries.sql
05-WaitStats.sql
/scripts
Invoke-SqlAiTriage.ps1
/reports
*.md
/logs
*.log
Query Store Plan Regression
Query execution duration increased from 120ms to 4.8s after a plan change.
Possible parameter sniffing regression caused by a plan change resulting in significantly increased logical reads and CPU usage.
- Review Query Store execution plans
- Compare estimated vs actual rows
- Evaluate indexing strategy
- Consider Query Store plan forcing
- Review recent statistics updates
- SQL Server Query Store
- Dynamic Management Views (DMVs)
- PowerShell
- Azure/OpenAI APIs
- JSON
- Markdown Reporting
The goal of the framework is to:
- Reduce triage time
- Improve operational visibility
- Standardize SQL Server diagnostics
- Reduce alert fatigue
- Provide actionable investigation guidance
- Assist DBAs during production incidents
This project is currently:
- Experimental
- Under active development
- Intended for lab/testing/learning environments
- Not yet production hardened
The framework is being iteratively expanded with additional detectors, scoring logic, and reporting capabilities.
Planned enhancements include:
- Execution plan XML analysis
- Deadlock detection
- TempDB pressure detection
- SQL Agent failure analysis
- Index usage anomaly detection
- Historical trend baselines
- Automatic issue correlation
- HTML dashboard reporting
- Multi-server orchestration
Suggestions, ideas, and feedback are welcome.
Future goals may include:
- Additional detectors
- Expanded AI analysis capabilities
- Community-contributed triage modules
- Extended reporting functionality
MIT License
Jeremy Hale