We have a RDP server which is running in 2008 version of SSMS and OS. Recently we migrated this server to 2016 version, both the OS(2016) and SSMS(2016).
Configured new machine(with ssms2016) is same to the old one(with ssms2008) in terms of system configuration. It has a 64-bit OS with x64-based processor. RAM memory is 64.0 GB and 2.39 GHz (32 processor).
We are facing severe performance issue while running stored procedures in SSMS 2016 version, as same code base has been migrated from sql server 2008.We are loading data to these servers using SSIS ETL tools.
For example if we are running a stored procedure in old server(with ssms2008) it is taking 1 hour of time but the same SP is taking 10 hours of time in new server(with ssms 2016) sometimes even more.
To identify the root cause we have tried below approaches but so far nothing worked.
- After migration we changed the compatibility level of sql server from 2008 to 2016.
- Restored the database once again from old server (ssms 2008) to new server (ssms 2016 ) without changing the compatibility level.
- Recompiled the stored procedures in new server(ssms2016).
- updated the statistics in new server(ssms2016).
- Disc reconfiguration/thickening of windows new server drives also.
- While running time consuming stored procedure in new server(ssms2016), parallely ran sql server profiler but nothing fruitful we have got.
- Ran parallelly same query in ssms 2008 and ssms 2016 servers at the same time, in old server(ssms2008) execution got completed much faster than new server(ssms2016)
Note: is there any solution we can try to get the same execution time in both servers.
Thanks in Advance.
Regards,
Prashant Agrawal
