We upgraded from SQL Server 2008 to 2016 and were checking the performance (execution time) of procedures. The newer SQL Server is installed on new and better hardware than the previous hardware as well. We expected the queries on newer hardware to run faster but surprisingly they run slower here.
Executing the query using different parameters some execute in same time as the previous SQL Server, but other takes older time multiplied by 2 to 10. I've tried comparing the execution plans on both the SQL Server machines. They are exactly the same.
The query I am trying is a stored procedure that does some string manipulation (making a select statement), executes it and return results.
The query on new hardware and SQL Server 2016 takes 2 to 10 times as long as on the old hardware and SQL Server 2008.
Are there any query performance techniques that I can try to execute the query faster on new software, hardware?