SQL Server MVP Deep Dives, Volume 2

Category: Programming
Author: Kalen Delaney, Louis Davidson, Greg Low, Kimberly Tripp
This Month Stack Overflow 1


by anonymous   2019-01-13

Add OPTION (RECOMPILE) to the end

... AND SensorValue != -32767 OPTION (RECOMPILE) 

I suspect you are experiencing "parameter sniffing"

If that's the case we can leave it with the OPTION or consider alternatives

Update 1

The following article will introduce you to "parameter sniffing" http://pratchev.blogspot.be/2007/08/parameter-sniffing.html

I advice that you get to know the ins and out because it will make you much better in understanding sql server internals (that can bite).

If you understand it you will know that the tradeoff with option recompile can be a performance decrease if the statement is executed very often.

I personally add option recompile after I know the root cause is parameter sniffing and leave it in unless there is a performance issue. Rewriting a statement to avoid bad parameter sniffing leads to loss of intent and this lowers maintainability. But there are cases when the rewrite is justified (use good comments when you do).

Update 2

The best read I had on the subject was in chapter 32 called "Parameter sniffing: your best friend... except when it isn't by " by GRANT FRITCHEY

It's recommended.

SQL Server MVP Deep Dives, Volume 2