SQL Server Index Tuning: From Basics to AI-Assisted Recommendations in 2026

---

Indexes are the single most impactful tool available to a SQL Server developer or DBA for improving query performance. A well-placed index can turn a 30-second query into a 200-millisecond one without touching a line of application code. A poorly designed index strategy — too many, too few, or the wrong columns — can make a fast system slow, and a slow system grind to a halt under load.

This article covers the full picture: what indexes are and how they work at a physical level, how to choose the right index based on query metadata, why clustered index scans cost what they cost, the difference between unique and non-unique indexes, how SQL Server combines multiple indexes through index unions and intersection, how to use hints intelligently (and when never to use them), and how AI tools in 2026 — from the SSMS Copilot to Azure SQL automatic tuning to SQL Server 2025 Query Intelligence — are changing the way DBAs approach index recommendations.

1 Introduction — Why Indexes Matter and When They Don’t Beginner

Every query SQL Server executes must find the rows it needs somehow. Without an index, it reads every single row in the table — a full table scan — and discards the rows that do not match the filter. On a table with 100,000 rows this is fast enough that you will never notice. On a table with 100 million rows, that full scan can take minutes. An index is a separate, pre-sorted data structure that lets SQL Server navigate directly to the relevant rows without reading every row in the table first.

Think of it like a phone book. To find “Smith, John” in an unsorted list of 2 million names, you read every entry. In a phone book sorted alphabetically, you go directly to the S section, then the Sm subsection, and find the entry in seconds. That alphabetical organization is the index. SQL Server’s B-tree index structure provides the same directed navigation for your data.

When Indexes Help

• Queries that filter on the indexed column with a selective predicate (WHERE OrderDate = '2025-01-15')
• Queries that join two tables on the indexed column
• Queries that sort or group by the indexed column (avoids an explicit sort operation)
• Covering queries where all needed columns are in the index (no table lookup needed)

When Indexes Do Not Help — and Can Hurt

• Small tables. A table with under 5,000–10,000 rows is often faster to scan entirely than to navigate a B-tree. The optimizer usually chooses a scan on small tables regardless of available indexes.
• Low-selectivity columns. An index on a Status column with only three values (Active, Inactive, Pending) is rarely useful — SQL Server retrieves a large percentage of the table regardless and a scan is cheaper.
• Heavy write workloads. Every INSERT, UPDATE, and DELETE must also update every index on that table. A table with 12 indexes pays 12 index maintenance operations for every write. Over-indexing a heavily written table is a common source of write latency and blocking.
• Non-SARGable predicates. A function applied to the indexed column (WHERE YEAR(OrderDate) = 2025) prevents the optimizer from using the index at all. The index exists but cannot be navigated with a wrapped column.

2 Nonclustered Index Basics — Structure, Storage, and B-Trees Beginner

A nonclustered index is a separate data structure stored alongside the table. It contains the indexed column values, sorted and organized into a B-tree, plus a pointer to the actual row in the table. You can have up to 999 nonclustered indexes per table in SQL Server, though in practice you should have as few as needed to cover your workload.

What a B-Tree Index Contains

A B-tree index has three levels: the root page at the top, intermediate pages in the middle, and leaf pages at the bottom. The leaf level contains the actual index key values in sorted order. For a nonclustered index, each leaf page entry also contains a row locator — the clustered index key of the corresponding table row (or a physical row ID if the table has no clustered index).

-- Create a basic nonclustered index
CREATE NONCLUSTERED INDEX IX_Orders_OrderDate
ON dbo.Orders (OrderDate);
-- This creates a B-tree sorted by OrderDate
-- Leaf pages contain: OrderDate value + clustered index key (OrderID) for each row

-- The index helps queries like:
SELECT OrderID, CustomerID, TotalAmount
FROM dbo.Orders
WHERE OrderDate = '2025-03-15';

-- But SQL Server must make a KEY LOOKUP back to the clustered index
-- to retrieve CustomerID and TotalAmount (not in the index)
-- High volume of key lookups = expensive

Covering Indexes — Eliminating Key Lookups

A covering index contains all the columns a specific query needs — both the filter columns in the key and the output columns in INCLUDE. When a query is fully covered, SQL Server reads only the index and never touches the base table. No key lookup. This is the highest-efficiency form of nonclustered index.

-- Covering index: key columns filter the data, INCLUDE columns provide the output
-- For the query: SELECT OrderID, CustomerID, TotalAmount FROM Orders WHERE OrderDate = '2025-03-15'

-- Without covering index: Index Seek on OrderDate + Key Lookup for CustomerID/TotalAmount
CREATE NONCLUSTERED INDEX IX_Orders_OrderDate
ON dbo.Orders (OrderDate);

