SQL Server Performance Tuning for DBAs and Developers
What SQLPerformance AI does for a DBA and for a developer, one finding traced from evidence to action, and real analysis reports exported from the app.
Getting Started
SQLPerformance AI is a read-only Windows desktop app for SQL Server performance analysis. This page shows what it does for a DBA and for a developer, and what its output looks like. For prerequisites, permissions, and AI setup, read the Overview.
Why SQLPerformance AI?
SQL Server already records most of what you need to diagnose a slow server, and SSMS gives you direct access to it. What it leaves to you is the reading: which of hundreds of queries matters, whether two plans for one query are a problem, whether an index with no reads is safe to drop. The app reads the same sources and does that first pass for you.
- Query Store: plans and runtime history per query.
- Dynamic management views: waits, sessions, locks, index usage.
- Graphical execution plans in SSMS.
- SQL Agent job history in msdb.
- Ranking: Queries ordered by Impact Score (total elapsed time), with a 0-100 risk score, a trend, and the ratio between the slowest and fastest plan.
- Stated rules: Index drop-safety states, blocking severity bands, and a scored security audit, each with documented thresholds.
- Cross-checks: Reports list the signals they checked and set aside, for example a missing-index suggestion that does not match the query.
- Reports: HTML and Markdown exports with evidence tables and read-only verification queries your team can review.
- Optional AI: An AI explanation in three modules, built on top of the rule-based result rather than replacing it.
It does not replace SSMS. You still make every change yourself, in SSMS or through your deployment process. The feature overview lists what each of the eight modules analyzes.
Built for DBAs and Developers
The same modules answer different questions depending on who is asking. Each card names the module to open and links to its documentation.
For DBAs
“Which queries cost the most, and which ones got worse?”
Ranks queries from Query Store, or from the plan cache when Query Store is not usable, and shows the trend and plan stability of each one.
Query Statistics“Users say the application is frozen.”
Shows live blocking chains as a graph and a tree, the head blocker and its statement, and a severity based on wait time. It does not kill sessions.
Blocking Analysis“The server is slow and nothing stands out.”
Groups waits into categories, measures them as the difference between two snapshots, and adds Query Store wait history where it is available.
Wait Statistics“Can I drop this index?”
Gives each index a class, a score, and a drop-safety state: Do Not Drop, Validate Before Drop, or Safe Drop Candidate. A DROP script is never generated for the first two.
Index Advisor“An auditor asks for evidence.”
Scores logins, permissions, configuration, and patch level, lists findings by severity, and saves the result as an HTML report.
Security Audit“A job failed overnight.”
Groups SQL Agent failures from the last 24 hours by root cause and shows schedules and Database Mail health from msdb.
Scheduled JobsFor developers
“Fast in my tests, slow in production.”
Compares the plans Query Store kept for one query. In a sample report, one procedure had a plan averaging 0.08 ms and another averaging 6.95 s.
Query Statistics“What is this execution plan telling me?”
The Execution Plan tab lists operators, plan warnings, and missing-index suggestions. AI reports check a suggestion against the query before recommending it.
Query Statistics“Review a stored procedure before release.”
Shows source code, statistics, and dependencies. AI Tune sends the source and its plan to the AI provider you configure and returns a report.
Object Explorer“Did my fix actually help?”
Run the analysis again after the change. In this use case RECOMPILE cut reads by 95%, and the re-analysis still showed that the design was the real cost.
Use caseFrom Evidence to Action
One finding from a real Index Advisor report, traced from the raw counters to the decision left to you. AI is optional: without a provider, the rule-based result in step 2 is what you get.
Evidence
Read from SQL Serversys.dm_db_index_usage_stats shows 0 seeks, 0 scans, 0 lookups, and 0 updates for ix_Cities_Archive. The table has 28 rows on one page, the statistics are 3,772 days old, and there is no Query Store context.
Rule-based result
Computed by the appClassification UNNECESSARY, score 41/100. Drop safety VALIDATE_BEFORE_DROP. Because there is no 14-day usage baseline, the validation gate suppresses executable index DDL.
AI explanation
Optional, your providerExplains the result and its caveats: DMV counters reset after a restart or failover, so zero reads over a short window can be a false negative. The report states that the rule-based classification is explained, not overridden.
Independent review
Second AI passA critic pass reviews the proposed actions before they reach the report. Here: 2 approved, 0 revised, 0 rejected.
Your decision
You run it, or notTwo next steps: refresh the statistics with one UPDATE STATISTICS command, and monitor usage for 14 days before any drop decision. The report adds read-only verification queries. The app does not run any of them.
The full report is the third card in the gallery below. The Index Advisor documentation lists the drop-safety criteria behind step 2.
Explore Real Analysis Reports
These files were exported from the app. Judge the output yourself: each report opens in a new tab, and the line under it says which build produced it.
- Two Query Store plans for one procedure: 0.08 ms and 2 logical reads versus 6.95 s and 5.72 million.
- A missing-index candidate checked against the query and cleared as unrelated.
- Server-level pressure ruled out with CPU, page life expectancy, and buffer cache figures.
- Key Lookups on two order tables traced to columns the index does not cover.
- A covering index proposal with a verification script and its rollback statement.
- The safety review marks the CREATE INDEX as a write action that needs DBA review.
- Zero reads, but drop safety stays at Validate Before Drop.
- Executable DDL suppressed; only a statistics refresh and 14 days of monitoring.
- An evidence table with the weight and caveat of each signal.
- Maturity level L3 / 5 and score 71 / 100 with a per-category breakdown.
- 22 issues by severity, patch status (558 days behind), surface area, and the login list.
- Rule-based: no AI provider is involved.
- Twenty checks with Severity, Category, Finding, Current, Recommended, and Action.
- Counters: 0 Critical, 1 Warning, 8 Info, 11 Pass.
- Read-only: it reads configuration and system views.
- Wait time by category: CPU holds 5,441 of the 6,647 ms, and CXPACKET leads the top waits.
- Signatures with a confidence: CPU Pressure at 0.95.
- An eight-day trend with the dominant category per day. No AI provider is involved.
The three AI reports were generated on 2026-10-04 with a build before 1.1.0 against a test database built on the WideWorldImporters sample. Each module page has more examples: Query Statistics, Object Explorer, Index Advisor, and Blocking Analysis.
Designed for Production Servers
The app is built to be safe to point at a production instance. What it does and what it does not do:
- Reads dynamic management views, catalog views, Query Store, and msdb.
- Generates scripts for you to review, copy, and run yourself.
- Checks AI-suggested SQL with SET PARSEONLY ON, so SQL Server checks the syntax without running it.
- Uses a local Ollama model by default; a cloud provider is used only if you configure one.
- Run the scripts it generates or apply AI recommendations.
- Kill sessions. Blocking Analysis shows the head blocker; you decide what to do.
- Install agents or collectors on your SQL Server hosts.
- Send anything to an AI provider before you start an AI analysis.
What each AI-enabled module sends, and to whom, is on the Trust & Security page. The minimum SQL Server permissions are in the Overview.