https://sqlmaster.mssqlplanner.com

Tag: Query Store

  • SQL Server Query Store: How It Works and How to Force a Better Plan

    SQL Server Query Store: How It Works and How to Force a Better Plan

    The plan cache (Module 1) resets on restart and only shows current state. Query Store persists query and plan performance history per database, across restarts — and lets you directly force a known-good plan.

    Catch the regression, force the fix(a plan’s history, sketched)Plan Aavg duration: 12msthe GOOD planrunning happily for weeks!Plan Bavg duration: 850msREGRESSED after recompilenew parameter, bad estimatePlan Anow FORCED (pinned)sp_query_store_force_planno code deploy neededrecompile — new paramforce_plan pins itQuery Store remembers every plan ever compiled for a query —you look up the good one and pin it, instead of guessing.Gotcha: forcing a plan isn’t forever. If it becomesinvalid (e.g. an index it needs gets dropped), SQL Serversilently falls back to a fresh compile — checklast_force_failure_reason, don’t assume it’s permanent. 📌

    Enabling It

    ALTER DATABASE YourDatabase SET QUERY_STORE = ON;
ALTER DATABASE YourDatabase SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

    Finding Regressed Queries

    SELECT q.query_id, qt.query_sql_text, rs.avg_duration, rs.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

    The Feature That Directly Fixes Parameter Sniffing: Forcing a Plan

    Query Store remembers every plan a query has ever used You can pin the good one, permanently, without changing code 

    EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 137;
-- Later, to release it:
EXEC sp_query_store_unforce_plan @query_id = 42, @plan_id = 137;

    This is a genuinely production-safe response to the Module 3 parameter sniffing problem — no code deployment needed, immediately reversible, and the exact plan is verifiable (unlike a hint that only influences future compilation).

    Enjoyed this?

    Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.

  • Module 6 Exercises: SQL Server Monitoring & Tooling Labs (10 Hands-On Exercises)

    Module 6 Exercises: SQL Server Monitoring & Tooling Labs

    5 guided labs, 3 challenge scenarios, and 2 break-it labs.

    Guided Labs

    Guided1. Enable Query Store on a test database and confirm its operation mode.

    ALTER DATABASE YourTestDb SET QUERY_STORE = ON;
SELECT actual_state_desc FROM sys.database_query_store_options;
    Guided2. Run the same query twice with different parameter selectivity, find both plans in Query Store, and force the better one.
    Guided3. Build a production-style Extended Events session filtered to duration > 2 seconds with a bounded rollover file target.
    Guided4. Add Page Life Expectancy, Batch Requests/sec, and Full Scans/sec to a PerfMon data collector set.
    Guided5. Read events back from an XEvents file target using sys.fn_xe_file_target_read_file.

    Challenge Scenarios

    Challenge6. A query performed well for months, then regressed after a deployment. Using Query Store, design a plan to find and force the pre-deployment plan without a code rollback.
    Challenge7. Design an Extended Events session to specifically catch queries causing tempdb spills, tying back to Module 4’s SpillToTempDb signature.
    Challenge8. A PerfMon dashboard shows Full Scans/sec climbing steadily over three months with no application changes. Propose which specific DMVs from this course you’d check next, in order.

    Break-It Labs

    Break-It9. Deliberately cause a query regression: force a bad plan via Query Store on a test query, observe degraded PerfMon/DMV metrics, then unforce it and confirm recovery.
    Break-It10. Deliberately create Extended Events overhead: run a session with NO duration filter capturing every statement on a busy test workload, observe the file size/overhead, then fix it with an aggressive filter and compare.

    Enjoyed this?

    Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.

  • Module 6 Quiz: SQL Server Monitoring & Tooling (10 Questions)

    Module 6 Quiz: SQL Server Monitoring & Tooling

    1. How does Query Store differ from the plan cache?

    A) They are identical
    B) Query Store persists history per-database across restarts; the plan cache resets on restart
    C) Query Store only works on Azure
    D) The plan cache is more detailed

    Show Answer

    Answer: B

    This persistence is exactly what makes Query Store useful for tracking regression over time, not just current state.

    2. What does sp_query_store_force_plan let you do?

    A) Delete a query from history
    B) Pin a specific known-good plan for a query, without changing code
    C) Force a full index rebuild
    D) Disable Query Store

    Show Answer

    Answer: B

    This is a direct, reversible, production-safe response to parameter sniffing regressions.

    3. Why should a production Extended Events session use an aggressive WHERE duration filter?

    A) It’s not necessary
    B) To capture only what actually matters and keep overhead/disk usage low
    C) Filters are required by SQL Server syntax
    D) It has no effect on overhead

    Show Answer

    Answer: B

    Unfiltered capture on a busy server generates enormous volume — filtering aggressively is the difference between diagnostic and production-safe.

    4. What does EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS accomplish?

    A) Guarantees zero events are ever lost
    B) Prioritizes server performance over perfect event capture completeness
    C) Disables the session entirely
    D) Doubles the memory buffer

    Show Answer

    Answer: B

    This ensures monitoring itself never becomes a bottleneck — an intentional trade-off for production sessions.

    5. What does a low Page Life Expectancy counter typically indicate?

    A) Excellent buffer pool health
    B) Buffer pool pressure — pages being evicted and re-read from disk quickly
    C) A CPU bottleneck only
    D) A network issue

    Show Answer

    Answer: B

    This ties directly back to Module 1’s buffer pool concept — low PLE means data isn’t staying cached.

    6. A rising Compilations/sec relative to Batch Requests/sec often signals what?

    A) Excellent plan reuse
    B) Excessive recompiling, often from ad-hoc (non-parameterized) query bloat
    C) A hardware failure
    D) Normal, healthy behavior always

    Show Answer

    Answer: B

    This connects back to Module 1’s plan cache bloat discussion — too many unique ad-hoc statements compiling fresh plans.

    7. A rising Full Scans/sec trend alongside a stable workload most likely points to what?

    A) Everything is fine
    B) Missing or degraded indexes (fragmentation, stale stats)
    C) A network slowdown
    D) Increased RAM

    Show Answer

    Answer: B

    A trend change with stable workload is a strong signal something structural (Module 2 territory) has degraded.

    8. Which tool is best suited for answering “how has this specific query’s performance evolved over the past month”?

    A) sys.dm_os_waiting_tasks (live-only)
    B) Query Store
    C) PerfMon alone
    D) The plan cache alone

    Show Answer

    Answer: B

    Query Store’s whole design purpose is persisted, per-query historical tracking — exactly this question shape.

    9. Which tool is best for answering “what is currently blocking session 55, right now”?

    A) Query Store
    B) sys.dm_os_waiting_tasks
    C) PerfMon historical logs
    D) sys.dm_exec_query_stats

    Show Answer

    Answer: B

    This is a live, real-time question — the DMV showing current wait state is the right tool, not a historical aggregate.

    10. Why is combining multiple monitoring tools (not relying on just one) the recommended approach?

    A) It’s unnecessary, one tool does everything
    B) Each tool answers a different question shape — trend over time, current state, specific events, or per-query history
    C) More tools always means more accuracy regardless of fit
    D) This is not actually recommended

    Show Answer

    Answer: B

    PerfMon (trend), DMVs (current/historical state), XEvents (specific captured events), Query Store (per-query history) are complementary, not redundant.

    Enjoyed this?

    Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.