The Query Store: What is it?
With SQL Server 2016, a performance monitoring tool called the Query Store was added. It records query execution history, query plans, and performance metrics over time, much like a black box recorder. Because Query Store retains historical data, it is simpler to troubleshoot performance issues that have already occurred, in contrast to standard DMVs that only provide live statistics.

Why Use Query Store?

  • Monitor how query performance changes over time.
  • Detect and troubleshoot query regressions (when performance drops).
  • Compare performance before and after deployments.
  • Identify and force optimal plans for queries.
  • Track resource usage (CPU, IO, duration) per query.

How to Enable Query Store?
Option 1. T-SQL
ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE = ON;

Option 2. SSMS GUI

  • Right-click the database → Properties
  • Navigate to the Query Store page
  • Set Operation Mode (Requested) to Read Write
  • Adjust other settings like capture mode and retention as needed


What Data does Query Store Capture?

Category Description
Query Text The full T-SQL text of the executed query
Query Plan The execution plan used to execute the query
Runtime Stats CPU time, duration, reads/writes, executions, etc.
Wait Stats (SQL 2017+) Time spent waiting on resources like locks or memory
Plan Forcing Force SQL Server to always use a specific plan

Internal Views for Query Store Analysis

  • sys.query_store_query: Stores each normalized query
  • sys.query_store_plan: Stores query execution plans
  • sys.query_store_runtime_stats: Stores execution metrics
  • sys.query_store_wait_stats: Captures wait types per query (SQL 2017+)

Built-in SSMS Reports
SSMS provides graphical reports for easier analysis.

  • Right-click the database
  • Select Reports → Standard Reports → Query Store
  • Explore reports like.
    • Top Resource Consuming Queries
    • Tracked Queries
    • Query Plan Changes
    • Regressed Queries

Plan Forcing Example
If SQL Server chooses a suboptimal plan, you can force a better one.

Steps to Force a Plan via T-SQL
EXEC sp_query_store_force_plan
    @query_id = 102,
    @plan_id = 301;

To Unforce a Plan
EXEC sp_query_store_unforce_plan
    @query_id = 102,
    @plan_id = 301;

Note: Forced plans remain in use until manually unforced or if the plan becomes invalid due to schema changes.

How to Clean Query Store?
Remove Specific Plan or Query
EXEC sp_query_store_remove_plan @plan_id = 123;
EXEC sp_query_store_remove_query @query_id = 456;

Clear All Data from Query Store
ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE CLEAR;

Query Store Modes

Mode Description
Off Query Store is disabled
Read Only Existing data is visible but not updated
Read Write Captures and stores new execution data

Limitations and Considerations

  • Slight overhead on heavily loaded systems (~1–5%)
  • Disk space usage can grow—monitor data size regularly
  • Not available in SQL Server 2014 or earlier
  • Retention settings control how long historical data is kept

Conclusion
The Query Store is a powerful feature for any SQL Server environment focused on performance, stability, and transparency. It simplifies identifying performance problems and helps maintain consistent performance by allowing query plan control. Use it proactively in development, staging, and production environments to avoid surprises and ensure optimal performance.

HostForLIFEASP.NET SQL Server 2022 Hosting