DEV Community

mohamed Tayel
mohamed Tayel

Posted on

SQL Server Query Store Hints: Fix Query Performance Without Changing Application Code

#sql #sqlserver #querystore

In the previous articles in this series, we introduced SQL Server Query Store, used it to investigate performance regressions, and explored how unstable execution plans can contribute to parameter sniffing problems.

Query Store helped us answer important diagnostic questions:

  • Has the query used multiple execution plans?
  • When did its performance begin to change?
  • Which plan consumed more CPU or execution time?
  • Was the problem related to parameter sensitivity?
  • Did SQL Server select a less efficient plan after recompilation?

Identifying the problem, however, is only the first step.

Once a problematic query has been found, the next question is:

How can we influence the query’s execution behavior when changing the application code is difficult, risky, or temporarily impossible?

This is where Query Store Hints become useful.

Query Store Hints allow database administrators to apply query-level optimizer hints without modifying the original SQL statement, changing a stored procedure, or redeploying the application.

In this article, we will explore:

  • What Query Store Hints are.
  • How they differ from traditional query hints.
  • How SQL Server associates a hint with a query.
  • How to find the correct query_id.
  • How to apply a simple MAXDOP hint.
  • How to verify that the hint was applied.
  • How to measure performance before and after the change.
  • How to safely remove the hint.

The Real-World Problem: Queries You Cannot Easily Change

In an ideal development process, a slow query can be modified, tested, reviewed, and deployed as part of a new application release.

Production environments, however, are rarely that simple.

The problematic SQL statement might be:

  • Hard-coded inside a legacy application.
  • Generated automatically by an ORM.
  • Produced by a reporting platform.
  • Executed by third-party software.
  • Located inside a stored procedure owned by another team.
  • Part of an application with a long release cycle.
  • Running during a critical business period when deployment is too risky.

In these situations, the database administrator might understand which optimizer behavior should be tested but still be unable to modify the query text directly.

Historically, the available alternatives included:

  • Creating a plan guide.
  • Changing a database-wide configuration.
  • Changing a server-wide configuration.
  • Forcing a previously captured execution plan.
  • Requesting an emergency application release.

Some of these approaches are complex to manage.

Others affect a much larger workload than the individual query causing the problem.

Query Store Hints provide a more targeted option.

They allow SQL Server to apply an optimizer hint to a specific query already captured by Query Store while the application continues sending the original SQL statement.

What Is a Query Store Hint?

A traditional query hint is written directly inside the SQL statement using an OPTION clause.

For example:

SELECT
    ProductID,
    SUM(LineTotal) AS TotalSales
FROM Sales.SalesOrderDetail
GROUP BY
    ProductID
OPTION (MAXDOP 1);
Enter fullscreen mode Exit fullscreen mode

The following part is the traditional query hint:

OPTION (MAXDOP 1)
Enter fullscreen mode Exit fullscreen mode

It tells SQL Server that this statement should use a maximum degree of parallelism of one.

The problem is that adding this hint requires changing the SQL statement itself.

With Query Store Hints, the application can continue executing the original query:

SELECT
    ProductID,
    SUM(LineTotal) AS TotalSales
FROM Sales.SalesOrderDetail
GROUP BY
    ProductID;
Enter fullscreen mode Exit fullscreen mode

The DBA applies the hint separately:

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints = N'OPTION (MAXDOP 1)';
Enter fullscreen mode Exit fullscreen mode

SQL Server associates the hint with the query identified by:

query_id = 123
Enter fullscreen mode Exit fullscreen mode

The application does not need to know that the hint exists.

There is no need to:

  • Modify the application source code.
  • Change the stored procedure.
  • Rebuild the application.
  • Create a new deployment package.
  • Schedule an immediate application release.

How Query Store Hints Work

The Query Store Hint workflow contains five main steps:

  1. The application executes a query.
  2. Query Store captures the query, its execution plans, and runtime statistics.
  3. The DBA identifies the query’s query_id.
  4. The DBA applies a hint using sys.sp_query_store_set_hints.
  5. SQL Server considers the hint when compiling the query.

