Different Execution Plan for the same Stored Procedure?

Your statistics are most likely out of date. If your data is the same, recompute the statistics on both servers and recompile. You should then see identical query plans.

Your statistics are most likely out of date. If your data is the same, recompute the statistics on both servers and recompile. You should then see identical query plans.

Also, double-check that your indexes are identical.

Most likely statistics. Some thoughts: Do you do maintenance on your non-prod systems? (eg rebuidl indexes, which will rebuild statistics) If so, do you use the same fillfactor and statistics sample ratio?

Do you restore the database regularly onto test so it's 100% like production?

Turns out that it was prod that didn't have maintenance done on it. – Robert Wagner Feb 3 '09 at 5:20.

Execution plans can be different in such cases because of the data in the tables and/or the statistics. Even in cases where auto update statistics is turned on, the statistics can get out of date (especially in very large tables) You may find that the optimizer has estimated a table is not that large and opted for a table scan or something like that.

There is not much difference in table size as it is a recent copy. The tables themselves are around 2000 rows. – Robert Wagner Feb 3 '09 at 4:55.

Provided there is no WITH RECOMPILE option on your proc, the execution plan will get cached after the first execution. Here is a trivial example on how you can get the wrong query plan cached: create proc spTest @id int as select * from sysobjects where @id is null or id = id go exec spTest null -- As expected its a clustered index scan go exec spTest 1 -- OH no its a clustered index scan Try running your Sql in QA on the production server outside of the stored proc to determine if you have an issue with your statistics being out of date or mysterious indexes missing from production.

As mentioned we have tried to refresh the query plan. We did try running the query outside the Sproc with the same results (pointing away from bad cached query plans). At least something is eliminated.

– Robert Wagner Feb 3 '09 at 5:00 Yerp, you have an issue with your stats, data or indexes, if I were you I would grab a backup of your production server and run diagnostics locally ... – Sam Saffron? Feb 3 '09 at 5:17.

Tying in to the first answer, the problem may lie with SQL Server's Parameter Sniffing feature. It uses the first value that caused compilation to help create the execution plan. Usually this is good but if the value is not normal (or somehow strange), it can contribute to a bad plan.

This would also explain the difference between production and testing. Turning off parameter sniffing would require modifying the SProc which I understand is undesirable. However, after using sp_recompile, pass in parameters that you'd consider "normal" and it should recompile based off of these new parameters.

I think the parameter sniffing behavior is different between 2005 and 2008 so this may not work.

As mentioned, we did did do this with no success. However good detail on how to test it. – Robert Wagner Feb 3 '09 at 5:01 I just pictured running sp_recompile and then using the same weird values which would make the same bad plan.It was a long-shot anyway.

– colithium Feb 3 '09 at 5:14.

The solution was to recalculate the statistics. I overlooked that as usually we have scheduled tasks to do all of that, but for some reason the admins didn't put one one this server, Doh. To summarize all the posts: Check the setup is the same Indexes Table sizes Restore Database Execution Plan Caching If the query runs the same outside the SProc, it's not the Execution Plan sp_recompile if it is different Parameter sniffing Recompute Statistics.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.