0

This question already has an answer here:

We have two server with identical hardware configuration and SQL server configuration. The only difference in the server's being:

Server SQL1 - 256 GB RAM

Server SQL2 - 128 GB RAM

The same database has been restored on both servers. The query execution plan for a query on both servers is different.The MAXDOP on both servers is 4. But the query runs in 10 mins on SQL1 but takes 1 min on SQL2.

What would cause the query execution plans to be different? SQL1 where the query is slower, does have more load on it when compared to SQL2. Does load matter when SQL server decides on a query execution plan?

We set MAXDOP = 1 on SQL1 and we saw the query complete in 1 minute.

What would explain this?

Thanks

marked as duplicate by billinkc, Taryn, Aaron Bertrand Oct 7 '14 at 13:52

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Are there differences in the execution plans? I'm willing to bet there are. – Max Vernon Oct 6 '14 at 18:52
  • Its very difficult to predict unless you show us execution plan. Plus are cores same on both machine – Shanky Oct 6 '14 at 18:54
  • Cores are same on both machines. The execution plans are different,but all else being same, why are they different? – sharadov Oct 6 '14 at 19:06
  • 2
    Identical hardware configuration yet you have 128GB of memory vs 256GB. Might be worth reading through Kin's question and findings – billinkc Oct 6 '14 at 21:38
  • 2
    @sharadov try running the query with - trace flag T2335. See more details here + make sure that your indexs are defragmented and your stats are up-to-date. – Kin Shah Oct 6 '14 at 21:41
0

To me it looks like that the statistics are way off on the server with the bad plan. For instance, Audit_New.dbo.RevOrder shows an estimated rows of 40k vs. actual rows = 8k. I'd recommend to rebuild/reorganize the indexes involved in the query on the poorly performing server.

0

We bumped down the memory to 128 Gb on the SQL2, and we see the same plan as SQL1. Kind of throws all logic out of the window. But Paul White's article explains it. difference in execution plans on UAT and PROD server Thanks a lot for your help Kin!

Not the answer you're looking for? Browse other questions tagged or ask your own question.