Search Results for: query store

Query Store and Plan Cache Plans Compared

Query Store plans and the plans in cache are identical, right? There won't be differences because the plan that is in cache is the plan that was used to execute the query. Similarly, the plan that is in the Query Store is the plan that was used to execute the query as well. Therefore, they will be the same. However, some small differences actually can show up. Differences Between Plans In order to compare the two plans, first, we need a query. Here's a stored procedure that I'm going to use to generate a plan that will be in cache and in the query store: [crayon-5c44c89998238527740100/] Nothing to it really. What I'm going to do is execute the query. That will load it into the cache and into query store.…
Read More

Query Store and Log Backups

A question that came up recently around Query Store is what happens when there are log backups in use on the database. Let's talk about it. Query Store and Log Backups The core of the answer is very simple. Query Store, like any other data written to a database, whether a system table or a user table, is a logged operation. So, when you backup the database, you're backing up Query Store data. When you backup the logs, you're also backing up Query Store data. A point in time will include all the data written to the Query Store at that point. However, that's the kicker. At what point was the Query Store information written to disk? By default, there's a fifteen minute cycle before the Query Store moves the…
Read More

Estimated Plans and Forced Plans from Query Store

While all plans are estimated plans, there is still a difference between capturing an estimated plan and looking at a plan from the cache or from query store. Or is there? A question came up during a recent presentation; what happens to capturing an estimated plan when you're forcing plans? Let's find out. The answer is interesting. Estimated Plans Here's my stored procedure that I'll be using with AdventureWorks2017: [crayon-5c44c8999bdc8774502771/] For reasons I'll explain in a bit, I'm going to free the procedure cache: [crayon-5c44c8999bdd9373687296/] Then, if I capture an estimated plan for two different values: [crayon-5c44c8999bddf570095196/] I end up with two different execution plans: Click to embiggen This is because the different values have different data distribution within my statistics and parameter sniffing leads to difference in the plans.…
Read More

Query Store and Automated Cleanup

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. Bryan Hundley of Marathon Consulting asked the question, so Bryan, this blog post is for you. Automated Cleanup There are actually two kinds of automated cleanup inside the Query Store. First, you have a time-based cleanup. By default it keeps queries that have been accessed within the last 30 days. Anything older, it tosses. If you have the stale_query_threshold_days (all in sys.database_query_store_options) set to 0, it…
Read More

Query Store Wait Statistics with sys.query_store_wait_stats

The second best thing to questions that people ask is when I sit down to write a book. It's so easy to miss things in the day-to-day grind of doing work. Then, late at night, you're working on a chapter, so you read up on the documentation to ensure that you're not missing anything. Of course, then you find, yes, you are missing something. In my case, sys.query_store_wait_stats. sys.query_store_wait_stats. If you follow the link above, it'll give you what you need to know, but, I figured I'd provide a little more clarity because I think there are some pitfalls in using this data. I love Query Store (do a search to see all the exploration I've done with it). One of my favorite things is the time intervals. It breaks…
Read More

Query Hash Values, Plan Guides and the Query Store

I was eating dinner with Hugo Kornelis and we started talking about query hash values. You know, like everyone does at dinner. As we talked about it, I suddenly thought about both Plan Guides and the Query Store. I wondered what happened to the query hash values in that case? Thus are blog posts born. Query Hash and Plan Guides The behavior of the query hash itself is fairly straight forward. The text of the query is run through a hashing algorithm within SQL Server and a value comes out, so these two queries: [crayon-5c44c899a4406732414197/] Result in two different query hash values: Unhinted Query Hash 0x7264738ED060F3C1 Hinted Query Hash 0xD763CBB6B860AFF3 Things get interesting if we create a Plan Guide for the first query to make it behave like the second…
Read More

Does Query Store Pre-Allocate Space

I love the questions I get while I'm presenting because they force me to think and learn. The question in the title is one I received recently. The answer, now that I'm not standing in front of people, is easy. Of course the space is not pre-allocated. Query Store tables are just system tables. They have a limit on how big they can grow (100mb by default), but that space isn't going to be pre-allocated in any way. The space will just get used as and when it's needed, just like any other system table. However, don't take my word for it, let's prove that. The Test Testing whether or not enabling Query Store is straight forward. Here's a query that should give us information rather quickly: [crayon-5c44c899a5880183766276/] The results come back…
Read More

Query Store and What Happened Earlier On Your Server

Here's a great question I received: We had a problem at 9:02 AM this morning, but we're not sure what happened. Can Query Store tell us? My first blush response is, no. Not really. Query Store keeps aggregate performance metrics about the queries on the database where Query Store is enabled. Aggregation means that we can't tell you what happened with an individual call at 9:02 AM... Well, not entirely true. The aggregations that Query Store keeps are actually broken up into intervals that you can control. The default interval is 60 minutes. This means that the information stored in the DMV covers sets of intervals. This means that if, at 9:02AM, you had a query, or queries, that ran considerably longer than normal, you may be able to take a look…
Read More

Query Store and Plan Forcing: What Do You Use It For

If you're working with Azure SQL Database or you've moved into SQL Server 2016, one of the biggest new tools is the Query Store. It provides a mechanism of capturing query performance over time and persisting it with the database. You also get the execution plans for those queries. Finally, you can choose to have the Query Store override execution plan selection by use of Plan Forcing. I've written about Query Store a few times: Query Store, Force Plan and "Better" Plans Query Store, Force Plan and Dropped Objects Precedence Goes to Query Store or Plan Guide Query Store, Forced Plans and New Plans Query Store and Optimize For Ad Hoc Query Store and Recompile Finding Your Query in Query Store Removing All Query Store Data Monitor Query Performance OK,…
Read More

Query Store, Force Plan and “Better” Plans

I am endlessly fascinated by how the Query Store works. I love teaching it at every opportunity too. Plus, almost every time I teach it, I get a new question about the behavior that makes me delve into the Query Store just a little bit more, enabling me to better understand how it works. I received just such a question at SQLSaturday Norway: If you are forcing a plan, and the physical structure changes such that a "better" plan is possible, what happens with plan forcing? Let's answer a different question first. What happens when the plan gets invalidated, when the index being used gets dropped or some other structural change occurs so that the plan is no longer valid? I answered that question in this blog post. The plan…
Read More