Parameter sniffing, pt 2

A while back I wrote about parameter sniffing, the situation where SQL compiles and caches an execution plan that is appropriate for a certain value of a parameter, but is non optimal for other values. There’s another side to parameter sniffing though – when the optimiser can’t sniff at all.

When a batch is submitted to the optimiser, the value of any parameters (from stored procedure, auto parametrisation or sp_executesql) are known to the optimiser. So are the value of any constants used in the SQL statement. The value of local variables, however, are not.

When the value of a variable used in the where clause is not known, the optimiser has know idea what value to use to estimate the number of affected rows. Hence, it has no idea how many rows from the table will satisfy the condition.

Let’s have a look at a quick example. I’m going to use the same table and data as I used in the other article on parameter sniffing.

From the tests that were done before, I know that the query

select * from largetable where somestring = 'zzz'

uses an index seek and returns 9 rows.Likewise, I know that the query

select * from largetable where somestring = 'abc'

uses a clustered index scan and returns 1667 rows. Now let’s try with variables.

DECLARE @SomeVar CHAR(3)
SET @SomeVar = 'abc'
select * from largetable where somestring = @SomeVar

If I run that and look at the execution plan, it looks fine. A clustered index scan was done, just as expected. A look at the estimated and actual rows shows an estimated of 1428 and an actual of 1667. Not bad, and certainly within acceptable levels. Now let’s try with the other value.

DECLARE @SomeVar CHAR(3)
SET @SomeVar = 'zzz'
select * from largetable where somestring = @SomeVar

The execution plan shows a clustered index scan. Not the index seek that I would expect. Furthermore, a look at the estimated and actual rows shows a large discrepancy. Estimated rows was 1428. Actual was 9.

Because variables were used in this case, not parameters or constants, the optimiser wasn’t able to get a good estimate of the number of rows affected by the query. It guessed. In one case that guess wasfairly good, in the other it was way off the mark.

So, how to get around this problem?

One option is to use parameters or constants where possible. Because the optimiser can see the value of parameters, it can make a better guess as to therows affected.

The recompile hint can be used at the query level to indicate that the query need to be compiled separately from the rest of the batch.

The optimise for hint can be used to give the optimiser a value to work with. Even if it isn’t the actual value used in the queries, it can often give the optimiser a better chance of producing a good plan, especially is an appropriate value is chosen.

Be sure to check part 3 of this mini-series, where we look at what happens if parameter values change within a procedure