Erik Darling has some mixed news. First up, if you branch a lot:
I’ve spent a bit of time talking about how IF branches can break query performance really badly in SQL Server.
While the Parameter Sensitive Plan (PSP) optimization won’t fix every problem with this lazy coding habit, it can fix some of them in very specific circumstances, assuming:
– The parameter is eligible for PSP
– The parameter is present across IF branches
Less sanguine news if you use local variables a lot:
One fix I’ve been wishing for, or wish I’ve been fixing for, is a cure for local variables. I’d even be cool if Forced Parameterization was that cure, but you know…
Time will tell.
Though I prefer to call local variables an “Optimize for mediocre” plan hint.