Different plans for the same SQL code

Question

I have a slow stored procedure like

CREATE PROCEDURE [dbo].[MyProc]
(
    @Param1 nvarchar(20),
    @Param2 nvarchar(20)
)
AS
BEGIN
    set nocount on

    ;with cte (col1, col2, col3)
    as
    (
        select col1, col2, col3
        from    someTable
    --And the rest of it

I am trying to optimise it because it was taking too long.

When I call it as exec MyProc @Param1=N'xxx', @Param2=N'yyy' it takes 20 seconds or so. To test what I could do I ran it like this:

DECLARE @Param1=N'xxx',
@Param2=N'yyy'
set nocount on

;with cte (col1, col2, col3)
as
(
    select col1, col2, col3
    from    someTable
--And the rest of it

and that takes about a second. Execution plans are completely different. Plan that is used when I call 'exec ...' is different to the plan that is used when I run the code directly. I re-indexed the tables used, I called sp_recompile on the table and the stored procedure itself, I found the plans that are used for the SP and called DBCC FREEPROCCACHE ( on the plan handles, but every time the plan is re-generated, they are different.

Any idea what's going on? Why would the same piece of code use different plans, one that takes 1 sec and the other taking 20 secs?

Answer 1

This is called parameter sniffing, and it's one of the hardest problems to solve in a database.

The basic problem is that data is skewed in a way that makes the cost based optimizer choose different plans for returning your data.

There are a lot of ways people think they "fix" parameter sniffing:

• Recompile hints
• Optimizer for unknown
• Optimize for a value

But these really just disable parameter sniffing in different ways. That's not to say they can't solve the problem, they just don't really get to the root cause.

That's because getting to the root cause is usually kind of difficult. You have to look for those pesky "plan quality issues".

Starting with the fast vs slow plans, look for differences like:

• Indexes used
• Join order
• Serial vs Parallel

Also look for different operators that make your code sensitive to parameter sniffing:

• Lookups
• Sorts
• Join type
• Memory grants (and by extension, spills)
• Spools

Don't get too wrapped up in seek vs scan, index fragmentation, or any of the cargo cult-y stuff people hem and haw about.

Usually, there's a pretty basic indexing problem. Sometimes the code needs a little rewriting. If you want more help, please share the query plans, along with the code, table and index definitions.

Otherwise, good luck!