Blog

This week’s query exercise asked you to find two kinds of locations in the Stack Overflow database:

• Locations populated with users who seem to be really helpful, meaning, they write really good answers
• Locations where people seem to need the most help, meaning, they ask a lot of questions, but they do not seem to be answering those of their neighbors

In the challenge post, I gave you the same kinds of requirements that our end users would typically give, but not the kinds of requirements that a business analyst might specify. This challenge was actually inspired by a friend of mine who transitioned into an analyst role, and I was reminded of just how hard that work really is. Translating vague requests into detailed specs is a lot harder than it looks, and the wide variety of this week’s answers reflects that.

Normally when I write these challenges, as soon as I’m done writing the challenge post, I immediately start writing my own solution. I didn’t do that here because I wanted to show your different interpretations of the request. As the answers started coming in, I copy/pasted ’em into SSMS just like everything else I find on the internet, and I used 3 sniff tests:

1. Did your top locations have a decent number of people in them? I didn’t wanna see answers that said, “This podunk town is a location we should focus on.”
2. Did your two sets of locations have overlaps? I didn’t wanna see answers that said, “This place has a lot of people who write questions, and a lot of people who write answers.” That’s just a list of big places, which violates the “they do not seem to be answering those of their neighbors” part of the request.
3. Do the result sets show how they were sorted? Anytime I see a ranked list, I want to know how the data was ranked. (In this particular challenge, I’m really flexible on sorting methods – I just wanna know what it was.)

As an example, I’m showing a picture of one answer’s results here. The person shouldn’t be ashamed by any means – this was a hard question, for sure – but it serves to illustrate an answer that doesn’t pass the sniff test. The top result set are the helpful locations, and the bottom result set are the locations that need help. A single location shouldn’t be in both – and yet the same locations show up in both list. This is just a list of the biggest locations, basically.

This would happen if your queries were just:

• The users who posted the most answers (without posting questions), and
• The users who posted the most questions (without posting answers themselves)

In any big location, you’ll get a lot of those, so that isn’t going to work. Instead, we’re going to need to get more creative with our filtering & sorting.

I’ll discuss two approaches.

I am a potato?’s Answer

I had to laugh out loud at their name on their comments, and they most certainly were not a potato. Here’s their answer in a Github Gist, and my edited version is a comment on theirs. The results will take a little explaining:

The first result is the helpful people, and to find it, they sorted by average answer score descending. This does favor places that seem to favor quality over quantity: note how #6 (Sherbrooke) produced only 326 answers, and #7 (Sunnyvale) produced over 64x more questions – but they’re ranked lower.

That sort order is completely fine – we gotta pick something to sort by, since the users didn’t tell us a specific sort order. This answer is just as valid as anybody else’s, and I wanted to pick a variety of sort orders to show how the request could be interpreted.

However, there’s another problem with line 6: population is only 31 people. If you look at both result sets, they’re both riddled with places with less than 100 people. So, how’d that happen? They started by building a list of locations with >100 people, but didn’t consider whether those people had produced over 100 questions or answers. To fix it, we just need to filter the result sets for >100 population, and then we’ll get better quality.

The second result set is the people who need help, and to find that, they sorted by a high ratio of questions to answers. The ratio isn’t shown in the results, and I would have preferred that, but that’s okay. The general idea makes sense. Again, we have small populations showing up here, and row 9 isn’t really better than row 10, but once we get a filter for at least 100 people in the location, it’ll be better.

Nick’s Answer

NotPotato’s answer averaged out Posts.Score across the entire population of the location. Nick’s answer took a different approach: he got the average answer score for each user in the location (because people may have posted multiple answers with varying scores), and then averaged out all of the users’ averages. As a result, this top 10 looks different than NotPotato’s:

But also, his second result set looks wildly different than NotPotato’s. Part of that is because Nick’s answer correctly filters for large populations, and another reason is because he used a different metric for what helpful means. He used the formula of (AnswerCount – QuestionCount), calling that AnswerDelta. This is interesting because places with higher volumes of answers will be favored, not necessarily tiny locations with tiny metrics (like 8 answers vs 1 question).

Both of those are valid approaches!