-- With covering index: Index Seek only -- no key lookup needed
CREATE NONCLUSTERED INDEX IX_Orders_OrderDate_Covering
ON dbo.Orders (OrderDate)
INCLUDE (CustomerID, TotalAmount, OrderID);
-- OrderDate: in the key (used for the seek -- navigation)
-- CustomerID, TotalAmount, OrderID: in INCLUDE (fetched from the leaf -- no round-trip needed)

-- Key design rule:
-- Key columns:     columns used in WHERE, JOIN, ORDER BY
-- INCLUDE columns: columns only needed in SELECT (output list)

Column Order in Composite Indexes

-- Composite index: multiple columns in the key
-- Column ORDER matters critically for which queries benefit

-- Index: (LastName, FirstName)
CREATE NONCLUSTERED INDEX IX_Customer_Name
ON dbo.Customers (LastName, FirstName);

-- This index HELPS:
WHERE LastName = 'Smith'                        -- leading column match
WHERE LastName = 'Smith' AND FirstName = 'John' -- both columns match

-- This index does NOT help efficiently:
WHERE FirstName = 'John'     -- FirstName is not the leading column
                             -- SQL Server cannot seek by FirstName alone in this index

-- Rule: most selective column first in most cases
-- Exception: if most queries filter by the less selective column alone,
--            put that column first so the index can be used for those queries

3 Clustered Index Basics — The Physical Table Beginner

A clustered index is fundamentally different from a nonclustered index. It does not create a separate data structure — it defines the physical sort order of the table itself. The table’s data pages are organized and stored in the order of the clustered index key. Because the data and the index are the same structure, there can only be one clustered index per table.

-- Create a clustered index (usually done at table creation)
CREATE TABLE dbo.Orders (
    OrderID      INT          NOT NULL,
    CustomerID   INT          NOT NULL,
    OrderDate    DATE         NOT NULL,
    TotalAmount  DECIMAL(18,2) NOT NULL,
    CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderID)
);
-- The PRIMARY KEY CLUSTERED constraint creates the clustered index on OrderID
-- The table's physical pages are sorted and stored in OrderID order

-- For OLAP or date-range heavy workloads, sometimes OrderDate is the better clustered key:
CREATE CLUSTERED INDEX IX_Orders_OrderDate_Clustered
ON dbo.Orders (OrderDate);
-- Now the table is physically sorted by OrderDate
-- Date range queries scan contiguous pages -- very efficient
-- But single-row lookups by OrderID require a full nonclustered seek

Heap vs Clustered Table

	Heap (No Clustered Index)	Clustered Table
Data storage	Unordered pages	Sorted pages by cluster key
Row locator in nonclustered indexes	Physical RID (file:page:slot)	Clustered index key value
Range scan performance	Poor — scattered random I/O	Excellent — contiguous pages
Forwarded records	Can occur on UPDATE causing double I/O	Not applicable
Best for	Staging tables, bulk load targets	Almost all production tables

4 Index Choice Based on Metadata — Reading DMVs to Find Gaps Intermediate

SQL Server tracks index usage and missing index recommendations continuously through Dynamic Management Views (DMVs). These are the foundation of evidence-based index tuning — instead of guessing which indexes to add or drop, you read what SQL Server itself is reporting about your workload.

Finding Missing Indexes — Old Way Legacy

-- Old approach: Database Engine Tuning Advisor (DTA)
-- In SSMS: Tools → Database Engine Tuning Advisor
-- Capture a workload via SQL Server Profiler trace or plan cache
-- Submit to DTA → DTA analyzes and suggests CREATE INDEX / CREATE STATISTICS scripts

-- DTA limitations:
-- 1. Requires a captured workload file -- extra steps to set up
-- 2. Runs offline analysis -- recommendations may not reflect live workload
-- 3. Can suggest overlapping or redundant indexes without deduplication
-- 4. Does not account for write cost trade-offs adequately
-- 5. Suggestions can be outdated if workload has shifted since capture

-- DTA is still available in SSMS and still useful for ad-hoc workload analysis
-- but the DMV-based approach below reflects the live production workload directly

Finding Missing Indexes — Current Approach Current

-- DMV-based missing index detection with impact scoring
-- SQL Server 2005+ -- reflects live workload since last restart

