Same query, different execution plans

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?

---

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.

---

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

---

If the execution plan was the same and one was slow, it would be database load, hardware, locking/blocking, etc.

However, if the execution plans are different something is different between the two databases. Are statistics up to date in both, have the exact same schemas, same indexes, similar number of rows, same distribution of PK and index values, etc. Where did the QA data come from, random data or is it a restore from production?

---

Disable parallel query execution on production :)

---

I ran into this recently and here's what I found.

I had two databases that were essentially copies of each other. On one version a TVF was taking 1 second to run, while on the other version took 15 minutes to run.

The execution plans of the underlying SQL code were very different. I was able to fix it by rebuilding some indexes that the TVF relied on. The execution plans aren't the same, but it did change a lot. And the execution time is back down to around a second.

Now, both versions had indexes that were highly fragmented. My assumption is that historical statistic or execution plan information allowed the fast version to continue to find an optimal execution plan.

So to sum up: make sure you look at the fragmentation of your indexes even if they have the same structure or similar rates of fragmentation.