Conceptually, SQL Server treats the Query Store Hint as though an OPTION clause had been added to the original SQL statement.

The application query remains unchanged.

The hint is stored separately inside the database and associated with the corresponding Query Store query record.

This persistence makes Query Store Hints powerful, but it also creates an important operational responsibility:

Every Query Store Hint should be documented, monitored, and periodically reevaluated.

A hint that improves performance today might become inappropriate after changes to:

  • Data volume.
  • Data distribution.
  • Indexes.
  • Statistics.
  • Hardware resources.
  • Database compatibility level.
  • SQL Server updates.
  • Application workload patterns.

Query Store Hints should not become permanent configurations that remain in the database after everyone has forgotten why they were added.

Requirements

Before using Query Store Hints, verify the SQL Server platform, Query Store status, and required permissions.

Supported Platforms

For on-premises environments, Query Store Hints were introduced in SQL Server 2022.

They are also supported on applicable Azure SQL platforms.

Query Store Must Be Enabled

Query Store must be enabled for the target database.

It should normally be operating in READ_WRITE mode when Query Store Hints are being managed.

Check the current Query Store state:

SELECT
    actual_state_desc,
    desired_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb
FROM sys.database_query_store_options;
Enter fullscreen mode Exit fullscreen mode

A healthy writable state normally shows:

actual_state_desc  = READ_WRITE
desired_state_desc = READ_WRITE
Enter fullscreen mode Exit fullscreen mode

If Query Store is disabled, enable it using:

ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE
);
Enter fullscreen mode Exit fullscreen mode

Replace:

YourDatabaseName
Enter fullscreen mode Exit fullscreen mode

with the actual database name.

Query Store can move into READ_ONLY mode for several reasons, including reaching its configured maximum storage size.

For this reason, always verify the actual state instead of assuming that Query Store is writable.

Required Permissions

Applying or removing a Query Store Hint requires the ALTER permission on the target database.

In SQL Server 2022 and later, querying sys.query_store_query_hints requires the VIEW SERVER PERFORMANCE STATE permission.

Note: Permissions for other Query Store catalog views may differ depending on the SQL Server version and the specific view being accessed.

Why query_id Is Critical

Query Store Hints are not applied using:

  • The stored procedure name.
  • The application name.
  • The login name.
  • A fragment of the SQL text.
  • The execution plan ID.

They are applied using the Query Store:

query_id
Enter fullscreen mode Exit fullscreen mode

The relationship can be simplified as follows:

Query Text
    ↓
Query Store Query
    ↓
query_id
    ↓
Execution Plans
    ↓
Runtime Statistics
    ↓
Query Store Hint
Enter fullscreen mode Exit fullscreen mode

The query_id connects a query to:

  • Its stored SQL text.
  • Its execution plans.
  • Its runtime history.
  • Its wait statistics.
  • Any forced execution plan.
  • Any Query Store Hint.

Finding the correct query_id is therefore one of the most important steps in the entire process.

Applying a hint to the wrong query_id can affect a completely different query.

Practical Example: Testing MAXDOP 1

Consider an application that executes the following query:

SELECT
    sod.ProductID,
    SUM(sod.LineTotal) AS TotalSales
FROM Sales.SalesOrderDetail AS sod
GROUP BY
    sod.ProductID;
Enter fullscreen mode Exit fullscreen mode

Assume that Query Store analysis shows that the query sometimes uses an inefficient parallel execution plan.

For demonstration purposes, we want to test the query with:

OPTION (MAXDOP 1)
Enter fullscreen mode Exit fullscreen mode

This example does not mean that parallelism is automatically a performance problem.

Parallel execution can significantly improve performance for analytical and reporting queries.

MAXDOP 1 should only be considered when testing shows that serial execution is more appropriate for the specific query and surrounding workload.

The purpose of this example is to demonstrate the Query Store Hint workflow, not to recommend MAXDOP 1 as a universal tuning solution.

Step 1: Make Sure the Query Is Captured

A query must exist in Query Store before a Query Store Hint can be associated with it.

