Last, but not least, here’s one final look at parameter sniffing.
In part 1 of this mini-series I wrote about data skew and the problems it can cause with parameters. In part 2, I looked at what happens when the optimiser can’t sniff values. In this post, I’m going to look at a third thing that can cause parameter sniffing problems. It’s fairly obvious once you know about it, but the first time it was pointed out to me I was a little surprised.
So, to recap. When the query optimiser gets a stored procedure to compile, it knows the values of any parameter passed to that procedure. It will then compile the procedure and optimise the queries based upon the value of those parameters. The optimiser cannot sniff the values of variables, because the values of the variables have not been set at the time that the procedure is optimised.
I’m going to use the same sample code and data as in the first article, to keep things consistent.
From the tests that were done before, I know that the query
select * from largetable where somestring = 'zzz'
executes optimally with an index seek and returns 9 rows. Likewise, I know that the query
select * from largetable where somestring = 'abc'
executes optimally with a clustered index scan and returns 1667 rows.
Now, let’s see if I can get the optimiser to make the wrong choice.
(more…)