Sure, we had some bumps along the way, but I like this kind of challenge, especially for testing potential new hires. I don’t really care if their sorting choices match my own ideas – I’m much more interested in seeing if they can spot obvious bugs in their result sets, and how their T-SQL looks.

I hope you enjoyed this week’s challenge! The Query Exercises will be taking a break for the next couple of weeks because I’m traveling to Hong Kong and Shanghai. I’ve still got plenty of blog posts queued up, but the interactive exercises will return in April when I’m back. If you’re starving for homework, check out the prior Query Exercises posts and catch up on the ones you missed so far.

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:

So if I’m looking for that date range, here’s the query I came up with:

You might have written a slightly different query, so of course your homework may start to diverge from mine. More importantly, EVERYTHING on your system might be different from mine, so the rest of our homework is going to diverge a LOT. Here’s how my system is configured:

• SQL Server 2022 Developer Edition
• 2018-06 Stack Overflow database in 2022 compatibility level
• Cost Threshold for Parallelism 50 (and changing that, changes the query plans a LOT in this example)

Question 2: Does It Perform?

The second part of our challenge was to compare the number of logical reads that our query does, versus the number of reads it would take to just scan the whole table. Let’s find out:

The results:

Redmond, we have a problem. Our query is doing more reads than scanning the entire table ten times over! What the hell does my execution plan look like?

SQL Server is seeking on Reputation, and then scanning backwards from high values down to low. Check out those estimated-versus-actual numbers on the index seek:

SQL Server thought, “I’m going to scan from high reputations to low. There are a LOT of people with Reputation > 50, and there are a LOT of users created in the last 90 days, so I bet I won’t have to check too many CreationDates on the key lookups before I’ve found 1000 users that match.”

The core of the problem is that:

• There are a lot of users with Reputation > 50
• There are a lot of users with CreationDate > ‘2018-06-03 05:18:45.117’
• But SQL Server doesn’t know there isn’t a lot of overlap in those two groups.

Fun Trivia About Parallelism

This query plan doesn’t get parallelism because SQL Server doesn’t use parallelism for backwards range scans, something I learned from Paul White’s excellent post. To see the scan direction, right-click on the Index Seek operator and click Properties, and check out the scan direction:

Neato mosquito. Anyway, moving on.

Question 3: Making It Perform Better

The core of this week’s problem is cardinality estimation: SQL Server doesn’t understand that there’s very little overlap in the Venn diagram’s circles for high Reputation and recent CreationDates. Your first instinct is probably to solve that problem, but let’s step back for a second. If a table scan performs better than using an index…

By adding an INDEX = 1 hint, we can force a table scan, and cut our logical reads by 10X. (And for bonus points, we even get parallelism.)

The drawback to this solution is that if the indexes change later, and a better index presents itself, our query won’t be able to use it. But hey, the whole point of this exercise was that we weren’t able to change the indexes. Sometimes at clients, I’m dealing with very mature databases with a core set of indexes that are needed to support a workload, and we can’t juggle indexes just to fix a single SQL Server estimation problem. In that case, Skeletor wins.

In Case You’re Not a Skeletor Fan

We do have indexes on CreationDate and Reputation. What if we have SQL Server find the people who match each filter individually, and then find their overlaps first before doing the key lookups? That’s the solution Peter Kruis went for:

The first part of his query plan is the CTE, which finds the reputation and creationdate people separately:

Note the estimates vs actuals on the join of the two result sets. SQL Server thought there was a lot more overlap in the Venn diagram: SQL Server expected to find 45301 rows, but only found 2644. But here’s the cool part: it doesn’t matter that the estimation is wrong here, because there are no key lookups here to get screwed up, no memory grant problems, etc.

Peter’s rewrite drops down to about 10,000 logical reads – about 14x better than Skeletor’s approach, and about 200x better than SQL Server’s original approach.

Peter rewrote the query with the knowledge of the indexes that were present on the database. Peter’s approach works well because there are separate indexes on Reputation and CreationDate. If a single covering index got added in later, Peter’s approach wouldn’t be able to leverage it to perform well – but that’s okay, after all, because remember that we said we can’t touch indexes in this challenge. This is a good, typical approach I use in mature databases where I can rely on the indexes to stay fairly stable over time.

