Query Store (QDS)

How to Track Performance of Queries That Use RECOMPILE Hints

Last Updated May 26, 2020

Parameter Sniffing, Query Store (QDS), sp_BlitzCache

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: Transact-SQL CREATE OR ALTER PROC dbo.usp_SearchUsers @Reputation INT AS BEGIN /* Query 1, always the same: */ SELECT COUNT(*) FROM dbo.Users; /* Query 2, recompiles…

Query Store And Cruel Defaults

Last Updated December 19, 2018

Erik Darling

Execution Plans, Query Store (QDS)

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…

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

Last Updated June 11, 2018

Brent Ozar

Query Store (QDS), SQL ConstantCare

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. We thought it was a good idea,…

sp_BlitzQueryStore: A Gentle Introduction

Last Updated May 26, 2020

Erik Darling

Execution Plans, First Responder Kit, Parameter Sniffing, Query Store (QDS), sp_BlitzQueryStore, SQL Server 2016, SQL Server 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.…

Introducing sp_BlitzQueryStore

Last Updated November 16, 2017

Erik Darling

Execution Plans, First Responder Kit, Query Store (QDS), sp_BlitzQueryStore

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…

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

Last Updated May 19, 2017

Erik Darling

Execution Plans, Monitoring, Query Store (QDS), SQL Server

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…

SQL Server 2017: query_store_wait_stats

Last Updated May 12, 2017

Erik Darling

Query Store (QDS), SQL Server 2017, Wait Stats

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. I was always surprised that wait stats weren’t a part of that, especially…

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

Last Updated September 21, 2017

Brent Ozar

Breaking News, First Responder Kit, Query Store (QDS), SQL Server

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…

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

Last Updated February 13, 2017

Brent Ozar

Execution Plans, Query Store (QDS), SQL Server

Bob Ward talking Query Store at SQL Intersection 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…

The case for Query Store in tempdb

Last Updated December 4, 2020

Erik Darling

Execution Plans, Query Store (QDS), TempDB

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. “Try looking into that place where you dare not look! You’ll find me there, staring out at you!”…

Enabling Query Store in Azure SQL Database

Last Updated February 13, 2017

Jeremiah Peschka

Execution Plans, Microsoft Azure, Query Store (QDS)

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…

SQL Server Query Store

Last Updated December 19, 2017

Brent Ozar

#SQLPass, Execution Plans, Query Store (QDS), SQL Server

Execution Plan Hell 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?” Nobody ever knows with any sense of accuracy. I understand – until now, SQL Server hasn’t…