SELECT
    DB_NAME(mid.database_id)                    AS DatabaseName,
    OBJECT_NAME(mid.object_id, mid.database_id) AS TableName,
    migs.avg_total_user_cost
        * migs.avg_user_impact
        * (migs.user_seeks + migs.user_scans)   AS ImpactScore,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_user_impact,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    -- Generate a ready-to-review CREATE INDEX statement
    'CREATE NONCLUSTERED INDEX IX_' + OBJECT_NAME(mid.object_id) + '_Missing'
  + ' ON ' + mid.statement
  + ' (' + ISNULL(mid.equality_columns, '')
  + CASE WHEN mid.equality_columns IS NOT NULL
          AND mid.inequality_columns IS NOT NULL THEN ', ' ELSE '' END
  + ISNULL(mid.inequality_columns, '') + ')'
  + ISNULL(' INCLUDE (' + mid.included_columns + ')', '')
  + ';'                                         AS CreateIndexStatement
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups      mig  ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details     mid  ON mig.index_handle  = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY ImpactScore DESC;

-- Key columns to understand:
-- equality_columns:   columns used with = in WHERE clauses -- put these in the key first
-- inequality_columns: columns used with >, <, BETWEEN     -- put these after equality columns
-- included_columns:   columns in SELECT list only          -- add as INCLUDE
-- ImpactScore:        higher = more benefit expected from creating this index
--                     NOT a guarantee -- always validate in non-production first

New in SQL Server 2019 — Query Text with Missing Index SQL Server 2019+

-- sys.dm_db_missing_index_group_stats_query: adds the actual query text
-- that triggered the missing index recommendation
-- Available: SQL Server 2019, SQL Server 2022, SQL Server 2025, Azure SQL

SELECT
    DB_NAME(mid.database_id)                     AS DatabaseName,
    OBJECT_NAME(mid.object_id, mid.database_id)  AS TableName,
    migs.user_seeks,
    migs.avg_user_impact,
    migs.avg_total_user_cost * migs.avg_user_impact
        * migs.user_seeks                        AS ImpactScore,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    -- The query text that triggered this recommendation (new in SQL Server 2019)
    qs.query_text                                AS TriggeringQueryText
FROM sys.dm_db_missing_index_group_stats_query   migs
JOIN sys.dm_db_missing_index_groups              mig  ON migs.group_handle  = mig.index_group_handle
JOIN sys.dm_db_missing_index_details             mid  ON mig.index_handle   = mid.index_handle
JOIN sys.dm_db_missing_index_group_stats_query   qs   ON migs.group_handle  = qs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY ImpactScore DESC;
-- This view shows you both WHAT index is missing AND the exact query that needs it
-- Previously you had to cross-reference execution plans -- now it is in one query

Finding Unused Indexes — Candidates for Dropping

-- Indexes that have never been read since last restart
-- These impose write overhead with no corresponding read benefit
-- Strong candidates for dropping (validate against full workload period first)

SELECT
    OBJECT_NAME(i.object_id)           AS TableName,
    i.name                             AS IndexName,
    i.type_desc                        AS IndexType,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,                  -- write cost: every INSERT/UPDATE/DELETE updates this
    ius.last_user_seek,
    ius.last_user_scan,
    ius.last_user_lookup
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
    ON ius.object_id   = i.object_id
    AND ius.index_id   = i.index_id
    AND ius.database_id = DB_ID()
WHERE i.object_id  = OBJECT_ID('dbo.Orders')  -- or remove to see all tables
  AND i.index_id   > 1                         -- skip clustered index (index_id = 1)
  AND i.type       = 2                         -- nonclustered only
ORDER BY ISNULL(ius.user_seeks, 0)
       + ISNULL(ius.user_scans, 0)
       + ISNULL(ius.user_lookups, 0) ASC;
-- Indexes at the top: lowest read usage -- review for removal
-- user_updates tells you the write cost you will recover if you drop the index

5 Clustered Index Scan Cost — When Scans Are Inevitable and When They Are Not Intermediate

A clustered index scan is not always a problem. Understanding when a scan is the right choice versus when it signals a missing or incorrectly designed index is one of the key diagnostic skills in SQL Server performance tuning.

When a Clustered Index Scan Is Correct and Expected

• The query retrieves a large percentage of the table. If a query returns 40% of rows, a scan is almost always cheaper than a seek followed by thousands of key lookups. The optimizer calculates a break-even point — typically around 10–30% of rows depending on table structure — and chooses accordingly.
• The table is small. On a table that fits on 5–10 data pages, a clustered index scan completes in microseconds. The optimizer correctly ignores nonclustered indexes on small tables.
• No filter exists. A query with no WHERE clause by definition must read every row. A scan is the only option.

When a Clustered Index Scan Is a Problem

-- Problem: query should be selective but scans the entire table
-- Symptom: clustered index scan in execution plan, high logical reads,
--          but the query is only retrieving a small number of rows

-- Example: 5 million row Orders table, query returns 50 rows
-- This should be a seek, not a scan

