Parameter sniffing, pt 3

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'</code>

executes optimally with an index seek and returns 9 rows. Likewise, I know that the query

select * from largetable where somestring = 'abc'</code>

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.

CREATE PROCEDURE TestSniffing3 @StringVar VARCHAR(10) AS
IF @StringVar not like 'a%'
SET @StringVar = 'abc'

SELECT *  from largetable where somestring = @StringVar

GO

Looks simple enough. It the parameter isn't within a certain range, set it to some default value. This kind of stored proc construction isn't unusual. I often see it in search procs. Pass in a NULL if you want all rows to match. Let's see what the optimiser makes of it.

EXEC TestSniffing3 'zzz'

It returns 1667 rows, as expected. Now take a look at the execution plan. It's doing an index seek. Not what was expected and not optimal. On my machine the key lookup's in at 87% of the query.

So, what went wrong?

A look at the properties of the index seek give a clue. Estimated rows - 9 actual rows - 1667. The xml form of the execution plan give another clue.

&lt;ColumnReference Column="@StringVar"
ParameterCompiledValue="'zzz'" ParameterRuntimeValue="'abc'" /&gt;

When this procedure was compiled, the value of the parameter was 'zzz'. The optimiser compiled a plan optimal for that value. It didn't and couldn't know that the parameter was going to change within the stored procedure before the query was executed.

This is something that can really bite badly. There often doesn't appear to be a good reason for the plan to be wrong. Most people don't go reading the raw XML of the execution plans, for good reason. It esspecially needs to be ketp in mind on procs that have multiple optional parameters, like the following

create procedure DoSearch @var1 varchar(10) = NULL, @var2 varchar(10) = NULL
AS
IF @var1 is null
@var1 = '%'

IF @var2 is null
@var2 = '%'

SELECT * FROM SomeBigTable WHERE Col1 like @var1 AND Col2 LIKE @var2
GO

If, when the proc is first compiled, either of the parameters is NULL, SQL will compile a plan optimal for no rows because a LIKE NULL will not match anything. If the query then goes on to match the entire table, the plan is not going to be very optimal at all.

That I think concludes the short series on parameter sniffing. As always, I'm interested in hearing other people's take on these problems and their favourite solutions.

10 Responses to “Parameter sniffing, pt 3”

  1. Hi Gail,

    Great articles you’ve written here.

    I’ve been reading these posts on sniffing with interest as I’ve had some problems with this in the past.

    It’s probably me but it seems that you’re stuffed either way.

    Would it be better to have a plan that’s optimised for a small rowset but returns a large one or vice versa?

    I realise that modifying the params within the proc will cause problems but is there a way of covering ‘abc’ and ‘xyz’ optimally?

    Rob said this on March 4th, 2009 at 18:53

  2. I haven’t tested, but gut feel is that you’ll have less problems with a plan that’s optimised for more rows than it gets.

    Still not ideal, memory allocations will be larger than necessary, expensive operators will be used where cheap ones would have sufficed, but it shouldn’t be as bad as a case where an operator that’s only optimal for small row counts was selected, but lots of rows were affected.

    I think I’d use something like that as a last resort when all other fixes have not worked.

    Gail said this on March 4th, 2009 at 23:52

  3. Thank you for the reply,

    I guess it’s a case of getting stats on the mean row count over say, a month and forcing the plan to optimise based on that if there is a strong trend rather than hoping the first execution will suffice.

    What I can gather is that in the case of a query that has a wide spread of row counts and no strong trend it would be better to have it scan.

    If it were seeking, and returned a large rowset it would be more innefficient compared to a scan only picking out one row if that’s all that was returned?

    On that note, off the top of your head do you know the overhead of forcing a recompile every execution? My gut feeling is that would just be daft.

    I appreciate that this isn’t a support forum, I should probably ask over on SqlSQLServerCentral.com :) but it would be nice to know that I’m thinking along the right lines.

    Rob said this on March 5th, 2009 at 11:32

  4. Please do ask this over at Central. It’s easier to go into detail in a post reply there than it is in blog comments. Post the link here and I’ll pick it up.

    Gail said this on March 5th, 2009 at 13:19

  5. HI GAIL,
    A well written series. Looking at the xml plans, the parameters appear as two different variables, the ParameterCompiledValue and the ParameterRuntimeValue. Was it is the difference between the two?

    joe Tigeleiro said this on August 24th, 2009 at 20:58

  6. The compiled value is the value that was passed as a parameter for the first execution of the query, the one that got optimised. The runtime value is the value of the parameter for this particular execution.

    Gail said this on August 24th, 2009 at 21:28

  7. So just to make sure I understand this. If I read the plan and there is only the compiled version, then this is what was used for this execution. If the runtime was listed, then that was used. I have seen plans where both were listed with the same parameter values. What should I count on.

    joe Tigeleiro said this on August 25th, 2009 at 18:09

  8. No. If you look at a plan and there’s only the compiled, you’ve got an estimated execution plan.
    If the plan you are looking at has both compiled and runtime, then it’s an actual execution plan.

    http://sqlinthewild.co.za/index.php/2009/02/19/estimated-and-actual-execution-plan-revisited/

    If they’re both the same value then it means that the plan was compiled with the same values as it was run with.

    Gail said this on August 25th, 2009 at 23:27

  9. Thank you for your responses and the link that you provided. The article was very informative and helpfull.

    joe Tigeleiro said this on August 26th, 2009 at 15:42

  10. hi gail…great article..thanks a ton.

    azad said this on July 14th, 2010 at 10:28

Leave a Reply