Just now we have migrated database from SQL 2012 to SQL 2016. we are facing some performance issues on SQL 2016.

I have identified there is one view is taking below time in :

SQL 2012: 1 sec

SQL 2016: 3 min

I don't know why the SQL Sever behave like this.

I have done some more analysis on that I found the if I join with any temp table in view it's take huge time. for example :

Declare @ReID int
Select * from View where REID=@REID --1 sec
Select * from Temptable inner join view on view.id=temptable.id - taking 3 min

Note: temp table having 1 records.

we have done the rebuild-index,statistics etc.

Please provide your inputs why SQL 2016 behave like this?