Posts by Brent Ozar

This week’s series on error handling has been an eye opener for many of us. We’ve laughed. We’ve cried. We’ve screamed in horror. We’ve read the documentation. I don’t blame you. This is a really confusing topic, and for many of us, it’s the first time we’ve actually looked at the mechanics of how transactions,…

Error Handling Quiz Week: Making a Turkey Sandwich with XACT_ABORT

Last Updated January 13, 2022

CAN YOU BELIEVE THAT HOT MESS IN YESTERDAY’S POST?!? I know, right? You thought that by combining try/catch with a transaction, you’d get robust error handling. Instead, you ended up with half the tables populated, and a leftover open transaction. You’d already forgotten Tuesday’s post in which I pointed out that TRY/CATCH doesn’t catch low…

Error Handling Quiz Week: Combining Transactions And TRY/CATCH

Last Updated January 13, 2022

In yesterday’s epic cliffhanger, you might have been shocked to discover that a plain ol’ transaction does not solve our problem – and in fact, it makes things worse. With yesterday’s code, we still got rows inserted into the Parent table, no rows in the Child table, and a nasty surprise that our session will…

Error Handling Quiz Week: Will a Transaction Help?

Last Updated January 12, 2022

Locking, Blocking, and Isolation Levels, T-SQL

In yesterday’s post, we tried a TRY/CATCH, and those of us who actually ran the demo scripts were disappointed. We found that a TRY/CATCH alone doesn’t fix it: we end up getting rows in the Parent table, but no rows in the Child table. It turns out there are some gotchas in Books Online’s summary of…

Error Handling Quiz Week: Tryin’ TRY/CATCH

Last Updated January 12, 2022

