Press "Enter" to skip to content

Category: Query Store

Applying the Pareto Principle to Query Store

Published 2026-04-16 by Kevin Feasel

Erik Darling channels an Italian economist:

In this video, I dive into an old-school approach to identifying SQL Server performance issues using SP Quickie Store and a novel method inspired by the Pomodoro [“Pareto” – ed] technique. Traditionally, Query Store surfaces queries that consumed a lot of CPU over the last seven days, but often these results are too broad for practical use. To address this, I’ve developed a multi-dimensional scoring system that evaluates queries based on their impact across several key metrics: CPU usage, duration, physical reads, writes, and executions. This approach helps pinpoint the most problematic queries more accurately, even when they run outside of typical working hours or are unparameterized. By sharing these insights, I hope to provide a practical tool for SQL Server administrators looking to optimize their databases without relying solely on modern monitoring tools.

The AI generated summary reminds me that I’ve been working for 25 minutes, so time to take a break.

I like the idea of calculating and calculating & displaying impact scores, as well as breaking it down into core components.

Leave a Comment

NOWAIT Hints and Annoyances with Query Store Hints and Plan Guides

Published 2026-02-04 by Kevin Feasel

Erik Darling performs a rather late Airing of Grievances:

In this video, I delve into some of the frustrations and annoyances associated with query store hints and plan guides in SQL Server. I explore how these tools can sometimes hinder rather than help, particularly when trying to override certain behaviors or improve performance. For instance, I demonstrate the quirks of using a `NO_WAIT` hint in a transactional context and highlight why Query Store’s inability to support table hints is such a significant limitation. Additionally, I discuss the cumbersome nature of plan guides, especially their requirement for maintaining semantic affecting hints that might be detrimental to query performance. These issues underscore the need for more robust and flexible tools within SQL Server to better meet the diverse needs of database administrators and developers.

Click through for the video.

Comments closed

Tracing Trigger Executions in SQL Server

Published 2026-01-06 by Kevin Feasel

Kendra Little does some investigating:

Triggers can be tricky to observe in SQL Server. When you need to understand exactly what a trigger is doing and what it impacts, Query Store and Extended Events both have something to offer, but using them effectively requires navigating some confusing nuances. Query Store tracks query-level executions aggregated by query_id, but has a habit of generating a lot of query_ids with different context settings for triggers. Extended Events can capture trigger module starts and individual statement completions within triggers.

This post walks through setting up both observation methods on a test trigger, then compares what each one shows. You’ll see how Query Store data gets spread across multiple query_ids and context_settings_id values, a variety of ways to trace triggers in XEvents, and why the numbers don’t always match up exactly between traces and Query Store.

This is definitely worth the read. Kendra does a great job laying things out in a thoughtful manner and provides a lot of additional insight.

Comments closed

ABORT_QUERY_EXECUTION in SQL Server 2025

Published 2025-08-11 by Kevin Feasel

Joey D’Antoni pulls out the big guns:

However, SQL Server 2025 gives us a bigger hammer (DBAs love hammers). Building on top of the query store hints feature that was added in SQL Server 2022, ABORT_QUERY_EXECUTION simply blocks the exection of known problematic queries.

Read on to see how this works. Note that it will prevent the query from running at all, ever. As a result, if your main concern is “Hey, don’t run this during the busiest hours of the data for this database,” this particular hint is overkill.

Comments closed

Query Store Plan Forcing and RECOMPILE

Published 2025-07-18 by Kevin Feasel

Vlad Drumea tests Betteridge’s Law of Headlines:

It’s 3AM and I’m curious if Query Store plan forcing works on queries that use the RECOMPILE hint (aka OPTION(RECOMPILE) ).
So might as well make a blog post out of it.

Hmm, on the one hand, Vlad blatantly violates Betteridge’s Law of Headlines, which loses points. On the other hand, Vlad dunks hard on LLMs, gaining points. I’m so conflicted right now…

But not about reading Vlad’s post. Go read it.

1 Comment

Adaptive Joins and Equivalent Plans

Published 2025-07-15 by Kevin Feasel

Forrest McDaniel has a public service announcement:

In case you haven’t heard, moral decline is everywhere. The latest generation of query operators is a prime example of this rot. “But this is programming” you say, “what do morals have to do with anything?” No. This is SQL. And for us, we have the term morally equivalent plans which allows me to finally have an ethical excuse for sprinkling a post with degenerate puns.

I bit my tongue specifically to avoid doing exactly what Forrest does. So click through to read all of the terrible puns.

Comments closed

Useful Query Store Metrics

Published 2025-06-16 by Kevin Feasel

Jared Poche gives us five:

Query Store is my favorite way to gather information about problem queries and plans, and I wanted to share some information on the useful metrics I use most.

The first two are obvious, but there’s a difference between them. The last two are not obvious but offer an unusual utility. I also wanted to explain why I use logical reads and mostly ignore physical reads.

Read on for Jared’s list.

Comments closed

Fixing OPTIMIZATION_REPLAY_FAILED Errors in SQL Server

Published 2025-05-01 by Kevin Feasel

Kendra Little fixes a problem:

Forcing plans with Query Store can be a powerful tool—until it mysteriously fails. In real production systems, plan forcing sometimes just… doesn’t work. One common culprit is the cryptic OPTIMIZATION_REPLAY_FAILED error.

If you’re hitting OPTIMIZATION_REPLAY_FAILED, try re-forcing the plan using @disable_optimized_plan_forcing=1.

Click through for a summary of the problem and additional helpful information on the issue.

Comments closed