9

I have a delete statement that is using a bad plan when run inside a stored procedure, but is choosing a much better plan when run ad-hoc.

I have rebuilt all the indexes for the tables used by the query and dropped all of the caches. The optimizer still chooses the wrong plan for the stored procedure.

I'd like to know why the optimizer is using a different execution plan for the stored procedure versus the ad-hoc SQL.

UPDATE: I guess it must have been the parameters after all- when I ran the ad-hoc code with the variable hardcoded, I can get the "bad" plan with the right value (it is a date, values that are a year old seem to generate the "good" plan). Now on to trying to force the "good" plan on the proc by using a query hint.

SOLUTION: I ended up getting the plan I wanted by using the OPTIMIZE FOR UNKNOWN hint.

Improve this question
7
  • It's probably parameter sniffing. Does the query reference a variable in its WHERE clause?
    – Nick Chammas
    Oct 4, 2011 at 16:00
  • 4
    Can you add code please
    – gbn
    Oct 4, 2011 at 16:10
  • Also post the plans somewhere please. As is, it is going to be pretty tough to tell you exactly why the plans are different.
    – Aaron Bertrand
    Oct 4, 2011 at 16:26
  • OK, more information: I can't post the plans and codes until I obfuscate them a little bit. I'll try to get them up in a few. The plan for the stored procedure (bad) does a clustered index scan of a large table (the entire thing, all partitions). It then uses a loop to find rows from a smaller table, and then deletes from the smaller table.
    – msgisme
    Oct 4, 2011 at 16:30
  • The plan for the ad-hoc code (good) does a table scan of the small table (which only has 5-10 rows) and uses a non-clustered index of the large table to find which rows it needs to check in the PK of the large table. I'll try to get the actual plans up as soon as I can.
    – msgisme
    Oct 4, 2011 at 16:31

2 Answers 2

Reset to default
5

Usual suspects:

  1. constants in adhoc, parameters in code
  2. mismatch of data types in code
  3. parameter sniffing

Point 1: the optimiser can choose the best plan for the constants.
Change the constants = change the plan. A parametrised plen is resuable

Point 2 will introduce implicit conversions because of datatype precedence
eg varchar column compared to nvarchar parameter

Point 3: use parameter masking or OPTIMISE FOR UNKNOWN
Edit: To test: run stored proc, run sp_updatestats, run again. This will invalidate cached plans which is better then clearing plan cache

Edit: after jcolebrand's comment

You can disable sniffing several ways. The main 3 are

  • RECOMPILE. This is silly IMO.
  • OPTIMIZE (sic) FOR UNKNOWN
  • Parameter masking

Parameter masking:

DECLARE @MaskedParam varchar(10)
SELECT @MaskedParam = @SignaureParam

SELECT...WHERE column = @MaskedParam

Masking and the OPTIMIZE hint have the same effect (maybe for different reasons). That is, the optimiser has to use statistics and data distribution (Note: still under testing by Mark Storey-Smith) evaluate the parameters on their own merits ? , rather than what they were last call. The optimiser can recompile or not. SQL Server 2005 added statement level recompilation so there was less impact

Now, why a plan with "sniffed" parameters is "sticky" compared to masked/"unknown" parameters, I'm not sure.

I've used parameter masking since SQL Server 2000 for all but the simplest code. I've noted that it is liable to happen with more complex code. And at my old job I has some report procs that I could change the plan parameter defaults. I reckon the "cargo cult" approach was easier than a support call.

Edit 2, 12 Oct 2011, after some chat

  • Parameter masking and OPTIMISE FOR UNKNOWN have the same effect as far as I can tell
    The hint is cleaner than masking but was added with SQL Server 2008.

  • Parameter sniffing happens at compile time.
    WITH RECOMPILE generates a new plan each execution. This means a poor choice of defaults will influence the plan. At my last job, I could demonstrate this easily with some report code: changing parameter defaults altered the plan regardless of supplied parameters.

  • This MS Connect article is interesting: Suboptimal index usage within stored procedure (mentioned in one of the SO answers below)

  • Bob Beauchemin mentions it too

Outstanding issues

  • Does sniffing still apply with WITH RECOMPILE? That is, if the optimiser knows to discard the plan does it aim for re-use?

  • Why are sniffed plans "sticky"?

Links from SO:

Improve this answer
11
  • 1. The param in the sp is a variable in the code 2. Again, same datatype 3. I have run both with a wide variety of parameters, and I get the same plan each time. I cleared the cache after each attempt.
    – msgisme
    Oct 4, 2011 at 16:18
  • 1
    Re: point 3. You can also run the statement with OPTION (RECOMPILE) or the whole proc WITH RECOMPILE to force SQL Server to disregard existing plans.
    – Nick Chammas
    Oct 4, 2011 at 16:53
  • 3
    BTW it's OPTIMIZE, because Microsoft is an American company. :)
    – Nick Chammas
    Oct 4, 2011 at 17:33
  • 1
    @Gbn any thoughts on preventing/defeating parameter sniffing?
    – jcolebrand ♦
    Oct 5, 2011 at 18:02
  • 1
    @jcolebrand: simple answer is "no" :-)
    – gbn
    Oct 5, 2011 at 21:55
2

Don't forget that the ANSI settings that you have setup for the connection plan a role in execution plan selection. When the app calls the stored procedure it probably has different ANSI settings than your SSMS connection.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.