You've built five indexes on a 339M-row table; the JOIN indexes are picked up fine, but the optimizer ignores every single WHERE clause index you created. It's one of the most frustrating moments in SQL development, mostly because the problem isn't obvious. The index exists. It's on the right table. So why is the optimizer completely ignoring it?

The answer is rarely "the index is broken." It's almost always that something in the query or in the state of the database is giving the query optimizer a reason to skip it. This article discusses how to read an execution plan to find that reason, and what to fix once you do.

Start With the Actual Execution Plan, Not the Estimated One

Before diagnosing anything, make sure you're looking at the right plan. SQL Server generates two types: estimated and actual. The estimated plan is produced before the query runs, based on statistics. The actual plan is captured after execution and includes real row counts, actual versus estimated rows, and runtime metrics that the estimated plan simply cannot show you.

To get the actual plan, run your query with SET STATISTICS IO ON and enable "Include Actual Execution Plan" in your query tool, or use OPTION (RECOMPILE) to force a fresh compile and capture. The difference matters because stale statistics can make the estimated plan look entirely reasonable while the actual plan reveals the optimizer was working with wildly wrong assumptions.

Once you have the actual plan, read it right to left; that's the direction of data flow. The rightmost operators are where data originates. The leftmost is the final output. The width of the arrows between operators represents row volume; a suddenly thick arrow indicates that row estimates went wrong somewhere upstream.

Related: Introduction to Indexes

The Scan vs. Seek Distinction

The first thing to look for is whether SQL Server is performing an Index Seek or an Index Scan, or worse, a Table Scan (also called a Clustered Index Scan on tables with a clustered index).

A seek is surgical. The optimizer navigates the B-tree structure of the index directly to the rows that match your predicate. A scan reads every row in the index or table. When you see a scan where you expected a seek, that's your starting point. Hover over the scan operator in the execution plan and look at the "Predicate" and "Seek Predicates" fields in the tooltip. If there's nothing listed under Seek Predicates but something shows under Predicate, the index isn't being used for navigation — it's being filtered after a full read, which defeats the purpose entirely.

Query Mistakes and Database Conditions That Can Stop SQL Server From Using Your Index

There are five query mistakes that can stop the query optimizer from taking advantage of an index. Each of these is described below.

The Function Wrapper

The most common reason an index goes unused is a function wrapped around the indexed column in the WHERE clause. Consider this query:

SELECT * FROM Orders WHERE YEAR(OrderDate) = 2023;

OrderDate has an index on it. But wrapping it in YEAR() makes the column non-sargable, SQL Server can't use the index to seek directly to matching rows because the function transforms the value before comparison. The optimizer has no choice but to scan every row, apply the function, and filter.

The fix is to rewrite the predicate so the column stands alone:

SELECT * FROM Orders
WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01';

Run both versions with SET STATISTICS IO ON and compare the logical reads. On a table with several million rows, the rewritten version will typically drop logical reads from hundreds of thousands to a few hundred, the difference between a scan and a precise seek. The execution plan will confirm it: the function version shows a Clustered Index Scan; the rewritten version shows an Index Seek.

The same problem appears with CONVERT(), CAST(), LEFT(), UPPER(), and any scalar function applied to a column in a filter. As a rule, keep your indexed columns untouched in WHERE clauses.

Implicit Type Conversion

This one is subtle because it doesn't look like a function - there's no explicit wrapping. But SQL Server is doing the conversion internally, and the execution plan will expose it.

If CustomerID is defined as INT and you write:

SELECT * FROM Customers WHERE CustomerID = '10042';

SQL Server has to convert the string, '10042', to an integer before it can compare. In this direction,  converting the literal, not the column, the index is usually still usable. But if the types are reversed, if the column is VARCHAR and the parameter is INT, SQL Server converts every value in the column to match the parameter type. That transforms a seek into a scan.

