A Fine Slice Of SQL Server
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 closedErin 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), andSIZE_BASED_CLEANUP_MODEsettings. 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 byCLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS). When the size of the Query Store exceeds 90% ofMAX_STORAGE_SIZE_MB, and ifSIZE_BASED_CLEANUP_MODEis set toAUTO, 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% ofMAX_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 closedJared 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.
Comments closedGrant 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.
Comments closedErin 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 closedErik 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 closedGrant Fritchey looks at Query Store on Azure SQL Database:
Under the covers, Azure SQL Database is just good old fashioned SQL Server and this includes Query Store on Azure. While many things can be different when working with Azure, Query Store just isn’t one of them. Let’s talk about it a bit.
Read on for the full report.
Comments closedErik Darling adds some commas:
Sometimes it’s hard to get people to understand the scale of a number without some commas.
I don’t know why that is, but that’s why I added the option to format numbers.
You can do that by running the procedure like so:
If only the client tools worked that easily.
Comments closedErik Darling continues a series on sp_QuickieStore:
What good is writing all this dynamic SQL and whatnot if you’re just going to firehose data at people?
None. None at all.
You’re going to want to answer questions about what happened and when, and you’re not going to want to struggle with it.
So what can you do?
Read on to learn what you can do.
Comments closedErik 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