Index Tuning Goal

Optimize SQL Server Performance with AI-Powered Index Tuning

Poorly designed indexes are the #1 cause of slow SQL Server queries. The Index Tuning goal in AI SQL Tuner Studio analyzes your SQL Server missing index recommendations, index usage patterns, plan cache and Query Store workload data, column cardinality, constraints, and database statistics settings — then delivers prioritized, AI-driven index and query recommendations that balance read performance with write overhead.

Whether you are troubleshooting slow queries, consolidating redundant indexes, or optimizing a high-transaction OLTP workload, the Index Tuning goal gives you actionable CREATE/ALTER/DROP scripts you can review and apply immediately.

Compare AI SQL Tuner Studio Editions to find the right plan for your environment.

---

Table of Contents

• What It Collects and Analyzes
  • Missing Indexes
  • Index Usage Stats
  • Plan Cache Stats
  • Query Store Workload
  • Constraints
  • Column Cardinality
  • Large Tables
  • Database Statistics Settings
• What You Get
• Key Differences from Other Goals
• Platform Support
• Permissions Required
• Output Format
• Troubleshooting
• Sample Report
• Get Started
• Related Resources

---

What It Collects and Analyzes

The Index Tuning goal collects data using a main multi-result-set diagnostic query plus a separate Query Store workload query. Each data section is logged with its character count for monitoring and transparency.

1. Missing Indexes (Top 50)

Missing index data is sourced from the sys.dm_db_missing_index_* DMVs. Only high-impact recommendations are included — those with an impact score above 100,000 (calculated as avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)). For each missing index, the analysis captures equality columns, inequality columns, and included columns, ordered by impact score descending so the most beneficial indexes surface first.

2. Index Usage Stats (Top 50)

All indexes on user tables are collected along with their key columns, included columns, usage statistics (seeks, scans, lookups, updates), size in MB, and compression state. Results are ordered by update-to-read ratio descending then total reads ascending — this surfaces high-maintenance, low-benefit indexes first, making it easy to identify candidates for consolidation or removal.

3. Plan Cache Stats (Top 15)

The top resource-consuming queries from the plan cache are captured, ordered by total CPU time. For each query, the analysis includes execution count, average CPU time, average logical reads, and query text (truncated to 600 characters). On SQL Server 2019 and later, max_grant_kb and max_spills are also captured to identify memory grant and tempdb spill issues; on earlier versions these are reported as 0.

Query Store Workload (When Enabled)

When Query Store is enabled, a separate workload query collects data from sys.query_store_query, sys.query_store_plan, sys.query_store_runtime_stats, and sys.query_store_query_text. This provides richer historical insight than the plan cache alone.

The Query Store analysis aggregates query-level and plan-level metrics over a configurable lookback window (default: 7 days) and reports the top queries by total duration, CPU, and execution count. Captured metrics include query_id, plan_id, execution_count, total_duration_ms, avg_duration_ms, total_cpu_ms, avg_cpu_ms, avg_logical_reads, max_duration_ms, and last_execution_time.

Plan metadata is also captured — whether a plan is forced, plan creation and last compile time, and the normalized query text — so recommendations can be tied to stable workloads rather than transient cached plans.

Plan regression detection: The analysis compares current plan performance to historical baselines (median and percentiles) and flags queries with significant performance regressions. When parameter sensitivity is evident, Query Store data is used to surface parameter-driven regressions and suggest parameterization or plan-stability mitigations.

Query Store is preferred over the plan cache for historical trend analysis and regression detection. If Query Store is not enabled or configured, the tool falls back to plan cache data and reports that Query Store data was not available.

4. Constraints (Top 200)

Primary keys (PK), unique constraints (UQ), and foreign keys (F) are collected. Foreign key data includes the referenced table and column — the AI uses this when recommending primary key changes that require FK drop/recreate scripts, ensuring all dependent objects are handled correctly.

5. Column Cardinality (Density Vector)

Column-level statistics are collected using DBCC SHOW_STATISTICS ... WITH DENSITY_VECTOR, capturing density, estimated distinct values, and average column length. This data is gathered for tables referenced by the top 20 highest-impact missing indexes (score > 100,000) or the top 20 largest user tables (> 1,000 rows). Column cardinality helps the AI prioritize high-selectivity columns for index keys and avoid indexing low-cardinality columns that would not improve seek performance.

6. Large Tables (Top 50)

User tables with more than 1,000,000 rows are identified separately. For these tables, the AI considers clustered and nonclustered columnstore indexes when appropriate, which can dramatically improve analytical query performance and reduce storage through columnar compression.

7. Database Statistics Settings

The analysis checks is_auto_create_stats_on, is_auto_update_stats_on, and is_auto_update_stats_async_on for the target database. A warning is included if automatic statistics management is not fully enabled, since outdated statistics are a common cause of suboptimal query plans.

---

What You Get

The AI produces a prioritized set of recommendations, typically including:

