0

First of all I want to apologize for my English, I hope you understand me correctly.

I have a database migrated from a SQL Server 2005 in a SQL Server 2014. Well, in addition, I have a .NET process that uses ODBC connectors to execute stored procedures in the database.

My problem is that this process lasts 20 minutes against SQL Server 2005, and against the migrated database in SQL Server 2014 it takes 8 hours. After many tests, if the level of compatibility of the database to SQL Server 2008, the process takes only 8 minutes.

Any idea of ​​the reason? What problems can I have when lowering the compatibility level?

  • 1
    ,hey, can you post the code and the execution plan in 2005 and the plan you are getting in 2014, that will help a lot of folks here. – Biju jose Oct 25 '18 at 9:14
  • 2
    Is your SQL Server 2014 patched to latest SP and CU ?. Did you updates the stats for all the databases after migration ?. You are facing what is called as CE regression where when in new CE the plan prepared is bad and this may be due to outdated stats – Shanky Oct 25 '18 at 9:21
  • I'm not a DBA sorry...I'm a :NET Developer. I just execute "sp_updatestats", but the results is the same, my process take hours... – José Miguel Oct 25 '18 at 9:48
3

Any idea of ​​the reason? What problems can I have when lowering the compatibility level?

SQL 2014 made substantial changes to the "Cardinality Estimator" in the query optimizer. For applications that were built and optimized using the older behavior, you may see a performance regression.

The workaround you selected of using the lower database compatibility level is fine. You can also use a Trace Flag 9481 to force the legacy cardinality estimator on newer database compatibility levels.

Eventually you should analyze and optimize the queries and indexes under the new cardinality estimator.

See, eg: SQL Server 2014’s new cardinality estimator (Part 1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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