Why does the SqlServer optimizer get so confused with parameters?

Question

I know this has something to do with parameter sniffing, but I'm just perplexed at how something like the following example is even possible with a piece of technology that does so many complex things well.

Many of us have run into stored procedures that intermittently run several of orders of magnitude slower than usual, and then if you copy out the sql from the procedure and use the same parameter values in a separate query window, it runs as fast as usual.

I just fixed a procedure like that by converting this:

alter procedure p_MyProc
(
    @param1 int
) as -- do a complex query with @param1

to this:

alter procedure p_MyProc
(
    @param1 int
)
as

declare @param1Copy int;
set @param1Copy = @param1;

-- Do the query using @param1Copy

It went from running in over a minute back down to under one second, like it usually runs. This behavior seems totally random. For 9 out of 10 @param1 inputs, the query is fast, regardless of how much data it ends up needing to crunch, or how big the result set it. But for that 1 out of 10, it just gets lost. And the fix is to replace an int with the same int in the query?

It makes no sense.

[Edit]

@gbn linked to this question, which details a similar problem:

Known issue?: SQL Server 2005 stored procedure fails to complete with a parameter

I hesitate to cry "Bug!" because that's so often a cop-out, but this really does seem like a bug to me. When I run the two versions of my stored procedure with the same input, I see identical query plans. The only difference is that the original takes more than a minute to run, and the version with the goofy parameter copying runs instantly.

Answer 1

The 1 in 10 gives the wrong plan that is cached.

RECOMPILE adds an overhead, masking allows each parameter to be evaluated on it's own merits (very simply).

By wrong plan, what if the 1 in 10 generates an scan on index 1 but the other 9 produce a seek on index 2? eg, the 1 in 10 is, say, 50% of the rows?

Edit: other questions

• Known issue?: SQL Server 2005 stored procedure fails to complete with a parameter
• Stored Procedure failing on a specific user

Edit 2:

Recompile does not work because the parameters are sniffed at compile time.
From other links (pasted in):

This article explains...

...parameter values are sniffed during compilation or recompilation...

Finally (edit 3):

Parameter sniffing was probably a good idea at the time and probably works well mostly. We use it across the board for any parameter that will end up in a WHERE clause. We don't need to use it because we know that only a few (more complex eg reports or many parameters) could cause issues but we use it for consistency.

And the fact that it will come back and bite us when the users complain and we should have used masking...