RDS Connectivity

AWS, PostgreSQL, RDS
I travel quite a lot for work. Most of it is in the US and Europe, but I get around to other places as well. Most of the time, connectivity, while not the greatest, isn't that bad, or hard. I run a VPN on my phone and laptop. I can set up my RDS connectivity by getting whatever IP address I've been assigned, and I'm good to go. However, recently, for whatever reason, I could not make a connection to AWS RDS no matter what I tried. I even switched over to my phone. I dropped my existing database (which hurt, just a test database, but now I have to rebuild it). Nothing. I validated every single possible problem. I went through all the troubleshooting steps. Nothing. It's Nice to…
Read More

The Only Valid Test of a Backup is a Restore

Professional Development
Please, let me reiterate: The only valid test of a backup is a restore. THE ONLY VALID TEST OF A BACKUP IS A RESTORE. I'm happy that you have backups. That's great. Can you restore them? Let's tell a story. I Play With Radios "WE KNOW! SHUT UP ABOUT IT! Damn radio people. Worse than crossfitters." Cool, cool! I won't get into the radio stuff.... too much. Just a quick setup. Please bear with me. There's an organization in the US, American Radio Relay League (ARRL). Founded back in the dawn of time, more or less the first radio club. Now, the ARRL does a lot of stuff for radio users in the US, but also everywhere, but there's controversy (when isn't there). One thing they did was build this…
Read More

Additional Use for sp_statement_completed

SQL Server
Another discussion in Gothenburg (such a great group of people) around Extended Events lead to a (admittedly, tiny) idea for an additional use for sp_statement_completed. The basics for sp_statement_completed are pretty straight forward. If you want to capture a procedure's behavior, you use rpc_completed. If you want to know about the individual statements within the procedure, you use sp_statement_completed. Now, I don't recommend turning this on across the board or without some good filtering in place because, it's likely to generate quite a bit of data. However, it can be useful, including in the following manner. What Path Did I Take? I've got a silly, example, procedure here: CREATE PROC dbo.PathTest ( @Path1 INT, @Path2 INT, @Path3 INT ) AS IF @Path1 = 1 PRINT 'Path 1'; ELSE IF @Path2…
Read More

Can You See Who Forced a Plan

SQL Server
I had an excellent group of people in Gothenburg Sweden when I taught there and I was asked: Can You See Who Forced a Plan? I didn't know the answer for certain, so I said what I always say: I don't know, but I'll see if I can find out. Query Store System Views One of the first places I'd look to see who forced a plan is the system views in Query Store. No, I don't think it'll be there, but it's worth a look. The obvious place it could be is sys.query_store_plan. After all, that's where a plan will be marked as is_forced. But you look through that and there's nothing about who forced a plan. And looking through the other views, there's nothing showing that. So, this…
Read More

Missing Columns in Extended Events Live Data Explorer

SQL Server
Let me be extremely clear up front, this is not my original work. I saw this post on DBA.StackExchange.com and I wanted to share and promote it. Nice work FevziKartal. The rest of this post is just me replicating work already done by others. I just want to see it in action. Columns in Live Data Explorer Back when I wrote the 2017 version of my query performance tuning book (no link, a) get the 2022 version for reasons I'm about to explain, b) this post is just about testing FevziKartal's work, not self-promotion), I was on board with #TeamXE. Jonathan Kehayias had taken me under his wing and explained the virtues of Extended Events and I was sold. I was also grossly ignorant. I thought that the way you…
Read More

AWS RDS PostgreSQL Restore to a Point in Time

AWS, PostgreSQL, RDS
One of the single biggest reasons to go with a Platform as a Service (PaaS) offering like AWS RDS are the things it does for you, like making it really easy to restore to a point in time. Let's take a look at it. Restore to a Point in Time When connect up to the console and look at your databases, all you have to do is select the "Maintenance and Backups" tab to get details on what backups are being taken: That's the basics of what's happening with your backups. However, you can get more detail by choosing the "Automated Backups" page on the left: And now you see all the information about the backups. But the real magic to restore to a point in time is over on…
Read More

What Are the Most Common Blockers to Adopting SQL Server Extended Events?

SQL Server
Yeah, stupid long title. It's a question I put to several different AI engines. I'm curious what the aggregated knowledge of the internet has to say on the topic of blockers to Extended Events adoption. I'll leave it to you to do the search on your favorite engine and get the wordy, long-winded, answers. However, I want to bring up some of the points that were raised in order to talk about them. First, we'll talk about some of the odd answers. Then, we'll talk about the consensus. Also, one engine gave me a blatantly poor example Session, so I want to point that out. Note: I'm not dinging on AIs. I'm growing to like the little monsters. We're going to address valid points that they bring up. However, I…
Read More

State of the Database Landscape Survey 2024

Professional Development
Hello all! This post is nothing but a simple request. Please, if you have a few spare minutes, meander on over to this link and fill out the State of the Database Landscape Survey for 2024. Yeah, it's for Redgate Software, my employer. But, really, it's for everyone. Why is it for everyone? Because, every time we do one of these surveys, we don't sit on the data, we share it. Here are the results from the 2023 survey, published earlier this year. Yeah, but, I hear you opining, what does this really do for me? Well, let's talk about it. Consuming Survey Results On the one hand, who cares. Gotta get to work. Reading about what other people are doing has no bearing on me. True. To a degree.…
Read More

Observing Extended Events

SQL Server, You Can't Do That In Profiler
Sure, you can right click on a running session for Extended Events and open the Live Data window, but are there other ways of observing what Extended Events is capturing? Yes. Extended Events Output Let me just get out of the way immediately, there's probably a way to do this programmatically with DBATools. I need to dig in there, and I'll post what I find. But what can we do right now? SQL Server Management Studio (SSMS). Here are all the sessions currently on my test/demo machine: Some of these are built in, system_health for example. Most of the rest are mine. So, how can we see what they do? Well, notice that every one has a little plus sign next to it: If we click on the plus sign,…
Read More

PostgreSQL and Instrumentation

PostgreSQL
I'm still learning PostgreSQL and one of the things I've been looking at a lot lately is instrumentation. What do I mean? Well, if you're in SQL Server, think, Dynamic Management Views (DMV), Extended Events, Query Store, <hack, spit> Trace <spit>. How do we know how long a query took to run? PostgreSQL can tell you, but, and this is one of those wild, cool, but, honestly, slightly frustrating things about PostgreSQL, not natively. Let's talk about it. Instrumentation If you connect up to a PostgreSQL database, you actually do have the equivalent of DMVs. It's called the Cumulative Statistics System. And yeah, it's a bunch of views on a bunch of functions. It displays all sorts of data about IO, tables, indexes, etc.. It will even show you acive…
Read More