Microsoft supplies quite a few knobs to control how Query Store performs data cleanup. You can set your cleanup various ways, and, they interact. Let's talk about how and why Query Store does it's data cleanup. Query Store Size Limit The most important thing to understand here is that Query Store won't just keep collecting data forever, filling your hard drive. There is a hard limit to how much data Query Store contains. By default, prior to 2019, that was 100mb. After 2019, it's 1,000mb. You can, of course, adjust this up, or down, as needed on your systems. It's a database-by-database setting (as so much of Query Store is). You can change this through SSMS: Change the "Max Size (MB)" value. That's it. Nice & simple. You can also…
I may have occasionally talked about the importance of Query Store, but today I want to emphasize just how much Microsoft is weaponizing query store. Of course, I don't mean they're creating the Death Star or something, I simply mean they're taking the information that Query Store gathers and using that to enable a number of new performance enhancements within SQL Server. Intelligent Query Processing Starting back in SQL Server 2017, Microsoft started making improvements to how queries get processed. The early stuff was mostly about interleaved execution, basically allowing for the ability to adjust an execution plan on the fly. This was expanded quite a bit in 2019. Follow the link for a great graphic showing how this stuff has progressed. SQL Server 2022, releasing soon I'm sure (no,…
I was recently asked what permissions were needed to force plans in query store. I'm sure I knew at one point, but at the moment I was asked, I couldn't remember to save my life. So, I went and looked it up. In the interest of sharing, especially for the poor person who I left hanging, here's what I found. Permissions in Query Store Look through the blog, you'll find I'm pretty enamored with Query Store. I even contributed to a book on the topic (a little, it was almost all Tracy's work on that book, I just helped out). I haven't addressed security and Query Store. You do need to think about security in Query Store. For example, should you give read access to Query Store to your dev…
A question that came up recently was how to track the query compile time. It's actually a pretty interesting question because, there aren't that many ways to tell how long it took to compile the query, and they don't necessarily agree. For most of us, most of the time, compile time for a given query doesn't matter. However, I love telling the story of the query I had on an old system that could run in 90ms, but took 5 minutes to compile. In short, sometimes compile time matters. How To See Query Compile Time If you want to see how long it takes a query to compile, you have, to my knowledge, three options. The first, and possibly easiest, is to look at the plan properties on an execution…
Under the covers, Azure SQL Database is just good old fashioned SQL Server and this includes Query Store on Azure. While many things can be different when working with Azure, Query Store just isn't one of them. Let's talk about it a bit. Query Store on Azure Unlike your databases created on a SQL Server instance (big iron, VM, hosted VM, wherever), the databases you create on Azure SQL Database have Query Store enabled by default. Managed Instance and Synapse are different. In their case, they operate the same as an instance of SQL Server, off by default. Further, in the single database of Azure SQL Database, you can't, as in can not, disable Query Store. It's on by default and it's staying that way. This leads to a simple…
I was recently asked a question on a forum by a person who was frustrated with all the tool choices we have for measuring performance. Moreover, they were frustrated that a simple and clear combination of the tools to achieve synergy was extremely challenging. In fact, they said that, just using the query_hash as an example, they never saw a single match between the DMVs, Query Store and Extended Events. Now, that's pretty unlikely and I'm sure we could talk about why that might be the case. However, this idea of combining the tools, I shared a bunch of thoughts on it. I decided, maybe it's worth sharing here too. Achieving Synergy Honestly, this is tough. I work for a company that makes a monitoring tool. We are trying to…
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? Indexes Used in Query Store Now Query Store itself doesn't store index usage statistics. It stores queries, wait statistics and runtime metrics on individual queries. All useful stuff. Oh, and,…
A question that comes up on the forums all the time: Which query used the most CPU. You may see variations on, memory, I/O, or just resources in general. However, people want to know this information, and it's not readily apparent how to get it. While you can look at what's in cache through the DMVs to see the queries there, you don't get any real history and you don't get any detail of when the executions occurred. You can certainly take advantage of the Query Store for this kind of information. However, even that data is aggregated by hour. If you really want a detailed analysis of which query used the most CPU, you need to first set up an Extended Events session and then consume that data. A…
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…
Probably the single most important factor when deciding which query to tune, or actively tuning a query, is how you go about measuring query execution time. SQL Server provides a number of different mechanisms (really, maybe too many) to get this done. However, all measures are not created equally. In fact, they frequently disagree with one another. Let's take a look at this odd phenomenon. Measuring Query Execution Time Before we get into all the choices and compare them, let's baseline on methodology and a query to use. Not sure why, but many people give me blow back when I say "on average, this query runs in X amount of time." The feedback goes "You can't say that. What if it was just blocking or resources or..." I get it.…