Stored procedure randomly using bad execution plan

yazalpizar 2014-09-29 13:20:51

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

Dan Holmes 2014-09-29 13:26:31
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?

Aaron Bertrand 2014-09-29 13:28:33
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).
yazalpizar 2014-09-29 13:40:19
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.
Aaron Bertrand 2014-09-29 13:46:54
That's actually the estimated plan from it's pulling from the cached_plans DMV.
SQLkiwi 2014-10-01 07:59:52
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.