Category: Query Tuning

A Long Time Ago

---

I had to write some hand-off training about query tuning when I was starting a new job.

As part of the training, I had to explain why writing “complicated logic” could lead to poor plan choices.

So I did what anyone would do: I found a picture of a pirate, named him Captain Or, and told the story of how he got Oared to death for giving confusing ORders.

This is something that I unfortunately still see people doing quite a bit, and then throwing their hands up as queries run forever.

I’m going to show you a simple example of when this can go wrong, and also beg and plead for the optimizer team to do something about it.

Big Bully

---

“Write the query in the simplest way possible”, they said.

So we did, and we got this.

SELECT u.Id, MAX(p.Score)
FROM   dbo.Users AS u
JOIN   dbo.Posts AS p
    ON u.Id = p.OwnerUserId
    OR u.Id = p.LastEditorUserId
WHERE  p.PostTypeId IN (1, 2)
GROUP BY u.Id;

Note the OR in the join condition — we can match on either of those columns.

Here’s the index we created to make this SUPERFAST.

CREATE NONCLUSTERED INDEX 36chambers
    ON dbo.Posts ( OwnerUserId, LastEditorUserId, PostTypeId )
    INCLUDE ( Score );

If we’re good DBAs, still doing as we’re told, we’ll read the query plan from right to left.

The first section we’re greeted with is this:

We spend a full minute organizing and ordering data. If you want to poke around, the plan XML is here.

The columns in the Compute Scalars are OwnerUserId and LastEditorUserId.

Next in the plan is this fresh hell:

24 seconds seeking into the Users table and joining that to the results of the Constant Scans, etc.

What’s a little confusing here is that the scan on the Posts table occurs on the outer side of Nested Loops.

It’s also responsible for feeding rows through the Constant Scans. That’s their data source.

Overall, this query takes 1:36 seconds to run.

My gripe with it is that it’s possible to rewrite this query in an obvious way to fix the problem.

Magick

---

Using a second join to Posts clears things up quite a bit.

SELECT u.Id, MAX(p.Score)
FROM   dbo.Users AS u
JOIN   dbo.Posts AS p
    ON u.Id = p.OwnerUserId
	AND p.PostTypeId IN (1, 2)
JOIN   dbo.Posts AS p2
    ON u.Id = p2.LastEditorUserId
	AND p2.PostTypeId IN (1, 2)  
GROUP BY u.Id;

I know, it probably sounds counterintuitive to touch a table twice.

Someone will scream that we’re doing more reads.

Someone else will faint at all the extra code we wrote.

But when we run this query, it finishes in 10 seconds.

This plan does something a bit different. It joins the nonclustered index we have on Posts to itself.

The optimizer has a rule that makes this possible, called Index Intersection.

Extra Magick

---

A more accurate description of what I’d want the optimizer to consider here would be the plan we get when we rewrite the query like this.

SELECT u.Id, MAX(p.Score)
FROM   dbo.Users AS u
CROSS APPLY
    (
        SELECT p.Score
        FROM   dbo.Posts AS p
        WHERE u.Id = p.OwnerUserId
        AND p.PostTypeId IN (1, 2)
        
        UNION ALL 
        
        SELECT p2.Score
        FROM  dbo.Posts AS p2
        WHERE u.Id = p2.LastEditorUserId
        AND p2.PostTypeId IN (1, 2)  
    ) AS p
GROUP BY u.Id;

This query runs a bit faster than the second one (around 7 seconds), and the plan is a little different.

Rather than a Hash Join between the index on the Posts table, we have a Concatenation operator.

The rest of the plan looks like this:

The optimizer has a rule that can produce this plan, too, called Index Union.

Problemagick

---

The thing is, these rules seem to be favored more with WHERE clauses than with JOINs.

CREATE INDEX ix_fangoria
    ON dbo.Posts(ClosedDate);

SELECT COUNT_BIG(*) AS records
FROM dbo.Posts AS p
WHERE p.ClosedDate IS NULL
OR p.ClosedDate >= '20170101'
AND 1 = (SELECT 1);

CREATE INDEX ix_somethingsomething 
    ON dbo.Posts(PostTypeId);

CREATE INDEX ix_wangchung 
    ON dbo.Posts(AcceptedAnswerId);

SELECT COUNT_BIG(*) AS records
FROM dbo.Posts AS p
WHERE p.PostTypeId = 1
AND p.AcceptedAnswerId = 0
AND 1 = (SELECT 1);

Knackered

---

It is possible to get these kind of plans with joins, but not without join hints and a couple indexes.

CREATE INDEX aussie 
    ON dbo.Posts (OwnerUserId, PostTypeId, Score);

CREATE INDEX kiwi 
    ON dbo.Posts (LastEditorUserId, PostTypeId, Score);

SELECT u.Id, MAX(p.Score)
FROM   dbo.Users AS u
JOIN   dbo.Posts AS p
    WITH (FORCESEEK)
    ON u.Id = p.OwnerUserId
    OR u.Id = p.LastEditorUserId
WHERE  p.PostTypeId IN (1, 2)
GROUP BY u.Id;

There’s more background from, of course, Paul White, here and here.

