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.

SQL Server Query Plan
The Times

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.

SQL Server Query Plan
The cached plan was totally unprepared
SQL Server Query Plan
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.

SQL Server Query Plan
teeeeeeeeen million

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.



2 thoughts on “Startup Expression Predicates And Parameter Sniffing In SQL Server

Comments are closed.