Since our preview announcement, hundreds of customers have been enabling Query Store to provide insight on query performance. We’re excited to share the general availability of Query Store worldwide for Azure SQL Data Warehouse.
Query Store automatically captures a history of queries, plans, and runtime statistics and retains them for your review when monitoring your data warehouse. Query Store separates data by time windows so you can see database usage patterns and understand when plan changes happen.
Given its power in the on-prem product, I’m glad that Azure SQL Data Warehouse is getting Query Store as well.
The latest CUs for SQL Server 2016 and 2017 contain some important Query Store fixes that I thought worth mentioning for those of you on either version or those of you looking to upgrade. As of this writing, the current CU for SQL Server 2016 SP2 is CU5, and for SQL Server 2017 it is CU13. Many times we see fixes that make it into a SQL Server 2017 CU ported back to a SQL Server 2016 build. Interestingly enough, there are some Query Store fixes in 2016 CUs that are not in 2017 CUs. I don’t know if that’s because the issues do not exist in 2017, or if it’s just that they have been fixed yet in 2017. I’m planning to update this post if the fixes are added down the read. So here we go, in descending CU order…
This post is a great reason to keep those SQL Server instances up to date.
Last week there was a question on #sqlhelp on Twitter about the status of Query Store for a database in an Availability Group. I’ve written about Query Store and Availability Groups before so if you’re not familiar with QS behavior in an AG, check out that post first. But this question was, I think, specific to the values that shows on a read-only replica and how there were different query store settings between a primary and secondary. Let’s set it up and take a look.
Click through to learn why this may be and why you shouldn’t panic.
Query Store retains query performance data at the plan level. This data is then broken out into intervals of time, determined by the INTERVAL_LENGTH_MINUTES setting. The time intervals are found in the sys.query_store_runtime_stats_interval system view, and the start_time and end_time columns are of the DATETIMEOFFSET data type. This means that the date is time-zone aware, and in Query Store the data is stored as UTC. Now why does all of this matter? Because handling dates in Query Store is important if you’re going to query the data directly.
Click through to see a query of what this looks like, as well as a few tips on parsing the data.
But the MAX_STORAGE_SIZE_MB is not a hard limit
I’ve seen this occur in production systems with heavy ad-hoc workloads. As the Query Store gathers data, there seems to be a point where the writes are queued up. When they are written to disk, the max size can be exceeded.
Anecdotally, I’ve seen a production systems where the max size has been vastly exceeded. But let me show you an example on a small scale on my demo machine.
Click through for a demonstration on the small. I’d much rather go over my self-imposed limit than lose useful data, so I’m okay with that decision. If you’re extremely tight on disk space, however, it’s good to know this detail.
When you look at articles, posts and documents about new features and enhancements in SQL Server 2019 CTP2, you will find nothing about Query Store. However, there are some graphical enhancements in SQL Server Management Studio in the version 18.0, also default configuration for Query Store attributes is changed too.
First SSMS 18.0. From this version, you can see another Query Store report – Query Wait Statistics. When you click on it, you can see aggregate waits per category in a given time interval (default is last hour).
It looks like there have been some incremental improvements to Query Store. I think the defaults also make a bit more sense.
Figuring out exactly what causes slow performance for a stored procedure can sometimes feel like trying to unravel a ball of Clark Griswold’s Christmas lights. It’s not uncommon to see procedures with hundreds, even thousands of lines of code. You may have been told which stored procedure runs slow by a user or manager, or you might have found it by looking in SQL Server DMVs. Either way, once you have detected the offending procedure, where do you start?
If you’re running SQL Server 2016, one option is Query Store. Query Store captures individual queries, but it also captures the object_id, so you can find all the queries that are associated with an object to determine which ones are problematic.
This is quite useful when you have to tune a procedure you’ve never seen before, and as you go to open that procedure, the vertical scroll bar keeps getting smaller and smaller.
If I look at the plan that is stored in Query Store, I’ll see the identical plan up above, including the PlanGuideDB and PlanGuideName properties.
So, let’s force the plan using the values returned from the query above:
1 EXEC sys.sp_query_store_force_plan 6,7;
Now, when we run the query, we’ll see both the plan guide in use and that the plan is forced (see this earlier blog post explaining this behavior). This is all expected behavior.
Check it out to see how SQL Server behaves.
We were enjoying a nice peaceful afternoon when we hear panicked shouting that a SQL Server had become unresponsive and the customers were unable to do anything.
We moseyed on down to the server in question to take a look at it. One thing stood out immediately, CPU was pegged out at 100% but SQL itself didn’t actually seem to be doing anything, transactions\second was on the floor. Unfortunately this happened a while back and I didn’t think to capture any graphs or metrics at the time so you’re just going to have to take my word for this.
The issue David ran into was subsequently fixed, making this a cautionary tale to keep those SQL Server instances patched.
As you can see, while the structure of the plans are identical, not everything is. The Compile values are different (although sometimes, they’ll be the same, that one is kind of luck of the draw to a degree) because they were compiled at different times with varying load on the system, so certainly that will be reflected. However, the other differences are also interesting. Which of the plans was retrieved from cache for example and, more importantly, the statement for the plans. The one on the left is the plan from the Query Store. It was not retrieved from cache and, the statement is for the query, not the stored procedure. Meanwhile, the plan on the right is from cache and, it’s based on the plan handle from the stored procedure, so it reflects that in the Statement value.
Click through for the full set of differences as well as Grant’s explanation.