Startup Expression Predicates And Parameter Sniffing In SQL Server

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 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.

ENHANCE

---

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!

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.