0

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

Community
  • 1
sharadov
  • 342
  • 1
  • 4
  • 14

2 Answers2

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.

Lmu92
  • 396
  • 1
  • 4
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!

Community
  • 1
sharadov
  • 342
  • 1
  • 4
  • 14