Press "Enter" to skip to content

Category: Query Store

Using Query Hints in Query Store

Published 2022-01-20 by Kevin Feasel

Deepthi Goguri takes us through hint usage in Query Store:

Query Store hints is another amazing feature added and is available in the preview mode in Azure SQL Database, managed instances, elastic pools and hyperscale databases as well. Query Store hints are just like the hints you use in your queries but the difference is you do not have to change the query code to apply these hints using Query store specifically designed stored procedure- sys.sp_query_store_set_hints. Cool, right?

Yes. Yes it is.

Comments closed

Automatic Plan Correction in Query Store

Published 2022-01-06 by Kevin Feasel

Deepthi Goguri hits on the type of benefit Query Store can provide:

How wonderful will that be if SQL Server has a way of automatically tune our Queries based on our workloads, amazing! Right?

Thanks to Microsoft for introducing the automatic tuning feature in SQL Server 2017 and available in Azure SQL Database. Automatic tuning has two features. Automatic plan correction and Automatic index correction (Source: Microsoft)

So, what is this automatic option, and how it works?

Click through to learn more. My experience with it has been very positive. It’s not perfect, but it does work really well.

Comments closed

When Query Store Plan Forcing Fails

Published 2021-12-28 by Kevin Feasel

Deepthi Goguri explains why that forced query plan isn’t behaving the way you might expect:

One of the advantages of using Query store is the ability to force a plan. Once you force a plan, there may be reasons when the optimizer still chose to create a new execution plan without using the forced plan. During this time, a failure reason is stored in the Query Store. When the forced plan cannot be used, the optimizer goes through the regular compilation and optimization phases to create a new plan but it doesn’t actually fail the query itself. It executes in a normal way with the new plan being created.

But what causes these failures and how to know the reason behind these forced plan failures?

Read on to find out.

Comments closed

Query Variation in QDS Toolbox

Published 2021-12-15 by Kevin Feasel

Jared Poche looks at a procedure to track query variation over time:

Since Query Store stores statistics long term, we can use it to look for trends or major changes in performance. The Query Variation component of the QDS Toolbox helps us review changes and has a lot of options to allow us to select the kinds of changes we are interested in.

So, let’s review our options and go through some examples.

Click through for some examples of how to use the query variation procedure.

Comments closed

Fundamentals of Query Store

Published 2021-11-16 by Kevin Feasel

Deepthi Goguri provides an intro to Query Store:

Introduced in SQL Server 2016, the Query Store feature will store the Query plans, Queries, resource consumption information along with the run time stats information for your queries. This feature is not automatically enabled on your databases. It is a database-level option only. You need to enable the Query Store on the database to capture the queries and query information. You cannot enable the Query store on master or tempdb databases. The information captured by the Query Store is stored in the database you enabled the feature on, in the internal tables created in the PRIMARY filegroup and this cannot be changed. (Microsoft docs reference).

One thing I would mention that Deepthi didn’t cover is, if you tried out Query Store a while ago and ran into problems, try again on the latest CUs of SQL Server. There have been a lot of bugfixes and optimizations which have made it a much more useful product.

3 Comments

Short Query Store Queries

Published 2021-10-13 by Kevin Feasel

Mala Mahadevan has a few short-ish Query Store scripts for us:

I use Query Store a lot where I work – and I’d like to share queries I use on Query Store DMVs that I find incredibly useful.

My favorite is one below, which I use to see stored procedure duration. It comes with other information including plan id, start and end time – all of us help me see red flags right away if there is any query not performing as well as it should.

Click through for that script as well as two more.

Comments closed

QDS Cache Cleanup in QDS Toolbox

Published 2021-09-21 by Kevin Feasel

Jared Poche continues a series on the QDS Toolbox:

When configuring Query Store, we have a few options for deciding how it retains data but little control over how it cleans up that data. We can set the max size of our query store, the max number of plans to keep per query, and how long to keep query statistics.

The QDS Cache Cleanup component of the QDS Toolbox gives us a number of other options for what data to remove:

Read on for that list and a good example of where it was useful.

Comments closed

Query Waits in the QDS Toolbox

Published 2021-08-24 by Kevin Feasel

Jared Poche continues a series on QDS Toolbox:

Carrying on from my previous post on the QDS Toolbox, let’s review the Query Waits component.

This component provides details about the wait types associated with a given object, query, or plan. This doesn’t pull data from the reports generated by the Server Top Queries component; this gets the data directly from the Query Store in the database you specify. So you can use this to review the waits on a given procedure, even if you haven’t generated any reports with Server Top Queries .

That being said, reports from Server Top Queries could identify problem queries and give you the PlanID, QueryID, or ObjectName you need to run the procedure. A query that spends a lot of time waiting would tend to have a higher duration and lower active metrics like CPU usage or logical reads. So those are the queries I would want to run the Query Waits report against.

Read on to see how to call this procedure and what you might expect.

Comments closed