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
  • Are there differences in the execution plans? I'm willing to bet there are. – Hannah Vernon Oct 06 '14 at 18:52
  • Its very difficult to predict unless you show us execution plan. Plus are cores same on both machine – Shanky Oct 06 '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 06 '14 at 19:06
  • I difficult to tell, I need to see the execution plan. – Shanky Oct 06 '14 at 19:08
  • Here is the link to query plans https://www.dropbox.com/s/ggfl5zg5b95rkm3/Bad%20Plan.sqlplan?dl=0 https://www.dropbox.com/s/k6jaxy1rvsmxqws/Good%20Plan.sqlplan?dl=0 – sharadov Oct 06 '14 at 19:22
  • The plans you list are quite radically different. It's pretty hard to tell where the problem lies when the plans are for two different sets of parameters. – Hannah Vernon Oct 06 '14 at 19:45
  • Does the query optimizer consider the load on the server when it compiles an execution plan? – sharadov Oct 06 '14 at 20:56
  • 2
    Identical hardware configuration yet you have 128GB of memory vs 256GB. Might be worth reading through Kin's question and findings – billinkc Oct 06 '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 06 '14 at 21:41
  • 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 Kin's article explains it. http://dba.stackexchange.com/questions/53726/difference-in-execution-plans-on-uat-and-prod-server/55254#55254 Thanks a lot for your help! – sharadov Oct 06 '14 at 23:51

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