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.
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
The official statement for this can be found in the following KB: