Elliott W (11/17/2009)
I got the parameter sniffing vibe off that article..
It's not so much parameter sniffing. It's that there's no single optimal execution plan for that kind of query and, because the optimiser has to pick a safe plan, it generally picks a sub-optimal one.
The optimizer knows that if the variable is NULL that is already has a positive result so it doesn't even have to use that in the plan..
Not really. Optimiser doesn't deal well with multiple '@variable is null OR' constructs. Plus it can't (except in 2008 when OPTION RECOMPILE is specified) not use a particular branch in the plan when a parameter is null, because that plan will be cached for reuse and next time the query runs the parameter may not be null. If it made that assumption, the plan would result in incorrect results sometimes. That's not allowed, the plan must always be safe for reuse.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass