Press "Enter" to skip to content

Category: Query Store

Two Query Store Stories

Published 2020-03-10 by Kevin Feasel

Mark Wilkinson gives us two separate takes on Query Store:

When the Query Data Store (QDS) feature was announced for SQL Server 2016, we were excited about the prospect of being able to have deep insight on any query running in our environment. I work for a company that deals heavily in the e-commerce space, and we have a large SQL Server footprint. Our environment is unique in that is essentially a multi-tenant system, but all the tenants could have wildly different workloads. It’s really the kind of query execution scenario QDS was built for. We had the pleasure of working with the Microsoft SQLCAT team to get 2016 and QDS up and running in our production environment before it was GA.

In this post I’m going to share two stories about our QDS experience (from pre and post GA of the feature). One from the perspective of the Database Developer, and one from the Database Administrator. For the most part this is not a technical post full of queries and code samples. It’s just me talking about some things I have experienced using QDS in production.

CentralQDS, by the way, is really cool. Hopefully we’re able to show that to the world someday (and note how I say “we” even though I did absolutely nothing with it except for being in the same company as the people who developed it).

Comments closed

Azure Data Studio and Query Store

Published 2020-03-10 by Kevin Feasel

Rob Farley explains why Azure Data Studio can’t be a 100% thing right now:

But the big thing with this new laptop is that I’ve made a conscious decision about what to install on it. And particularly, what things to NOT install.

For example, I’ve only installed SQL inside docker, not directly on Windows. I’m running the Linux version just because I can.

But today I feel like I’ve compromised.

Today I’ve installed SSMS, instead of persisting with just Azure Data Studio. It only took a week for me to cave, and the reason is Query Store.

Read on to understand why. I ended up having to cave on Docker-only for SQL Server because of PolyBase components that I needed and which aren’t (yet?) supported in the Linux version.

Comments closed

Plan Forcing and Stored Procedure Alteration

Published 2020-03-06 by Kevin Feasel

Grant Fritchey takes us through a scenario where plan forcing with Query Store has some unexpected results:

I absolutely love Query Store and the ability it provides to force a plan is amazing. However, there are a lot of little gotchas in this functionality. I just recently found one that has quite a far reaching effect. Let’s talk about what happens when you DROP and then CREATE a stored procedure.

It’s one of those things where you read Grant’s blog post and say, “Yeah, of course that makes sense; how could it be otherwise?” And yet, without having read the blog post, the thought might never have occurred.

Comments closed

Removing Ad Hoc Plans from the Query Store

Published 2020-02-20 by Kevin Feasel

Jeff Iannucci has a script which removes ad hoc plans from the Query Store:

Now, rather than being my usual rambling self I want to be very direct here: this solution will NOT give you the same behavior as “optimize for ad hoc workloads.” That setting keeps query info without the plan during the first execution, but then keeps the plan after the second execution.

That’s kinda like a surgeon with a scalpel. What is below is much more drastic. We’re going to break out a chainsaw for Query Store.

Chainsaw solutions to scalpel problems? Now you have my interest.

Comments closed

Tracking Query Store Changes

Published 2020-02-05 by Kevin Feasel

Erin Stellato shows how to watch for Query Store changes whether due to settings modifications or running out of space:

The Query Store feature is a bit unique in that its status can change without user interference, which means it is important to understand how changes to Query Store are logged.  A couple weeks ago John Deardurff posted a question on Twitter asking specifically whether the SQL Server ERRORLOG is written to when the OPERATION_MODE changes to READ_ONLY because  MAX_STORAGE_SIZE_MB is exceeded.  I had never tested to confirm, but I know there is an event in Extended Events that will fire when the limit is reached.  I also know that when a user makes a change to a Query Store setting, it is logged in the ERRORLOG.

Click through to see how to watch for this and what the changes look like.

Comments closed

Database Compatibility Level and Query Store

Published 2020-01-20 by Kevin Feasel

Erin Stellato gives us a moment of zen:

A question I’ve gotten a few times when teaching relates to database compatibility level and Query Store. I was talking to a client yesterday about post-upgrade plans and implementing Query Store, and the topic came again. They wanted to know what compatibility level the database needed in order to use Query Store.

The quick answer: it doesn’t matter.

Read on for a demonstration.

Comments closed

What’s New with Query Store in SSMS 18.4

Published 2019-11-13 by Kevin Feasel

Erin Stellato takes us through changes to Query Store components in SQL Server Management Studio 18.4:

Last week the SQL Server Tools team released a new version of Management Studio, 18.4, and it dropped in the middle of my Query Store full day pre-con on Monday. Those two are related. There were some changes in SSMS 18.4 specific to Query Store, and for a few seconds over lunch I thought about downloading the new version and running it for my afternoon demos. But…I didn’t want to anger the demo Gods, so I stuck with 18.3.

Read on for info on the changes.

Comments closed

Plan Forcing and Availability Groups

Published 2019-11-01 by Kevin Feasel

Milos Radivojevic shows an example where plan forcing might not work:

In my previous post about Query Store, you saw that persistence of forced plans does not imply that they are used. This post brings another example where forced plans are still in the system, with no failures, but they are not applied against a desired query.

For this demo, we need a database which is a part of the availability group. 

Click through to see why failover might cause your forced queries no longer to work.

Comments closed