8

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?

Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
A..
  • 81
  • 1
  • 2
  • 2
    is the hardware exactly the same between the two environments? is the data *in* the database exactly the same between the two environments? – ninesided May 25 '10 at 11:50

5 Answers5

2

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.

Eddy Pronk
  • 6,527
  • 5
  • 33
  • 57
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

Raja
  • 3,608
  • 3
  • 28
  • 39
  • No, both databases are exactly the same... same tables, triggers, indexes.... and data... – A.. May 25 '10 at 12:32
1

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?

KM.
  • 101,727
  • 34
  • 178
  • 212
  • The QA database is a manual backup/restore from the production database, so I think everything should be the same, right? I just don't understand what I am getting different execution plans... – A.. May 25 '10 at 12:34
  • And the difference in the execution is just unreal... one query takes 3 seconds in QA and almost 5 minutes in production... – A.. May 25 '10 at 12:35
  • how about posting the execution plans? or at least telling what is the actual difference – KM. May 25 '10 at 12:38
  • Sure, but I think they are too big to post them here... can I send them to you? – A.. May 25 '10 at 12:49
0

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.

geoffrobinson
  • 1,580
  • 3
  • 15
  • 23
0

Disable parallel query execution on production :)

Arvo
  • 10,349
  • 1
  • 31
  • 34
  • And on production? It depends on database and query nature; for our main application it is often preferrable to turn parallelism off, but sure there are scenarios, where parallelism helps. – Arvo May 25 '10 at 13:48