SharePoint Performance : After SQL upgrade

Issues : Performance problems

Root Cause :  SQL compatibility level  of 130 or 140 which caused decreased query performance and increased CPU usage on the database server.

Resolution : Changing the compatibility level to 110 resolved the performance problems

How to fix :

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

 Detail URL :
https://blogs.technet.microsoft.com/stefan_gossner/2018/12/02/sp2016-performance-problems-caused-by-sql-database-compatibility-level-130-or-140/


This also applied to SharePoint2013 ( when you upgrade to SQL server and new content database version will created under compatibility level 120 )