1

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?

Improve this question
6
  • enable optimize for adhoc, 1; reconfigure with override` .. so did the change helped or not ?
    – Kin Shah
    Commented Jun 8, 2018 at 19:41
  • Enables optimize for adhoc ,but it didn't made any difference.But yeah enabling tf 9481 helped.
    – user9516827
    Commented Jun 8, 2018 at 19:50
  • Did you rebuild your indexes after the upgrade?
    – alroc
    Commented Jun 9, 2018 at 2:03
  • ...or at least update statistics? Also SQL Server doesn't make up implicit conversions to process for the fun of it, which column do you think this is happening with but is not mentioned in the query? The link only shows the truncated version of the text (and it didn't even make it to the whole select list), but there are lots of reasons why a column may participate in a plan even though it is not in the select list.
    – Aaron Bertrand
    Commented Jun 9, 2018 at 6:11
  • Statistics are all updated and the there is no insert or delete to the tables,as i am testing the performance.I have rebuilt the indexes as well.
    – user9516827
    Commented Jun 10, 2018 at 17:01

1 Answer 1

Reset to default
3

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.

Improve this answer
2
  • I appreciate the suggestions.But i am not allowed to change the queries as the application for which the db serves data is 3rd party and the queries are generated from the .net application using linq.I have enabled trace flag 9481 and i believe it made the queries a little faster ,but still queries run for more than a minute.I have compared the execution plan from the old server where the queries were much faster ,and the execution plan is very different,as you can see in the attachment.
    – user9516827
    Commented Jun 11, 2018 at 13:06
  • @user9516827 he was talking about the Query Store, so yes you could. But if that is not enough, then consider reaching out to the developers. My guess is that they support MSSQL 2016, so they should be able to guide you. Heck, if you ask, sometimes you get yes. 😅
    – clifton_h
    Commented Jun 11, 2018 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.