Development

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

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

March 9, 2021

Development

#tsql2sday

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)

March 2, 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

February 16, 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

February 2, 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?

January 28, 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?”

January 27, 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

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

December 22, 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

November 25, 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

November 24, 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

November 23, 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.

November 19, 2020

When I’m troubleshooting a blocking emergency, the culprit is usually the query at the head of a blocking chain. Somebody did something ill-advised like starting a transaction and then locking a whole bunch of tables. But sometimes, the lead blocker isn’t the real problem. It’s query #2. Here’s a sample scenario: A long-running select with…

How Scalar User-Defined Functions Slow Down Queries

November 18, 2020

When your query has a scalar user-defined function in it, SQL Server may not parallelize it and may hide the work that it’s doing in your execution plan. To show it, I’ll run a simple query against the Users table in the Stack Overflow database. SELECT TOP 100 DisplayName, Location, Reputation, Id FROM dbo.Users ORDER…

How Bad Statistics Cause Bad SQL Server Query Performance

November 17, 2020

Statistics

SQL Server uses statistics to guess how many rows will match what your query is looking for. When it guesses too low, your queries will perform poorly because they won’t get enough memory or CPU resources. When it guesses too high, SQL Server will allocate too much memory and your Page Life Expectancy (PLE) will…

MAXDOP Isn’t Really MAXDOP. It’s More Like DOP.

November 10, 2020

Execution Plans

Here’s how Books Online describes the Max Degree of Parallelism setting: You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. And here’s what the SQL Server 2019 setup screen says: When an instance of SQL Server runs on a computer that has more…

Why Full Text’s CONTAINS Queries Are So Slow

November 7, 2020

SQL Server’s full text search is amazing. Well, it amazes me at least – it has so many cool capabilities: looking for prefixes, words near each other, different verb tenses, and even thesaurus searches. However, that’s not how I see most people using it: I’ve seen so many shops using it for matching specific strings,…

Statistics Aren’t Guarantees: SQL Server Still Checks.

November 4, 2020