Development

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

Last Updated April 29, 2021

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

Brent Ozar

T-SQL

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

Brent Ozar

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

Brent Ozar

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

Brent Ozar

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

Brent Ozar

T-SQL

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

Brent Ozar

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

Brent Ozar

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

Brent Ozar

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

Brent Ozar

Locking, Blocking, and Isolation Levels

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

Brent Ozar

Locking, Blocking, and Isolation Levels

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

Brent Ozar

T-SQL

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…