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:

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:

The first finishes instantly, the second not so instantly.

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.

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 *