A Parameterization Puzzle With TOP: Part 1

The Gift That Keeps On Giving


I love when a demo written for one purpose turns into an even better demo for another purpose.

While working with a client recently, they ran into a performance issue when trying to promote plan reuse by parameterizing the user-input number for TOP.

In part 1, I’m going to show you what happened and why, and in part 2 I’ll discuss some workarounds.

Regresso Chicken Face Soup


When executed with a literal value in the top, this query runs for around 10 seconds.

I’m not saying that’s great, but it’s a good enough starting place.

SELECT TOP (38)
        u.DisplayName,
        b.Name
FROM dbo.Users u
CROSS APPLY 
(
    SELECT TOP (1) 
            b.Name
    FROM dbo.Badges AS b
    WHERE b.UserId = u.Id
    ORDER BY b.Date DESC
) AS b
WHERE u.Reputation >= 10000
ORDER BY u.Reputation DESC;
glamping

If we take that same query, put it in a proc, and run it with an identical value in the TOP, things will turn out not-so-well.

CREATE OR ALTER PROCEDURE dbo.SniffedTop (@Top INT)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;

    SELECT TOP (@Top)
            u.DisplayName,
            b.Name
    FROM dbo.Users u
    CROSS APPLY 
    (
        SELECT TOP (1) 
                b.Name
        FROM dbo.Badges AS b
        WHERE b.UserId = u.Id
        ORDER BY b.Date DESC
    ) AS b
    WHERE u.Reputation >= 10000
    ORDER BY u.Reputation DESC;

END
GO 

EXEC dbo.SniffedTop @Top = 38;

The query runs for a significantly longer amount of time.

half-day

What Happened?


Unofficially, when TOP uses a constant and the constant is “small” (under 101), the exchange packets are allowed to send earlier than normal, as long as the exchange is below the TOP operator. They’re allowed to send as soon as they have a row, rather than waiting for them to fill up completely.

This can only happen with constants (or…!), and the behavior is true going back to 2005. It may change in the future, so if you’re reading this at some far off date, please don’t be too harsh on me in the comments.

When you parameterize TOP, it’s considered unsafe to send the exchange buffers early. After all, you could stick anything in there, up through the BIGINT max. In cases where you’ve got a BIG TOP, sending, say, 9,223,372,036,854,775,807 rows one at a time would be significantly ickier than sending over a smaller number of full exchange buffers.

If you’re surprised to hear that parallel exchange buffers can send at different times, you’re not alone. I was also surprised.

SQL Server: Full Of Surprises. Horrible surprises.

In the second query, where exchange buffers are sent when full, we spend a lot longer waiting for them to fill up. This isn’t exposed anywhere in the plan, and you’d need either a debugger or this blog post to figure it out.

Yep.

Yep. Yep. Yep. Yep. Yep. Yep.

Thanks for reading!



12 thoughts on “A Parameterization Puzzle With TOP: Part 1

  1. Really interesting…

    In the second plan, the number of rows read in the Badges PK scan is actually quite higher. is this related to the packets being sent later? how so?

    Thanks Erik

  2. Why not “tune” the original query before turning it into a SP? I created a simple index on Userid in the Badges table and the SP is smoking fast. I really abhor query plans that have Clustered index scans unless there is no other way.

      1. Got it.

        If the original query had a top of 200 (for example) then then the SP and the query would perform the same (badly). It is just that the SP doesn’t know the value ahead of time.

        I’ll wait to see your workarounds tomorrow.

        1. The original query gets way slower with just a top 39. You don’t have to go up to 200. But it’s for a different reason.

          This is all based on a much more complicated scenario — it’s just the simplest demo I could come up with to show the behavior.

          1. Yes, I see the difference between 38 and 39. Emptying the plan cache gives different plans for Top 38 and Top 39. Do you have a reference where I can read up on why that would be that way? (using 39 adds an Eager Spool to the plan)

  3. “…you’d need either a debugger or this blog post to figure it out…”

    As usual, you ARE our debugger and we all appreciate it.

    Thanks, Erik

Leave a Reply

Your email address will not be published. Required fields are marked *