In last week’s Query Exercise, our developers had a query that wasn’t going as fast as they’d like:

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:

1. Why is SQL Server’s estimated number of rows here so far off? Where’s that estimate coming from?
2. Can you get that estimate to be more accurate?
3. Can you get the logical reads to drop 100x from where it’s at right now?

Let’s get our learn on.

1. The estimate is hard-coded.

When SQL Server is just making up an estimate out of thin air, I really wish it would put a yellow bang on the execution plan. “Danger: I’m just pulling this number out of my back end.” Because when you’re looking at the query plan:

The number 2675250 looks awfully scientific. It looks like SQL Server put a lot of thought into that number. In reality…

It’s just a hard-coded 30% of the table. It doesn’t matter whether you’re looking for LEN > 35, or LEN < 35, or LEN > 10000. SQL Server just says, “You’re doing an inequality comparison here – 30% sounds good.” To learn more about these not-so-magic hardcoded numbers, check out Dave Ballantyne’s cardinality estimation sessions.

2. The estimate can indeed be improved.

SQL Server has statistics on the DisplayName column’s contents, alphabetically sorted, but not sorted by length. If we want statistics on the length of a column’s contents, we have to create a computed column focused on that length. This is gonna sound weird, but:

Add a computed column, and then rerun the query. Suddenly, the execution plan is better:

ENHANCE!

Three things changed:

• The estimated number of rows is way more accurate – down from the hard-coded 30% (2,675,250) to just 2,986
• Because the estimate was more accurate, SQL Server understood that it wouldn’t have to do so many key lookups, so it scanned the DisplayName index, calculating the length of each one
• Our logical reads dropped from ~142K down to ~40K – good, but still not 100X better

The estimate is better because SQL Server created a statistic on the computed column:

The estimate isn’t perfect because SQL Server created the stats in a hurry, only sampling a small percentage of the table’s data. If we updated those statistics with fullscan, we’d get absolutely perfect estimates – but we don’t need that here. We’ve done enough of an improvement to answer the question, which leads us to…

3. Getting a 100X Drop in Page Reads

The execution plan is still scanning the entire DisplayName index, calculating the length of each name, because the index on DisplayName is arranged alphabetically – not by length. Is there a way we can seek directly to the long DisplayNames? Indeed, there is – by indexing our newly created computed column.

The new execution plan is amazeballs:

We didn’t even have to change our query! SQL Server figured out that there’s an index sorted by the length of DisplayName, and seeked into just the long rows. This new plan is better in so many ways, but most importantly for the purposes of this challenge, we’re down to just 682 reads – an improvement of over 100x. Awww yeah.

There’s even a solution that gets the number of reads down to just 6! For that, check out Vlad Drumea’s post, Finding Long Values Faster. On Enterprise Edition, it works with no query changes, and has extremely low overhead.

Summary: 100x better, no code changes.

Computed columns, especially when indexed, can be a great way to make non-sargable queries go way faster. They’re certainly not the first tool I reach for – they have plenty of drawbacks and gotchas – but that’s why they’re covered in my Mastering Index Tuning class rather than the Fundamentals one.

Do I wish the developers would change the way the app works? Of course – but as a consultant, clients love me the most when I can simply leverage a built-in database tool and make things go faster right away. This is one of those awesome tools. Sure, like a chainsaw or a nail gun, you need to know what you’re doing – but you’re a smart cookie. You know which classes to attend.

Your challenge for last week was to take this Stack Overflow database query to show the top-ranking users in the most popular location:

And make it read less pages only by tuning the query? You weren’t allowed to make index or server changes, and you weren’t allowed to hard code the location in the query since it might change over time.

The Core of the Problem

The main problem is that when we run a statement (like SELECT), SQL Server:

1. Designs the execution plan for the entire statement all at once, without executing anything, then
2. Executes the entire statement all at once, with no plan changes allowed

Think of it as two different departments, neither of which are allowed to do the job of the other. The design department sketches out the query plan, but it has no idea of what this part of the query will produce:

You might think, “Well, doesn’t SQL Server have statistics on Location? After all, we have an index on Location, right?” That’s true, and here are the stats:

In my histogram, the table has 8.9 million rows, and NULL is 6.8 million of them. Easy then, right? Our top location is null! Use the 6.8M row estimate. Except… look at the WHERE clause of our query again:

