Grant Fritchey

03Apr 2023 by Grant Fritchey No Comments

How Does SELECT * Affect Query Store?

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…

20Mar 2023 by Grant Fritchey No Comments

20 Minute Sessions: A Couple of Thoughts

Professional Development

At the most recent SQL Bits, I gave two 20 minute sessions. I also gave one last year. There is a little bit of controversy around these (nothing real, come on), and I'd like to take a moment to record a few thoughts. Let me say this up front, and then we'll go from there:I'm not a fan. 20 Minute Sessions Can Be Challenging Most conferences have a 60 minute session as the default. Quite a few have 75 minutes too. The standard session at Bits is the 50 minute session (which, can be mildly uncomfortable when you're used to 60, but is honestly no big deal). So what are the 20 minute sessions for? You'll also see a lot of shorter sessions, usually 5-10 minutes, sometimes called lightning talks.…

13Mar 2023 by Grant Fritchey No Comments

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.…

06Mar 2023 by Grant Fritchey No Comments

#PGSQLPhriday 006: Wrap Up & Summary

PostgreSQL

Your mission, if you chose to accept it, was to share a single tip/hint/fact/something that you wish you'd known about PostgreSQL when you were just getting started learning it. Well, we've got several people who have put together some excellent posts. Let's go over 'em all, in no particular order. First up, Lætitia Avrot has a post all about the PostgreSQL documentation and it is well worth the read. I couldn't agree more with all her points. Plus, I love, love, LOVE the summary list of pointers to documentation broken down by role. Thanks so much for sharing all this. I also wrote a post about the docs. Lætitia covers them better. Next, Andreas Scherbaum has some very cool tips for using psql. My knowledge here is a bit slim.…

03Mar 2023 by Grant Fritchey 1 Comment

#PGSQLPhriday 006: What I Wish I Knew While Learning PostgreSQL

PostgreSQL

Well, for those who don't know me, I'm just barely started on my PostgreSQL journey. So, what the heck can I contribute to this conversation? Not much, but I do have one tip. It's about the official documentation and search engines. YaDuBiGo When you use Yahoo, DuckDuckGo, Bing, Google, or whatever search engine you use, what you don't get is a simple, "here are the pages you asked for". All the search engines use various mechanisms to surface you "here's the BEST and MOST POPULAR pages you asked for." What defines best in these circumstances? Most accurate? Maybe, maybe not. I've never worked for one of these organizations and they tend to keep their algorithms to themselves, for obvious reasons, both good & evil. However, I do know one thing…

27Feb 2023 by Grant Fritchey No Comments

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…

24Feb 2023 by Grant Fritchey No Comments

One Thing You Wish You Knew While Learning PostgreSQL: PGSqlPhriday #006

PostgreSQL

I will not even attempt to hide it, I'm very much in the learning phase of my PostgreSQL journey. As such, I'm constantly picking up new facts. However, I'm interested in that one thing that you wish someone had told you. That tiny bit of "AH HA!!!" information that you have now, but it took you a while to find. Just that teeniest, tiniest slice of the behavior of PostgreSQL that proved to be a game-changer for you. In short, help me and all the others out there trying to learn this stuff. Make learning PostgreSQL just a little bit faster & easier for us. Please. Why am I asking? Because this is all a part of #PGSqlPhriday 006. What is #PGSqlPhriday and how do I participate? As to what…

20Feb 2023 by Grant Fritchey No Comments

What Happens On AWS RDS?

AWS, RDS

I was talking with some developers from my team about monitoring, and I said, "We all use the same tools," referring to other monitoring software. Then, it hit me. How is AWS collecting monitoring data on it's RDS servers, specifically, the SQL Server instances. So, I set out to determine what happens on AWS RDS when it comes to the native monitoring. The Setup This part should be as obvious as it is easy. I'm going to use Extended Events. I've written before about how AWS RDS supports Extended Events, so I won't repeat all that here. I'll just leave you with the session I'm running to see what happens on AWS RDS: CREATE EVENT SESSION [ExEventTesting] ON SERVER ADD EVENT sqlserver.rpc_completed, ADD EVENT sqlserver.sql_batch_completed ADD TARGET package0.event_file (SET filename…

13Feb 2023 by Grant Fritchey No Comments

Queries Impacting TempDB

Uncategorized

The question came up that someone wanted to monitor the queries impacting tempdb using Extended Events. Initially, I was pretty sure that there was no real way to do this. The standard query events like rpc_completed includes spill information, if any occurred, but that's it. There are also no actions that you can add to an event that would capture tempdb behaviors. So, I want to my senior DBA, YaDuBiGle. I found this ancient (2009) article on SQLTips, that surprised me with a pretty slick solution using, are you ready, waits. Clever. How well does it work? Let's try it out. Testing Capturing Queries Impacting TempDB I'm not stealing their code, so you can follow the link to see the setup. I'm much more interested in the results. How well…

06Feb 2023 by Grant Fritchey 3 Comments

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…