Startup Expression Predicates And 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);

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 Times


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.

The cached plan was totally unprepared
It shows when the second query runs

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.

teeeeeeeeen million

So uh, you know. Be careful out there, when you’re trying to be more cleverer than the optimizerer.

Thanks for reading!

2 thoughts on “Startup Expression Predicates And Parameter Sniffing

Leave a Reply

Your email address will not be published. Required fields are marked *