Indexing and Statistics Archives - Page 5 of 29

Performance Tuning

Does Your GROUP BY Order Matter?

March 20, 2024

Sometimes when you do GROUP BY, the order of the columns does matter. For example, these two SELECT queries produce different results:
[crayon-6aae14b6454fb778254010/]
Their actual execution plans are wildly different:

They both use the index to retrieve their data, sure, but:

[Video] Office Hours: Speed Round

March 14, 2024

Not all of the questions y'all post at https://pollgab.com/room/brento require long-winded answers. Let's conquer 22 questions in 15 minutes!

https://www.youtube.com/watch?v=XGvqkIRwupk

Performance Tuning

Find Recent Superstars: Answers & Discussion

February 28, 2024

Your query exercise for this week was to write a query to find users created in the last 90 days, with a reputation higher than 50 points, from highest reputation to lowest. Because everyone's Stack Overflow database might be slightly different, we had to start by finding the "end date" for our query. I'm working with the 2018-06 export that I use in my training classes, so here's my end date:

[Video] Office Hours: Hot Tub Edition

February 23, 2024

Today's Office Hours comes from a very different office! I climbed into the hot tub to review your top-voted questions from https://pollgab.com/room/brento. Don't worry, the webcast is completely safe for work.

https://www.youtube.com/watch?v=e1jg1_uHBxM

Performance Tuning

Query Exercise: Find Recent Superstars

February 22, 2024

For this week's Query Exercise, we're working with the Stack Overflow database, and our business users have asked us to find the new superstars. They're looking for the top 1000 users who were created in the last 90 days, who have a reputation higher than 50 points, from highest reputation to lowest. In your Stack…

Performance Tuning

Finding Long Values Faster: Answers & Discussion

February 21, 2024

In last week's Query Exercise, our developers had a query that wasn't going as fast as they'd like:
[crayon-6aae14b64fd8d332353954/]
The query had an index, but SQL Server was refusing to use the index - even though the query would do way less logical reads if it used the index. You had 3 questions to answer:

Performance Tuning

Query Exercise: Finding Long Values Faster

February 15, 2024

Our developers have come to us with a problem query that isn't as fast as they'd like. Using any Stack Overflow database:
[crayon-6aae14b65008e605878396/]
It has an index, but SQL Server refuses to use that index in the execution plan:

If we force the index with a query hint, we do indeed get dramatically lower logical reads. In my particular database's case, the clustered index scan is 141,573 logical reads - but scanning the DisplayName index alone is just 38,641 logical reads.

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-6aae14b6507e2962713521/]
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-6aae14b6507e8467515838/]
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-6aae14b652855729392948/]
That's currently using this index in its execution plan:
[crayon-6aae14b65285a809550435/]
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".

[Video] Office Hours: The Question is Not the Problem Edition

January 19, 2024

I answer your top-voted Azure SQL DB and SQL Server questions from https://pollgab.com/room/brento and lose my temper about what you're doing.

https://youtu.be/64e2r7sn130

Read more Be the first to comment

[Video] Office Hours: Ask Me Anything About the Microsoft Data Platform

January 12, 2024

Forgive the hoarse voice - I'm recovering from something COVID-ish. I went through your top-voted questions from https://pollgab.com/room/brento and did my best:

https://youtu.be/x-x4j_VND-s

Read more Be the first to comment

[Video] Office Hours: Holiday Speed Round Edition

December 29, 2023

Most of the https://pollgab.com/room/brento questions from this episode had really short and sweet answers. Let's take a break for the holidays and knock 'em out:

https://youtu.be/nhB-dT8_VTg

[Video] Office Hours: 21 Good Questions

December 21, 2023

This one is a 3-part episode: I take 21 questions from https://pollgab.com/room/brento and then later, work on First Responder Kit pull requests.

https://youtu.be/MNwCDQX8zl4

[Video] Office Hours: Sunshine Edition

December 14, 2023

You know how this works: you posta the questions at https://pollgab.com/room/brento, and I giva the answers:

https://youtu.be/o1pDHHDS4R0

[Video] Office Hours: Really Good Questions Edition

December 13, 2023

Y'all were on fire in this one! You posted great questions at https://pollgab.com/room/brento and I worked through 'em:

https://youtu.be/JDTS5KnqewA

Read more Be the first to comment

[Video] Office Hours: Live on Black Friday Edition

November 29, 2023

On Black Friday, I took a break from answering support emails to hit your top-voted questions from https://pollgab.com/room/brento.

https://youtu.be/ridbqcclXuY

[Video] Office Hours: Brent’s Cursed Technology Edition

November 23, 2023

I first answered these while standing at a harbor in Maine, but upon arriving home, I realized my camera had failed to record any audio. Doh! So I took the list of questions from https://pollgab.com/room/brento and streamed 'em from my home office in Vegas instead.

https://youtu.be/IwAz7lCZK_Y

Read more Be the first to comment

[Video] Office Hours in Camden Harbor

November 22, 2023

On an overcast morning in scenic Camden, Maine, I stood on the docks and took your top-voted questions from https://pollgab.com/room/brento.

https://youtu.be/OFRjC6D7vTs