MosherData

When SQL Server performance issues pop up, your first stop might be the cache, but it’s a quick view and gets wiped out on a reboot. That’s where Query Store comes in. Rolled out in 2016, it had a few bumps at first, but after some fixes, it’s now a solid go-to for anyone needing historical performance data. It’s the place to start when you want to dig into slow-running queries and get the insights that the cache just can’t offer.

Understanding Query Store

Query Store tracks query performance and execution history, letting you see which queries are causing problems. It’s a powerful tool for identifying performance issues and making targeted optimizations.

Step 1: Enable Query Store

First, enable Query Store on your database (with defaults):

ALTER DATABASE StackOverflow2013

SET QUERY_STORE = ON;

Step 2: Identify Slow Queries

Use Query Store reports in SQL Server Management Studio to find slow queries:

Step 3: Analyze Performance History

Review query performance over time to spot trends:

SELECT

    qsqt.query_sql_text,

    qsp.plan_id,

    qsp.last_execution_time,

    qsr.avg_duration,

    qsr.avg_cpu_time,

    qsr.avg_logical_io_reads,

    qsr.avg_logical_io_writes,

    qsr.avg_physical_io_reads,

 qsr.avg_rowcount

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 qsr

    ON qsp.plan_id = qsr.plan_id

ORDER BY

    qsp.last_execution_time DESC;

Step 4: Review Execution Plans

Examine historical execution plans to identify inefficiencies:

Step 5: Optimize Queries

Use Query Store insights to make optimizations:

• Indexing: Add or update indexes to improve performance.
• Query Refactoring: Simplify queries to enhance readability and efficiency.
• Update Statistics: Keep database statistics current for optimal query plans.
• Parameterization: Use parameterized queries to improve plan reuse.

Query Store provides insights into query performance, allowing you to troubleshoot and optimize slow-running queries effectively.

If you aren’t already using it, I would definitely test it out to see if it is right for you. That kind of sounds like a drug advertisement. Cue list of ridiculous side effects: may cause sudden bursts of excitement when queries run faster, an uncontrollable urge to optimize indexes at 3 AM, and excessive bragging about query performance improvements.