Execute the target query:

SELECT
    sod.ProductID,
    SUM(sod.LineTotal) AS TotalSales
FROM Sales.SalesOrderDetail AS sod
GROUP BY
    sod.ProductID;
Enter fullscreen mode Exit fullscreen mode

In a real production system, the application might already have executed the query many times.

Query Store captures information asynchronously, so a newly executed query might not appear immediately in the catalog views.

In a controlled test environment, you can flush the in-memory Query Store information:

EXEC sys.sp_query_store_flush_db;
Enter fullscreen mode Exit fullscreen mode

This can make recently collected Query Store information available for investigation.

It should not be executed repeatedly in production merely to refresh a monitoring query.

Step 2: Find the Correct query_id

Search Query Store using a distinctive fragment of the query text:

SELECT
    q.query_id,
    q.object_id,
    q.query_hash,
    qt.query_sql_text,
    q.initial_compile_start_time,
    q.last_compile_start_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
WHERE qt.query_sql_text LIKE N'%SUM(sod.LineTotal)%'
  AND qt.query_sql_text NOT LIKE N'%sys.query_store_query_text%';
Enter fullscreen mode Exit fullscreen mode

An example result might look like this:

query_id   query_sql_text
--------   -------------------------------------------------------
123        SELECT sod.ProductID, SUM(sod.LineTotal) AS TotalSales...
Enter fullscreen mode Exit fullscreen mode

Before applying a hint, verify that:

  • The SQL text is the intended statement.
  • You are connected to the correct database.
  • The result is not the Query Store search statement itself.
  • The query belongs to the expected stored procedure or batch.
  • There are no other relevant query_id values for equivalent query text.
  • The query context is correct.

A logically similar query can appear under more than one query_id.

This can happen because of differences in:

  • SQL text.
  • Parameterization.
  • Batch context.
  • Database context.
  • Object context.
  • Query compilation settings.

Never assume that the first result returned by the search is automatically the correct target.

Step 3: Capture a Performance Baseline

Do not apply a Query Store Hint before collecting baseline information.

Without a baseline, it is impossible to prove whether the hint improved or degraded performance.

The following query summarizes runtime statistics by execution plan:

DECLARE @QueryId bigint = 123;

