Hugo Kornelis has a new video:
The video starts with an explanation of two good features: parameter sniffing and plan caching. But those features can interact in an unwanted way, resulting in erratic bad performance. Now we have what I call “bad parameter sniffing”.
Starting at approximately 8:30, I then describe the three most common root causes for bad parameter sniffing: equality filters on a column with a skewed data distribution; inequality filters with varying selectivity; and optional parameters.
Click through for the rest of the synopsis, as well as the video itself.