Stored procedure randomly using bad execution plan

I've a stored procedure that normally is executing perfectly well (check 20140929_OK_Anonymized). But then without notice it gets a wrong execution plan (20140929_Bad_Anonymized). Other than the visible differences on the estimated rows, estimated executions and data sizes, what else do I'm missing that could trigger the differences we are having?

link text

avatar image By yazalpizar 1 asked Sep 29, 2014 at 01:20 PM
more ▼
(comments are locked)
avatar image Dan Holmes Sep 29, 2014 at 01:26 PM

Maybe someone else can see something but that estimated anonymized plan is just too vague to be of any use. Can you provide two actual plans: one with the problem and one without?

What have you tried? What is the SQL? Have to updated statistics?

avatar image Aaron Bertrand ♦ Sep 29, 2014 at 01:28 PM

My first guess would be parameter sniffing - try adding OPTION (RECOMPILE) to the statement within the procedure to pay a small hit every time to avoid the really bad plan. Also make sure that different people executing the procedure have the same SET settings, the same default schema, that all references are full (no table references without schema prefix), etc. If you want specific advice, please post an actual post-execution plan (not an estimated plan) from within Plan Explorer, and don't anonymize it (this blanks out important details like the actual statement).

avatar image yazalpizar Sep 29, 2014 at 01:40 PM

I'm using **sp_WhoIsActive** with @get_plans=1 so the plans attached are the actual executed plans at the moment I run it. I'm already using OPTION (OPTIMIZE FOR UNKNOWN) to avoid parameter sniffing issue and is doing its work. But after some weeks of good performance we are rarely getting again a bad execution plan on same stored procedure. Will try to post the actual stored procedure. I will clean up the before upload.

avatar image Aaron Bertrand ♦ Sep 29, 2014 at 01:46 PM

That's actually the estimated plan from it's pulling from the cached_plans DMV.

avatar image SQLkiwi ♦ Oct 01, 2014 at 07:59 AM

As Aaron said, the plans you have uploaded are both pre-execution anonymized plans. We would need post-execution (actual) plans, ideally executed from Plan Explorer to offer advice here.

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x117
x89
x20

asked: Sep 29, 2014 at 01:20 PM

Seen: 558 times

Last Updated: Oct 01, 2014 at 07:59 AM