Same query, different execution plans

Question

Hi,

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.

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

Answer 3

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?

Answer 4

Disable parallel query execution on production :)

Answer 5

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