SP2016 – Performance Problems caused by SQL database compatibility level 130 or 140

We recently investigated performance problems in context of a couple customers support cases for SharePoint Server 2016. During the analysis we identified that the SQL databases were configured with a SQL compatibility level  of 130 or 140 which caused decreased query performance and increased CPU usage on the database server. Changing the compatibility level to 110 resolved the performance problems.

Background Information:

SharePoint Server 2016 content databases are tested and validated to work best with compatibility level 110. Content databases that are created by SharePoint Server 2016 use the default database compatibility level for the version of SQL Server that the database is installed on. For example, if the SharePoint databases are deployed in an instance of SQL Server 2016, the databases are set to the 130 database compatibility level. Similarly, in an instance of SQL Server 2017, the databases are set to the 140 database compatibility level.

How to fix this:

We strongly recommend that you set the database compatibility level to 110 for SharePoint Server 2016 content databases. To change the compatibility level, run the following TSQL command:

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 110

You can view the compatibility level of all the databases in an instance of SQL Server by using the following TSQL query:

SELECT name, compatibility_level FROM sys.databases

References:

The official statement for this can be found in the following KB:

32 Comments


  1. Hi, Is this scenario valid if we use Project Server 2016 on the same Content DB ? Currently our DB Compatibility level is @ 130.
    SharePoint 2016+ProjectServer2016+SQL 2016 is our current setup.

    Reply

  2. Hi,
    could you describe how you found out that it’s because of the compatibility level? I’ve checked and my databases are on compatibility level 130 (I never changed that manually), so you got me a bit worried, but I can’t get myself to just randomly try that on my production databases. Is there a specific scenario when this issue manifests? Are there any trace logs or performance monitor/windows performance recorder traces that could show that this is causing higher CPU usage?

    Reply

    1. Hi Piotr,
      I’m not aware of specific acenarios and I do not have all the details about the differences between the different compat levels.
      In general you should have better performance with level 110 than with level 130 or 140 with SharePoint Server 2016.
      Cheers,
      Stefan

      Reply

  3. Hi Stefan,

    does this also apply to SQL Server 2014 R2 with compatibility level set to 120?

    Cheers,
    David

    Reply

    1. Hi David,
      although we are currently not aware of perf issues caused by level 120 – SharePoint Server 2016 was optimized for compatiblity level 110 and that is the recommended one to be used.
      Cheers,
      Stefan

      Reply

  4. Is this due to the new cardinality estimator introduced in SQL Server 2014?

    Reply

    1. Hi Joie,
      sorry, I cannot share the technical details here.
      Cheers,
      Stefan

      Reply

  5. Is there a way to ensure all new SharePoint (Content) databases will be created with this recommended compatibility level of 110? Or is the only option to change them back to 110 manually after they have been created?

    Reply

    1. Hi Koen,
      the only way Right now is to change it later. A hotfix is in the works which ensures that new DBs are created with Level 110 going forward.
      Cheers,
      Stefan

      Reply

  6. How come this recommendation is published now? The product is like 2 years old, and we never heard about this earlier.

    Reply

    1. Hi Stefan,
      the code to create it with level 110 was in the product from the very beginning but not active due to a problem in SharePoint Server 2016.
      We detected this when we analyzed these performance problems.
      Cheers,
      Stefan

      Reply

  7. Is it too early to tell if this will have any effect on SharePoint 2019 databases?

    Reply

    1. Hi Mark,
      SP2019 supports compat level 130 and should correctly create databases with this compatibility level.
      Cheers,
      Stefan

      Reply

  8. You mention content databases. Does this also apply to all SharePoint databases like config db.?

    Thanks, ozsp

    Reply

  9. (The content was deleted per user request)

    Reply

  10. Stefan, does this recommendation apply to SP2013 w/ SQL’14 also?

    Reply

  11. Hi Thomas,
    this can happen if you upgrade the databases from an older SharePoint version. Yes. you change them to use Level 110.
    From my current knowledge service application databases should also be using 110 but we are not currently aware of performance implications caused by Level 130 or 140 for these databases.
    Cheers,
    Stefan

    Reply

  12. Hi Stefan
    Do you know if this also applies to SP2019 On Prem
    Thank you

    Reply

    1. Hi Geoff,
      SharePoint Server 2019 is not affected.
      SharePoint Server 2019 supports SQL compatibility level 130 and correctly creates the databases with this compatibility level.
      Cheers,
      Stefan

      Reply

  13. Hi Stefan,
    does this also apply for the config database and all service application databases?
    Thanks
    Thomas

    Reply

      1. Hi Stefan,
        It would be great if official statement in KB 4469993 would include that database compatibility level to 110 is for all databases and not only content databases.
        Also if possible that the Hardware and software requirements for SharePoint Server 2016 – include this information.
        Best regards
        vladan

        Reply

  14. Hi, I did a lot of test runs concerning this issue.
    SharePoint 2013 = SQL compatibility level 100 – and it will fail to set this correctly when provisioning DBs on SQL 2012 and higher.
    SharePoint 2016 = SQL compatibility level 110 – and it will fail to set this correctly when provisioning DBs on SQL 2014 and higher.
    SharePoint 2019 = SQL compatibility level 130 – and it will set this correctly when provisioning DBs on SQL 2016 and higher.
    And then there is the notorious SQL Trace Flag 4199.
    Which will enable SQL query optimizer improvements implemented in SQL Service Packs and CUs.
    These SQL query optimizer improvements are disabled by default on SQL Server up to 2014 – in SQL Server 2016 and later they are enabled by default.
    So there is plenty of room for analysis / clarifications / official statements by Microsoft.
    Questions: Which is the OPTIMAL SQL platform for SharePoint 2013? SQL 2008 R2 with Trace Flag 4199 On or Off? SQL 2012 with compatibility level 100 or 110? And with Trace Flag 4199 On or Off? SQL 2014 with compatibility level 100 or 110 or 120? And with Trace Flag 4199 On or Off?
    Which is the OPTIMAL SQL platform for SharePoint 2016? SQL 2014 with compatibility level 110 or 120? And with Trace Flag 4199 On or Off? SQL 2016 with compatibility level 110 or 120 (130 we know now is bad)? SQL 2017 with compatibility level 110 or 120 (130 and 140 we know now are bad)?
    Since both products are supported for many more years to come I think it would be great to have an in-depth analysis of the interaction between SharePoint SQL queries and the various SQL compatibility AND query optimizer levels.
    And as a result an official statement / recommendation from Microsoft.

    Reply

    1. Hi,
      if you need an official statement, please open a support case with Microsoft to allow us to forward your questions to the product group.
      Thanks,
      Stefan

      Reply

  15. We are at compatibility level 130. Do you foresee any problems switching to 110?

    Reply

  16. Hi Stefan,

    We are using Sql Server 2014 with SharePoint 2016 and compatibility level is set to 120 for config n content databases. Should i change it to 110 or we are ok?

    Reply

    1. Hi Waqas,
      the recommendation is to Change it to 110.
      Cheers,
      Stefan

      Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.