We are in process of upgrading one of the SQL Database from 2012 to 2016 version. The Database is moved/upgraded successfully to SQL 2016 version, now the problem is, the database is holding a Stored Proc which is taking longer time in the new version i.e on SQL 2016.
On SQL 2012 stored proc will execute in 13 minutes where as on SQL 2016 it's taking 13 hours to complete. We been stuck with this since stored proc is having lot more dependencies to business.
We increased the memory , ram on the new server to 32 GB where as on old server it's 28 GB only, but both the servers are holding 2 cores only.
Old server OS is win 2008 and new box build with Win 2012.
We tried to find out blockings by running profiler when ever the stored proc runs but we haven't found any blockings ,this seems mysterious for us since no blockings, dead locks found.
Settings on SQL server are similar on both of the servers we haven't any odd.
Let us know if have any suggestions on how to solve this issue.
Quick help is really appreciated.