-- BAD plan: Clustered Index Scan → 50,000+ logical reads
SELECT OrderID, CustomerID, TotalAmount
FROM dbo.Orders
WHERE Status = 'Pending'
AND   OrderDate > '2025-01-01';

-- Root causes of unexpected clustered scans:
-- 1. No nonclustered index on the filter columns
-- 2. Index exists but predicate is non-SARGable (function on column)
-- 3. Statistics are stale -- optimizer estimates high row count and chooses scan
-- 4. Implicit type conversion blocking index use (column is INT, parameter is VARCHAR)

-- Diagnosis: check the predicate in the plan
-- Seek Predicate: empty or missing = the index cannot be navigated with this filter
-- Predicate (residual): filter is applied AFTER reading rows -- scan confirmed

-- Fix: create an appropriate nonclustered index
CREATE NONCLUSTERED INDEX IX_Orders_Status_Date
ON dbo.Orders (Status, OrderDate)
INCLUDE (CustomerID, TotalAmount);
-- Plan changes: Clustered Index Scan → Index Seek (massive logical read reduction)

Measuring the Actual Cost — Before and After

-- Always measure logical reads before and after any index change

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

SELECT OrderID, CustomerID, TotalAmount
FROM dbo.Orders
WHERE Status = 'Pending'
AND OrderDate > '2025-01-01';

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

-- BEFORE index:
-- Table 'Orders'. Scan count 1, logical reads 65,420
-- CPU time = 2,140 ms, elapsed time = 3,312 ms

-- AFTER creating IX_Orders_Status_Date:
-- Table 'Orders'. Scan count 1, logical reads 38
-- CPU time = 0 ms, elapsed time = 4 ms
-- 99.9% reduction in logical reads

6 Unique vs Non-Unique Indexes — What the Difference Actually Costs Intermediate

The distinction between unique and non-unique indexes is more significant than it first appears. It affects more than just whether duplicates are allowed — it affects the optimizer's cardinality estimates, join plan choices, and the internal structure of the index itself.

How SQL Server Stores Non-Unique Indexes Internally

When you create a non-unique nonclustered index, SQL Server has a problem: how does it uniquely identify each leaf entry when values may repeat? It solves this by appending the clustered index key to every leaf entry as a hidden column. This hidden column makes each leaf entry unique internally even when the indexed value itself is not unique.

This has three practical consequences. First, a non-unique index is slightly wider than a unique index because of the hidden key column. Second, the optimizer knows a unique index will return at most one row per key value — this certainty allows more aggressive join optimizations. Third, a unique index enforces a constraint as a side effect, which has its own value for data integrity.

-- Unique index: enforces uniqueness AND helps the optimizer
CREATE UNIQUE NONCLUSTERED INDEX UIX_Customers_Email
ON dbo.Customers (Email);

-- The optimizer KNOWS: WHERE Email = 'alice@company.com' returns AT MOST 1 row
-- This allows Nested Loop join without concern about row explosion
-- Also enforces the business rule: no duplicate emails

-- Non-unique index: allows duplicates, optimizer must plan for multiple rows
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON dbo.Orders (CustomerID);
-- Optimizer knows each CustomerID can have MANY orders
-- Plans join strategies accordingly (often Hash Match for large sets)

-- Comparison: impact on a point-lookup query
-- UNIQUE index seek: optimizer plans for 1 row → Nested Loop join (fast)
-- Non-unique seek:   optimizer plans for N rows → may choose Hash Match (different cost)

-- Check if an existing index is effectively unique (all values distinct)
-- even if not declared UNIQUE:
SELECT
    i.name                                          AS IndexName,
    SUM(1)                                          AS TotalRows,
    COUNT(DISTINCT CustomerID)                      AS DistinctValues,
    CASE WHEN SUM(1) = COUNT(DISTINCT CustomerID)
         THEN 'Effectively unique -- consider UNIQUE constraint'
         ELSE 'Has duplicates'
    END                                             AS Recommendation
FROM dbo.Customers
CROSS JOIN sys.indexes i
WHERE i.object_id = OBJECT_ID('dbo.Customers')
AND i.name = 'IX_Customers_CustomerID'
GROUP BY i.name;

Filtered Indexes — A Powerful Unique Index Variant

-- Filtered indexes: a nonclustered index with a WHERE clause
-- Smaller index (only rows matching the filter are included)
-- Can enforce conditional uniqueness

-- Example: enforce uniqueness only for active customers
-- (deleted customers can have duplicate email due to data retention rules)
CREATE UNIQUE NONCLUSTERED INDEX UIX_Customers_Email_Active
ON dbo.Customers (Email)
WHERE IsActive = 1;
-- This enforces: no two ACTIVE customers can have the same email
-- Inactive customers are outside the filtered index entirely

