Up From The Floor
Now that we’ve covered what happened with our query, how can we fix it?
Remember when I said that this only happens with literals? I sort of lied.
Pants On Fire
Probably the simplest thing to do would be to set MAXDOP to 1 for the query. Avoiding the parallel exchanges avoids the problem, but the query does run longer than the original with a literal TOP. That being said, it may be the simplest solution in some cases for you if it stabilizes performance.
If you’re feeling as brave as Sir Robin, you can add an OPTIMIZE FOR hint to bring back the early-buffer-send behavior.
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 OPTION(OPTIMIZE FOR(@Top = 1)); END; GO
Are they always better? I have no idea, but if you’ve got long-running queries with a parameterized TOP, this might be something worth experimenting with.
Another rewrite that works is slightly more complicated. Though for maximum benefit, Batch Mode is necessary.
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 * FROM ( SELECT *, ROW_NUMBER() OVER( PARTITION BY b.UserId ORDER BY b.Date DESC ) AS n FROM dbo.Badges AS b ) AS b WHERE b.UserId = u.Id AND b.n = 1 ) AS b WHERE u.Reputation >= 10000 ORDER BY u.Reputation DESC; END; GO
So that’s fun. We’re having fun. I like fun.
I’m gonna make a PowerPoint about fun.
Other Things, And Drawbacks
So like, you could add a recompile hint to allow the TOP parameter to be sniffed, sure. But then you’re sort of undoing the good you did parameterizing in the first place.
You could also write unparameterized dynamic SQL, but see above. Same problem, plus a blown out plan cache if people ask for different values.
Optimize for unknown, and OFFSET/FETCH also don’t work.
Of course, one thing that would help here is a more appropriate index leading with UserId. However, most good demos arise from less than ideal indexing, so you’re just going to have to deal with it.
Thanks for reading!