Press "Enter" to skip to content

Category: Query Store

Query Store and Cross-Database Queries

Matthew McGiffen does some research:

When I was writing the script shared in my last post Identify the (Top 20) most expensive queries across your SQL Server using Query Store a question crossed my mind:

Query Store is a configuration that is enabled per database, and the plans and stats for queries executed in that database are stored in the database itself. So what does query store do when a query spans more than one database?

Read on for the answer.

Leave a Comment

Identifying Expensive Queries with Query Store

Matthew McGiffen has a query for us:

Some time ago I wrote a query store version of the “Top 20 queries” query that will produce a ranked list of your most expensive queries – and I’ve ended up using this a lot.

The only downside of using the DMVs for Query Store is that they are per database whereas dm_exec_query_stats is a view across the whole instance. So I had to use a cursor and a temp table, populating the temp table for each database in turn.

Click through for the query.

Comments closed

Query Store Checks in dbatools

Jess Pomfret contributes to dbatools:

Once I was happy with my settings, I realised we were missing a ‘test’ command for dbatools. The suite of ‘test’ functions in dbatools (a lot that end up as checks in dbachecks btw!), give us an easy way to check our environment against best practices, or our desired settings.

Since dbatools is open-source I was able to write this function (Test-DbaDbQueryStore) and get it added into the module. It’s included as of version 1.0.131, so make sure you’re up to date.  Taking Erin’s suggestions and wrapping them in a little PowerShell, I can make it easier for myself and everyone else to make sure we’re following her guidelines.

Click through to see what those settings look like and how you can compare against current settings.

Comments closed

The Performance Overhead for Query Store

Erin Stellato updates a prior post:

I wrote the original Query Store performance overhead post just over two years ago, and just like the data in your database keeps changing, so does SQL Server.  However, the question, “What is the performance overhead of enabling Query Store?” is still the most frequent question I am asked. 

So why am I writing this post?  Because there have been many improvements specific to Query Store that have taken the feature to the point where it can support all workloads, including those that are ad-hoc.  This is a big deal.

Read on to learn more.

Comments closed

The Big Red Button for Query Store

Erin Stellato shows us the emergency off switch for Query Store:

Have you ever tried to turn off Query Store when there was an issue, and you thought the problem might be related to Query Store, and the ALTER DATABASE statement was blocked?  And then you couldn’t do anything but wait?  Me too.  Imagine my excitement when I discovered that the SQL Server team snuck a helpful back door into ALL versions for which Query Store is supported. 

Read on for more, including which SP / CU levels support it.

Comments closed

Using Query Store over the Plan Cache

Erik Darling has a dream:

I used to think the plan cache was so cool.

– You can find queries that aren’t good there
– Plans are full of details (and XML)
– Supporting DMVs give you extra insights about resource usage

But most of the time now, I’m totally frustrated with it.

It clears out a lot, plans aren’t there for some queries, and the plans that are there can be very misleading.

Can you really tell someone what their worst performing queries are when everything in there is from the last 15 minutes?

No.

Read on for what’s nice about Query Store, as well as a few fixes which need to be there before it’s really useful. I’ve used Query Store in big environments to good effect (though our DBAs had to rewrite the cleanup processes because they’re bad) and I’ve had to turn it off in medium-sized environments running 2016 because it was harming performance. It’s a great concept and reasonable implementation with a few too many sharp edges.

Comments closed

With Query Store, Alter instead of Drop and Create

Erin Stellato has a recommendation when running Query Store:

When I talk about Plan Forcing I always discuss how users should ALTER procedures when using Query Store, and not use DROP and CREATE. This is valid beyond Plan Forcing cases; it’s a best practice I recommend however you are using Query Store. Every query stored in Query Store has an object_id associated with it, which ties it back to its object (stored procedure, function, etc.). This is critical not just for plan forcing, but also when you want to look at historical performance for a query after a change to the object.

Read on for a demonstration of why this is important.

Comments closed

Understanding Conditions Under Which Query Store Captures Queries

Milos Radivojevic dives into testing Query Store:

From SQL Server 2019, you can use the fourth option – CUSTOM which allows you to define what does „relevant query“ mean. For that purpose, you can use three parameters. A query will be captured by Query Store if it is executed at least EXECUTION_COUNT times or if for its compilation SQL Server used at least TOTAL_COMPILE_CPU_TIME_MS or the entire query execution used at least TOTAL_EXECUTION_CPU_TIME_MS CPU. All three parameters are evaluated within a period defined by using the fourth parameter – STALE_CAPTURE_POLICY_THRESHOLD with a default value of 1 hour.

You cannot define exceptions, i.e. queries that you do not want to see captured by Query Store, such as system queries or queries related to Query Store itself, you can just filter them out when you write queries against Query Store’s catalog views.

As mentioned earlier, in this post, I will go in details for the ALL option. This is default option in SQL Server 2019, and the decision to change from ALL to AUTO was a good one. However, the lack of documentation for this option can lead to mistrust and misunderstandings. Defaults are important for the feature’s newcomers; they will use defaults until they learn and test enough to probably choose better options for their workload.

There’s a lot of depth in this post, and Milos wraps up with a few rules of thumb.

Comments closed

Finding Index Usage Stats in Query Store

Grant Fritchey gives us another option for determining whether an index is in use:

One of the most frequent questions you’ll hear online is how to determine if a particular index is in use. There is no perfect answer to this question. You can look at the sys.dm_db_index_usage_stats to get a pretty good picture of whether or not an index is in use. However, this DMV has a few holes through which you could be mislead.

I thought of another way to get an idea of how and where an index is being used. This is also a flawed solution, but, still, an interesting one.

What if we queried the information in Query Store?

Be sure to read Grant’s warning before jumping into this, but at least it gives us another option, as well as a better understanding of which queries are using particular indexes.

Comments closed