Press "Enter" to skip to content

Category: Query Store

Having Fun with the QDS Toolbox

Jared Poche shares a few queries with us:

The QDS Toolbox is set of tools that can help you review and store the performance related data in Query Store. This was released by ChannelAdvisor last September thanks to the hard work of a number of my coworkers.

If you aren’t experienced with Query Store, this can provide a good starting point for getting familiar with data that is available and what you can do with it. If you are experienced with Query Store, this may give you an easy way to set up customizable reports that help you find issues and see trends.

The QDS Toolbox has several components, and I intend to post about each in turn. Two new components were added to this recently by @sqlozano (https://www.sqlozano.com/), bringing the current total to eight.

Click through for a deeper dive into the Server Top Queries report.

Leave a Comment

Required Permissions for Forcing Query Store Plans

Grant Fritchey reviews minimum requirements:

I was recently asked what permissions were needed to force plans in query store. I’m sure I knew at one point, but at the moment I was asked, I couldn’t remember to save my life. So, I went and looked it up. In the interest of sharing, especially for the poor person who I left hanging, here’s what I found.

Click through for the disappointing answer.

Leave a Comment

Query Store Hints

Erin Stellato takes a look at a new Microsoft announcement:

Query hints can be really helpful in some scenarios, but if you do not have direct access to the code (hello third party applications), then you cannot add a query hint to the statement.  Historically, you’ve been able to use a plan guide to apply a hint, and that doesn’t require direct code access, but plan guides are not always intuitive and I find that most people are not familiar with how to use them.

Enter Query Store Hints.  With this feature, you can now add a hint to a specific statement, even if you don’t have access to the code base.  

Read on to see what it does and how you can use it.

Comments closed

Wait Stats Not in Query Store

Erik Darling says wait, wait, don’t tell me:

There are some oddities in the documentation for query store wait stats.

One is that RESOURCE_SEMAPHORE_QUERY_COMPILE is listed as a collected wait, but with an asterisk that says it’s not actually collected. I’ve tested workloads that generate lots of that wait, and just like the docs say, it doesn’t end up there.

Of course, since I added wait stats recently to sp_QuickieStore, I wanted to make sure other waits that I care about actually show up in there.

Read on to see which wait stats you can find in Query Store and which you’ll have to get from someplace else.

Comments closed

sp_QuickieStore in Action

Erik Darling has a new stored procedure. First up, an introduction:

If you’ve ever tried to write a query to find stuff in Query Store and quickly gotten frustrated, you’re not alone.

Whomever designed that schema hates you. It’s probably the same person who designed Extended Events.

I know, there’s a GUI. The stuff you can find in there is definitely useful, but I often find myself wishing it did a little bit more. Asking for additional features often feels like porridge-begging, and even if something new did get added eventually, it wouldn’t help you in the moment.

With all that in mind, I wrote sp_QuickieStore to try to take some of that pain away. As of this post, it’s V1. There will likely be changes and fixes and all that good stuff, but right now it’s good enough for an introduction.

Erik then shows off the results:

Under the more concise mode, you get one set of results back that combines metrics from query_store_runtime_stats, along with query text, query plan, and context settings. Some previews below. There’s a lot more in the actual results, but the screen caps would get very repetitive.

This looks really interesting, so go check it out.

Comments closed

Finding Query Performance Issues with Query Store

Andrea Allred has a primer on Query Store:

So if I can’t modify or add indexes and I can’t change code, how do I get my query times to drop? Query Store to the rescue.

I love to tune queries. I feel so satisfied to see the times dropping on my server as I tune things. Recently, I have been tracking my Batch Requests per Second and my Instance Waits to see if I am making improvements when I tune. It has been awesome!

What I am going to show you today is how I dig into my query store to find those misbehaving queries and make their performance better.

Click through for some high-level tips on how to use Query Store.

Comments closed

Using Query Store to Track Regressions after Upgrades

Grant Fritchey has another use for Query Store:

There are a lot of uses for Query Store, but one of the most interesting is as an upgrade tool. We all know that upgrades in SQL Server can be more than a little bit nerve wracking. No matter how much you tested stuff in lower environments, deploying an update to production might result in performance issues as your code hits a regression. This is even more true when upgrading from versions of SQL Server prior to 2014 to anything 2014 and above. That’s because of the new cardinality estimation engine introduced in 2014. Most queries won’t notice it. Some queries will benefit from the better estimates. A few, problematic, queries will suffer. This is where Query Store can be used as an upgrade tool.

Read on to learn how.

Comments closed