05Jul 2022 by Grant Fritchey 2 Comments

Extended Events for Anything But Query Tuning: Unique Constraint Violations

SQL Server
Most of the time when I talk about or demo Extended Events, I spend more time talking about query tuning (I have a problem). However, there are tons of things that you can do with Extended Events. Here's a little one that came up, auditing unique constraint violations. Unique Constraint Violations Whether we're talking a primary key or just a constraint, the error you get is number 2627 when you attempt to add a non-unique value. So, the code for a simple way to track this in Extended Events would look like this: CREATE EVENT SESSION [UniqueConstraintViolation] ON SERVER ADD EVENT sqlserver.error_reported (WHERE ([error_number] = (2627))); That's it. That's all you need. Probably, it'd be a good idea to output this to a file (that's usually what I do). However,…
Read More
13Jun 2022 by Grant Fritchey No Comments

2022 State of PostgreSQL Survey

PostgreSQL
Hello all. I know most of you are still working within SQL Server. However, a few of you are become more like me, hybrid data managers, working in more than one platform. For those currently doing a little PostgreSQL management, I'd like to point out to you the 2022 State of PostgreSQL survey. These kinds of things are great ways to understand where the technology is headed, your place on that path, and how well your organization is doing with the management of PostgreSQL compared to others. If you have a couple of minutes, please follow the link and fill out the survey. And watch this space for a lot more SQL Server content coming up. I'm rewriting my query tuning book, from scratch, so lots of stuff in my…
Read More
06Jun 2022 by Grant Fritchey No Comments

PostgreSQL and Azure Data Studio

PostgreSQL
As I've been working more with PostgreSQL, I've found that I'm basically pretty happy just issuing SQL commands to get work done. However, it's handy to have an actual programming environment to work from. A few reasons for this. First, and probably most important, a good programming environment has easy connections to source control. Also, you'll get, hopefully, some help with type-ahead for the code you're writing. Finally, having a visual of the stuff in your database can be handy while working on it, so a GUI is nice. I use Azure Data Studio for most of my PostgreSQL work. Azure Data Studio and PostgreSQL I'm not sure exactly when support for PostgreSQL was added. The two years I've been poking around with PostgreSQL, Azure Data Studio (ADS) has supported…
Read More
02May 2022 by Grant Fritchey No Comments

Extended Events in AWS RDS

AWS, RDS
For the longest time, we didn't have one of the most useful tools for monitoring SQL Server behavior, but I just found out that, indeed, you can use Extended Events in AWS RDS. I'm not waiting around. Let's see it in action. Setup For Extended Events in AWS RDS AWS has posted the documentation on what you have to do in order to enable the collection of Extended Events within RDS. Normallly, I'd follow along with the documentation. However, I'm going to approach this like I knew that Extended Events support was there, but I wasn't aware of the docs. So, I'm starting in SSMS and I'm just going to try plugging in the Extended Events GUI to see what happens. Further, I'm going to use the simplest method for…
Read More
11Apr 2022 by Grant Fritchey 7 Comments

Query Tuning and Easy Solutions

SQL Server
TLDR: There ain't one. I was privileged last week to be able to present a couple of sessions at the SQL Server and Azure SQL Conference (great event, I recommend it). One of my sessions was an intro to query tuning. Basically, I went through a bunch of common code smells and suggested different possible solutions. The one thing that came up, both from my own stories and the questions from the (engaged) audience, is just how much everyone wants a magic, run fast, switch. Query Tuning The Easy Way Here you go. The single easiest way to make your queries run faster: Just throw money at the problem. Buy more hardware. Buy bigger hardware. Get more disks and disk controllers (not just more disks). Go to the next highest…
Read More
04Apr 2022 by Grant Fritchey No Comments

Two Years of “Good Morning!”

Professional Development
Two years ago at the start of the pandemic, I wasn't feeling great about things. I saw that quite a few others weren't all that thrilled about how things were going either. So, to help my own mood, and to try to be a service to others, I started tweeting a little "Good Morning!" post every work day. I filled them with happy thoughts, tips I'd read on handling depression, suggestions I learned on health, anything that would help people, just a teeny amount, at the start of the day. Now, I'm not some licensed therapist or anything like that. I just wanted to do something to help others where I know I needed help. That's it. Two years later and I've written a bunch of tweets. Based on feedback…
Read More
14Mar 2022 by Grant Fritchey No Comments

Execution Plan Properties

Uncategorized
I've watched several people recently go straight to XML when reading execution plans because they didn't know about the execution plan properties in the first operator. Now, don't get me wrong. If going straight to the XML is working for you, that's fine. Keep doing it. I'm absolutely not questioning how anyone does things. I just want people to know that "hidden" information isn't so much hidden as much as it's not too obvious. First Operator I've written before about the first operator in an execution plan (here, as well as here, and here, and even a problem with them here). I don't have a whole lot to add to those posts. If you look around at other blog posts I've done on execution plans, I use the properties of…
Read More
07Mar 2022 by Grant Fritchey 4 Comments

Query Plans in Azure Data Studio

SQL Server
I have long been a fan of Azure Data Studio, but one shortcoming has kept me from truly adopting it: Query Plans in Azure Data Studio. Sure, there was a plug-in you could install. Also, you could use a somewhat truncated version of Plan Explorer, but all I wanted was for SQL Server Management Studio plans to be query plans in Azure Data Studio. Go and get version 1.35 of the tool. Right now. DUDE! You have 1.35 of Azure Data Studio? Cool. Now, go to the menu bar. Click on "File." Click on "Preferences". Click on "Settings". Now, type the following into the search box: workbench editor enable preview. You should see this: Check the box below where it says "Workbench > Editor: Enable Preview" just like I have…
Read More
28Feb 2022 by Grant Fritchey No Comments

Database Fundamentals #31: Unique Constraints from the GUI

Database Fundamentals, SQL Server
In the last few Fundamentals posts you were introduced to a couple of ways to limit and control the data stored in the tables in your database. A primary key won’t allow a duplicate value. A foreign key won’t allow a value to be added that doesn’t already exist in the parent table and it will prevent data from being deleted. These are types of constraints on data in your database. There are a bunch of other ways to constrain the data in an effort to ensure that the data stored is exactly what the business needs. The next few Fundamentals posts will cover several methods of limiting data. Unique Constraints to Stop Duplicates When the concept of the primary key was introduced earlier in the series, two different types…
Read More
22Feb 2022 by Grant Fritchey No Comments

Azure Data Studio Intellisense

SQL Server
I recently saw a question about the Azure Data Studio Intellisense: "Why won't intellisense in Azure Data Studio work with different schemas?" Immediately I thought, "Wait, it does." But, testing is your buddy. Azure Data Studio Intellisense Azure Data Studio Intellisense is on by default. Also, I like it a little better than the one in SSMS because it will start trying to help you, as you type (like a 3rd party software I can't live without). However, they're partly right. Let's say I want 'Person.Address' so I start typing like this: It doesn't know that I have a table named 'Person.Address'. It's trying to be helpful. You can even see how it's doing a form of wild card search, suggesting that maybe 'db_accessadmin' is what I want since it…
Read More