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 OR ALTER PROCEDURE dbo.sup (@check_posts BIT, @check_comments BIT, @post_score INT, @comment_score INT) AS BEGIN SELECT MAX(ISNULL(p.CreationDate, c.CreationDate)) AS max_date, COUNT_BIG(*) AS records FROM dbo.Users AS u LEFT JOIN dbo.Posts AS p ON @check_posts = 1 AND p.OwnerUserId = u.Id AND p.Score > @post_score LEFT JOIN dbo.Comments AS c ON @check_comments = 1 AND c.UserId = u.Id AND c.Score > @comment_score; END GO
This gives users — and users only — an easy way to get data from certain tables.
This does not give the optimizer a good way of coming up with an execution plan to get or or the other, or both.
Giving a couple test runs:
EXEC dbo.sup @check_posts = 1, @check_comments = 0, @post_score = 100, @comment_score = NULL; EXEC dbo.sup @check_posts = 0, @check_comments = 1, @comment_score = 0, @post_score = NULL;
The first finishes instantly, the second not so instantly.
The problem is a bit easier to visualize in Sentry One Plan Explorer than SSMS, which greys out sections of the query plan that aren’t used.
Four million Key Lookups isn’t my idea of a good time.
If we switch things up, the results are even worse. The bad plan runs for nearly a full minute.
So uh, you know. Be careful out there, when you’re trying to be more cleverer than the optimizerer.
Thanks for reading!