In SQL Server Stored Procedures, Parameters In TOP Are Sniffed Too

Thank You For Your Business

In yesterday’s post, we looked at how row goals affected performance when data matching our join and where clauses didn’t exist.

Today we’re going to look at something similar, and perhaps a workaround to avoid the same issues.

Here’s our index setup:

CREATE INDEX whatever ON dbo.Votes(CreationDate DESC, VoteTypeId)
GO

Now we’re gonna wrap our query in a stored procedure.

CREATE OR ALTER PROCEDURE dbo.top_sniffer (@top INT, @vtid INT)
AS
BEGIN

    SELECT   TOP (@top) v.*
    FROM     dbo.Votes AS v
    WHERE    v.VoteTypeId = @vtid
    ORDER BY v.CreationDate DESC;

END;

Video Killed MTV

You know what? I don’t wanna write all this stuff. Let’s do a video.

Thanks for reading!

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.