Even with Paul White ~*~Magick~*~, the hinted query runs for ~16 seconds.

If you remember, the Index Intersection plan ran for around 10 seconds, and the Index Union plan ran for around 7 seconds.

This plan uses Index Union:

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Batch Cussidy

---

In the last post, we looked at how SQL Server 2019’s Batch Mode for Row Store could have helped our query.

In short, it didn’t. Not because it’s bad, just because the original guess was still bad.

Without a hint, we still got a poorly performing Merge Join plan. With a hint, we got a less-badly-skewed parallel plan.

Ideally, I’d like a good plan without a hint.

In this post, I’ll focus on more traditional things we could do to improve our query.

I’ll approach this like I would if you gave me any ol’ query to tune.

Here’s what we’re starting with:

    SELECT   p.*
    FROM     dbo.Posts AS p
    JOIN     dbo.Votes AS v
        ON p.Id = v.PostId
    WHERE    p.PostTypeId = 2
    AND      p.CreationDate >= '20131225'
    ORDER BY p.Id;

First Pass

---

I don’t know about you, but I typically like to index my join columns.

Maybe not always, but when the optimizer is choosing to SORT 52 MILLION ROWS each and every time, I consider that a cry for help.

Indexes sort data.

Let’s try that first.

	CREATE INDEX ix_fluffy 
	    ON dbo.Votes(PostId);

This is… Okay.

I’m not saying this plan is great. It’s certainly faster than the Merge Join plan, and the optimizer chose it without us having to hint anything.

It takes 3.6 seconds total. I think we can do better.

Second Pass

---

I wonder if a temp table might help us.

    SELECT   *
	INTO     #p
    FROM     dbo.Posts AS p
    WHERE    p.PostTypeId = 2
    AND      p.CreationDate >= '20131225';

This is… Okay. Again.

The Insert takes 1.8 seconds:

The final select takes 670ms:

Usually this is the point where I’ll stop and report in:

“I’ve spent X amount of time working on this, and I’ve gotten the query from 27 seconds down to about 2.5 seconds. I can do a little more and maybe shave more time off, but I’ll probably need to add another index. It’s up to you though, and how important this query is to end users.”

We could totally stop here, but sometimes people wanna go one step further. That’s cool with me.

Third Pass

---

The insert query is asking for an index, but it’s a dumb dumb booty head index.

Yep. Include every column in the table. Sounds legit.

Let’s hedge our bets a little.

    CREATE INDEX ix_froggy
	    ON dbo.Posts(PostTypeId, CreationDate);

I bet we’ll use a narrow index on just the key columns here, and do a key lookup for the rest.

This time I was right, and our Insert is down to 200ms.

This doesn’t change the speed of our final select — it’s still around 630-670ms when I run it.

Buuuuuuut, this does get us down to ~900ms total.

Final Destination

---

Would end users notice 900ms over 2.5 seconds? Maybe if they’re just running it in SSMS.

In my experience, by the time data ends up in the application, gets rendered, and then displayed to end users, your query tuning work can feel a bit sabotaged.

They’ll notice 27 seconds vs 2.5 seconds, but not usually 2.5 seconds vs 1 second.

It might make you feel better as a query tuner, but I’m not sure another index is totally worth that gain (unless it’s really helping other queries, too).

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Egg Meet Face

---

The other night I was presenting for a user group, and I had a demo break on me.

Not the first time, not the last time. But this was weird because I had rehearsed things that morning.

I skipped over it at the time, but afterwards I started thinking about what had happened, and walking back through other stuff I had done that day.

Turns out, I had fragmented my indexes, and that broke a trivial plan demo.

Just Too Trivial

---

The working demo looks like this.

I run these queries. The top one receives a trivial plan, and doesn’t have a missing index request.

The bottom one gets full optimization, and one shows up.

	/*Nothing for you*/
	SELECT *
	FROM dbo.Users AS u
	WHERE u.Reputation = 2;

	/*Missing index requests*/
	SELECT *
	FROM dbo.Users AS u
	WHERE u.Reputation = 2
	AND 1 = (SELECT 1);

Snap Your Shot

---

How I had broken this demo was by playing with Snapshot Isolation.

At some point earlier in the day, I had done something like this:

ALTER DATABASE StackOverflow2013 
    SET ALLOW_SNAPSHOT_ISOLATION ON;

BEGIN TRAN
UPDATE dbo.Users SET Reputation += 1
ROLLBACK

When you use optimistic isolation levels, a 14 byte pointer gets added to every row to keep track of its version.

If I had run the update without it, it wouldn’t have been a big deal. But, you know.

I’m not that lucky. Or smart.

See, after updating every row in the table, my table got uh… bigger.

Now, if I rebuild the table with snapshot still on, the problem goes away.

The problem is that I didn’t do that before or after my little experiment.

With a heavily fragmented index, both queries not only get fully optimized, but also go parallel.

They’re both a bit faster. They both use a little more resources.

Why? Because SQL Server looked at the size of the table and decided it would be expensive to scan this big chonker.

Egg Wipes

---

Moral of the story: Index fragmentation makes your queries better.

Don’t @ me.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.