A Fine Slice Of SQL Server
Kendra Little shows that you can block Query Store cleanup:
This is an isolated test system, so I went to clean out Query Store as a reset. I didn’t need any of the old information in there, so I ran:
- ALTER DATABASE BabbyNames SET QUERY_STORE CLEAR ALL;
- GO
I was surprised when I didn’t see this complete very quickly, as it normally does.
Click through to see how Kendra diagnoses the issue.
Comments closedGrant Fritchey discusses Query Store’s automated cleanup and also looks at an interesting question:
Query Store has mechanisms for automatically cleaning your data. It is possible to cause them to break down. While presenting a session about the Query Store recently, I was asked what happened if you set the size of the Query Store below the amount of data currently in the store. I didn’t know the answer, so we tried it. Things got a little weird.
Click through to see how weird.
Comments closedAndy Mallon ran into a weird issue with Query Store:
I tried the configuration a couple of times just to make sure it wasn’t a one-off problem. I installed the latest Cumulative Update (CU). I made sure nothing else was connected to the instance. I rebooted my machine. I restarted services. I banged my head against the wall. I asked a friend if I was insane or stupid. After confirming that I was both, my friend Aaron Bertrand (blog|twitter) confirmed it wasn’t a problem for him.
I discovered I could reproduce the problem simply by running the same simple statement that SSRS used when creating the ReportServer database. SSRS uses a non-standard collation, and specifying that collation seems to be the difference in causing the deadlock. Then I discovered that specifying ANY non-standard collation was causing the deadlock. This had nothing to do with SSRS, and everything to do with non-default collations.
Vote for his User Voice item too.
Comments closedErin Stellato has a compendium of Query Store UserVoice requests:
In early January Microsoft announced that Connect, the method for filing SQL Server bugs and feature requests, was being retired. It was replaced by User Voice, and any bugs/requests were ported over. Sadly, the votes from Connect did not come across to User Voice, so I went through and found all the Query Store requests, which are listed below. If you could please take the time to up-vote them, that would be fantastic. If you could also take time to write about why this would help your business, help you upgrade, or purchase more SQL Server licenses, that is even better. It helps the product team immensely to understand how this feature/fix/functionality helps you and your company, so taking 5 minutes to write about that is important.
Check them out and upvote any which look interesting.
Comments closedArthur Daniels shows what you can learn from the indexes on Query Store tables:
It looks like internally Query Store is referred to as plan_persist. That makes sense, thinking about how the Query Store persists query plans to your database’s storage. Let’s take a look at those catalog views vs their clustered and nonclustered indexes. I’ve modified the query a bit at this point, to group together the key columns.
This lets you see how the Query Store authors expected us to use these tables. Which isn’t always how people use them…
Comments closedKendra Little follows Betteridge’s Law:
Can I Force Multiple Plans for a Query in Query Store?
Nope.
At least, not right now.
I started thinking about this when I noticed that the sys.sp_query_store_unforce_plan requires you to specify both a @query_id and a @plan_id.
If there’s only ever one plan that can be forced for a query, why would I need to specify the @plan_id?
I’ve got no insider knowledge on this, I just started thinking about it.
Read on for Kendra’s thoughts. Maybe we will get something like multiple plans for a single query in the future, though figuring out which forced plan would relate to which combination of parameters would get complex pretty fast.
Comments closedThere is a Query Store fix in the latest Cumulative Updates of SQL Server 2017, and if you are keeping current with the latest cumulative updates, there’s a script you need to run if you’ve installed CU2 at any point.
First, if you are still on CU2, you might want to consider upgrading to CU4 (released February 17, 2018).
Second, if you previously had CU2 installed (and are now on CU3 or CU4), you want to make sure that you’ve run the script included with CU3 and CU4 release notes.
The script removes plans from Query Store that were captured when running CU2.
Read the whole thing and keep those servers patched.
Comments closedYou can see that there are two rows for the same query_id and plan_id, but the count_executions is different, as are the avg_duration and avg_logical_io_reads values . The data is not truly a duplicate. This behavior occurs because the Query Store data is stored in memory before it is flushed to disk, and when you query the data SQL Server is pulling it from both locations (and doing a UNION ALL) and displaying it in the output. If I waited a bit and ran the query again, the two rows for that interval would probably disappear – most likely because the in memory data had been flushed to disk.
Read the whole thing.
Comments closedErin Stellato gives us a starting point for good values for Query Store settings:
QUERY_CAPTURE_MODE
The default value for SQL Server 2016 and SQL Server 2017 is ALL. For Azure SQL Database, the default value is AUTO.
With AUTO, queries that are insignificant from a resource utilization perspective, or executed infrequently, are not captured. If you need to capture queries that may only execute a few times, or those that use very few resources, then use ALL. Otherwise, use AUTO, as this will capture the relevant majority of your workload.
There is a third option, NONE, where no new queries are captured. Runtime and wait statistics will continue to be captured for queries that are already in Query Store.
I recommend setting this option to AUTO, as the number of queries in your environment that need tuning/your attention is a small percentage of the total number of queries that execute. You won’t miss out on important data if you exclude queries that don’t use a lot of resources or don’t execute very often.
Click through for good defaults for all nine settings.
Comments closedErin Stellato shows how to retrieve wait stats per query from Query Store:
In SQL Server 2016 a new DMV is exposed, sys.dm_exec_session_wait_stats, which provides information about waits for an existing, active session. If you know the session_id, you can track waits for a query when it starts and when it completes (snapshot the information at the beginning and end of the query and then diff the information). The challenge is that you have to know the session_id for the query, and you have to set up data capture in advance – which isn’t trivial when you’re in the midst of a high priority issue.
Wait statistics information exists in an actual execution plan starting in SQL Server 2016 SP1. Only the top 10 waits are captured, and there are limitations in terms of what this data represents. For example, CXPACKET is ignored and not included in the output, but it will be included in 2016 SP2 and 2017 CU3 and up – where irrelevant parallelism waits are instead captured by CXCONSUMER (which will not be included in actual plan waits).
So how can we see what a specific query is truly waiting on? We can use Query Store! SQL Server 2017 includes the capture of wait statistics information in Query Store, and this functionality is also available in Azure SQL Database. Wait statistics are tied to a query plan, and are captured over time, just like the runtime statistics. The addition of wait statistics information in Query Store was the number one feature request after its initial release, and all that information together creates powerful troubleshooting capabilities.
Read on for a demo-filled post. It’s hard to overstate just how useful this information is for query tuning.
Comments closed