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:

Comments (28)

  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.

  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?

    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

  3. DBluhm says:

    Hi Stefan,

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

    Cheers,
    David

    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

  4. Joie Andrew says:

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

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

  5. Koen Zomers says:

    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?

    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

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

    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

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

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

  8. ozsp says:

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

    Thanks, ozsp

  9. Anonymous says:
    (The content was deleted per user request)
  10. Somu76 says:

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

  11. Geoff Firth says:

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

    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

  12. thool007 says:

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

      1. vladan7503 says:

        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

  13. 859 says:

    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.

    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

Skip to main content