I setup and configured an new SQL database server with SQL Server 2016 Standard Edition. This is going to replace our existing SQL database server which is 2008 R2 database server. I restored recent backup form the current database server(SQL Server 2008 R2) and pointed the application to the new database server. But when i run queries from the application,the queries are very slow.Even those queries which used to run fast current database server is very slow in the new server. New database server hardware specs are much better then the current database server.
I have been trying out what is causing this slow performance. Steps i tried.
- I changed the compatibility of the database from 2008(100) to 2016(130) after restore.
- Tried to find changes in the execution plan.
- Saw Implicit conversion warning in the execution plan,but the column mentioned in that message is not found in the query.
FYI,the sql queries are generating from the application using linq and not stored as a stored procedure.
Rebuilt Indexes and Updated statistics,but not much help as of now. I have noticed a SORT operation with cost 95% ,but i already have an index for that SORT filed,so i don't think that SORT is an issue.Correct me if i am wrong.
I have noticed that in the actual execution plan,for most of my operation Estimated Number of execution and Actual number of execution is having big difference.What i should make of that?