Press "Enter" to skip to content

Category: Query Store

Plan Forcing and Stored Procedure Alteration

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

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

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

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

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

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

Query Store Q&A

Erin Stellato had a lot of questions about Query Store, and the answers turned into a blog post:

5. If you have 3+ plans how does SQL Server decide which plan to use?
A: I assume this is specific to the Automatic Plan Correction feature, and if so, it will force the last good plan (most recent plan that performed better than the current plan).  More details in my Automatic Plan Correction in SQL Server post.
 
6. What equivalent options we have for lower versions?
A:  There is an open-source tool called Open Query Store for versions prior to SQL Server 2016.

Click through for all 19 of the questions.

Comments closed

Workload Analysis with Query Store

Erin Stellato shows how you can mine the Query Store tables to learn more about your workload:

The query text and plan are stored in two separate tables, sys.query_store_query_text and sys.query_store_plan, respectively.  The text is stored as a nvarchar(max) data type, and the plan is stored as varbinary(max).  This means that the data can be mined, looking for patterns and explicit use of objects.  Want to know what queries use an index?  Look for it in the plans.  Want to know what queries have a RECOMPILE hint on them?  Look for it in the query text.  Anything you want to find with regard to query text or plans is there, you just have to know how to query the data.

It’s hard to tune queries if you don’t know what’s running.

Comments closed