Indexing Strategies for Your Queries

Indexes speed up queries, but they are not automatic. The wrong combination of query and index can hurt performance instead of helping it, and every index on a table also has to be maintained on every INSERT, UPDATE, and DELETE against it. Before adjusting an index, or writing a query that depends on one, it helps to know exactly how SQL Server 2025 is indexing that query today. That can be checked at the single-query level or across your whole workload.

An alternative to using an index is a table scan, which reads every record in the table. A table scan is reasonable for small tables, or when most of a table's rows are going to be read anyway; on a large table, it usually signals a missing index. The Query Optimizer is the component that decides, for a given query, whether to scan or to use an index, and that decision is recorded in the query's execution plan.

In SQL Server Management Studio (SSMS), two keyboard shortcuts produce the execution plan for a query:

• Estimated Execution Plan (Ctrl+L): shows the optimizer's chosen strategy without running the query, which makes it safe to use on a heavy INSERT, UPDATE, or DELETE.
• Actual Execution Plan (Ctrl+M): runs the query and shows the indexes actually used, along with runtime metrics such as actual rows read versus estimated rows.

If you work in the SQL Server (mssql) extension for Visual Studio Code instead of SSMS, the same two views are available from the query toolbar as Estimated Plan and Enable Actual Plan, opening a comparable graphical plan viewer with zoom, cost highlighting, and an Open XML option.

What to look for in the plan:

• Index Seek / Clustered Index Seek: the optimizer used an index efficiently to jump directly to the matching rows.
• Index Scan / Clustered Index Scan / Table Scan: the engine read the entire index or table. On a large table, this usually points to a missing index, or a non-SARGable predicate, such as wrapping an indexed column in a function like YEAR(OrderDate) = 2025.
• Key Lookup (or RID Lookup): a nonclustered index found the right rows, but SQL Server had to go back to the base table for additional columns. Adding those columns to the index's INCLUDE (...) clause removes the lookup.
• Green "missing index" text: SSMS displays this at the top of a graphical plan, with an estimated percentage impact, when the optimizer identifies a missing index during compilation. Right-click the plan and choose Missing Index Details to generate the exact CREATE NONCLUSTERED INDEX script.

For a text or XML plan instead of the graphical one:

SET SHOWPLAN_XML ON;
GO
SELECT CustomerID, OrderDate, TotalAmount
FROM Sales.Orders
WHERE CustomerID = 402 AND Status = 'Shipped';
GO
SET SHOWPLAN_XML OFF;
GO

Like Ctrl+L, SHOWPLAN_XML shows the plan without executing the query.

Every time SQL Server compiles a query and determines an index would have reduced its cost, it records the columns that index would need in three Dynamic Management Views (DMVs):

• Equality columns: columns used in = or IN predicates. These should go first in the index key.
• Inequality columns: columns used in <, >, BETWEEN, or != predicates. These should go after the equality columns.
• Included columns: columns the SELECT list needs but that are not part of the filter. These belong in the index's INCLUDE clause, to make the index covering.

This query ranks the highest-impact missing indexes in the current database and generates the CREATE INDEX statement for each one:

SELECT TOP (20)
    CONVERT(DECIMAL(18,2), migs.user_seeks * migs.avg_total_user_cost * (migs.avg_user_impact * 0.01)) AS IndexAdvantageScore,
    migs.last_user_seek,
    mid.[statement] AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.avg_user_impact AS EstimatedImpactPct,
    'CREATE NONCLUSTERED INDEX [IX_' + OBJECT_NAME(mid.object_id) + '_Missing_'
        + CAST(mid.index_handle AS VARCHAR(10)) + '] 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 AS migs
JOIN sys.dm_db_missing_index_groups AS mig
    ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY IndexAdvantageScore DESC;

These DMV statistics reset whenever the SQL Server service restarts. For history that survives a restart, use Query Store instead.

Query Store has been enabled by default for new databases since SQL Server 2022, and it remains on by default in SQL Server 2025; it keeps execution plans across server restarts. You can query its catalog views to find cached plans containing a <MissingIndexes> XML node, along with the exact query text that triggered them:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (25)
    qsq.query_id,
    qst.query_sql_text,
    qsp.plan_id,
    CAST(qsp.query_plan AS XML) AS ExecutionPlanXML,
    rs.count_executions,
    rs.avg_duration / 1000.0 AS AvgDurationMs,
    rs.avg_logical_reads
FROM sys.query_store_plan AS qsp
JOIN sys.query_store_query AS qsq
    ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text AS qst
    ON qsq.query_text_id = qst.query_text_id
JOIN sys.query_store_runtime_stats AS rs
    ON qsp.plan_id = rs.plan_id
WHERE qsp.query_plan LIKE '%<MissingIndexes>%'
ORDER BY rs.avg_logical_reads DESC;

Clicking the ExecutionPlanXML value in the SSMS results grid opens the graphical plan directly.

Knowing how a query is indexed also means checking whether the indexes you already have are helping, or just adding overhead to every INSERT, UPDATE, and DELETE without ever being read:

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    ISNULL(ius.user_seeks, 0) AS Seeks,
    ISNULL(ius.user_scans, 0) AS Scans,
    ISNULL(ius.user_lookups, 0) AS Lookups,
    ISNULL(ius.user_updates, 0) AS WriteUpdates,
    ius.last_user_seek,
    ius.last_user_scan
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS ius
    ON i.object_id = ius.object_id
    AND i.index_id = ius.index_id
    AND ius.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.type_desc <> 'HEAP'
ORDER BY (ISNULL(ius.user_seeks, 0) + ISNULL(ius.user_scans, 0) + ISNULL(ius.user_lookups, 0)) ASC,
         ius.user_updates DESC;

An index with high Seeks and low WriteUpdates is working as intended. An index with zero Seeks or Scans and high WriteUpdates is pure overhead: every write maintains an index that no SELECT ever reads.

Three capabilities in SQL Server 2025 (default compatibility level 170) change how queries get indexed.

Optional Parameter Plan Optimization (OPPO). A query with an optional filter, such as WHERE (CustomerID = @id OR @id IS NULL), used to be stuck with one cached plan for every execution, usually a conservative scan, since a seek-based plan would be wrong whenever @id is NULL. OPPO compiles a dispatcher plan with separate variants, one for the NULL case and one for the non-NULL case, so a selective parameter gets a seek and a NULL parameter gets a scan, without rewriting the query or adding hints. It is on by default and works alongside Parameter Sensitive Plan (PSP) optimization, which handles the related case of plans that differ by estimated row count rather than by NULL-ness.

Native JSON and vector indexes. The standard missing-index DMVs are built around B-tree indexes and will not flag a table scan against a JSON or VECTOR column. SQL Server 2025 adds dedicated CREATE JSON INDEX and CREATE VECTOR INDEX statements for those columns, letting you build an index to accelerate VECTOR_DISTANCE similarity searches. Vector indexes use the DiskANN algorithm and, in the box product, currently require enabling preview features first with ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON.

Database Engine Tuning Advisor (DTA). The missing-index DMVs evaluate one query at a time and never recommend a clustered index, a filtered index, or partitioning. Feeding a Query Store workload or a trace into DTA evaluates a consolidated set of indexes against your whole workload, balancing read benefit against write cost.

In the next lesson, we will look at how to use the information we have accumulated.