Query Store in SQL Server

 The Query Store in SQL Server is a feature that helps with performance monitoring and troubleshooting by capturing a history of queries, plans, and runtime statistics. It enables you to analyze the performance of queries over time and identify performance regressions.

Enabling Query Store

You can enable Query Store on a database using the following T-SQL command:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;

Configuring Query Store

You can configure various settings of the Query Store such as the operation mode, data retention period, and more. Here’s an example:

ALTER DATABASE [YourDatabaseName] 
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1000
);

Querying Query Store

You can query the Query Store views to retrieve information about the queries. Some useful views include:

• sys.query_store_query: Contains information about the queries.
• sys.query_store_query_text: Contains the text of the queries.
• sys.query_store_plan: Contains information about the query plans.
• sys.query_store_runtime_stats: Contains runtime statistics for the queries.

Here's an example query to get the top 10 queries by execution count:

SELECT TOP 10
    qsqt.query_sql_text,
    qsp.plan_id,
    qsq.execution_count,
    qsq.last_execution_time
FROM 
    sys.query_store_query_text AS qsqt
JOIN 
    sys.query_store_query AS qsq
    ON qsqt.query_text_id = qsq.query_text_id
JOIN 
    sys.query_store_plan AS qsp
    ON qsq.query_id = qsp.query_id
JOIN 
    sys.query_store_runtime_stats AS qsrs
    ON qsp.plan_id = qsrs.plan_id
ORDER BY 
    qsq.execution_count DESC;

Forcing a Plan

If you identify a query plan that performs better, you can force SQL Server to use that plan for a specific query:

EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 1;

Unforcing a Plan

To unforce a plan, use the following command:

EXEC sp_query_store_unforce_plan @query_id = 1, @plan_id = 1;

Disabling Query Store

If you want to disable the Query Store, you can do so with the following command:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF;

Query Store is a powerful tool for monitoring and improving query performance in SQL Server, providing valuable insights into query behavior over time.