-- Example: high-value order index (only orders over $10,000)
CREATE NONCLUSTERED INDEX IX_Orders_HighValue
ON dbo.Orders (OrderDate, CustomerID)
INCLUDE (TotalAmount)
WHERE TotalAmount >= 10000;
-- Benefit: very small index (only large orders)
-- Queries filtered to TotalAmount >= 10000 use this index efficiently
-- Other queries are unaffected -- the index is invisible to them

-- Filtered index limitation: requires compatible WHERE clause in the query
-- The query filter must be a superset of the index filter for the index to be used
-- WHERE TotalAmount >= 10000  → index is used (matches filter)
-- WHERE TotalAmount >= 5000   → index is NOT used (superset, not subset)

7 Index Intersection and Index Union — SQL Server Combining Multiple Indexes Advanced

SQL Server has two mechanisms for combining multiple nonclustered indexes to satisfy a single query — intersection and union. Both are advanced optimizer behaviors that most developers and even many DBAs never encounter, but understanding them helps you design leaner index strategies and avoid creating wide covering indexes unnecessarily.

Index Intersection (AND Logic)

Index intersection occurs when SQL Server seeks two different indexes separately, then merges the row locators using an intersection operator — keeping only rows that appeared in both results. This satisfies a query with AND conditions across columns that are in separate indexes, without needing a single index that covers both columns.

-- Table: dbo.Orders with two separate indexes
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID ON dbo.Orders (CustomerID);
CREATE NONCLUSTERED INDEX IX_Orders_Status     ON dbo.Orders (Status);

-- Query with AND on both indexed columns:
SELECT OrderID, TotalAmount
FROM dbo.Orders
WHERE CustomerID = 12345
AND   Status     = 'Pending';

-- Possible plan 1: Index Seek on CustomerID → Key Lookup + filter Status
-- Possible plan 2: Index Intersection
--   [Index Seek: IX_Orders_CustomerID (CustomerID = 12345)] → row IDs
--   [Index Seek: IX_Orders_Status (Status = 'Pending')]     → row IDs
--   [Merge Join (Intersection)] → row IDs in BOTH result sets
--   [Clustered Index Seek] → fetch full rows for matching IDs

-- The optimizer chooses intersection when BOTH conditions are selective
-- and the row count from each seek is small enough that the merge is cheap

-- In SSMS execution plan: look for "Merge Join" with logical operation "Inner Join"
-- where both inputs are Index Seek operators on the SAME table

-- When the optimizer prefers a single covering index over intersection:
-- If one condition is highly selective (returns very few rows),
-- a seek on that column + residual filter on the other is faster than two seeks + merge

Index Union (OR Logic)

Index union occurs when a query has OR conditions across multiple columns. SQL Server seeks each index separately for the OR conditions, then concatenates and deduplicates the results. This is the index equivalent of a UNION operation — it returns rows matching either condition without a full table scan.

-- Index union: satisfying OR conditions with multiple indexes
SELECT OrderID, TotalAmount
FROM dbo.Orders
WHERE CustomerID = 12345
OR    Status     = 'Urgent';

-- Without union: SQL Server scans the entire table (OR prevents index seeks normally)
-- With index union:
--   [Index Seek: IX_Orders_CustomerID (CustomerID = 12345)] → row set A
--   [Index Seek: IX_Orders_Status (Status = 'Urgent')]      → row set B
--   [Merge Join (Union)] → combined row set (with deduplication for rows matching both)
--   [Clustered Index Seek] → fetch full rows

-- In the execution plan: look for "Merge Join" with logical operation "Union"
-- where both inputs come from the same table

-- IMPORTANT: index union is relatively rare
-- The optimizer only chooses it when both OR conditions have high selectivity
-- AND both columns have their own indexes
-- If either condition returns a large fraction of the table, a full scan is chosen instead

-- You can also write OR as UNION to force the optimizer to consider separate seeks:
SELECT OrderID, TotalAmount FROM dbo.Orders WHERE CustomerID = 12345
UNION
SELECT OrderID, TotalAmount FROM dbo.Orders WHERE Status = 'Urgent';
-- This guarantees two separate seeks and a union
-- Use this pattern when the optimizer is not choosing the union path automatically

When to Rely on Intersection/Union vs When to Create a Covering Index

Scenario	Prefer Intersection/Union	Prefer Covering Index
Both columns are highly selective	Intersection works well — each seek is small	Covering index adds width unnecessarily
One column is low selectivity	One seek returns many rows — expensive merge	Covering index on selective column only
Columns queried together frequently	Two seeks + merge vs one seek	Composite covering index is faster
OR condition on two columns	Only option for index use on OR	No single index covers OR efficiently
Write-heavy table, minimize index count	Reuse existing single-column indexes	New composite index adds write overhead

