SQL Queries Slow in the Upgraded environment 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.

This is the plan for one of the query which is taking almost 3 minutes to execute.

This plan is from current server(SQL Server 2008 R2) and it takes 10 seconds only for the same query.

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.

See the attached screenshot when i run EXEC sp_BlitzFirst @SinceStartup = 1;


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?
Queries in need of query and index tuning are often those that are most vulnerable to performance regression after a SQL Server upgrade. That may be the best long term solution. It won't hurt to update stats to make sure the optimizer has the best information for informed choices.

If you identify issues where the legacy cardinality estimator performs significantly better after tuning, I suggest the most granular approach to address performance regressions. With a limited number of specific queries, use the FORCE_LEGACY_CARDINALITY_ESTIMATION hint. For a large number of queries within specific database(s), use ALTER DATABASE SCOPED CONFIGURATION. In the case where you experience CE problems with most or all databases, turn on trace flag 9481 at the instance level.

You might first try the ENABLE_QUERY_OPTIMIZER_HOTFIXES query hint or  QUERY_OPTIMIZER_HOTFIXES database scoped configuration so that patches made the CE available in the SP/CUs you've installed are used. These are not used by default due to an overabundance of caution.