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:

http://stackoverflow.com/questions/379007/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

It's probably caused by the fact that SQL Server compiles stored procedures and caches execution plans for them and the cached execution plan is probably unsuitable for this new set of parameters. You can try WITH RECOMPILE option to see if it's the cause.

EXECUTE MyProcedure [parameters] WITH RECOMPILE

WITH RECOMPILE option will force SQL Server to ignore the cached plan.

Answer 2

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

http://stackoverflow.com/questions/379007/known-issue-sql-server-2005-stored-procedure-fails-to-complete-with-a-parameter

http://stackoverflow.com/questions/272726/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):

I think this article explains...

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

Finally (edit 2): 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...

Answer 3

As indicated it be a compilation issue. Does this issue still occur if you revert the procedure? One thing you can try if this occurs again to force a recompilation is to use:

sp_recompile [ @objname = ] 'object'

Right from BOL in regards to @objname parameter:

Is the qualified or unqualified name of a stored procedure, trigger, table, or view in the current database. object is nvarchar(776), with no default. If object is the name of a stored procedure or trigger, the stored procedure or trigger will be recompiled the next time that it is run. If object is the name of a table or view, all the stored procedures that reference the table or view will be recompiled the next time they are run.

If you drop and recreate the procedure you could cause clients to fail if they try and execute the procedure. You will also need to reapply security settings.

Answer 4

Is there any chance that the parameter value being provided is sometimes not int?

Answer 5

Could you check on the SQL Profiler how many reads and execution time when it is quick and when it is slow? It could be related to the number of rows fetched depending on the parameter value. It doesn't sound like a cache plan issue.

Answer 6

It is a problem with plan caching, and it isn't always related to parameters, as it was in your scenario.

(Parameter Sniffing problems occur when a proc is called with unusual parameters the FIRST time it runs, and so the cached plan works great for those odd values, but lousy for most other times the proc is called.)

We had a similar situation when the app team deleted all old records from a highly-used log table on a production server. Removing records improves performance, right? Nope, performance immediately tanked.

Turns out that a frequently-used stored proc was recompiled right when the table was nearly empty, and it cached an extremely poor execution plan ("hey, there's only 50 records here, might as well do a Table Scan!"). Would have happened no matter what the initial parameters.

Our fix was to force a recompile with sp_recompile.

Answer 7

Is every query reference to the parameter comparing it with int values, without functions and without casting?

Can you increase the specificity of any expressions using the parameter to make the use of multifield indexes more likely?

Answer 8

I have had this problem repeatedly on moving my code from a test server to production - on two different builds of SQL Server 2005. I think there are some big problems with the parameter sniffing in some builds of SQL Server 2005. I never had this problem on the dev server, or on two local developer edition boxes. I've never seen it it be such a big problem on SQL Server 2000 or any version going back to 6.5 either.

The cases where I found it, the only workaround was to use parameter masking, and I'm still hoping the DBAs will patch up the production server to SP3 so it will maybe go away. Things which did not work:

• using the WITH RECOMPILE hint on EXEC or in the SP itself.
• dropping and recreating the SP
• using sp_recompile

Note that in the case I was working on, the data was not changing since an earlier invocation - I had simply scripted the code onto the production box which already had data loaded. All the invocations came with no changes to the data since before the SPs existed.

Oh, and if SQL Server can't handle this without masking, they need to add a parameter modifier NOSNIFF or something. What happens if you mask all your parameters, so you have @Something_parm and @Something_var and someone changes the code to use the wrong one and all of a sudden you have a sniffing problem again? Plus you are polluting the namespace within the SP. All these SPs I am "fixing" drive me nuts because I know they are going to be a maintenance nightmare for the less experienced satff I will be handing this project off to one day.