Introduction

The Query Store in SQL Server is a powerful feature that enables database administrators and developers to track and analyze query performance over time. By storing runtime statistics and execution plans, it provides invaluable insights into how queries behave and how they can be optimized. This blog looks into the mechanics of the Query Store, the necessity of plan forcing, and how to effectively use it to stabilize and enhance query performance.

Introduced in SQL Server 2016, the Query Store is designed to monitor query performance by capturing execution plans and runtime statistics. Unlike traditional monitoring tools that require external setup, the Query Store is built into the database engine, offering a seamless way to collect and analyze performance data.

Key Features:

• Statement-Level Tracking: The Query Store evaluates queries at the statement level, not at the batch or stored procedure level. This granularity allows for precise performance tuning.
• Historical Data Retention: It retains historical data, enabling analysis of query performance over time.
• Automatic Plan Correction: The Query Store can automatically force the last known good plan if it detects a regression.

Understanding How the Query Store Works

Query Identification

Each query is assigned a unique query_id, determined by its query_hash. The query_hash ensures that structurally identical queries—ignoring literals—are tracked under the same query_id.

Example:

SELECT * FROM Customers WHERE ID = 1;
SELECT * FROM Customers WHERE ID = 2;

Both queries share the same query_id because they are structurally identical.

Execution Plans

For each query_id, the Query Store tracks all associated execution plans using a unique plan_id. The plan_id is derived from the query_plan_hash, which identifies the plan’s structure.

Scenario:

• A query’s execution plan may change due to recompilation or parameter sniffing.
• Each new plan receives a unique plan_id, allowing the Query Store to track multiple plans for the same query.

Runtime Statistics

The Query Store collects metrics such as reads, writes, duration, and execution counts for each combination of query_id and plan_id. This data provides a comprehensive view of query performance and execution history.

Why Plan Forcing Is Necessary

Parameter Sniffing and Performance Variability

SQL Server compiles execution plans based on the parameter values used during the first execution of a query. This behavior, known as parameter sniffing, can lead to performance issues if the initial parameters are not representative of typical usage.

Example Scenario:

• Common Case: 99.9% of orders are small and require an index seek for optimal performance.
• Edge Case: 0.1% of orders are large and benefit from an index scan.
• If the query is first executed with a large order, SQL Server generates a plan optimized for an index scan.
• Subsequent executions for small orders suffer performance degradation because the plan is not optimal for them.

The Need for Plan Forcing

To stabilize performance, you can force SQL Server to use a specific execution plan that is optimal for the majority of cases. Plan forcing ensures consistent performance by preventing SQL Server from generating suboptimal plans due to parameter sniffing.

How Plan Forcing Works

Recompilation

When you force a plan, SQL Server recompiles the query to generate the specified plan. If the recompilation is successful, the forced plan becomes the current plan in the cache, and new executions use it.

Plan Matching

• Matching Plans: If the recompiled plan matches the forced plan (identical query_plan_hash), the same plan_id is retained.
• Differing Plans: Minor changes during recompilation may result in a new plan_id. The original plan remains marked as is_forced_plan = 1, but the new plan is executed.

Runtime Statistics Update

Runtime statistics for subsequent executions are tied to the new plan_id if a different plan is generated during recompilation. This ensures accurate tracking of performance metrics for the actual plan in use.

Handling Unexpected Behavior in Plan Forcing

Plan forcing does not always guarantee that the exact execution plan will be used due to several factors.

Plan Adjustments During Recompilation

SQL Server may adjust the execution plan during recompilation while preserving the query logic. These adjustments can lead to a new plan_id, even if the plan is functionally equivalent.

Environment Changes

Changes in the database environment can prevent the forced plan from being applied:

• Missing Indexes: If an index used by the forced plan is dropped, SQL Server cannot generate the same plan.
• Schema Modifications: Alterations to table structures, such as adding or removing columns, affect plan validity.
• Updated Statistics: Changes in data distribution can influence the optimizer’s decisions.

When a forced plan cannot be applied, SQL Server logs a failure in the force_failure_count column of sys.query_store_plan.

Query Parameter Changes

A forced plan optimized for specific parameter values may not perform well with different parameters. In such cases, SQL Server might generate a new plan to handle the varied workload.

Plan Matching Issues

Minor runtime optimizations, such as reordering operations for efficiency, can result in a different query_plan_hash, causing the forced plan to be bypassed.

Common Scenarios Where Forced Plans Fail

Environment Changes

• Indexes: Dropped or altered indexes invalidate the forced plan.
• Schema Changes: Modifications to tables or views affect plan applicability.

Query Plan Recompilation Failure

• If SQL Server encounters errors during recompilation, it uses a fallback plan.
• Failures are recorded in force_failure_count in sys.query_store_plan.

Plan Restrictions

Forced plans may not be applicable in certain contexts:

• Bulk inserts
• External tables
• Distributed queries
• Full-text queries
• Dynamic or keyset cursors

Plan Issues

• Invalid XML: Corrupted plan definitions prevent application.
• Optimizer Limits: The query optimizer has limits on the number of joins and complexity; exceeding these can cause failures.

Tools for Plan Forcing

SQL Server Management Studio (SSMS)

SSMS provides graphical tools to:

• Compare execution plans visually.
• Evaluate runtime statistics and identify bottlenecks.
• Force plans by right-clicking on an execution plan in the Query Store reports and selecting “Force Plan.”

T-SQL Commands

You can programmatically force plans using T-SQL:

-- Enable the Query Store if not already enabled
ALTER DATABASE [YourDatabase]
SET QUERY_STORE = ON;

-- Force a specific plan
EXEC sp_query_store_force_plan
    @query_id = 12345,
    @plan_id = 67890;

Query Store Views

Analyze and manage plans using system views:

SELECT
    query_id,
    plan_id,
    is_forced_plan,
    force_failure_count
FROM sys.query_store_plan
WHERE query_id = 12345;

Best Practices for Using Plan Forcing

• Monitor Regularly: Keep an eye on forced plans to ensure they remain effective.
• Use Sparingly: Force plans only when necessary; overuse can hinder the optimizer’s ability to adapt.
• Test Thoroughly: Before forcing a plan in production, test it in a controlled environment.
• Stay Updated: Keep statistics up to date and be aware of any changes in the database schema or workload patterns.

Conclusion

The SQL Server Query Store is an indispensable tool for monitoring and optimizing query performance. By understanding how it tracks queries and execution plans, you can leverage plan forcing to stabilize performance and mitigate issues caused by parameter sniffing. While plan forcing is a powerful feature, it requires careful management to ensure that it continues to serve your performance goals effectively.

By utilizing the Query Store’s capabilities and following best practices, you can achieve a more predictable and efficient database environment, ultimately leading to better application performance and user satisfaction.

References:

• Microsoft Docs: Monitoring Performance By Using the Query Store
• Microsoft Docs: sp_query_store_force_plan (Transact-SQL)