We’re filtering out the nulls and empty strings. To get that estimate right, SQL Server would have to execute the query by evaluating the where clause, doing the group by, doing the order by, etc. That’s way, way beyond what the query optimizer will do.

The design department has to design a query plan without knowing the output of the CTE. It simply doesn’t know what the top location will be, so it makes a guess:

And of course that 14-row guess is nowhere near the 49,358 people who actually live in the top location. That’s fair: if the design department is forced to make a guess without being allowed to execute the query, then the estimate is going to be hot garbage.

The Core of the Solution

If the problem is:

1. Designs the execution plan for the entire statement all at once, without executing anything, then
2. Executes the entire statement all at once, with no plan changes allowed

Then the solution is to break the statement up into multiple statements. In pseudocode:

1. Find the top location, then
2. Find the users who live in that location, after we know what the location is

But it’s not enough to just do them in two statements. If I just do this:

Then the second query in the batch still has a crappy plan with bad estimates:

Because SQL Server doesn’t just build plans for a statement all at once, it builds plans for the entire stored procedure all at once, too. So the real core of the solution is:

1. Find the top location, then
2. Go back to the design department for a new plan to find the users who live in that location, after we know what the location is

The Easiest Solution: Option Recompile

One of the easiest-to-implement methods is to slap an OPTION (RECOMPILE) on the second statement in the newly fixed proc:

That tells SQL Server to go back to the design department at that specific point in the stored procedure, and build a new execution plan based on what we’ve learned so far. At that point in the plan, the @TopLocation’s value has been set, so instead of a crappy 15-row estimate, SQL Server estimates the exact 49,358 rows that’ll come back, as shown in the new plan:

The new plan has more accurate estimates, which drives:

• Adding parallelism into the plan
• Higher memory grant estimates for the sort, avoiding spills
• A clustered index scan instead of using the index on Location

You might think that last one is a problem, but it’s actually good, because we’re doing less logical reads than we did before:

• Original query: 160,419 logical reads
• Two statements: 160,419 logical reads
• Recompile: 155,726 logical reads

Option recompile has a bad reputation amongst DBAs because it does add additional CPU time to compile a new execution plan every time that the statement runs. Odds are, the top location isn’t changing all that often – so wouldn’t it be cooler if we could cache a plan for a longer period of time?

Harder, But Better: Caching a Plan

Ideally, we need SQL Server to sniff the most popular location, build a plan for it, and then cache that plan. One way to do that is by using dynamic SQL:

Now, when SQL Server goes to execute the entire stored procedure, it builds the execution plan for the entire stored procedure. Here’s what it thinks:

• “I’m gonna declare a variable, @TopLocation.”
• “I’m gonna set the contents of that variable with this here query, so I’ll build a plan for that.”
• “I’m gonna declare another variable, @StringToExec, and set its contents to a string.”
• “I’m gonna call sp_executesql with the contents of that string.”

But when it designs this execution plan, it doesn’t build the plan for what sp_executesql is gonna do. That’ll be done later, at the time sp_executesql is called. When sp_executesql is called, SQL Server will be spoon-fed the contents of @TopLocation – so parameter sniffing works in our favor! The plan will only be built once and the plan will stick around as long as practical in the plan cache.

A similar solution would be to break out the SELECT TOP 200 query into a separate child stored procedure, and that would have the same effect.

One drawback of both of those solutions: they’re not appropriate under this circumstance:

• The @TopLocation keeps changing, and
• The @TopLocation has dramatically different numbers of people who live in it, and
• The statistics on the Users table don’t change quickly enough to keep up with those changes, and
• Our plan cache is stable enough to keep the original plan around

But that’s quite unlikely.

How’d you do? What’d you think?

I hope you had fun, enjoyed this exercise, and learned a little something. If you’ve got comments or questions, hit the comment section below.

I know some folks are going to say, “But why didn’t you cover temp tables?” The problem with temp tables is that they’re like OPTION (RANDOM RECOMPILE), as I discuss in my Fundamentals of TempDB class and Paul White discusses in his temp table caching series. They’ll work great in standalone testing, and then at scale, your users will keep asking why they’re getting someone else’s execution plan.