Development

It’s hard to set absolute rules about, “Feature X should absolutely never be used.” However, there are some features that set off alarm bells when I see them. Usually, when I start asking more questions about when we’re using those particular features, I get answers of, “Oh, I didn’t know that was a problem.” As…

Free Video: Deadlocks: Let’s Do One, Understand It, and Fix It

Last Updated May 10, 2021

Deadlocks, Videos

You keep getting warnings and emails about deadlocks, but let’s be honest: you’re not really sure how they happen or what to do about it. In this one-hour session from the SQLBits conference, I will show one, use sp_BlitzLock to analyze it, then fix it. If you like this video, check out the SQLBits YouTube…

What’s the Buggiest Feature in SQL Server?

Last Updated May 5, 2021

Execution Plans, SQL Server

Not the one you like the least, or the one that requires the most work. What’s the one that you can’t use because it’s just flat out busted? For me, it’s SSMS’s Live Query Statistics, aka live query plans. The idea is so awesome – you click Query, Include Live Query Statistics, and then when you…

Execution Plans Don’t Have the Yellow Bang They Really Need.

Last Updated April 29, 2021

Execution Plans

When SQL Server is purely making an estimate up out of thin air, with no freakin’ idea what the real answer is, it doesn’t give you any kind of warning. It just produces an estimate that looks scientifically accurate – until you know how it’s actually getting calculated. Let’s ask SQL Server how many users have…

Never, Ever, Ever Start T-SQL Comments with Two Dashes

Last Updated April 5, 2021

138

There are two ways you can write comments in T-SQL: --By starting a line with two dashes, or /* By framing it on both sides like this */ 123 --By starting a line with two dashes, or /* By framing it on both sides like this */ Never, ever use two dashes. Never. The problem is that…

The Way Developers and Users Interact is Broken.

Last Updated March 24, 2021

Azure Data Studio, SQL Server Management Studio

This month, to mark the 20th anniversary of BrentOzar.com, I’m stepping back and looking at the big picture rather than blogging about database problems & solutions like I usually do. Twenty years ago, I tried to switch to Linux. Again. That wasn’t my first attempt, nor was it my last – there were so many…

#TSQL2sday 136 Wrap-Up: Your Favorite (and Least Favorite) Data Types

Last Updated March 15, 2021

Development

For this month’s T-SQL Tuesday, I asked you to blog about your most-loved and least-loved data types. Crazy, right? How could people possibly love or hate data types? Well, if you’ve been working with them for a while, I figured you’d have built up an array of tips or pain points, and y’all delivered with…

4 Things I Love About Integers #TSQL2sday

Last Updated March 9, 2021

Development

I love integers. Furthermore, I think we’ve all been taking them for granted. Integers are clean, elegant datatypes that do exactly what they’re supposed to. They’re the Golden Retrievers of the database world: they have a few shortcomings, but so, so few. And so for this month’s T-SQL Tuesday, I wanna share my love for…

#TSQL2sday 136 Invitation: Blog About Your Favorite Data Type (Or Least Favorite)

Last Updated March 7, 2021

T-SQL Tuesday is a monthly blogothon where we get together and write about a different topic. I’m hosting this month’s episode. Your mission: write a blog post about your favorite data type, and schedule it for next Tuesday, March 9. Mad about money? Feverish about float? Tell us what you use your favorite data type…

Changing Statistics Cause Longer Compilation Times

Last Updated February 13, 2021

Execution Plans, Statistics

I need to be up front with you, dear reader, and tell you that you’re probably never going to need to know this. I try to blog about stuff people need to know to get their job done – things that will be genuinely useful in your day-to-day performance tuning and management of SQL Server.…

Unusual Parameter Sniffing: Big Problems with Small Data

Last Updated February 3, 2021

Parameter Sniffing

Normally when we think about parameter sniffing, we think about a tiny data vs big data problem: the tiny data’s plan goes in first, and when we try to process big data, performance is terrible. But sometimes, it’s the exact opposite. I’ll start with the large Stack Overflow database (I’m using the 2018-06 version, but…

Can You Get Parameter Sniffing on Updates and Deletes?

Last Updated January 27, 2021

Execution Plans

Sure you can – parameter sniffing is everywhere. Anytime that you have one piece of code that needs to handle a varying number of rows, you’re probably gonna have to worry about parameter sniffing. I’ll start with any Stack Overflow database and run a couple of non-parameterized update statements. I’m using literals here, not parameters:…

“But Surely NOLOCK Is Okay If No One’s Changing Data, Right?”

Last Updated January 25, 2021

Some of y’all, bless your hearts, are really, really, really in love with NOLOCK. I’ve shown you how you get incorrect results when someone’s updating the rows, and I’ve shown how you get wrong-o results when someone’s updating unrelated rows. It doesn’t matter – there’s always one of you out there who believes NOLOCK is okay…

Query Hints You Can Use to Avoid Blocking

Last Updated January 21, 2021

No, not NOLOCK. You get the wrong query results, even when your query results aren’t supposed to be changing. I’m talking about when you need to do writes, but you want your query to get along with others in high concurrency environments, without creating a blocking firestorm. SET LOCK_TIMEOUT 1000 – if you run this…

How to Batch Updates A Few Thousand Rows at a Time

Last Updated December 17, 2020

You’ve got a staging table with millions of rows, and you want to join that over to a production table and update the contents. However, when you try to do it all in one big statement, you end up with lock escalation, large transaction log usage, slow replication to Availability Groups, and angry users with…

How to Set & Get the Next ID Without Serializable Isolation

Last Updated November 30, 2020

Say for some reason you can’t use identity columns or sequences, or maybe your application was built a long, long time ago – and you’ve got a table with a list of IDs to use next: DROP TABLE IF EXISTS dbo.Ids; CREATE TABLE dbo.Ids (TableName NVARCHAR(255) PRIMARY KEY CLUSTERED, CurrentId BIGINT); INSERT INTO dbo.Ids (TableName,…

How to Insert Rows and Get Their Identity Values with the OUTPUT Clause

Last Updated November 30, 2020

Say you’ve got a two-step process where you’re: Inserting rows into a table that has an identity column, then Querying that table to figure out what identities you got There’s a a faster way that doesn’t require hitting the table twice: the OUTPUT clause. I’ll demonstrate it with the Badges table from the Stack Overflow…

Finding the One Query to Tune in a Multi-Query Batch

Last Updated November 30, 2020

Execution Plans

When you have a stored procedure or application query that has multiple queries inside of it, how can you tell which query to focus on? For starters, do NOT believe the percentages you see in execution plans. Let’s take a classic performance tuning issue: scalar functions. I’ll start with the Stack Overflow database – if…

When You’re Troubleshooting Blocking, Look at Query #2, Too.

Last Updated November 30, 2020