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?