SELECT
    q.query_id,
    p.plan_id,
    p.is_forced_plan,
    SUM(rs.count_executions) AS execution_count,
    CAST
    (
        SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_duration_ms,
    CAST
    (
        SUM(rs.avg_cpu_time * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_cpu_ms,
    CAST
    (
        MAX(rs.max_duration) / 1000.0
        AS decimal(18,2)
    ) AS max_duration_ms
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
WHERE q.query_id = @QueryId
GROUP BY
    q.query_id,
    p.plan_id,
    p.is_forced_plan
ORDER BY
    weighted_avg_duration_ms DESC;
Enter fullscreen mode Exit fullscreen mode

A useful performance baseline should include more than average duration.

Depending on the query, collect:

  • Execution count.
  • Average duration.
  • Maximum duration.
  • Average CPU time.
  • Logical reads.
  • Physical reads.
  • Memory grant information.
  • Wait statistics.
  • Number of execution plans.
  • Execution-plan shape.
  • Workload concurrency.
  • Representative parameter values.

The baseline should reflect normal workload conditions whenever possible.

Step 4: Apply the Query Store Hint

After confirming the correct query_id, apply the hint:

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints = N'OPTION (MAXDOP 1)';
Enter fullscreen mode Exit fullscreen mode

The general syntax is:

EXEC sys.sp_query_store_set_hints
    @query_id = <QueryStoreQueryId>,
    @query_hints = N'OPTION (<SupportedQueryHints>)';
Enter fullscreen mode Exit fullscreen mode

For example:

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints = N'OPTION (RECOMPILE)';
Enter fullscreen mode Exit fullscreen mode

Multiple compatible hints can be included in a single call:

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints = N'OPTION (RECOMPILE, MAXDOP 1)';
Enter fullscreen mode Exit fullscreen mode

When testing a performance change, however, start with the smallest possible modification.

Applying several hints simultaneously makes it difficult to identify which hint caused the improvement or regression.

Important: A New Hint Replaces the Existing Definition

A Query Store query has one stored Query Store Hint definition for the applicable scope.

Calling sys.sp_query_store_set_hints again does not append a second independent hint.

The new hint text replaces the previous definition.

Assume the current hint is:

OPTION (MAXDOP 1)
Enter fullscreen mode Exit fullscreen mode

Then the DBA executes:

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints = N'OPTION (RECOMPILE)';
Enter fullscreen mode Exit fullscreen mode

The resulting Query Store Hint becomes:

OPTION (RECOMPILE)
Enter fullscreen mode Exit fullscreen mode

It does not automatically become:

OPTION (MAXDOP 1, RECOMPILE)
Enter fullscreen mode Exit fullscreen mode

To preserve both hints, they must be supplied together:

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints = N'OPTION (MAXDOP 1, RECOMPILE)';
Enter fullscreen mode Exit fullscreen mode

This replacement behavior is particularly important when Query Store Hints are managed by:

  • Multiple DBAs.
  • Automated tuning scripts.
  • Deployment pipelines.
  • Operational support teams.

Always review the existing hint before changing it.

Step 5: Verify the Stored Hint

Query the sys.query_store_query_hints catalog view:

SELECT
    query_hint_id,
    query_id,
    query_hint_text,
    last_query_hint_failure_reason,
    last_query_hint_failure_reason_desc,
    query_hint_failure_count,
    source,
    source_desc
FROM sys.query_store_query_hints
WHERE query_id = 123;
Enter fullscreen mode Exit fullscreen mode

An expected result might look like this:

query_id                  123
query_hint_text           OPTION (MAXDOP 1)
failure_reason            NULL
failure_reason_desc       NULL
query_hint_failure_count  0
source_desc               User
Enter fullscreen mode Exit fullscreen mode

The most important columns are described below.

query_hint_text

Shows the currently stored Query Store Hint.

For this example:

OPTION (MAXDOP 1)
Enter fullscreen mode Exit fullscreen mode

last_query_hint_failure_reason

Shows the most recent error number generated when SQL Server could not apply the hint.

A successful hint normally shows:

NULL
Enter fullscreen mode Exit fullscreen mode

last_query_hint_failure_reason_desc

Provides a description of the most recent hint-application failure.

query_hint_failure_count

Shows how many times SQL Server failed to apply the hint.

For a successfully applied hint, the expected value is:

0
Enter fullscreen mode Exit fullscreen mode

source_desc

Shows the source of the hint.

A manually created Query Store Hint normally appears as:

User
Enter fullscreen mode Exit fullscreen mode

The existence of a row in sys.query_store_query_hints proves that the hint was stored.

It does not, by itself, prove that SQL Server successfully applied the hint during compilation.

This is why the failure-related columns must also be checked.

Step 6: Execute the Query Again

Execute the original application query again:

SELECT
    sod.ProductID,
    SUM(sod.LineTotal) AS TotalSales
FROM Sales.SalesOrderDetail AS sod
GROUP BY
    sod.ProductID;
Enter fullscreen mode Exit fullscreen mode

The SQL statement remains unchanged.

The application does not include:

OPTION (MAXDOP 1)
Enter fullscreen mode Exit fullscreen mode

SQL Server applies the behavior through Query Store.

Capture the actual execution plan and verify whether the query is now using a serial plan.

You can also inspect the execution-plan XML for Query Store Hint attributes.

Depending on the SQL Server version and plan format, the plan can contain information such as:

QueryStoreStatementHintText
QueryStoreStatementHintId
QueryStoreStatementHintSource
Enter fullscreen mode Exit fullscreen mode

These attributes provide additional evidence that SQL Server compiled the query using a Query Store Hint.

However, verifying the plan shape is not enough.

The real questions are:

  • Did execution duration improve?
  • Did CPU consumption decrease?
  • Did logical reads change?
  • Did the query begin waiting on different resources?
  • Did concurrency improve?
  • Did the query become slower for other parameter values?
  • Did limiting parallelism increase elapsed time?
  • Did the change negatively affect another part of the workload?
  • Is the improvement stable across multiple Query Store intervals?

A different execution plan is not automatically a better execution plan.

Step 7: Compare Performance After the Hint

After allowing the query to execute under representative workload conditions, review the Query Store runtime statistics again.

Use the same measurement method that was used to capture the baseline.

Compare:

Before the Hint
vs.
After the Hint
Enter fullscreen mode Exit fullscreen mode

A simple comparison can include:

Metric Before Hint After Hint Result
Average duration Record value Record value Improved or regressed
Average CPU Record value Record value Improved or regressed
Maximum duration Record value Record value Improved or regressed
Logical reads Record value Record value Improved or regressed
Execution count Record value Record value Comparable workload
Main waits Record value Record value Changed or unchanged

Be careful when comparing Query Store intervals.

An interval containing five executions should not be compared directly with another interval containing thousands of executions without considering the workload difference.

Also verify that the parameter distribution is representative.

A hint that improves one parameter value might degrade performance for another.

What Happens When SQL Server Cannot Apply the Hint?

A Query Store Hint can fail for several reasons:

  • The hint syntax is invalid.
  • The hint is not supported.
  • The hint conflicts with another optimizer directive.
  • The optimizer cannot produce a valid execution plan using the hint.
  • The query is not eligible for the selected hint.
  • The query uses an unsupported parameterization pattern.
  • The hint conflicts with an existing query configuration.

In many cases, SQL Server allows the application query to continue executing even when the Query Store Hint cannot be applied.

The query might execute without the requested hint while the failure information is recorded in:

sys.query_store_query_hints
Enter fullscreen mode Exit fullscreen mode

This behavior helps protect application availability.

However, it also means that DBAs should never assume that a stored hint is automatically active.

Use the following query to identify Query Store Hints with recorded failures:

SELECT
    query_hint_id,
    query_id,
    query_hint_text,
    last_query_hint_failure_reason,
    last_query_hint_failure_reason_desc,
    query_hint_failure_count,
    source_desc
FROM sys.query_store_query_hints
WHERE query_hint_failure_count > 0
   OR last_query_hint_failure_reason IS NOT NULL
ORDER BY
    query_hint_failure_count DESC;
Enter fullscreen mode Exit fullscreen mode

This query should be included in regular Query Store monitoring when Query Store Hints are used in production.

Examples of Query Store Hints

Query Store Hints can apply several useful optimizer hints, including:

MAXDOP
RECOMPILE
OPTIMIZE FOR UNKNOWN
MAX_GRANT_PERCENT
MIN_GRANT_PERCENT
HASH JOIN
MERGE JOIN
LOOP JOIN
FORCE ORDER
KEEP PLAN
KEEPFIXED PLAN
NO_PERFORMANCE_SPOOL
USE HINT
Enter fullscreen mode Exit fullscreen mode

Not every traditional SQL Server hint is supported through Query Store Hints.

Examples that require special consideration include:

OPTIMIZE FOR (@Variable = Value)
MAXRECURSION
USE PLAN
Table hints such as FORCESEEK or INDEX
Enter fullscreen mode Exit fullscreen mode

The supported list can depend on the SQL Server platform and version.

Always verify the selected hint against the official documentation for the environment being managed.

In the next article in this series, we will examine the most important Query Store Hints individually and discuss when each one should and should not be used.

Query Store Hint Versus Plan Forcing

Query Store Hints and Query Store plan forcing both influence query execution, but they solve different problems.

Plan Forcing

Plan forcing tells SQL Server:

Try to reproduce this specific historical execution plan.

This approach is useful when:

  • A previously efficient plan already exists.
  • A new plan caused a performance regression.
  • The DBA wants SQL Server to return to the known plan.

Query Store Hint

A Query Store Hint tells SQL Server:

Compile the query while applying this optimizer behavior.

Examples include:

Use a maximum DOP of 1.
Recompile the query for every execution.
Optimize for an unknown parameter value.
Limit the memory grant.
Prefer a particular join strategy.
Enter fullscreen mode Exit fullscreen mode

The optimizer can still generate a new execution plan, but it must consider the applied hint.

The difference can be summarized as follows:

Plan Forcing
=
Attempt to reproduce a specific plan shape.

Query Store Hint
=
Influence the rules used to generate a plan.
Enter fullscreen mode Exit fullscreen mode

A more detailed comparison between plan forcing and Query Store Hints will be covered in a later article.

When Query Store Hints Are Useful

Query Store Hints can be useful in several practical scenarios.

1. The Application Cannot Be Modified

The query might be generated by:

  • Vendor software.
  • An ORM.
  • A reporting system.
  • A legacy application.
  • A packaged product.

2. An Emergency Performance Fix Is Required

A critical production query might need immediate stabilization before a permanent application change can complete the normal development and release process.

3. One Query Needs Different Behavior

A single query might need:

  • A lower MAXDOP.
  • A memory-grant limit.
  • Recompilation.
  • A different optimizer compatibility behavior.

Changing a server-wide or database-wide setting would affect too many other queries.

4. A Migration Caused a Regression

After upgrading SQL Server or changing the database compatibility level, one query might begin behaving differently.

A targeted Query Store Hint can provide temporary stabilization while the root cause is investigated.

5. A DBA Wants to Test an Optimizer Behavior

Query Store Hints can be used to test a possible optimization without immediately modifying the application source code.

The DBA can then compare Query Store runtime statistics before and after the change.

When Query Store Hints Should Not Be the First Fix

Query Store Hints are powerful, but they should not replace proper investigation.

Before applying a hint, investigate possible root causes such as:

  • Missing indexes.
  • Ineffective indexes.
  • Outdated statistics.
  • Poor cardinality estimates.
  • Incorrect data types.
  • Implicit conversions.
  • Non-SARGable predicates.
  • Parameter sensitivity.
  • Excessive query complexity.
  • Blocking.
  • Lock contention.
  • Memory pressure.
  • Storage latency.
  • Inappropriate database configuration.
  • Available Intelligent Query Processing features.

A hint can improve the visible symptom while leaving the underlying design problem unresolved.

For example, forcing serial execution might reduce parallelism-related waits but increase total elapsed time.

Similarly, adding RECOMPILE might solve a parameter sensitivity problem but create excessive compilation CPU for a high-frequency query.

The correct hint should always be selected based on evidence rather than assumptions.

Production Safety Checklist

Before applying a Query Store Hint in production:

  1. Confirm the correct database.
  2. Confirm the correct query_id.
  3. Review the exact SQL text.
  4. Check for multiple related query_id values.
  5. Capture a performance baseline.
  6. Review all existing execution plans.
  7. Check for an existing Query Store Hint.
  8. Test with production-like data.
  9. Test multiple representative parameter values.
  10. Measure duration, CPU, reads, waits, and concurrency.
  11. Prepare a rollback command.
  12. Document the reason for the hint.
  13. Record the owner and implementation date.
  14. Define a monitoring period.
  15. Schedule a future review date.
  16. Reevaluate the hint after major environment changes.

Remember:

A Query Store Hint can affect every execution associated with the targeted query_id.

The change is not limited to the DBA’s test session.

Removing the Query Store Hint

Every production change should have a rollback path.

To remove the Query Store Hint:

EXEC sys.sp_query_store_clear_hints
    @query_id = 123;
Enter fullscreen mode Exit fullscreen mode

Verify that the hint has been removed:

SELECT
    query_id,
    query_hint_text,
    source_desc
FROM sys.query_store_query_hints
WHERE query_id = 123;
Enter fullscreen mode Exit fullscreen mode

After the hint is cleared, the query should no longer appear in this view for that query_id.

The application query remains unchanged throughout the entire process.

Complete Demonstration Script

The following script summarizes the complete workflow:

/*==============================================================
  1. Verify Query Store state
==============================================================*/

SELECT
    actual_state_desc,
    desired_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb
FROM sys.database_query_store_options;


/*==============================================================
  2. Find the target query_id
==============================================================*/

SELECT
    q.query_id,
    q.object_id,
    q.query_hash,
    qt.query_sql_text,
    q.initial_compile_start_time,
    q.last_compile_start_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
WHERE qt.query_sql_text LIKE N'%SUM(sod.LineTotal)%'
  AND qt.query_sql_text NOT LIKE N'%sys.query_store_query_text%';


/*==============================================================
  3. Capture the baseline
==============================================================*/

DECLARE @QueryId bigint = 123;

SELECT
    q.query_id,
    p.plan_id,
    p.is_forced_plan,
    SUM(rs.count_executions) AS execution_count,
    CAST
    (
        SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_duration_ms,
    CAST
    (
        SUM(rs.avg_cpu_time * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_cpu_ms,
    CAST
    (
        MAX(rs.max_duration) / 1000.0
        AS decimal(18,2)
    ) AS max_duration_ms
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
WHERE q.query_id = @QueryId
GROUP BY
    q.query_id,
    p.plan_id,
    p.is_forced_plan
ORDER BY
    weighted_avg_duration_ms DESC;


/*==============================================================
  4. Check for an existing Query Store Hint
==============================================================*/

SELECT
    query_hint_id,
    query_id,
    query_hint_text,
    last_query_hint_failure_reason,
    last_query_hint_failure_reason_desc,
    query_hint_failure_count,
    source_desc
FROM sys.query_store_query_hints
WHERE query_id = @QueryId;


/*==============================================================
  5. Apply the Query Store Hint
==============================================================*/

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints = N'OPTION (MAXDOP 1)';


/*==============================================================
  6. Verify the stored hint and failure status
==============================================================*/

SELECT
    query_hint_id,
    query_id,
    query_hint_text,
    last_query_hint_failure_reason,
    last_query_hint_failure_reason_desc,
    query_hint_failure_count,
    source_desc
FROM sys.query_store_query_hints
WHERE query_id = 123;


/*==============================================================
  7. Roll back when required
==============================================================*/

EXEC sys.sp_query_store_clear_hints
    @query_id = 123;
Enter fullscreen mode Exit fullscreen mode

Do not execute this script directly in production without:

  • Replacing the example query_id.
  • Confirming the target SQL statement.
  • Capturing a performance baseline.
  • Reviewing the existing hint.
  • Preparing a validation plan.
  • Preparing the rollback command.

Final Thoughts

Query Store is more than a historical performance recorder.

With Query Store Hints, it can also become a controlled mechanism for influencing the compilation behavior of individual queries.

The complete workflow is:

Query Store captures the problem.
The DBA identifies the query.
The DBA applies a targeted hint.
SQL Server compiles the query using the hint.
Query Store measures the result.
The DBA keeps, changes, or removes the hint.
Enter fullscreen mode Exit fullscreen mode

The greatest advantage of Query Store Hints is their ability to address query-level performance problems without immediately changing application code.

Their greatest risk comes from the same capability.

A Query Store Hint can quickly improve a critical query, but it can also enforce behavior that becomes inappropriate as the system changes.

For this reason, every Query Store Hint should be:

  • Evidence-based.
  • Carefully tested.
  • Easy to roll back.
  • Clearly documented.
  • Continuously monitored.
  • Periodically reevaluated.

Query Store Hints should be treated as controlled performance configurations, not forgotten emergency fixes.

Continue the Query Store Series

This article is Part 4 of the Query Store series:

  • Part 1: Introduction to SQL Server Query Store.
  • Part 2: Performance Regression Detection and Execution Plan Analysis.
  • Part 3: Detecting Unstable Execution Plans and Parameter Sniffing.
  • Part 4: Fixing Query Performance with Query Store Hints.

In the next article, we will examine the most useful Query Store Hints—including RECOMPILE, OPTIMIZE FOR UNKNOWN, MAXDOP, and memory-grant hints—and explain when each one should and should not be used.

Top comments (0)