When I blogged about passing parameters to TOP, we ran into a problem that has many possible solutions.
Perhaps the least complex answer was just to fix the index. Nine times outta ten, that’s what I’d do.
Since I offered some other possible solutions, I do wanna talk about the pros and cons of them.
In this post, I’ll talk about using slightly less than fully parameterized dynamic SQL, which will, of course, terrify even people who live in Baltimore.
While I’m not smart enough to figure out a SQL injection method without altering the stored procedure, that doesn’t mean it can’t happen.
It might be more difficult, but not impossible. Here’s our prize:
CREATE OR ALTER PROCEDURE dbo.top_sniffer (@top INT, @vtid INT) AS BEGIN DECLARE @sql NVARCHAR(MAX) = N''; SET @sql = @sql + N' SELECT TOP (' + RTRIM(@top) + ') v.Id, v.PostId, v.UserId, v.BountyAmount, v.VoteTypeId, v.CreationDate FROM dbo.Votes AS v WHERE v.VoteTypeId = @i_vtid ORDER BY v.CreationDate DESC; ' PRINT @sql; EXEC sys.sp_executesql @sql, N'@i_vtid INT', @i_vtid = @vtid END;
If we fully parameterize this, we’ll end up with the same problem we had before with plan reuse.
Since we don’t, we can can use a trick that works on filtered indexes.
But There’s Another Downside
Every different top will get a new plan. The upside is that plans with the same top may get reused, so it’s got a little something over recompile there.
So if I execute these:
/*Run me*/ EXEC dbo.top_sniffer @top = 1, @vtid = 4; /*Run me*/ EXEC dbo.top_sniffer @top = 5000, @vtid = 4;
They each get their own plan:
And of course, their own plan cache entry.
If lots of people look for lots of different TOPs (which you could cut down on by limiting the values your app will take, like via a dropdown), you can end up with a lot of plans kicking around.
Would I Do This?
Likely not, because of the potential risk, and the potential impact on the plan cache, but I thought it was interesting enough to follow up on.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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.
- SQL Server 2022 Parameter Sensitive Plan Optimization: The Problem With Sniffed Parameter Sensitivity
- SQL Server 2022 Is Going To Mess Up Your Query Monitoring Scripts
- Defeating Parameter Sniffing With Dynamic SQL In SQL Server
- SQL Server 2022 Parameter Sensitive Plan Optimization: Does PSP Work With Dynamic SQL?