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);
CREATE OR ALTER PROCEDURE dbo.sup (@check_posts BIT, @check_comments BIT, @post_score INT, @comment_score INT)
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;
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!