Query Store (QDS) Archives - Brent Ozar Unlimited®

Performance Tuning

How to Track Performance of Queries That Use RECOMPILE Hints

March 12, 2020

Say we have a stored procedure that has two queries in it - the second query uses a recompile hint, and you might recognize it from my parameter sniffing session:
[crayon-6a42713385bfe683299346/]
The first query will always get the same plan, but the second query will get different plans and return different numbers of rows depending on which reputation we pass in.

Performance Tuning

Query Store And Cruel Defaults

December 19, 2018

Gosh Darn
Now, look, it's no secret that I think Query Store is cool. Heck, I went and wrote sp_BlitzQueryStore so you can examine it similarly to how sp_BlitzCache looks at the plan cache.

For a variety of reasons, though, we run into people who can't or won't turn it on, or who had to turn it off.

Performance Tuning

Building SQL ConstantCare®: Why People Aren’t Using Query Store

June 11, 2018

When Microsoft first talked about Query Store, I was wildly excited. It's basically a black box flight data recorder for SQL Server, tracking which queries run over time, how much work they do, and how their execution plans change.

And then it came out, and...hardly anybody used it.

Performance Tuning

sp_BlitzQueryStore: A Gentle Introduction

December 7, 2017

Odds and ends
During the precon, we asked how many people were on 2016, how many people were aware of query store, how many people were using it, and how many people were using sp_BlitzQueryStore.

About 1/3 of the hands went up at first, then for each successive question, fewer and fewer hands stayed up.

Performance Tuning

Introducing sp_BlitzQueryStore

June 6, 2017

Oh, so you're the one While SQL Server 2016 adoption is still pretty low, and 2017 still quite isn't out yet, that's no reason to not invest a few days of dev time into a stored procedure that is only usable by the smallest portion of SQL Server users. Right? Look, someone around here has…

Performance Tuning

What Do You Want to See in a Free Query Store Tool?

May 19, 2017

I love Query Plans
No seriously, I love digging into query plans. Even writing XQuery to dig into them in sp_BlitzCache. I was both honored and horrified to take over development of it from Jeremiah.

It's a beast, but it's my kind of beast. At around 4500 lines of code, when you want to make a change, well, &^%$ happens sometimes.
It used to make sense

Performance Tuning

SQL Server 2017: query_store_wait_stats

April 22, 2017

The missing link
Back when the Query Store first got announced, I was amazed.

The amount of information it gathered and persisted between reboots was such a big improvement over the plan cache, plus being able to force plans was really cool.

Performance Tuning

Breaking News: 2016 Query Store cleanup doesn’t work on Standard or Express Editions

July 4, 2016

If you're using SQL Server 2016's awesome new feature, Query Store, there's a new bug with automatic cleanup. Books Online explains: Automatic data cleanup fails on editions other than Enterprise and Developer. Consequently, space used by the Query Store will grow over time until configured limit is reached, if data is not purged manually. If…

Performance Tuning

Breaking News: Query Store in All Editions of SQL Server 2016

April 21, 2016

Onstage at SQL Intersections in Orlando this morning, Bob Ward announced that Query Store will be available in all editions of SQL Server 2016.

This is awesome, because Query Store is a fantastic flight data recorder for your query execution plans. It'll help you troubleshoot parameter sniffing issues, connection settings issues, plan regressions, bad stats, and much more.

Performance Tuning

The case for Query Store in tempdb

November 3, 2015

Query Store is so cool
Billed as a flight data recorder for SQL Server, the Query Store is a repository of execution plan information, like the plan cache, except a bit more actionable. And it has a GUI.

You can read all about what it does and what you can do with it around the internet. You can be suitably impressed and enchanted by the promise of data that's persisted between restarts, being able to quickly and easily address plan regression issues, and so forth.

Performance Tuning

Enabling Query Store in Azure SQL Database

September 2, 2015

Enter Query Store Query Store, in short, is a way to track query performance over time. In Microsoft's words, "The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review". It's like sys.dm_exec_query_stats but it persists across reboots! And it has execution plans! The Query Store is a…

Performance Tuning

SQL Server Query Store

November 4, 2014

I spend most of my day tuning SQL Server to make it go faster. I'm usually called in after the fact, when the app has become intolerably slow.

One of the first things I ask is, "What's changed?"