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?


  • Are there differences in the execution plans? I'm willing to bet there are.
    – Hannah 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

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.


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.