Same query, different execution plans

Question

I am trying to find a solution for a problem that is driving me mad...

I have a query which runs very fast in a QA Server but it is very slow in production. I realized that they have different execution plans... so I have try recompiling, cleanning the cache for the execution plans, update statistics, check the type of collation... but I still can't find what's going on...

The databases where the query is running are exactly the same and the SQL Servers have also the same configuration.

Any new ideas would be much appreciated.

Thanks, A.

---

I just realised the the QA server is running SP3 and in production is SP2. Could this have any impact on this issue?

Answer 1

Is it possible the production server has a larger database size? The plan can be different because it is based on statistics on the data it contains.

Answer 2

I think it could be due to the volume of data present. It happened to us one time where the query literally flew in QA server but was incredibly slow in the production. After breaking our heads for a while we found out that QA server had 15K rows where as production had 1.5 million.

HTH