Same Query producing different plans on different servers.

Amadou 2017-03-28 21:01:49

Environment: SQL Server 2012 SP2 Developer; Memory 16GB; CPU 16 procs.
I have a query that produce different query plans when I run it on 2 different servers. What I know: All indexes that are involved in the query are rebuilt on both servers. Stats are up to date. Data set is the same on both servers. SP_Configure values are the same. Differece: SQL Server 2012 SP2 Developer vs SQL Server 2012 SP1 Enterprise. Also I noticed that a one of the plans have Index Seek and the other has Index Scan on one of the tables. The Index Scan plan runs for over 10hrs without completing. The Index Seek plan runs for 6mins and completes successfully. What could be the cause of the different plans? I will send the other plan seperately.

Aaron Bertrand 2017-04-11 21:40:04
A lot of possible reasons: Different Plans for "Identical" Servers
Amadou 2017-03-28 21:07:37
This is the second plan that takes longer.