• There are a few clues around this. If you look at the properties of the SELECT operator, there are differences in the apparent setup of the servers. One has 12 processors available for parallelism and the other only 6. Both plans are timing out. This alone could account for the differences in the plan if one server was able to try a couple of extra plans more than the other before the timeout occurred. One big difference here is that you're moving from Standard to Enterprise. The Standard edition has limits on the processors. That's going to result in differences to the plans (and might explain why we're seeing differences in the SELECT operation).

    However, I'm seeing differences in the cardinality between the tables in the two plans. Just taking one example, [DB_FRMIS_det].[dbo].[MI_DEP_Department_T].[IX_MI_DEP_Department_T_1] [MI_DEP_Department_T] shows as having a cardinality of 14013 in the slow plan and 14211 in the fast one. That difference isn't enough to make a difference in the plans, but it makes me wonder if there are other differences. Are you sure these are the same databases with the same structures, the same data, and updated statistics? They don't appear to be. Another example [DB_FRMIS_det].[dbo].[MI_ORT_OrderRowTransaction_T].[IX_MI_ORT_ACT_ID] [ORT] shows as 187,690,000 in the fast plan and 178,890,000 in the slow plan. That many rows different, that's going to lead to problems.

    I also think the statistics are way off in the slow instance. Your estimate for the same table above is 169,083 while the actual is 76,852,437. That's a huge disparity.

    From what I can see, your issues are, at least in part, caused by the move from Standard to Enterprise. However, beyond that, these are not the same data sets and the statistics don't seem to be updated at all. We're comparing apples to hammers because of this.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning