[hobs]

I do a lot of minor performance tuning in mssql, and while I am not any senior professional, I often see parameter sniffing or a cached plan that makes no sense causing an issue when the server thinks its too expensive to create a new plan, and as a result the cardinality estimates are all wrong, the operators are often wrong, and you can see a query that often runs in insignificant time take forever.

As far as I know, the small changes you made just invalidated the plan (as the code wasnt the same) and make the server recompile your query's plan. You can accomplish the same thing by using sp_recompile

Depending on the code, I have fixed this with a few different answers: 1. You can write code that is clearer and simpler for the optimizer, sargeable queries and all that. 2. You can actually (I think in 2012 or later) issue a plan guide so that the query always runs in the way you decide. 3. You can do more hacky things like deciding to recompile the plan each time you run the query, or do a similar thing by assigning all your parameters to local variables, the query optimizer then cant parameter sniff.

You want to learn a little bit about parameter sniffing, because an execution plan that is poor can often be caused by re-using old values to plan the query,