T-SQL Archives - Page 3 of 7 - Brent Ozar Unlimited®

Performance Tuning

Query Exercise: Improving Cardinality Estimation

February 8, 2024

Your challenge for this week is to tune a query. Say Stack Overflow has a dashboard that shows the top-ranking users in their most popular location. It's even got an index to support it:
[crayon-6ab740ce9ec0c096659709/]
You can test it with any version of the Stack Overflow database. To test it, we'll turn on a couple of tuning options:
[crayon-6ab740ce9ec18575956886/]
The actual execution plan does use our index - not just once, but twice:

Performance Tuning

Database Changes to Find Tagged Questions Faster: Answers and Discussion

February 7, 2024

In the last Query Exercise discussion, we hit a performance wall when we were trying to quickly find questions with a specific tag. We threw up our hands and said it's time to make database changes, but we had a few restrictions: We have a lot of existing code that uses the Posts.Tags column That…

Performance Tuning

Finding Tagged Questions Faster: Answers & Discussion

January 31, 2024

Your query exercise was to take this Stack Overflow query to find the top-voted questions for any given tag:
[crayon-6ab740ceaac16956138466/]
That's currently using this index in its execution plan:
[crayon-6ab740ceaac1d938030245/]
And answer 3 questions:

What kinds of tags will perform worse than others for this query?
Could you change the query to perform better?
Could you change the indexes to perform better, without changing the table structure?

Performance Tuning

Query Exercise: Find Tagged Questions Faster.

January 25, 2024

For this week's Query Exercise, we're going to start with an existing query that has performance problems from time to time, depending on how it's used.

This query's goal is to find the top-voted Stack Overflow questions for any given tag - for example, here are the top-voted questions tagged "sql-server".

Performance Tuning

Finding the Best Time for Maintenance: Answers & Discussion

January 24, 2024

Your Query Exercise was to find the best time to do database maintenance by querying the Users table, looking for a one-hour window with the lowest number of created users. We kept this one pretty simple by looking at the data in just one table, and we didn't hassle with time zones. We just wanted the 3 lowest-load hours, in this format:

Performance Tuning

Query Exercise: Find the Best Time for Maintenance

January 18, 2024

If we've gotta take the database down for maintenance - perhaps a version upgrade, perhaps upgrading our own code, maybe scaling up the hardware - when's the best time to do it?

For this week's query exercise, the business has asked us to identify 3 1-hour periods with the least user activity, in this format:

Performance Tuning

Find Posts with the Wrong CommentCount: Answers & Discussion

January 17, 2024

Your Query Exercise was to find denormalization accuracy problems: checking the accuracy of a reporting column, Posts.CommentCount. There were two parts: finding the top 100 most problematic Posts with the biggest variances, and thinking about a long term solution to keep the CommentCount accuracy as high as practical. Question 1: Finding the Problematic Posts Your…

Performance Tuning

Query Exercise: Find Posts with the Wrong CommentCount

January 11, 2024

For 2024, I'm trying something new: weekly homework challenges! For this week's query challenge, we're going to do some data validation. Pop open the Stack Overflow database and check out the CommentCount column on the Posts table: In theory, that's a fast way to check how many Comments a particular Post has, without querying the…

Performance Tuning

Find Foreign Key Problems: Answers & Discussion

January 10, 2024

Your Query Exercise was to find foreign key problems: data that might stop us from successfully implementing foreign keys in the Stack Overflow database.

Question 1: Write queries to find the problematic data.

Performance Tuning

Query Exercise: Find Foreign Key Problems

January 4, 2024

For 2024, I'm trying something new: weekly homework challenges! For this week, let's say we've decided to implement foreign keys, and we need to find data that's going to violate our desired keys.

We're going to use the Stack Overflow database, and we'll focus on these 3 tables:

T-SQL & Development

FOR XML PATH Changed The Way I Think About T-SQL #TSQL2sday

July 11, 2023

The first time I saw FOR XML PATH being used to generate a comma-delimited list, I think I stared at it, shook my head to clear the cobwebs, stared at it some more, and then closed the code editor thinking it was complete witchcraft.

And that same thing probably happened the next several times, too.

T-SQL & Development

Can You Nest Transactions in SQL Server?

February 28, 2023

To find out, let's set up a simple status log table:
[crayon-6ab740ceacd07995831138/]
And then let's try a two-part transaction:
[crayon-6ab740ceacd0e569992172/]
Right now, SQL Server shows that I have 2 open transactions:

What Happens If I Roll Back?
But what does "2 open transactions" mean, really? If I do a rollback, what gets rolled back? Let's find out:
[crayon-6ab740ceacd10449611582/]
The results:

T-SQL & Development

Find 40 Problems in This Stored Procedure.

February 24, 2023

Aaron Bertrand posted a challenge:
We’re going to use the AdventureWorks sample database (get your copy here), where the folks in marketing requested a list of users to e-mail a new promotional campaign. The customers need to meet at least one of the following criteria:

last placed an order more than a year ago
placed 3 or more orders in the past year
have ordered from a specific category in the past two weeks

3 Ways to Debug T-SQL Code

January 10, 2023

Writing new code = bugging. That part's easy.

Taking those bugs back out, that's the hard part.

T-SQL & Development

How to Find Missing Rows in a Table

January 5, 2023

When someone says, "Find all the rows that have been deleted," it's a lot easier when the table has an Id/Identity column. Let's take the Stack Overflow Users table: It has Ids -1, 1, 2, 3, 4, 5 ... but no 6 or 7. (Or 0.) If someone asks you to find all the Ids…

T-SQL & Development

Should You Use SQL Server 2022’s STRING_SPLIT?

December 21, 2022

SQL Server 2022 improved the STRING_SPLIT function so that it can now return lists that are guaranteed to be in order. However, that's the only thing they improved - there's still a critical performance problem with it. Let's take the Stack Overflow database, Users table, put in an index on Location, and then test a…

T-SQL & Development

Should You Use SQL Server 2022’s DATETRUNC?

December 20, 2022

SQL Server 2022 introduced a new T-SQL element, DATETRUNC, that truncates parts of dates. For example:
[crayon-6ab740ceaffdf372115821/]
Truncates everything in that date other than the year, so it returns just 2017-01-01 00:00:

You might ask, "Well, why not just use YEAR()?" That's a good question - there are times when you need a start or end date for a date range, and this could make it easier than trying to construct a full start & end date yourself.

T-SQL & Development

Slow “Having” Query? Try Pre-Filtering.

November 29, 2022

I was helping a client with a query, and I'm going to rework the example to use the Stack Overflow database for easier storytelling.

Say we need to:

T-SQL & Development

#tsql2sday: Start Your Dynamic SQL with a Comment.

October 11, 2022

When you write dynamic SQL, start like this:
[crayon-6ab740ceb0ecb914200436/]
Right after the SELECT (or INSERT or UPDATE or whatever), immediately put a comment - using /*, of course, because you're not a terrible person.

That way, when you're looking at the plan cache or monitoring tools, you can see what generated the dynamic SQL, and where you need to go if you need to performance tune or fix it.

T-SQL & Development

[Video] Fundamentals of Stored Procedures at SQLBits

July 27, 2022

Anybody can write a stored procedure with a little help from Google. This session is about how to write stored procedures that have a high likelihood of performing well and are easy to troubleshoot.

This fast-paced, all-demo session from SQLBits will NOT cover how to write a query, syntax, or performance tuning. This is about good best practices after you've written the first one - things like how to catch errors, how to pass in multiple values, how to debug without the debugger, and more.