8 Index Hints and Join Type Hints — When to Use Them and When to Avoid Them Advanced

Query hints force the optimizer to use a specific index or a specific join algorithm instead of choosing the one it calculates to be cheapest. They are a last resort tool — not a first response. Understanding when hints are justified and when they create long-term problems is one of the marks of a mature SQL Server developer.

Index Hints

-- Force a specific index:
SELECT OrderID, CustomerID, TotalAmount
FROM dbo.Orders WITH (INDEX(IX_Orders_OrderDate_Covering))
WHERE OrderDate > '2025-01-01'
AND   Status    = 'Pending';

-- The optimizer is FORBIDDEN from choosing any other index or a table scan
-- even if statistics indicate another plan would be cheaper

-- When an index hint might be justified:
-- 1. You know the optimizer is consistently choosing the wrong index
--    due to stale statistics that you cannot update immediately
-- 2. A known parameter sniffing problem produces a bad cached plan
--    and you need a temporary fix while working on the real solution
-- 3. Testing: you want to measure a specific index's performance
--    without waiting for the optimizer to choose it

-- When NEVER to use index hints:
-- 1. "It seemed faster in testing" -- without measuring logical reads, you do not know
-- 2. As a permanent production fix -- statistics will eventually be updated,
--    the workload will change, and the hint becomes actively harmful
-- 3. When the correct fix is updating statistics or creating a better index

Join Type Hints

-- Force a specific join algorithm:
-- LOOP  = Nested Loops
-- HASH  = Hash Match
-- MERGE = Merge Join

-- Force Nested Loop (useful when you know the outer input is very small):
SELECT o.OrderID, c.CustomerName
FROM dbo.Orders    o
INNER LOOP JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate = '2025-03-15';

-- Force Hash Join (useful when Nested Loop was chosen due to bad cardinality estimates
-- and you know both inputs are actually large):
SELECT o.OrderID, c.CustomerName
FROM dbo.Orders    o
INNER HASH JOIN dbo.Customers c ON o.CustomerID = c.CustomerID;

-- Force Merge Join (useful when both inputs are pre-sorted and optimizer
-- chose Hash due to missing index statistics):
SELECT o.OrderID, c.CustomerName
FROM dbo.Orders    o
INNER MERGE JOIN dbo.Customers c ON o.CustomerID = c.CustomerID;

Query-Level Hints (OPTION clause)

-- OPTION clause hints apply to the whole query rather than individual joins

-- OPTION (RECOMPILE): recompile the plan on every execution
-- Use when parameter sniffing produces a consistently bad plan
-- Eliminates plan caching entirely -- each execution gets a fresh optimal plan
-- Cost: small CPU overhead per execution -- acceptable for infrequent queries
SELECT * FROM dbo.Orders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
OPTION (RECOMPILE);

-- OPTION (OPTIMIZE FOR): hint the optimizer to plan for a specific parameter value
-- Use when one parameter value dominates and others are rare
DECLARE @StatusParam VARCHAR(20) = @InputStatus;
SELECT * FROM dbo.Orders WHERE Status = @StatusParam
OPTION (OPTIMIZE FOR (@StatusParam = 'Pending'));

-- OPTION (MAXDOP n): limit or set degree of parallelism for this query
-- MAXDOP 1 = force serial execution (useful when parallelism overhead exceeds benefit)
SELECT SUM(TotalAmount) FROM dbo.Orders WHERE OrderDate > '2025-01-01'
OPTION (MAXDOP 1);

-- OPTION (USE HINT): newer, documented hints (SQL Server 2016+)
-- Preferred over undocumented trace flags
SELECT * FROM dbo.Orders WHERE OrderDate > '2025-01-01'
OPTION (USE HINT ('ENABLE_PARALLEL_PLAN_PREFERENCE')); -- prefer parallel plans
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')); -- use SQL Server 2012 CE

9 AI-Assisted Index Recommendations in 2026 Everyone

AI-assisted index tuning has moved from experimental to production-ready between 2020 and 2026. The tools available now range from the mature DMV-based missing index suggestions built into the engine, through Azure SQL's automatic index management, to SQL Server 2025's Query Intelligence features and Copilot integration in SSMS. Each operates at a different level of automation and requires a different level of DBA oversight.

Tool 1: Missing Index DMVs — The Foundation All Versions

