Category: Query Store

Erin Stellato ties together a number of best practices around Query Store:

I’m a huge fan of Query Store, which regular readers may know, but there’s a need to write a bit more about Query Store best practices.  This isn’t a “you must use this feature” post, this is a “here is what you must know if you want to use this feature” post.

I have a lot of content about Query Store, but maybe what’s really important gets lost amongst everything else.  Glenn prompted me to write this, after I worked with two customers last week that ran into issues related to their configuration of Query Store.  Listed below are the things you must know before you enable Query Store.  If you already have Query Store enabled and running, I recommend reviewing the information to ensure you are following best practices.

Click through for the full set of practices and links to additional details.

Comments closed

Lonny Niederstadt has a new series on usability “soft spots” with Query Store. Part one looks at plan identifiers:

Yeah. That’s a lotta plans in the right-hand legend.  22 of them.  In a not very helpful order.  In fact… though I’ve tried to figure it out, I don’t know what type of order is used for that right-hand legend.  It’s not chronological.  It’s not based on duration which is the metric displayed by the graph. I dunno.

Part two looks at “morally equivalent plans”:

Let’s refresh the “Tracked Queries” activity.

Ohhhh.  I forced plan_id 2 (in the purple box below) but what showed up was plan_id 3220 (in the yellow box below).

Lonny promises more, so keep on the lookout.

Comments closed

Milos Radivojevic notes some changes in Query Store with SQL Server 2019 CTP 3.0:

In addition to default value, the minimum number of query executions in the AUTOquery_capture_mode for storing the query, its plan(s) and runtime statistics in SQL Server 2019 has been increased from 3 to 30. That means, Query Store does not store anything for first 29 query executions. It reserves query_ids, but it starts storing execution plan and runtime stats from 30^thexecution in a single day.

These look like reasonable changes to me.

Comments closed

Erin Stellato answers two questions relating to recompile hints and whether those queries show up in Query Store:

Last week in our IEPTO2 class I was asked about queries with OPTION (RECOMPILE) and Query Store. Specifically: Do queries that have the OPTION (RECOMPILE) hint go into Query Store, AND do queries in a stored procedure created with the RECOMPILE option go into Query Store? I knew the answer to the first question, and was pretty sure I know the answer to the second one, but I wanted to test to verify. Let’s take a look.

Erin gives you a tl;dr version but I’m going to ask you to read the whole thing anyhow.

Comments closed

Grant Fritchey takes us through an interesting scenario around Query Store:

This weekend I was in Stockholm in Sweden, talking Query Store and plan forcing with Steinar Anderson, when he mentioned the problems he had while forcing plans that had table variables in them.

Don’t panic. Of course you can force a plan with a table variable, most of the time. Steinar had a fairly focused problem. Before I go on to explain the issue, let me be really clear, Steinar figured out the issue all on his own. When he outlined the problem, I saw immediately what his conclusion was going to be. What’s spurring this blog post is that Steinar said he’d searched on the internet and no one had talked about the issue yet. So, let’s talk about it.

Read on for the problem as well as solution.

Comments closed