1. Create Index Scripts

   • Proposed nonclustered indexes with optimized key and included columns
   • Filtered index suggestions when predicates target specific value ranges
   • Columnstore index suggestions for large fact tables (1M+ rows) when appropriate
   • Scripts include compression settings and fillfactor when appropriate

2. Index Modifications and Consolidation

   • Combine overlapping indexes to reduce maintenance overhead
   • Reduce unnecessary INCLUDE columns
   • Fillfactor and compression optimization suggestions

3. Query Optimization Recommendations

   • SARGability improvements to enable index seeks
   • Predicate and join clause optimizations
   • Eliminate SELECT * anti-patterns
   • Parameter sniffing mitigation suggestions
   • Plan cache or Query Store statement examples that benefit from each recommendation

Each recommendation includes a priority level, confidence score, and estimated impact so you can focus on the changes that matter most.

What does “SARGable” mean?

SARGable stands for Search ARGument Able. A predicate is SARGable when SQL Server can efficiently use an index seek to evaluate it. Non-SARGable predicates — such as wrapping a column in a function (WHERE YEAR(OrderDate) = 2025), using leading wildcards (WHERE Name LIKE '%smith'), or performing implicit type conversions — force full index scans and negate the benefit of indexes. The AI identifies these patterns and suggests rewrites.

AI Prompt Behavior

The AI is instructed to:

• Weigh write overhead — analyze user_updates vs. user_seeks + user_scans before recommending indexes, especially for indexes not in the missing-index report
• Apply row-count thresholds — avoid indexes on tables under ~1,000 rows; consider selectively for 1,000-10,000 rows; generally recommend for > 10,000 rows with selective predicates
• Prioritize high-cardinality columns — use density and distinct-value data to favor selective columns
• Consider columnstore — for tables over 1,000,000 rows where analytical patterns are detected
• Consider filtered indexes — when predicates target specific value ranges
• Include FK scripts — when recommending PK changes, also script dependent FK drop/recreate
• Cite plan cache and Query Store queries — link recommendations to specific high-cost statements when possible
• Detect plan regressions — flag queries with significant performance regressions vs. historical baselines
• Consider compression and fillfactor in all scripts
• Skip system databases — recommendations are scoped to user objects only

---

Key Differences from Other Goals

Goal	Focus	Use When	Database Required
Index Tuning	Index and query optimization	Slow queries, missing indexes, high IO/CPU	Yes
Code Review	T-SQL best practices and anti-patterns	Reviewing stored procedures/functions/triggers quality	Yes
Fix Deadlocks	Deadlock analysis and prevention	Investigating deadlock graphs and blocking patterns	No (defaults to master for supported platforms)
Server Health	Overall health, config, security, operations	Regular checkups or general performance issues	No (defaults to master)

Platform Support

• On-premises SQL Server: Fully supported
• Azure SQL Database: Supported
• Azure SQL Managed Instance: Supported
• Microsoft Fabric SQL Database: Supported (treated similarly to Azure SQL Database)

Permissions Required

Minimum permissions needed:

• VIEW SERVER STATE — required to read plan cache DMVs (sys.dm_exec_query_stats, sys.dm_exec_sql_text)
• 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 2012 SP1+)
• VIEW DEFINITION — required to read sys.objects, sys.schemas, sys.indexes, sys.index_columns, sys.columns, sys.stats, sys.foreign_keys, sys.foreign_key_columns, sys.sysconstraints

Grant with:

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

Depending on your environment and policies, some DMV access may be restricted.

Output Format

The analysis generates an HTML report with an executive summary, detailed recommendations with priority levels, and ready-to-execute T-SQL scripts. The report footer includes the AI model and reasoning effort used. Reports can be viewed in a browser or saved as an .html file for sharing with your team.

Troubleshooting

“User Database is required for Index Tuning goal.”

• Choose a connection with a user database selected — Index Tuning cannot run against system databases.

“Access denied” / DMV permission errors

• Ensure the login has VIEW DATABASE STATE and VIEW SERVER STATE permissions.

Query Store data not available

• Verify that Query Store is enabled and configured with sufficient retention. The tool will fall back to plan cache analysis if Query Store is not available.

---

See a Sample Index Tuning Report

Want to see what an AI-powered index tuning report looks like? View a real example generated using GPT-5.3 Codex:

View Sample Index Tuning Report

The report includes an executive summary, prioritized recommendations with confidence levels, ready-to-execute T-SQL scripts, and plan cache query examples — all generated automatically from a single analysis run.

---

Get Started with SQL Server Index Tuning

Ready to optimize your SQL Server indexes? AI SQL Tuner Studio analyzes your workload and delivers prioritized, AI-powered recommendations in minutes — no manual DMV scripting required.

Compare AI SQL Tuner Studio Editions

Related Resources

• SQL Server Index Design Guide (Microsoft)
• System Dynamic Management Views (Microsoft)
• Server Health Check
• Code Review Goal
• Fix Deadlocks Goal