In the execution plan, look for a CONVERT_IMPLICIT function in the Predicate field of the scan operator. That's the tell. The fix is to ensure the data types of your parameters or literals match the column definition exactly. This is especially common in applications that pass all parameters as strings, and in stored procedures where parameter types weren't defined carefully at the start.

The Leading Column Violation

Composite indexes (indexes on more than one column) only support seeks that begin with the leftmost column in the index definition. If your index is defined as (LastName, FirstName) and your query filters only on FirstName SQL Server cannot seek into that index. It has to scan it.

Consider an index that looks like this: (LastName, FirstName). This can seek:

WHERE LastName = 'Reyes' AND FirstName = 'Marco'

This can also seek with this filter, because the leading column is present:

WHERE LastName = 'Reyes'

However, this cannot seek with this claude because the leading column is missing:

WHERE FirstName = 'Marco'

When you see a scan on a composite index, check the column order in the index definition against what your WHERE clause is actually filtering on. The fix is either to reorder the index to match the most common query pattern or to create a separate index on FirstName if that filter is used independently at high frequency.

Low Selectivity and the Optimizer's Cost Math

Sometimes the index could be used, and the optimizer still chooses a scan; not because of a query problem, but because it's genuinely cheaper. This happens when selectivity is low: when a large proportion of rows match the predicate.

If a Status column has only three distinct values and one of them covers 80% of the table; an index on Status is nearly worthless for that value. Scanning the clustered index and reading rows sequentially is faster than bouncing back and forth between the index B-tree and the base table via key lookups. SQL Server's optimizer knows this, and it will choose the scan.

You can verify this by looking at the estimated row count in the execution plan tooltip and comparing it to the actual table size. If the optimizer expects to return 60% of the table, a scan is the right call. The fix here isn't forcing the index but reconsidering whether the index makes sense, or whether a filtered index covering only the minority values would be more useful.

Stale Statistics Misleading the Optimizer

Even when the query is written correctly and the index is well-designed, the optimizer can still make a bad choice if it's working with outdated statistics. Statistics tell SQL Server how data is distributed across a column, how many distinct values exist, how rows are spread, and where the density clusters. When statistics are stale, cardinality estimates go wrong, and a plan that looked reasonable at the time of compilation becomes increasingly wrong as data changes.

In the actual execution plan, the clearest signal is a large discrepancy between "Estimated Number of Rows" and "Actual Number of Rows" on an operator. If the optimizer estimated 200 rows and got 2 million, it almost certainly made downstream decisions, including whether to use an index, based on that wrong estimate.

The fix is to update statistics on the relevant table:

UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

After updating, rerun the query and pull a fresh actual execution plan. In many cases, the optimizer will switch from a scan to a seek once it has accurate distribution data to work with.

Hints Are a Band-Aid, Not a Fix

When developers discover their index is being ignored, the temptation is to force it with an index hint:

SELECT * FROM Orders WITH (INDEX(IX_Orders_OrderDate))
WHERE YEAR(OrderDate) = 2023;

This forces the index, but it doesn't fix the underlying problem; the non-sargable function is still there, and you're now forcing a range scan over the index instead of a table scan. Performance may not improve at all, and you've locked the query into a specific plan regardless of how data distribution changes in the future. Index hints also make maintenance harder because they silently prevent the optimizer from adapting to schema changes.

Only use hints as a temporary diagnostic tool to confirm that an index would help if the query were written correctly. Then fix the query.

The End

When an index isn't used, the execution plan is telling you something specific. A scan where you expected a seek is a symptom, not the diagnosis. The actual cause is almost always one of a handful of reproducible patterns: a function on the column, a type mismatch, a composite index queried out of column order, a selectivity problem, or stale statistics giving the optimizer bad data to work with.

Reading execution plans isn't a skill reserved for DBAs. Any developer who writes SQL regularly will debug faster and ship more confidently once they can look at a plan and understand what the optimizer decided and why. The logic reads don't lie, and neither does the gap between estimated and actual rows.