Silent and Grey
In yesterday’s post, we looked at plans with a good index. The row number queries were unfortunate, but the MAX and TOP 1 queries did really well.
Today, I wanna see what the future holds. I’m gonna test stuff out on SQL Server 2019 CTP 3.1 and see how things go.
I’m only going to hit the interesting points. If plans don’t change, I’m not gonna go into them.
Query #1
With no indexes, this query positively RIPS. Batch Mode For Row Store kicks in, and this finishes in 2 seconds.
SELECT u.Id,
u.DisplayName,
u.Reputation,
ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
SELECT MAX(Score) AS Score
FROM dbo.Posts AS p
WHERE p.OwnerUserId = u.Id
) AS ca
WHERE u.Reputation >= 100000
ORDER BY u.Id;
With an index, we use the same plan as in SQL Server 2017, and it finishes in around 200 ms.
No big surprise there. Not worth the picture.
Query #2
Our TOP 1 query should be BOTTOM 1 here. It goes back to its index spooling ways, and runs for a minute.
SELECT u.Id,
u.DisplayName,
u.Reputation,
ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
SELECT TOP (1) p.Score
FROM dbo.Posts AS p
WHERE p.OwnerUserId = u.Id
ORDER BY p.Score DESC
) AS ca
WHERE u.Reputation >= 100000
ORDER BY u.Id;
With an index, we use the same plan as in SQL Server 2017, and it finishes in around 200 ms.
No big surprise there. Not worth the picture.
I feel like I’m repeating myself.
Query #3
This is our first attempt at row number. It’s particularly disappointing when we see the next query plan.
SELECT u.Id,
u.DisplayName,
u.Reputation,
ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
SELECT p.Score,
ROW_NUMBER() OVER (ORDER BY p.Score DESC) AS n
FROM dbo.Posts AS p
WHERE p.OwnerUserId = u.Id
) AS ca
WHERE u.Reputation >= 100000
AND ca.n = 1
ORDER BY u.Id;
On its own, it’s just regular disappointing.
Serial. Spool. 57 seconds.
With an index, we use the same plan as in SQL Server 2017, and it finishes in around 200 ms.
No big surprise there. Not worth the picture.
I feel like I’m repeating myself.
Myself.
Query #4
Why this plan is cool, and why it makes the previous plans very disappointing, is because we get a Batch Mode Window Aggregate.
SELECT u.Id,
u.DisplayName,
u.Reputation,
ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
SELECT *
FROM
(
SELECT p.OwnerUserId,
p.Score,
ROW_NUMBER() OVER (PARTITION BY p.OwnerUserId
ORDER BY p.Score DESC) AS n
FROM dbo.Posts AS p
) AS p
WHERE p.OwnerUserId = u.Id
AND p.n = 1
) AS ca
WHERE u.Reputation >= 100000
ORDER BY u.Id;
It finishes in 1.7 seconds. This is nice. Good job, 2019.
With the index we get a serial Batch Mode plan, which finishes in about 1.4 seconds.
If you’re confused about where 1.4 seconds come from, watch this video.
Why Aren’t You Out Yet?
SQL Server 2019 did some interesting things, here.
In some cases, it made fast queries faster.
In other cases, queries stayed… exactly the same.
When Batch Mode kicks in, you may find queries like this speeding up. But when it doesn’t, you may find yourself having to do some good ol’ fashion query and index tuning.
No big surprise there. Not worth the picture.
I feel like I’m repeating myself.
Myself.
Myself.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.