A Fine Slice Of SQL Server
Jared Poche looks at a procedure to track query variation over time:
Since Query Store stores statistics long term, we can use it to look for trends or major changes in performance. The Query Variation component of the QDS Toolbox helps us review changes and has a lot of options to allow us to select the kinds of changes we are interested in.
So, let’s review our options and go through some examples.
Click through for some examples of how to use the query variation procedure.
Comments closedDeepthi Goguri provides an intro to Query Store:
Introduced in SQL Server 2016, the Query Store feature will store the Query plans, Queries, resource consumption information along with the run time stats information for your queries. This feature is not automatically enabled on your databases. It is a database-level option only. You need to enable the Query Store on the database to capture the queries and query information. You cannot enable the Query store on master or tempdb databases. The information captured by the Query Store is stored in the database you enabled the feature on, in the internal tables created in the PRIMARY filegroup and this cannot be changed. (Microsoft docs reference).
One thing I would mention that Deepthi didn’t cover is, if you tried out Query Store a while ago and ran into problems, try again on the latest CUs of SQL Server. There have been a lot of bugfixes and optimizations which have made it a much more useful product.
3 CommentsJared Poche cranes his head 90 degrees:
In contrast to the previous QDS Toolbox components, this one is relatively simple. Instead of a tool that’s ready to be scheduled to give you a report, this just adds the dbo.query_store_wait_stats_pivoted view.
So, what’s the value here?
Click through for the value.
Comments closedAndrea Allred riffs on automatic tuning (but, uh, not that AutoTune):
A few weeks ago, we were talking to a new employee about how much time we spend with Query Store and they asked, “Why aren’t you using the Auto Tuning?”
THE WHAT NOW?!!!!
Click through for Andrea’s thoughts on how automatic tuning works in SQL Server Enterprise Edition.
Comments closedMala Mahadevan has a few short-ish Query Store scripts for us:
I use Query Store a lot where I work – and I’d like to share queries I use on Query Store DMVs that I find incredibly useful.
My favorite is one below, which I use to see stored procedure duration. It comes with other information including plan id, start and end time – all of us help me see red flags right away if there is any query not performing as well as it should.
Click through for that script as well as two more.
Comments closedJared Poche continues a series on the QDS Toolbox:
When configuring Query Store, we have a few options for deciding how it retains data but little control over how it cleans up that data. We can set the max size of our query store, the max number of plans to keep per query, and how long to keep query statistics.
The QDS Cache Cleanup component of the QDS Toolbox gives us a number of other options for what data to remove:
Read on for that list and a good example of where it was useful.
Comments closedJared Poche continues a series on QDS Toolbox:
Carrying on from my previous post on the QDS Toolbox, let’s review the Query Waits component.
This component provides details about the wait types associated with a given object, query, or plan. This doesn’t pull data from the reports generated by the Server Top Queries component; this gets the data directly from the Query Store in the database you specify. So you can use this to review the waits on a given procedure, even if you haven’t generated any reports with Server Top Queries .
That being said, reports from Server Top Queries could identify problem queries and give you the PlanID, QueryID, or ObjectName you need to run the procedure. A query that spends a lot of time waiting would tend to have a higher duration and lower active metrics like CPU usage or logical reads. So those are the queries I would want to run the Query Waits report against.
Read on to see how to call this procedure and what you might expect.
Comments closedErik 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 closed