Category: Query Store

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

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

Erik Darling visits the used query lot looking for some low-mileage models:

This metric gets looked at a lot in the plan cache to see how effective it is. The main problem is that with high enough churn, you might not catch all the queries involved in the problem. Here are a couple ways to look at this in Query Store.

Click through for the queries.

Comments closed

Erin Stellato shows us how to do some spring cleaning:

Automatic removal of data from Query Store is based on configuration, notably the MAX_STORAGE_SIZE_MB, CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS), and SIZE_BASED_CLEANUP_MODE settings.  While these are detailed in another post, it’s worth reiterating that ideally, size-based cleanup never kicks in.  A database’s Query Store should be sized to accommodate N days’ worth of data, where N is set by CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS).  When the size of the Query Store exceeds 90% of MAX_STORAGE_SIZE_MB, and if SIZE_BASED_CLEANUP_MODE is set to AUTO, then clean up will kick in.  This algorithm is not efficient, and it runs single-threaded.  It looks for queries that are infrequently executed/less important and deletes those, one by one, until the size is less than 80% of MAX_STORAGE_SIZE_MB.  Avoid this type of cleanup if at all possible.

There is a better way. And an even better way thanks to some of my colleagues.

Comments closed