Different execution plan from the same query in different SQL Server -

i have issue query:

select distinct     date_int,     codf,     desc_com,     datadesc_com,     codc,     function,     tratt_number,     tratt_state      tmp_sic_trattative_stato_com_l2  union   select distinct     case         when (ts.date_int not null)            ts.date_int            else all_day.date_int      end date_int,     case         when (ts.codf not null)            ts.codf            else all_day.codf       end codf,     case         when (ts.desc_com not null)            ts.desc_com            else all_day.desc_com      end desc_com,     case         when (ts.datadesc_com not null)            ts.datadesc_com            else all_day.datadesc_com      end datadesc_com,     case         when (ts.codc not null)            ts.codc            else all_day.codc      end codc,     case when (ts.function not null) ts.function else all_day.function end function,     case when (ts.tratt_number not null) ts.tratt_number else all_day.tratt_number end tratt_number,     case when (ts.tratt_state not null) ts.tratt_state else all_day.tratt_state end tratt_state      commerciali_all_day all_day  left outer join     tmp_sic_trattative_stato_com_l2 ts on ts.date_int = all_day.date_int                                           , ts.codf = all_day.codf                                           , ts.desc_com = all_day.desc_com                                           , ts.datadesc_com = all_day.datadesc_com                                           , ts.codc = all_day.codc                                           , ts.function = all_day.function                                           , ts.tratt_state = all_day.tratt_state      ts.date_int null 

i execute query in stored procedure execution plan changes if stored procedure executed using production sql server or using test sql server.

this test execution plan:

test execution plan

and production execution plan:

production execution plan

the source table , stored procedure same in test , production , not understand because execution plan , time different.

in test query execute in 6 minutes , in production in 15 minutes.

the test , production sql server microsoft sql server 2014 version 12.0.4100.1.

• the production server has 24 gb ram , 8 cpu 2ghz
• the test server has 16 gb ram , and 4 cpu 2ghz

i not understand why procedure performing better in test environment , not in production environment.

the title of question not asking for. have same query plan between prod , test server. you're asking why prod server slower test server same query.

in comments answered tables , contents identical between test , prod. mention have same number of rows.

the prod plan shows more data being returned test plan. biggest point of interest data being returned table scan on commerciali_all_day build input hash table. in test returns 725,858 rows total size of 47mb. in prod returns 728,941 rows total size of 120mb. thats more double size difference of 3,083 rows.

with hash build input table returning more double amount of data larger in prod in test. in test hash table 19,897,066 rows size of 2,713mb. in prod hash table 20,006,362 rows size of 4,732mb. prod crunching through 2gb of data.

you need go , take better @ difference between data in prod , test. none of tables returning same amount of data when comparing prod , test plans. real pain point query though commerciali_all_day table.