Two servers, different execution plans. Several orders of magnitude difference in run time
Comparing details from the two execution plans provided:


Slow plan was compiled on build 11.0.2100.60 - SQL Server 2012 RTM
Fast plan was compiled on build 12.0.4100.1 - SQL Server 2014 SP1
Slow plan uses the version 70 cardinality estimator
Fast plan uses the version 120 cardinality estimator
Slow plan has estimated available DOP of 2 (half the visible CPUs)
Fast plan has estimated available DOP of 4 (half the visible CPUs)
Estimated memory grant available is also different between the two
Parameter values used during compilation are identical


With so many fundamental software and hardware differences, it is not really surprising the plans are different.

Ideally, development and test environments should match production as closely as possible. They should certainly be on the same version of SQL Server (and note the 2012 installation there is still at RTM, two full Service Packs and seven further Cumulative Updates behind current.

Without a statistics-only copy of the schema involved in the query, it is hard to know what to suggest to improve the 2012 query plan as it stands. If you are comfortable using hints, you might try adding:

OPTION (LOOP JOIN, HASH JOIN, CONCAT UNION, QUERYTRACEON 8649);


at the end of the query to encourage the optimizer in the general direction of the better plan. The last part of that hint collection (setting trace flag 8649 for the query) is undocumented and not supported for production.

Those hints may or may not be successful, since the underlying issue is the inaccurate cardinality estimation provided by the 70 level cardinality estimator. Query rewrites or indexing changes might be successful as well, but it's really not practical to guess on that from here.

Related reading:

Forcing a Parallel Execution Plan
Different Plans on UAT and PROD
Multiple Plans for an "Identical" Query
Different Plans for "Identical" Servers