The missing index DMVs covered in Section 4 are the original SQL Server "AI" for index recommendations. They are rule-based rather than machine-learning-based, but they reflect actual production workload and generate actionable CREATE INDEX statements. They remain the most reliable starting point for index analysis in any SQL Server version.

Tool 2: Database Engine Tuning Advisor (DTA) Legacy Tool

DTA analyzes a captured workload and recommends indexes, statistics, and partitioning changes. It generates T-SQL scripts you can review and apply. It remains available in SSMS and is still useful for analyzing a specific batch of queries in isolation. Its main limitations are that it requires an offline workload capture, can suggest redundant or overlapping indexes, and does not account well for the write cost of adding new indexes to heavy OLTP workloads. Use it as one data point, not as a sole authority.

Tool 3: Copilot in SSMS SQL Server 2025 / Azure SQL

Microsoft introduced Copilot integration into SSMS as part of the SQL Server 2025 release cycle. Copilot in SSMS can analyze a selected query or execution plan and suggest index changes, rewrites, and performance improvements in natural language. You describe the problem or paste the slow query, and Copilot explains what it sees and what it recommends.

-- Using Copilot in SSMS for index recommendations (SQL Server 2025 / Azure SQL):
-- 1. Open SSMS 21 or later and connect to SQL Server 2025 or Azure SQL
-- 2. Write or open a slow query in the query editor
-- 3. Select the query text
-- 4. Right-click → "Explain with Copilot" or click the Copilot icon in the toolbar
-- 5. Copilot analyzes the query and the available schema metadata

-- Example prompts you can ask Copilot in SSMS:
-- "Why is this query slow and what index would help?"
-- "What does this execution plan tell me about performance?"
-- "Can you suggest a covering index for this query?"
-- "Are there any implicit conversions preventing index use?"

-- Copilot output includes:
-- Natural language explanation of what the query is doing
-- Identification of missing index opportunities
-- Ready-to-review CREATE INDEX suggestions
-- Warnings about non-SARGable predicates or type mismatches

-- IMPORTANT: treat Copilot suggestions as a starting point, not a final answer
-- Always validate suggested indexes in a non-production environment first
-- Measure logical reads before and after -- numbers, not trust

Tool 4: Azure SQL Automatic Index Tuning Azure SQL Database

Azure SQL Database provides the most automated index management available in the Microsoft SQL Server ecosystem. It monitors the workload through Query Store, identifies missing index opportunities from the missing index DMVs, creates indexes experimentally, monitors whether they actually improve performance, and automatically drops them if they do not. All of this happens without DBA intervention — though you can review and override every decision.

-- Azure SQL: check automatic tuning recommendations
SELECT
    name,
    type,
    reason,
    score,
    details,
    state_reason
FROM sys.dm_db_tuning_recommendations
ORDER BY score DESC;
-- Returns pending CREATE INDEX and DROP INDEX recommendations
-- score: estimated improvement (higher = more confident recommendation)
-- state_reason: why the recommendation is in its current state

-- Check current automatic tuning configuration:
SELECT
    name,
    desired_state_desc,
    actual_state_desc,
    reason_desc
FROM sys.database_automatic_tuning_options;
-- FORCE_LAST_GOOD_PLAN: automatically reverts to last good plan on regression
-- CREATE_INDEX:         automatically creates recommended indexes
-- DROP_INDEX:           automatically drops unused indexes

-- Enable automatic index tuning on Azure SQL:
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (CREATE_INDEX = ON);
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (DROP_INDEX   = ON);

-- Azure's approach:
-- 1. Identifies missing index from DMV with high impact score
-- 2. Creates the index (online, non-blocking)
-- 3. Monitors query performance for the next workload period using Query Store
-- 4. If performance improves: keeps the index
-- 5. If performance does not improve or degrades: drops the index automatically
--    (this is the key difference from manual tuning -- automatic rollback)

Tool 5: SQL Server 2025 Query Intelligence SQL Server 2025

SQL Server 2025 introduces Query Intelligence — a suite of AI-driven capabilities that includes automatic query rewrites, plan recommendations, and natural language query generation. It acts as a performance partner for developers and DBAs, though as with any AI-assisted tool, automatic rewrites do not always align with specific performance goals and DBA review remains essential. Approach Query Intelligence as a partner, not a replacement for developer and DBA judgment.

-- SQL Server 2025: Query Intelligence features relevant to index tuning

-- Cardinality Estimation Feedback (SQL Server 2025 enhancement):
-- SQL Server 2025 improves on the CE Feedback feature from 2022
-- It learns from actual vs estimated row counts and persists corrections
-- This directly reduces bad plan choices caused by cardinality errors
-- Check CE Feedback activity:
SELECT
    plan_id,
    query_id,
    feature_desc,
    feedback_data
FROM sys.query_store_plan_feedback
WHERE feature_desc = 'CeFeedback'
ORDER BY query_id;

-- Optional Parameter Plan Optimization (SQL Server 2025):
-- Stores multiple plans for the same query -- one per common parameter pattern
-- Reduces parameter sniffing problems without requiring OPTION (RECOMPILE)
-- Relevant to index tuning: each plan can use different indexes for different parameters
SELECT
    q.query_id,
    p.plan_id,
    p.count_compiles,
    p.is_forced_plan,
    p.query_plan_hash
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON q.query_id = p.query_id
WHERE p.count_compiles > 1    -- queries with multiple compiled plans
ORDER BY p.count_compiles DESC;

-- Intelligent Query Processing (IQP) in SQL Server 2025:
-- Builds on 2022 IQP features with memory grant feedback for expressions,
-- DOP feedback improvements, and batch mode enhancements
-- These reduce cases where an index is correct but the plan around it is suboptimal

AI Tool Comparison — 2026 Landscape

Tool	Platform	Automation Level	DBA Review Required	Best For
Missing Index DMVs	All versions	Suggestions only	Always — manual create	Starting point for all index analysis
Database Engine Tuning Advisor	On-premises	Suggestions + scripts	Always — manual apply	Batch workload offline analysis
Copilot in SSMS	SQL Server 2025 / Azure SQL	Suggestions + explanation	Always — manual apply	Ad-hoc query analysis, learning
Azure SQL Automatic Tuning	Azure SQL Database only	Fully automatic with rollback	Optional — can review decisions	Cloud-managed OLTP with stable workload
SQL Server 2025 Query Intelligence	SQL Server 2025	Automatic plan correction, suggestions	Recommended — verify results	Modern on-premises workloads
Amazon Q / Claude	Any (external AI)	Suggestions + explanation	Always — no database access	Schema review, query analysis, learning

10 Conclusion — The Index Tuning Decision Framework Everyone

Index tuning is not a one-time activity — it is an ongoing practice that evolves as your data, workload, and queries change. The framework below summarizes the approach covered across this article into a repeatable decision process.

1. Start with measurement, not assumptions. Run SET STATISTICS IO ON and capture logical reads for your slow queries before making any changes. This is your baseline. Every improvement must be measured against it.
2. Read the execution plan, not the cost percentages. Find the clustered index scans on large tables. Find the key lookups. Find the cardinality estimation gaps (estimated vs actual rows). These are your actual problems.
3. Query the missing index DMVs. Sort by impact score. Identify the top candidates. Cross-reference with sys.dm_db_index_usage_stats to confirm the table is genuinely being queried heavily and the suggestion is not a one-off low-frequency query.
4. Design, don't copy. The DMV-generated CREATE INDEX statement is a starting point. Combine overlapping suggestions into composite indexes. Add INCLUDE columns to eliminate key lookups. Check whether the suggestion duplicates an existing index. Verify column order (equality columns first, then inequality).
5. Use AI tools to accelerate, not replace, this process. Copilot in SSMS, Azure SQL automatic tuning, and SQL Server 2025 Query Intelligence all compress the time to identify candidates. Your job is to validate their recommendations with measurements and workload knowledge before applying them to production.
6. Measure after, not just before. After creating the index, re-run SET STATISTICS IO ON. Confirm logical reads dropped. Confirm the execution plan changed (seek not scan). Check sys.dm_db_index_usage_stats after a week to confirm the index is being used in production, not just in your test query.
7. Maintain regularly. Review unused indexes quarterly. Drop indexes with zero user_seeks and high user_updates — they are pure write overhead. Rebuild or reorganize fragmented indexes on a schedule. Update statistics after large data changes.

References

• Microsoft Docs — Tune Nonclustered Indexes with Missing Index Suggestions
• Microsoft Docs — sys.dm_db_missing_index_details
• Microsoft Docs — sys.dm_db_missing_index_group_stats_query (SQL Server 2019+)
• Microsoft Docs — sys.dm_db_index_usage_stats
• Microsoft Docs — Automatic Tuning in SQL Server and Azure SQL
• Microsoft Docs — Azure SQL Automatic Index Management
• Microsoft Docs — Intelligent Query Processing (IQP)
• Microsoft Docs — Query Store Overview
• Microsoft Docs — Create Filtered Indexes
• Microsoft Docs — SQL Server Index Architecture and Design Guide
• SQLServerCentral — Query Intelligence in SQL Server 2025
• SQLYARD — SQL Server Performance Tuning: The Complete Guide
• SQLYARD — How to Read SQL Server Execution Plans
• SQLYARD — Understanding Parameter Sniffing in SQL Server