SQL Server Query Tuner

Tune Any SQL Query with AI-Powered Execution Plan Analysis

Is a specific SQL query dragging down your SQL Server performance? Slow queries cost time and money, but identifying the root cause — buried inside execution plans, missing indexes, and outdated statistics — requires deep expertise and hours of manual analysis.

AI SQL Tuner Studio’s Query Tuner goal analyzes any individual SQL query you provide. It retrieves the estimated execution plan, identifies every referenced table, collects targeted metadata, and feeds everything to an AI reasoning model that delivers prioritized, actionable tuning recommendations in minutes.

Compare Editions and Download AI SQL Tuner Studio — the Free edition works with SQL Server Developer Edition at no cost. For Standard, Enterprise, and other editions, start a 14-day free trial. All paid editions include a 30-day money-back guarantee.

---

What Is SQL Query Tuning?

SQL query tuning is the process of analyzing and optimizing a SQL statement to reduce execution time, lower resource consumption, and improve overall database performance. It typically involves examining the execution plan, evaluating index usage, reviewing join strategies, and rewriting queries to eliminate inefficiencies.

DBAs and developers traditionally approach query tuning by manually running SET STATISTICS and SHOWPLAN commands, scrolling through XML execution plans, cross-referencing DMV outputs, and testing rewrites one at a time. Even experienced professionals can spend hours tuning a single complex query.

AI SQL Tuner Studio’s Query Tuner automates this entire workflow. Paste your SQL query, click Run, and receive a detailed HTML report with execution plan analysis, index recommendations with ready-to-run DDL scripts, query rewrite suggestions with before-and-after examples, and parameter sniffing mitigation strategies — all prioritized by impact.

---

What Query Tuner Collects and Analyzes

Query Tuner performs a focused, query-level analysis. Here is exactly what it gathers and examines for your SQL query.

1. Estimated Execution Plan

Query Tuner executes SET SHOWPLAN_XML ON to capture the full XML execution plan without actually running your query. This means zero impact on your production workload. The plan is retrieved on a separate connection to avoid affecting your session state.

2. Execution Plan Operator Analysis

The XML plan is parsed to extract every operator in the query’s execution: seeks, scans, hash joins, merge joins, nested loops, sorts, spools, and more. Each operator is evaluated for cost contribution and potential inefficiency.

3. Execution Plan Warnings

The tool specifically identifies critical warnings embedded in the execution plan:

• Missing index recommendations from the query optimizer
• TempDB spills indicating insufficient memory grants
• No join predicate warnings
• Implicit conversions that prevent index seeks
• Columns with no statistics
• Memory grant warnings

4. Referenced Table Identification

Every table referenced in the execution plan is identified by database, schema, table name, and index name. This includes tables accessed through views — the underlying base tables are captured from the plan XML.

5. Targeted Metadata Collection

For each referenced table, Query Tuner collects:

• Index usage statistics from sys.dm_db_index_usage_stats — seeks, scans, lookups, updates, size, and compression settings
• Missing index recommendations from the sys.dm_db_missing_index_details DMVs
• Table sizes — row counts and space usage
• Foreign key relationships between referenced tables
• Column cardinality via DBCC SHOW_STATISTICS density vectors
• Database statistics settings — auto create/update stats configuration

Temp tables and table variables are automatically excluded from metadata collection since they don’t have persistent statistics.

---

What You Get in the Report

The AI produces a prioritized HTML report containing the following sections.

Query Performance Analysis

The report begins with an executive summary identifying the top bottlenecks in your query. The AI analyzes every operator in the execution plan — seeks vs. scans, join types, sort operations, spool operators — and identifies which ones contribute the most to query cost. Warnings like TempDB spills, implicit conversions, and missing statistics are highlighted with specific remediation steps.

Index Recommendations

Query Tuner delivers ready-to-run CREATE INDEX scripts for missing indexes that would benefit your specific query. Beyond new indexes, it also evaluates existing indexes for consolidation opportunities and provides compression and fill factor guidance when the data supports it.

Query Rewrite Suggestions

The report includes before-and-after T-SQL examples showing exactly how to rewrite your query for better performance. Common improvements include SARGability fixes (making predicates index-friendly), join and predicate optimizations, SELECT * elimination, and CTE or subquery restructuring.

Table-Specific Insights

Each referenced table gets its own analysis section with row count and size context, foreign key relationship analysis, and column cardinality considerations that affect join strategies and index effectiveness.

Statistics Recommendations

The AI reviews your database’s auto-stats configuration and the state of statistics on referenced columns. Recommendations may include updating stale statistics, creating statistics on unindexed columns, or adjusting auto stats settings.

Parameter Sniffing Considerations

Query Tuner detects parameter-sensitive patterns in your query and suggests mitigation strategies such as OPTION (RECOMPILE), OPTIMIZE FOR hints, or plan guide approaches when appropriate.

---

How to Run Query Tuner

Running a query analysis takes just a few steps.

Step 1: Select or Create a Connection

Choose an existing connection from the Connections list or create a new one. The connection must specify a user database — Query Tuner cannot run against system databases (master, tempdb, model, msdb).

