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
  • Index Page Density
• 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.

4. Constraints (Top 400)

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 100)

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.

8. Index page density (top 200)

This signal highlights indexes with inefficient page usage—often the earliest warning signs of wasted I/O, excessive page splits, and suboptimal fill factors.

9. 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.

10. Query Store forced plan failures (when enabled)

• Scope: Filtered by schema and/or table when specified by the user, using referenced objects in Query Store plan XML (//Object)
• Collected via a separate query against sys.query_store_plan and sys.query_store_query
• Identifies all forced plans (is_forced_plan = 1) and reports force_failure_count, last_force_failure_reason_desc, and the associated query text
• Ordered by failure count descending
• Uses TRY_CAST(query_plan AS xml) before parsing object nodes, skipping non-XML plans safely
• Uses namespace-safe object matching (local-name()='Object') for compatibility across Showplan XML namespace variants
• Uses a top-N runtime-impact candidate prefilter before XML parsing to reduce overhead on large Query Store datasets
• Helps the AI identify queries where forced plans are failing due to dropped indexes, schema changes, or stale plans, and recommend corrective actions (unforce, recreate objects, etc.)
• Gracefully handles Query Store not available or not enabled

11. Automatic tuning options (SQL Server 2017+)

• Scope: Database-wide (not filtered by schema/table scope)
• Collected via a separate query against sys.database_automatic_tuning_options
• Checks whether FORCE_LAST_GOOD_PLAN is enabled, reporting desired_state_desc, actual_state_desc, and reason_desc
• Uses an OBJECT_ID guard so the query is skipped gracefully on SQL Server 2016 and earlier
• Helps the AI recommend enabling automatic plan correction when it is not active, or diagnose why the actual state differs from the desired state

12. Duplicate and overlapping indexes (top 30)

• Scope: Filtered by schema and/or table when specified by the user
• Detects two patterns within each table:
  • Exact key duplicates – two indexes with identical ordered key column lists
  • Left-prefix overlaps – a narrower index whose key list is a strict left-prefix of a wider index’s key list
• Reports key and included columns for both indexes, plus reads, writes, size, and uniqueness of the narrower index so consolidation recommendations are evidence-based
• Notes when the pair has different filter definitions (filtered vs. unfiltered indexes are not interchangeable)
• Excludes disabled and hypothetical indexes; uses exact string comparison rather than LIKE so special characters in column names cannot skew matching
• The AI is instructed never to recommend dropping an index that enforces a primary key or unique constraint without replacing the constraint

13. Untrusted or disabled constraints (top 100)

• Scope: Filtered by schema and/or table when specified by the user
• Lists foreign keys and check constraints where is_not_trusted = 1 or is_disabled = 1
• Untrusted constraints block optimizer join elimination and predicate pruning, silently degrading query plans
• The AI recommends re-validating with ALTER TABLE ... WITH CHECK CHECK CONSTRAINT ... after confirming existing data complies, noting the validation scan cost on large tables

14. Missing index overlap signals (top 15)

• Scope: Filtered by schema and/or table when specified by the user
• For the top missing-index candidates, finds the best-matching existing index and computes a redundancy score
• Includes table row counts, estimated storage, and estimated write penalty so the AI can weigh creating a new index against widening an existing one

15. Heap table analysis (top 15)

• Scope: Filtered by schema and/or table when specified by the user
• Large heap tables with row totals, reserved size, forwarded-record counts, and fragmentation
• Forwarded records on heaps cause extra I/O; the AI typically recommends adding a clustered index or rebuilding the heap

16. Index operational hotspots (top 15)

• Scope: Filtered by schema and/or table when specified by the user
• Per-index lock, latch, and leaf-operation pressure from sys.dm_db_index_operational_stats
• Surfaces row/page lock waits and page-latch contention that indicate hotspot indexes

17. Query Store write workload mix (when enabled)

• Scope: Filtered by schema and/or table when specified by the user, using referenced objects in Query Store plan XML
• Top recent INSERT/UPDATE/DELETE/MERGE workload buckets, helping the AI weigh index write overhead against the actual write rate

Scope Filtering

When you specify a schema or schema + table in the Index Tuning UI, the following object-level collectors are automatically filtered to that scope:

• Filtered Collectors: Missing indexes, index usage stats, duplicate and overlapping indexes, missing index overlap signals, heap table analysis, index operational hotspots, constraints, untrusted or disabled constraints, column cardinality, large tables, index page density, Query Store workload insights, Query Store write workload mix, and Query Store forced plan failures
• Database-wide Collectors: Plan cache, automatic tuning options, and database statistics settings

This allows you to:

• Deep-dive into a specific table’s indexes by selecting schema + table
• Analyze all indexes within a schema by selecting schema only
• Get comprehensive database-wide analysis by leaving scope empty

All scope filters are validated against a safe identifier pattern and double-escaped to prevent SQL injection.

---

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

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

3. Constraint trust repairs

   • Re-validate untrusted foreign keys and check constraints (ALTER TABLE ... WITH CHECK CHECK CONSTRAINT ...)
   • Restores optimizer join elimination and predicate pruning

4. Index rebuilds / page density improvements

   • Indexes with low page utilization (below ~75%) flagged for rebuild
   • Fill factor adjustment recommendations
   • Identification of page split issues from random inserts

5. Forced plan failure resolution

   • Identification of queries with failing forced plans
   • Root cause analysis (dropped indexes, schema changes, stale plans)
   • Recommendations to unforce plans or recreate required objects

6. Automatic tuning recommendations

   • Recommend enabling FORCE_LAST_GOOD_PLAN when it is not active (SQL Server 2017+)
   • Report and recommend corrective action when the actual state differs from the desired state

7. 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

8. Statistics recommendations

   • Update statistics guidance
   • Consider auto stats settings where relevant
   • Recommend statistics creation or drops when beneficial

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
• Analyze page density – identify indexes with low page utilization (below ~75%) and recommend rebuilds, fill factor adjustments, or investigate page split causes
• Consolidate duplicate and overlapping indexes – drop exact duplicates and merge left-prefix overlaps using reported reads/writes/size as evidence, never dropping constraint-backing indexes without replacement
• Repair constraint trust – recommend WITH CHECK CHECK CONSTRAINT re-validation for untrusted foreign keys and check constraints, noting validation cost on large tables
• Investigate forced plan failures – identify affected queries and failure reasons, recommend resolving the underlying cause or unforcing the plan
• Review automatic tuning options – if FORCE_LAST_GOOD_PLAN is not enabled, recommend enabling it; if the actual state differs from the desired state, report the reason and recommend corrective action
• 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
QueryTuner	Single-query performance tuning	Tuning a specific slow query with execution plan analysis	Yes
Code Review	T-SQL best practices and anti-patterns	Reviewing stored procedures/functions/triggers quality	Yes
Locking and Blocking Analysis	Live concurrency diagnostics and contention root-cause analysis	Investigating active blocking chains, lock waits, and transaction contention	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 Claude Opus 4.8:

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