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
Time will tell.
Though I prefer to call local variables an “Optimize for mediocre” plan hint.