Step 2: Choose the Query Tuner Goal

Select Query Tuner from the tuning goal dropdown in the main toolbar.

Step 3: Enter Your SQL Query

Paste or type your SQL query in the “Query to analyze” text box. Query Tuner handles SELECT statements, multi-table JOIN queries, CTEs, subqueries, window functions, and queries referencing views.

Step 4: Set Reasoning Effort

Optionally adjust the reasoning effort level — Low, Medium, or High — to control the depth of AI analysis. Higher effort produces more detailed recommendations but takes longer and uses more tokens.

Step 5: Click Run

Progress and data collection status appear in the Console panel. The AI model being used is displayed during execution. When analysis completes, the HTML report appears in the Report panel and can be saved or exported.

---

Supported Query Types

Query Tuner handles a wide range of SQL statement patterns:

• Simple SELECT statements
• Multi-table JOIN queries (inner, outer, cross)
• Common Table Expressions (CTEs) and subqueries
• Window functions (ROW_NUMBER, RANK, LAG, LEAD, etc.)
• Queries with missing indexes or plan warnings
• Queries with TempDB spills or memory grant issues
• Views (underlying base tables are captured from the execution plan)
• Temp tables and table variables (automatically excluded from metadata collection)

---

Platform Support

Query Tuner works across all supported SQL Server platforms:

Platform	Support Level
SQL Server on-premises	Fully supported
Azure SQL Database	Fully supported
Azure SQL Managed Instance	Fully supported
Microsoft Fabric SQL Database	Fully supported

---

Permissions Required

Query Tuner requires minimal permissions on the target database. Grant these three permissions to the login used by AI SQL Tuner Studio:

• SHOWPLAN — required to retrieve estimated execution plans
• VIEW DATABASE STATE — required to read sys.dm_db_missing_index_*, sys.dm_db_index_usage_stats, sys.dm_db_partition_stats, sys.partitions, and DBCC SHOW_STATISTICS (SQL Server 2012 SP1+)
• VIEW DEFINITION — required to read sys.objects, sys.schemas, sys.indexes, sys.index_columns, sys.columns, sys.foreign_keys, and sys.foreign_key_columns

Grant with:

USE [YourDatabase];
GRANT SHOWPLAN TO [YourLogin];
GRANT VIEW DEFINITION TO [YourLogin];
GRANT VIEW DATABASE STATE TO [YourLogin];

For a complete guide on setting up a least-privilege account, see the Least Privileged Account Creation Guide.

---

How Query Tuner Compares to Other Tuning Goals

AI SQL Tuner Studio offers several tuning goals, each designed for a different scenario.

Goal	Focus	Best For	Database Required
Query Tuner	Single-query performance tuning	Tuning a specific slow query with execution plan analysis	Yes
Index Tuning	Index and query optimization	Slow queries, missing indexes, high IO/CPU across the database	Yes
Code Review	T-SQL best practices and anti-patterns	Reviewing stored procedures, functions, triggers, and views	Yes
Fix Deadlocks	Deadlock analysis and prevention	Investigating deadlock graphs and blocking patterns	No
Server Health	Overall health, config, security, operations	Regular checkups or general performance issues	No

Use Query Tuner when you have a specific query to optimize. Use Index Tuning for broader database-wide index analysis. Use Code Review when evaluating stored procedure quality rather than runtime performance.

---

Output Format

The HTML report includes:

• Executive summary with top priorities
• Query performance analysis identifying bottlenecks from the execution plan
• Detailed, prioritized recommendations with T-SQL code examples
• Index recommendations with full CREATE INDEX scripts
• Query rewrite suggestions with before-and-after examples
• Server name, SQL Server version, and database context
• Footer showing the AI model and reasoning effort used

If the AI response is truncated due to output token limits, a warning banner is displayed in the report. To resolve, lower the reasoning effort level.

---

Troubleshooting

Query Tuner goal is disabled or shows a database validation error

Ensure your connection specifies a user database — not master, tempdb, model, or msdb. Edit the connection in the Connections section and set a valid user database.

Invalid query syntax error

The SQL query could not be parsed by the server. Check for syntax errors in the “Query to analyze” text box.

SHOWPLAN permission error

Ensure the login has SHOWPLAN, VIEW DEFINITION, and VIEW DATABASE STATE permissions on the target database. See the Permissions Required section above.

No tables found in execution plan

The query does not reference any user tables. Metadata collection is skipped, but the AI will still analyze the execution plan.

Request too large dialog

The collected data exceeds the model’s context limit. Consider simplifying the query, or configure a secondary model with a larger context window in AI Configuration.

---

Get Started — Download AI SQL Tuner Studio

Ready to tune your slow SQL queries? Compare Editions and Download AI SQL Tuner Studio to start analyzing query execution plans and receiving AI-powered tuning recommendations today.

The Free edition supports SQL Server Developer Edition at no cost. For Standard, Enterprise, Azure SQL Database, Azure SQL Managed Instance, and Microsoft Fabric SQL Database, start a 14-day free trial. All paid editions include a 30-day money-back guarantee.