Using Index Hints with Query Store in Microsoft SQL Server

Index hints have always been a controversial feature in Microsoft SQL Server. On one hand, they allow experienced database engineers to override the query optimizer’s choices and force the use of a specific index. On the other hand, they can easily become a long-term maintenance burden if used carelessly.

With the introduction and evolution of Query Store, SQL Server provides a much safer and more controlled way to influence query plans, including plans that rely on specific indexes. Rather than hard-coding index hints directly into application queries, Query Store allows you to capture, evaluate, and selectively force execution plans that already include the desired index usage.

What Are Index Hints?

An index hint is a directive embedded in a T-SQL query that tells the optimizer which index to use when accessing a table or view. A common example looks like this: 

SELECT *
FROM dbo.Orders WITH (INDEX(IX_Orders_OrderDate))
WHERE OrderDate >= '2025-01-01';

When SQL Server sees this hint, it bypasses its usual cost-based index selection logic and attempts to use the specified index. While this can fix certain performance issues, it also removes the optimizer’s flexibility to adapt to data growth, distribution changes, or schema updates.

Why Index Hints Are Risky in Application Code

Embedding index hints directly in application queries creates tight coupling between code and physical database design. If the hinted index is dropped, renamed, or becomes suboptimal, queries may fail or perform poorly.

Common risks include:

This is where Query Store provides a more robust alternative.

Query Store as a Safer Control Mechanism

Query Store captures query text, execution plans, and runtime statistics over time. This historical view allows you to understand how a query behaves under different plans and data conditions.

Instead of forcing an index hint in the query text, you can:

The application query remains unchanged, and the optimizer can still evolve naturally for other queries.

How Index Usage Appears in Query Store

Query Store does not store “index hints” as a separate concept. Instead, index choices are part of the execution plan itself. If a plan uses a specific index, that decision is visible in the plan XML and in graphical execution plans.

When you force a plan via Query Store, you are implicitly forcing all of its physical operators, including index seeks, scans, and lookups.

Note: This approach works best when the plan you want to force is already stable and well-understood. Forcing a poor plan can be just as damaging as a bad index hint.

Forcing a Plan That Uses a Specific Index

A typical workflow looks like this:

  1. Enable Query Store (if not already enabled).
  2. Let the workload run and collect multiple plans.
  3. Identify the plan with the best performance.
  4. Confirm the index usage in that plan.
  5. Force the plan using Query Store.

Example of forcing a plan using T-SQL: 

EXEC sys.sp_query_store_force_plan
    @query_id = 123,
    @plan_id = 456;

From this point on, SQL Server will attempt to use the forced plan, including the index selections embedded in it.

Benefits Over Traditional Index Hints

Using Query Store to control index usage offers several advantages:

This makes Query Store particularly attractive in environments where application changes are slow, risky, or owned by a different team.

When You Should Still Be Careful

Warning: Forcing plans is not a replacement for proper indexing and query design. It is a tactical tool, not a strategic solution.

You should re-evaluate forced plans regularly, especially after:

Query Store will automatically unforce plans if they become invalid, but it will not decide whether a forced plan is still optimal.

Conclusion

Index hints can solve real performance problems, but they come with long-term risks when embedded directly in queries. Query Store provides a powerful middle ground: it allows you to influence index usage by forcing known-good execution plans, without polluting application code or permanently overriding the optimizer.

Used carefully and reviewed regularly, Query Store-based plan forcing can be an effective and maintainable way to handle index-related performance regressions in Microsoft SQL Server.