none
SQL Server 2016 running on compatibility mode 2008 RRS feed

  • Question

  • Hi,

    Does anyone have any experience with running SQL Server 2016 on compatibility mode 2008 in terms of performance/ query plans and also general compatibility with 3rd party applications? It will be reassuring if not much changes on the new database.

    We are planning the migration of a high volume, few terabytes of data 2008 based system to 2016. We are hoping that performance stay the same (at least).

    We will eventually migrate to 2016 compatibility level but not at this stage. Any other advise you can give?

    Thanks very much.

    Panos.


    • Edited by panlondon Wednesday, October 16, 2019 9:19 AM
    Wednesday, October 16, 2019 9:19 AM

Answers

  • Going straight to 130 is not an option again. We will eventually migrate to 130 but that's for 2nd half 2020. Any opinions regarding compatibility levels and how similarly they perform to the original version? Thanks again.

    Things can run faster withi compat level 130, or it can run slower. With compat level 130, you will get all optimizer fixes since SQL 2008 was released, and you will get the new cardinality estimator. Both these can help you to speed up queries by giving better plans. However, there is also the risk that there are queries for which this backfires.

    A good idea is to acticate Query Store as soon as you have installed SQL 2016. Later when you change compat level, Query Store can easily help you to find the regressed plans. There is also a tool for this in SSMS.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by panlondon Thursday, October 17, 2019 8:16 AM
    Wednesday, October 16, 2019 9:45 PM
  • Regarding your question ,please read this article

    https://www.sqlskills.com/blogs/glenn/database-compatibility-level-in-sql-server/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by panlondon Thursday, October 17, 2019 8:18 AM
    Thursday, October 17, 2019 6:30 AM
  • Hi panlondon,

    In addition, starting with SQL Server Management Studio v18, the new Query Tuning Assistant (QTA) feature will guide users through the recommended workflow to keep performance stability during upgrades to newer SQL Server versions. The starting point of QTA assumes that a database from a previous version of SQL Server is moved (through CREATE DATABASE ... FOR ATTACH or RESTORE) to a newer version of the SQL Server Database Engine, and the before-upgrade database compatibility level is not changed immediately. For more details, please refer to Upgrading Databases by using the Query Tuning Assistant.

    Hope this could help you.

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by panlondon Thursday, October 17, 2019 8:19 AM
    Thursday, October 17, 2019 6:35 AM

All replies

  • If you plan to migrate, so I would advise you going to SQL Server 2017...Many features was added and one of my favorite is automatic plan correction

    https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/05/17/automatic-plan-correction-in-sql-server-2017/

    Regarding SQL Server 2016, we did face some performance degradation after migration from SQL Server 2012..

    First of all install latest service pack (SP2), start with 130 compatibility level, and monitor performance, more over enable query store tool to see what queries perform bad and on what period of time....


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, October 16, 2019 9:37 AM
  • Thanks Uri!

     Unfortunately we don't have 2017 as an option. 2016 is the approved migration plan and have tried to suggest 2017 but it's not an option for my company. Also we are taking the low risk route 2016 with compatibility level 2008 (100).

    Going straight to 130 is not an option again. We will eventually migrate to 130 but that's for 2nd half 2020. Any opinions regarding compatibility levels and how similarly they perform to the original version? Thanks again.

    Wednesday, October 16, 2019 9:53 AM
  • Going straight to 130 is not an option again. We will eventually migrate to 130 but that's for 2nd half 2020. Any opinions regarding compatibility levels and how similarly they perform to the original version? Thanks again.

    Things can run faster withi compat level 130, or it can run slower. With compat level 130, you will get all optimizer fixes since SQL 2008 was released, and you will get the new cardinality estimator. Both these can help you to speed up queries by giving better plans. However, there is also the risk that there are queries for which this backfires.

    A good idea is to acticate Query Store as soon as you have installed SQL 2016. Later when you change compat level, Query Store can easily help you to find the regressed plans. There is also a tool for this in SSMS.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by panlondon Thursday, October 17, 2019 8:16 AM
    Wednesday, October 16, 2019 9:45 PM
  • Regarding your question ,please read this article

    https://www.sqlskills.com/blogs/glenn/database-compatibility-level-in-sql-server/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by panlondon Thursday, October 17, 2019 8:18 AM
    Thursday, October 17, 2019 6:30 AM
  • Hi panlondon,

    In addition, starting with SQL Server Management Studio v18, the new Query Tuning Assistant (QTA) feature will guide users through the recommended workflow to keep performance stability during upgrades to newer SQL Server versions. The starting point of QTA assumes that a database from a previous version of SQL Server is moved (through CREATE DATABASE ... FOR ATTACH or RESTORE) to a newer version of the SQL Server Database Engine, and the before-upgrade database compatibility level is not changed immediately. For more details, please refer to Upgrading Databases by using the Query Tuning Assistant.

    Hope this could help you.

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by panlondon Thursday, October 17, 2019 8:19 AM
    Thursday, October 17, 2019 6:35 AM