Query Store Reports Time Intervals

SQL Server
A great question came up over at DBA.StackExchange regarding the query store reports time intervals: How can SQL Server's Query Store Reports show data for minute-length intervals, when "Statistics Collection Interval" is set to 1 hour? I was intrigued because it's not something I had thought about at all. How Does the Report Decided on Time? The first thing you need to know is that all performance information inside Query Store is aggregated. By default, the aggregation interval is 60 minutes. You can adjust that up or down (although, I wouldn't recommend making it too granular, you'll see a massive increase in storage size). It's aggregated because trying to capture every execution of every query, as anyone who has done it using Extended Events knows, is expensive and has a…
Read More

T-SQL Tuesday #166: Wrap-up

SQL Server
Once more, my apologies for being late on getting the T-SQL Tuesday announcement out. I have no excuse. However, our extended event on Extended Events (yes, I'm the third person to make this joke, yes, I'm blatantly stealing) still has several entries, so let's talk about them. Let's get mine out of the way. I was simply curious what the search engines revealed when I asked a pretty common question: how do you identify slow queries? What I found was, the answers on most search engines to this question are old, very old. Not to say wrong, but since many of them were created before a working version of Extended Events (let alone Query Store) was released, how could they tell you. On to actually good posts. One of my…
Read More

T-SQL Tuesday #166: Why Not Extended Events?

SQL Server
With 165 T-SQL Tuesday events, two, just two, this one, T-SQL Tuesday #166, and another one back in 2018 or 2019 (I forget and I'm far too lazy to go look) have been on Extended Events. At conferences I'm frequently the only one doing sessions on Extended Events (although, sometimes, Erin Stellato is there, presenting a better session than mine). I did a session at SQL Konferenz in Germany earlier this week on Extended Events. Hanging out in the hallway at the event (which was great by the way), I was talking with some consultants. Here's their paraphrased (probably badly) story: "I was working with an organization just a few weeks back. They found that Trace was truncating the text on some queries they were trying to track. I asked…
Read More

Battle of the Query Hints in Query Store

SQL Server
I recently presented a session on the Query Store at Data Saturday Rhineland and the question came up: If there's already a query hint on a query, what happens when you try to force a similar query hint? Yeah, OK, that is a weird one. I don't know the answer, but I'm about to find out. Setting up the Battle I've got this simple procedure I use a lot to illustrate bad parameter sniffing. In AdventureWorks, this query can produce up to five different plans, depending on the values called. Most of the time, it's one of two plans, which I'll get to in a minute. Here's the query: CREATE OR ALTER PROC dbo.ProductTransactionHistoryByReference (@ReferenceOrderID INT) AS BEGIN SELECT p.Name, p.ProductNumber, th.ReferenceOrderID FROM Production.Product AS p JOIN Production.TransactionHistory AS th…
Read More

How Does SELECT * Affect Query Store?

SQL Server
I live for questions and recently, I had someone ask me, does using SELECT * affect Query Store. My immediate gut reaction was, hell no. Of course it doesn't. Then, yeah, I started thinking. It might. Let's test it and see. The Setup The trick here is to get a good setup. I need a query that's... interesting. Meaning, I need the query to do more than just SELECT * from a table. Although, let's start there: SELECT * FROM dbo.Customers AS c; GO 20 I ran it several times so that it will, for sure, get captured by Query Store (when on Auto, in 2022, one execution of a query may not be enough to see it captured by QS). Then, I took a look at the plan: SELECT…
Read More

Restore AWS RDS Databases On-Premises

AWS, RDS, SQL Server, Uncategorized
Did you know that you can restore AWS RDS databases to a SQL Server 2022 instance running locally using native backup and restore? Well you can. Let's talk about it. Why S3? So, why put backups on S3 at all? Two big reasons. Let's say you're mostly, or strictly, on-premises with your servers. You can use S3 storage on AWS as a way to get your backups offsite. I just recently tried to help someone in the forums who was taking backups, but not testing them in any way, only to find that their local storage had become corrupted and when they needed the backup, it wasn't there. First, of course, they should be testing their backups. However, in addition, they should have moved the backup to an offsite location.…
Read More

What Happens on Azure SQL Database?

Azure, SQL Server
Last week I posted the results from using Extended Events to snoop on what happens inside an AWS RDS database. This week, I'm taking a look at what happens on Azure SQL Database. I'm using the same toolset again, if for no other reason that I'm consistent in my approach. So it's basically just rpc_completed & sql_batch_completed on the database in question. Let's check out the results. What Happens on Azure SQL Database I would be doing the same thing as before, breaking apart the batch commands from the stored procs and/or prepared statements. However, after 48 hours, I only have 116 of both, so I'm just going to combine them this time. The batch called most frequently, for a whopping total of 8 times over 48 hours, isn't even…
Read More

Query Store Data Cleanup

SQL Server
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…
Read More

SQL Server 2022 Query Performance Tuning

SQL Server
If you're interested in getting a digital copy, my brand spanking new book is now available here. It's in the intro, but let me tell you a little bit about the new book. It's really new. Some of the older versions of the book were simply updated, a bunch of changes to most chapters, a couple of new chapters, fixes for old mistakes, ta-da, new book. Not this time. This time, I rewrote it all. From scratch. Now, some of the chapter titles are the same. Quite a few of the examples are the same (if code illustrates something successfully, I'm reusing it). However, overall, it's a brand new book. There's a lot of new material too. The last update was for SQL Server 2017. There has (almost) been two…
Read More