Development Archives - Page 3 of 27 - Brent Ozar Unlimited®

Performance Tuning

SQL Server 2022 Finally Fixed a SQL Server 2008 Query Plan Bug!

April 29, 2025

For yeeeeeears, when I've explained execution plans, part of my explanation has included the instructions, "Read the plan from right to left, top to bottom, looking for the place where the estimates vs actuals are suddenly way off." Here's an example:

Things seem to be going okay on the query plan until you hit the key lookup, which brought back 13 rows of an estimated 19,452. That would appear to be a pretty doggone bad estimate.

Performance Tuning

Query Exercise Answer: Finding Email Addresses

March 26, 2025

For this week's Query Exercise, your mission had two parts:

Do a first pass search through the Users table looking for columns where people had snuck in email addresses where they didn't belong
Write a more high-quality query to identify exactly which rows we needed to handle

T-SQL & Development

T-SQL Has Regex in SQL Server 2025. Don’t Get Too Excited.

March 19, 2025

Regular expressions are a way of doing complex string searches. They can be really useful, but they have a reputation: they're hard to write, hard to read, and they're even harder to troubleshoot. Once you master 'em, though, they come in handy for very specific situations.

This post isn't about their complexity, though. This post is about Azure SQL DB & SQL Server 2025's regex performance.

Performance Tuning

Query Exercise: Looking for Email Addresses

March 18, 2025

Personally identifiable information (PII) is freakin' everywhere.

When companies first start looking to identify and lock down their data, they think it's going to be as easy as identifying common columns like EmailAddress, DateOfBirth, SocialSecurityNumber, and so forth. They think, "We'll just encrypt those columns and we'll be fine."

T-SQL & Development

Can AI Rewrite Bad Queries in Seconds? Kinda.

January 22, 2025

When I see horrific code, stuff that would take a lot of manual labor to fix, I like to lob it over to an LLM like ChatGPT just to see how it does.

For example, on a call with a client, I opened up one of their slowest pieces of code to find that it was running a cursor, processing data row by row rather than working in sets. To simulate it, I've written up a few stored procedures against the Stack Overflow database:

T-SQL & Development

I Feel Sorry for Untrained Developers Using Entity Framework.

January 8, 2025

Most of the time, I love Entity Framework, and ORMs in general. These tools make it easier for companies to ship applications. Are the apps perfect? Of course not - but they're good enough to get to market, bring in revenue to pay salaries, and move a company forwards.

However, just like any tool, if you don't know how to use it, you're gonna get hurt.

How to Use Github in SSMS v21

December 12, 2024

SQL Server Management Studio v21 added native Git support, making it easier to use source control natively inside SSMS. This feature is for developers who are already used to working with Github to manage specific object changes as they work, and who are already accustomed to Git terminologies like branches, commits, pushes, and pulls. This…

T-SQL & Development

How to Send Email with SQL Server’s sp_send_dbmail

December 10, 2024

So you're writing T-SQL code and you've decided that you want to send emails to customers, employees, or the general public. Perhaps you need to send order updates or low stock notifications.

Stop right there. You don't really wanna do that with SQL Server. Let's talk about why.

Performance Tuning

Updated Training Modules for SQL Server 2019 & 2022

November 21, 2024

At the PASS Summit this month, I presented a 1-day workshop called T-SQL Tuning for SQL Server 2019 & 2022. I just took that material and refactored it into a few different modules for various classes: Mastering Server Tuning - Why Query Monitoring is Hard Now - because I see so many people making bad…

T-SQL & Development

Should That Be One Update Statement or Multiple?

October 15, 2024

Let's say we have a couple of update statements we need to run every 15 minutes in the Stack Overflow database, and we've built indexes to support them:

[crayon-6ab9f01d5fd46059778736/]

Performance Tuning

Yes, Cardinality Estimation Keeps Changing After SQL Server 2014.

September 25, 2024

About 10 years ago, Microsoft made changes to the Cardinality Estimator (CE) which caused some problems for SQL Server upgrades. When folks upgraded to SQL Server 2014, they also casually switched their databases' compatibility level to the latest version, because for years that hadn't really affected query plans. They just figured they wanted the "latest and greatest" compat level, without regard to the effects. That backfired badly when they suddenly got 2014's Cardinality Estimation changes.

T-SQL & Development

TRY/CATCH Doesn’t Always Work.

September 24, 2024

If you're using TRY/CATCH to do exception handling in T-SQL, you need to be aware that there are a lot of things it doesn't catch. Here's a quick example.

Let's set up two tables - bookmarks, and a process log to track whether our stored proc is working or not:

Performance Tuning

Query Exercise Answers: Why Are These 3 Estimates So Wrong?

September 18, 2024

This week's Query Exercise challenged you to figure out why these 3 estimates went so badly:

[crayon-6ab9f01d60757128288000/]

Performance Tuning

Query Exercise: Why Are These 3 Estimates So Wrong?

September 11, 2024

Our prior Query Exercise introduced SQL Server's 201 buckets problem: its inability to accurately estimate rows for more than 201 outliers in a table. I followed up with a solution using filtered statistics to help with the next 200 outliers, and I talked about how that's really overkill for the simple problem we were facing in that initial challenge.

T-SQL & Development

Geographic Sharding is Usually a Pretty Bad Idea.

September 4, 2024

Let's say you built a web app and it became globally popular. You've got users scattered across North America, Europe, and Asia.

In theory, you could shard your data globally: build 3 sets of SQL Servers, one on each continent, and split up their data.

Performance Tuning

Query Exercise Answers: Solving the 201 Buckets Problem

August 28, 2024

In this week's Query Exercise challenge, I explained SQL Server's 201 buckets problem. SQL Server's statistics only handle up to ~201 outliers, which means that outliers ~202-300 get wildly inaccurate estimates.

In our example, I had an index on Location and perfectly accurate statistics, but even still, this query gets bad estimates because Lithuania is in outliers ~202-300:

Performance Tuning

Query Exercise: Solving The 201 Buckets Problem

August 21, 2024

When you run a query, SQL Server needs to estimate the number of matching rows it'll find - so that it can decide which indexes to use, whether to go parallel, how much memory to grant, and more.

For example, take any Stack Overflow database, and let's say I have an index on Location, and I want to find the top-ranking users in Lithuania:

Performance Tuning

Query Exercise Answer: What Makes SELECT TOP 1 or SELECT MAX Different?

August 14, 2024

This Query Exercise was very different: I didn't ask you to solve a particular problem. I pointed out that I've heard advice that SELECT MAX is faster than SELECT TOP 1, and that's not quite true. I asked you to find factors that would cause these two queries to get different execution plans: [crayon-6ab9f01d62b06657251266/] In the…

Performance Tuning

Query Exercise: What Makes SELECT TOP 1 or SELECT MAX Different?

August 7, 2024

These two queries both get the same answer from the Stack Overflow database:

[crayon-6ab9f01d62dd3345409684/]

Performance Tuning

Query Exercise Answer: Fixing a Slow Computed Column

July 24, 2024

In last week's Query Exercise, we added a user-defined function to the Users table to check whether their WebsiteUrl was valid or not. I noted that even with an index on Reputation, SQL Server 2022 simply ignored the index, did a table scan, and spent 2 minutes of time calling the user-defined function on a row-by-row basis.