Category: Parameter Sniffing

Sounds Great

Startup Expression Predicates can be helpful. They may also exacerbate parameter sniffing issues in similar ways to IF branches.

Take a simple example:

CREATE INDEX bud_light ON dbo.Posts(OwnerUserId, Score);
CREATE INDEX coors_light ON dbo.Comments(UserId, Score);
GO 

CREATE 

But It Used To Be Fast

Parameter sniffing gets blamed for a lot of things, and, well, sometimes it is parameter sniffing.

It’s probably not parameter sniffing if:

• You use local variables
• You use optimize for unknown
• You’re recompiling anyway