Development

Why Are Linked Server Queries So Bad?

Last Updated July 15, 2021

No, I don’t want to be your valentine. Remember when you were in school, and you had a crush on someone? You would write a note asking them to be your valentine, and you’d ask a mutual friend to pass the note over to them. The adult equivalent is linked server queries. When your query…

What does SET NOCOUNT ON do?

Last Updated June 4, 2021

Brent Ozar

T-SQL

When you’re working with T-SQL, you’ll often see SET NOCOUNT ON at the beginning of stored procedures and triggers. What SET NCOUNT ON does is prevent the “1 row affected” messages from being returned for every operation. I’ll demo it by writing a stored procedure in the Stack Overflow database to cast a vote: CREATE…

“I’m getting index seeks. Why are my row estimates still wrong?”

Last Updated June 6, 2021

Brent Ozar

Execution Plans

If you’ve got good indexes to support your query, and statistics to help SQL Server guess how many rows will come back, how can SQL Server still come up with terribly incorrect row estimates? To demonstrate, I’ll use the 2018-06 version of the Stack Overflow database, but any recent version will work as long as…

9 Signs Your T-SQL Might Fail a Code Review

Last Updated May 17, 2021

Brent Ozar

T-SQL

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

Brent Ozar

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

Brent Ozar

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

Brent Ozar

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

Brent Ozar

T-SQL

117

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…

How to Set & Get the Next ID Without Serializable Isolation

Last Updated November 30, 2020

Brent Ozar

Locking, Blocking, and Isolation Levels

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

Brent Ozar

T-SQL

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…