An AI-powered CLI tool that diagnoses SQL Server performance issues and generates actionable fix scripts — powered by GitHub Copilot.
Features • Getting Started • Usage • Extensible Toolbox • Creating Your Own Tool • Architecture • Documentation
SQLPerfAgent connects to your SQL Server instance, runs diagnostic DMV queries and extensible toolbox plugins, then uses GitHub Copilot to analyze the results. It identifies performance bottlenecks, configuration issues, and security concerns — and generates ready-to-run T-SQL scripts to fix them.
┌──────────────┐ ┌──────────────┐
│ SQL Server │ ◄── DMV Queries ─────── │ │
│ │ ──► Diagnostics ───────► │ SQLPerfAgent │
└──────────────┘ │ │
│ + Copilot │
┌──────────────┐ │ + Toolbox │
│ Actionable │ ◄── Fix Scripts ──────── │ Plugins │
│ Fixes │ │ │
└──────────────┘ └──────────────┘
Diagnostics
- Missing index detection with impact scoring
- Index fragmentation analysis and rebuild/reorganize recommendations
- Expensive query identification (CPU, I/O, memory)
- Unused index detection with write overhead analysis
- Extensible toolbox plugins for additional checks (TempDB, VLFs, duplicate indexes, best practices, and more)
AI-Powered Analysis
- Natural language explanations of complex performance issues
- Contextual fix generation with safety checks and rollback instructions
- Interactive Q&A mode for free-form questions about your database
- AI-assisted tool selection — describe your problem and Copilot suggests which diagnostics to run
Interactive CLI
- Guided connection setup with Windows Auth and SQL Auth support
- Multi-database scanning capability
- Four tool selection modes: run all, manual pick, AI suggestions, or skip
- One-by-one or batch fix application with confirmation prompts
| Requirement | Purpose |
|---|---|
| .NET 10.0 SDK | Build and run the application |
| GitHub Copilot subscription | AI analysis and fix generation (Free works, Pro recommended) |
| Node.js 18+ | Required for the mssql-mcp server (launched automatically via npx) |
| SQL Server instance | Target database (2016+ recommended) |
Note
Both on-premises SQL Server and Azure SQL Database are supported. The Copilot SDK authenticates independently via its own OAuth device flow — no GitHub CLI required. The tool validates your Copilot authentication at startup and provides setup instructions if needed.
The Free tier has a 50 messages/month limit. A single SQLPerfAgent run can consume 5–15+ messages, so Copilot Pro or higher is recommended for regular use.
-
Clone the repository
git clone https://github.com/fatihdurgut/SQLPerfAgent.git cd SQLPerfAgent -
Build the project
dotnet build
-
Run the agent
dotnet run
Tip
On first run, the Copilot SDK will prompt you to authenticate via an OAuth device flow. Visit the displayed URL and enter the code. This is a one-time step.
The agent walks you through an interactive workflow:
── SQL Server Connection Setup ──
Server [localhost]: myserver.database.windows.net
Authentication method:
[1] Windows Authentication
[2] SQL Server Authentication
Username [sa]: admin
Password: ********
── Database Selection ──
[1] ProductionDB
[2] StagingDB
[3] Scan all user databases
After toolbox discovery, you pick how to select diagnostics:
── Tool Selection ──
Which additional toolbox checks would you like to run?
[1] Run all toolbox tools
[2] Let me pick specific tools
[3] Describe my problem — get AI suggestions
[4] Skip toolbox tools (DMV queries only)
Option 3 opens an AI-assisted conversation — describe your concern and Copilot recommends the most relevant tools:
Describe your concern: My database is experiencing slow writes during peak hours
Based on your description, I recommend:
• VLFCheck — high VLF counts directly impact write performance
• TempDBChecks — TempDB contention causes write bottlenecks under concurrency
• BestPracticesChecks — MaxDOP and memory settings affect write throughput
Suggested so far: VLFCheck, TempDBChecks, BestPracticesChecks
Ask another question, or type 'done' to proceed.
Tip
Standard DMV queries (missing indexes, fragmentation, expensive queries, unused indexes) always run regardless of tool selection.
The AI analyzes combined results and returns categorized findings:
── Found 5 Recommendation(s) ──
── Performance ──
1. [High] Missing index on Orders.CustomerID (Impact: 15.2M)
2. [Medium] Fragmented index IX_Products_Name (72.3%)
── Configuration ──
3. [Medium] TempDB has 2 files, but server has 8 CPUs
4. [Low] Percentage-based autogrow on TempDB
── Security ──
5. [High] No full backup for ProductionDB in 14 days
Enter interactive Q&A mode to explore specific concerns before applying fixes:
Q: Why is my buffer pool showing memory pressure?
A: Your Page Life Expectancy is 180 seconds, below the recommended 300...
[Runs additional diagnostics via mssql-mcp]
Q: done
Choose one-by-one (review each script individually) or batch mode (generate all, review, then execute):
-- Fix: Missing index on Orders.CustomerID
-- Impact Score: 15,234,789
IF NOT EXISTS (
SELECT 1 FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.Orders')
AND name = 'IX_Orders_CustomerID'
)
BEGIN
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON dbo.Orders (CustomerID)
INCLUDE (OrderDate, TotalAmount)
WITH (ONLINE = ON, MAXDOP = 4);
END
GO
-- Rollback: DROP INDEX IX_Orders_CustomerID ON dbo.Orders;Important
Always review generated scripts before applying to production. While the agent includes safety checks and follows best practices, human verification is essential.
SQLPerfAgent uses a plugin-based toolbox system. At startup, it scans the Toolbox/ folder for subfolders containing a tool.md manifest and one or more .sql scripts.
| Tool | Description |
|---|---|
| BestPracticesChecks | Memory pressure, backup status, MaxDOP, instant file initialization, deprecated features |
| VLFCheck | Virtual Log File count analysis across all databases |
| TempDBChecks | File count, size equality, autogrow settings, file location validation |
| DuplicateIndexes | Exact duplicate, duplicate key, and redundant index detection |
These are sourced from Microsoft's Tiger Toolbox, adapted for the plugin format.
- Scans
Toolbox/for subfolders with atool.md+ at least one.sqlfile - Reads
tool.mdto determine script execution order and batch separation mode - Passes
tool.mdcontent to the AI as context so Copilot knows how to interpret each tool's output - Displays all discovered tools for the user to select from
Tip
Add or remove tools by adding or removing subfolders in Toolbox/. No code changes required.
Toolbox/
└── WaitStats/
├── tool.md # Required: manifest & AI context
└── WaitStats.sql # Required: one or more .sql files
This file serves dual purpose — documentation for users and prompt context for the AI:
# Wait Statistics Analysis
Analyzes SQL Server wait statistics to identify performance bottlenecks.
## Scripts
Run `WaitStats.sql` as a single execution.
## What It Checks
- Top 10 wait types by total wait time (excluding benign waits)
- Identifies I/O, CPU, memory, and locking bottlenecks
## Interpretation
- PAGEIOLATCH waits → disk I/O bottleneck, check for missing indexes
- CXPACKET waits → parallelism issues, review MaxDOP settings
- High severity if any single wait type exceeds 40% of total waitsConventions:
- Include
"GO batch separation"in the text if your script usesGOstatements between batches - Script execution order follows the order
.sqlfilenames appear in the markdown (alphabetical fallback) - Write the interpretation section as if explaining to a DBA — the AI uses it directly
SELECT TOP 10
wait_type AS WaitType,
waiting_tasks_count AS WaitCount,
wait_time_ms / 1000.0 AS WaitTimeSec,
CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,1)) AS PctOfTotal
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP_TASK', 'BROKER_IO_FLUSH', 'WAITFOR')
ORDER BY wait_time_ms DESC;Scripts should return result sets with descriptive column names. Include Status or Recommendation columns when possible for best AI interpretation.
┌────────────────────────────────────────────────────────────┐
│ SQLPerfAgent CLI │
│ │
│ ┌───────────────┐ ┌─────────────────┐ ┌──────────────┐ │
│ │ SqlQuery │ │ Toolbox │ │ CopilotFix │ │
│ │ Service │ │ Discovery & │ │ Service │ │
│ │ (DMV queries) │ │ Execution │ │ (AI + MCP) │ │
│ └───────┬───────┘ └────────┬────────┘ └──────┬───────┘ │
└──────────┼───────────────────┼──────────────────┼──────────┘
│ │ │
▼ ▼ ▼
┌──────────┐ ┌──────────┐ ┌──────────────┐
│ SQL │ │ Toolbox/ │ │ GitHub │
│ Server │ │ Plugins │ │ Copilot │
└──────────┘ └──────────┘ │ SDK │
└──────┬───────┘
│
▼
┌──────────────┐
│ mssql-mcp │
│ Server │
└──────────────┘
For detailed architecture documentation — including project structure, component responsibilities, technology stack, data flow diagrams, and how the AI integration works — see the full documentation.
The docs/ folder contains in-depth documentation:
- Problem & Solution — Why this tool exists and how it works
- Deployment Guide — Self-contained publish, framework-dependent deployment, Docker, and deployment checklist
- Architecture Deep Dive — Component responsibilities, technology stack, AI integration pipeline, data flow diagrams
- Data Flow & Privacy — What data is sent where, credential handling
- Responsible AI Notes — Intended use, human oversight, limitations, transparency
- GitHub Copilot SDK — AI engine powering the analysis
- Microsoft Tiger Toolbox — Source for built-in diagnostic scripts
- SQL Server DMV Reference — Dynamic Management Views documentation
- mssql-mcp — MCP server for SQL Server connectivity
- GitHub Copilot Privacy — Data handling policies