Let’s say we have two tables, Parent and Child, and we need to guarantee that they both get populated at once. We’ll write a single stored procedure to do both inserts: DROP TABLE IF EXISTS dbo.Parent; DROP TABLE IF EXISTS dbo.Child; CREATE TABLE dbo.Parent (ID INT IDENTITY(1,1), DateAdded DATETIME2); CREATE TABLE dbo.Child (ID INT IDENTITY(1,1),…

Registration is Open Now for My SQLBits Workshop!

Last Updated January 6, 2022

SQLBits

Registration just opened up for my SQLBits workshop on Mastering Parameter Sniffing! Move fast – due to COVID, they’re spacing out the attendees, so there are even less seats available than usual. My workshops usually sell out quickly, so if you want a seat, you’ll need to hustle in. Here’s the session abstract: You’re a…

[Video] Office Hours in Cabo: Ask Me Anything About SQL Server

Last Updated January 2, 2022

Let’s get together at sunrise in Cabo San Lucas, Mexico and talk through your highest-upvoted questions from https://pollgab.com/room/brento. Here’s what we covered: 00:00 Introductions 00:33 AtHomeWithCOVID: Historical advice is to spread multiple datafiles/filgroups across disks. Now, storage is allocated from a balanced SAN as one drive, e.g “D” data, “L” Logs Are there any gains…

New Year’s Resolution: Lose Weight in Your Database

Last Updated January 5, 2022

Indexing

Made a New Year’s resolution to lose weight this year? Forget taking it off your waistline – take it off your database instead with: sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2, @SortOrder = ‘size’ Or: sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2, @SortOrder = ‘rows’ This produces an inventory of all the indexes sorted by…

Who’s Hiring in the Database Community? January 2022 Edition

Last Updated January 3, 2022

Who's Hiring

Last year, I started this series of “who’s hiring” blog posts because The Great Resignation was clearly a real thing. The pandemic is causing so many folks to rethink what they do for a living, where they do it, who they do it for, and in light of yesterday’s salary survey results, how much money…

Here Are The Results of the 2022 Data Professional Salary Survey.

Last Updated January 3, 2022

Salary

Every year, I run a salary survey for folks in the database industry. This year, I was especially curious to see the results to find out whether salaries went up. Anecdotally, I’ve seen a lot of people jumping ship to new companies due to the Great Resignation – but what does the data actually show?…

The SQL Server Posts You Read the Most in 2021

Last Updated December 20, 2021

SQL Server

I think of my blog posts in a few different categories: Posts that will only be read at the moment they’re published (like upcoming webcasts, humorous stuff, and career advice) Posts that will show up in search history over time and continue to be evergreen (like how a feature works) Posts that provoke discussion and…

[Video] Office Hours: SQL Server at Sunset

Last Updated December 22, 2021

Let’s kick back with a tasty beverage and go through your highest-upvoted questions from https://pollgab.com/room/brento on the beach: Here’s what we covered: 00:00 Introductions 00:49 Alexander Speshilov: How to determine amount of memory (RAM) used by query store? 02:23 Aslan: Merhaba Brent, in your opinion, what are the top 4 databases to administer by DBA…

Check Your SQL Server Backup Performance & Safety with sp_BlitzBackups

Last Updated December 18, 2021

Backup and Recovery, First Responder Kit

Out of all of the scripts in our free First Responder Kit, sp_BlitzBackups is probably the one you’ve used the least. Let’s talk about what comes out of it, and why you should use it more often. First, let’s define two terms: Recovery Point Objective (RPO) – measured in time, it’s how much data you…

[Video] Office Hours: SQL Server Q&A in Cabo at Sunrise

Last Updated December 15, 2021

Let’s watch the sun rise at Land’s End and cover your top-voted questions from https://pollgab.com/room/brento/. Here’s what we covered: 00:00 Introductions 01:04 Jr Wannabe DBA: Hi Brent, a colleague wants to upgrade from Standard to Enterprise purely for performance gains on several regular servers (up to 8 CPU, 128GB of RAM, SQL 2016 or 2019).…

[Video] Office Hours: First Post From Cabo

Last Updated December 13, 2021

Join me on my balcony in Cabo as I review your top-rated questions from https://pollgab.com/room/brento. Here’s what we covered: 00:00 Introductions 00:48 md: Hi Brent, as SQL Server works with 8K pages why is the recommended NTFS Unit Allocation Size for SQL volumes 64K? Wouldn’t 8K be a more efficient fit? Sneaking in a second…

Don’t Use SQL Server to Send Emails.

Last Updated December 12, 2021

Production Database Administration

I would like to unsubscribe from this idea. I’m not talking about Agent alerts when jobs fail. That’s fine when you can’t get the business to spring for a monitoring system. I’m talking about using SQL Server to send emails to confirm a customer’s order, alert that a product is low on stock, or other…

[Video] Office Hours: SQL Server 2008 R2 Edition, I Guess

Last Updated December 7, 2021

I went through your highest-voted questions from Pollgab and talked through ’em, and strangely, there was a bit of a 2008R2 theme! https://youtu.be/3-B5L3Komq4 Here’s what we covered: 00:00 Introductions 02:00 Pete Donnelly: Hi Brent, I have a 7 TB DB and the largest table is 2 TB, has 6 indexes and 6 related Statistics. It…

How Do You Test IOPs, Latency, and Throughput?

Last Updated December 7, 2021

Storage

You’ve heard these terms thrown around about storage, and you’re not sure what they mean, and how they relate to each other. Let’s make it simpler by using an analogy. Say you want to measure a shipping company. You’re trying to decide whether to use DHL, FedEx, UPS, or your local postal service. You could…

Going to SQLBits? Join me for my Mastering Parameter Sniffing workshop.

Last Updated December 7, 2021

SQLBits

You’re a database developer or DBA with at least 3 years experience performance tuning queries and indexes. You already know you have parameter sniffing issues, and now you need to figure out how to fix it. In this one-day class, you’ll learn how to reduce the blast radius with index changes, query tuning, and database-level…

Who’s Hiring in the Database Community? December 2021 Edition

Last Updated November 28, 2021