A Fine Slice Of SQL Server
I announce something I didn’t do:
I wanted to announce the first open source project officially released by ChannelAdvisor: the QDS Toolbox. This is an effort which Pablo Lozano and Efraim Sharon pushed hard internally and several database administrators and database engineers contributed to (though I wasn’t one of them).
Pablo, Efraim, &co have done a great job with this, so check out the repo.
Comments closedErin 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 closedI 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 usageBut 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 closedErin 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 closedMilos 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 closedGrant 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 closedJeff Iannucci takes us through queries which Query Store doesn’t capture:
But what if you wanted to use Query Store for a different reason in a migration? What if to see which stored procedures are actually being used, and which are just sitting there unused like that box of commercial toilet paper you panic purchased last month?
What if you set the capture mode to “All”, but then noticed you weren’t actually capturing All of the queries in your database?
Click through for the full report.
Comments closedQuery Store only allows you to force plans that the Query Store has “seen” on that instance, and in that database. If you have a query and you want to force a specific plan, you will need to first ensure that the query runs, at least once, under the right circumstances to create the desired plan, so that the Query Store can capture it. Sometimes that is easy, in which case this blog is not for you.
What if you know you need to force the execution plan, you know you’ll get that execution plan only when a lot of data is already in the database, and you need to ship your software with an empty database to your customers? How do you set it up to force the plan you need?
Click through for the answer, and I give Hugo bonus points for using Raleigh as the example.
Comments closedErin Stellato wants to know whether a DBA forced a plan or SQL Server did automatically:
If you use Automatic Plan Correction, and thus also Query Store, you may wonder how was a plan forced: manually or automatically with APC? The type of forced plan is tracked in sys.query_store_plan, and you can use this simple query to determine how a plan was forced:
Click through for a simple query, as well as a more complex form which gives you a bit more info.
Comments closedTracy Boggiano asked about Query Store and got some good responses:
Happy St. Patrick’s Day (are you wearing something green)! For March I asked everyone to blog something related to Query Store, here is a summary of in alphabetical order of the blog posts we received although Rob Farley gets credit for being first.
To be fair, Rob does live in the future.
Comments closed