Different Execution Plan for the same Stored Procedure

Question

We have a query that is taking around 5 sec on our production system, but on our mirror system (as identical as possible to production) and dev systems it takes under 1 second.

We have checked out the query plans and we can see that they differ. Also from these plans we can see why one is taking longer than the other. The data, schame and servers are similar and the stored procedures identical.

We know how to fix it by re-arranging the joins and adding hints, However at the moment it would be easier if we didn't have to make any changes to the SProc (Paperwork). We have also tried a sp_recompile.

What could cause the difference between the two query plans?

System: SQL 2005 SP2 Enterprise on Win2k3 Enterprise

Update: Thanks for your responses, it turns out that it was statistics. See summary below.

Answer 1

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.

Answer 2

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?