MS SQL Server - Any benefit to raising compatibility level?

Question

We're running enterprise geodatabases on SQL Server 2016 standard edition. (i.e., not Express).  We discovered recently that our databases are running in 'compatibility level' 100 - i.e. SQL Server 2008.

I'm aware of the SQL commands to ALTER DATABASE to set the compatibility level from 100 to 130 (i.e. from 2008 to 2016).

We have no reason that I can think of to be maintaining compatibility with SQL Server 2008. We don't share our databases with other organizations nor do we join them to tables from 3rd parties.

It doesn't seem to be interfering with using our 10.7.1 enterprise geodatabases on SQL Server 2016.

Is there any benefit in upgrading our 'compatibility level'? As long as we're sure we don't need to maintain compatibility with 2008?

ctalleygreenville · Answer

I would like to know the answer to this as well.

GeorgeRiner · Answer

Mr. Lohr asks the question: should the compatibility level be set to something earlier than the installed version of SQL Server.  He is pointed to the same set of links.

The technical article at this ESRI technical article
explains that the version of either Microsoft's ODBC driver or SQL Server Native Client has to match the version of the database being connected to. No mention of any effects caused by a different compatibility version.

The link at: MS SQL Server - Alter Database 
is a discussion of the SQL statement to 'ALTER DATABASE' to change the compatibility level of a database. It is a Microsoft Document and is not intended to nor does it address the effects of compatibility level on any ESRI product. An interesting statement on this page under the heading "Compatibility Levels and Stored Procedures" states that stored procedures execute using the current compatibility level of the database in which it is defined; and that when the compatibility setting of a database is changed that all of its stored procedures are automatically recompiled. ESRI is silent on any risk/benefit of this recompiling of stored procedures.

the link at: ESRI Upgrading Geodatabase
for the 10.7. version, addresses such issues as: upgrading the database to various ESRI release versions, listing 10.2.x, 10,3.x, 10.4.x, 10.5.x, 10.6.x, 10.7 with no mention of SQL Server compatibility settings. In this context the article states that you cannot downgrade. There is a note that ALLOW_SNAPSHOT_ISOLATION and "READ_COMMITTED_SNAPSHOT" options are required (and turned ON) for geodatabases at release 10.4.1.2 or later. No mention if upgrading an ESRI geodatabase makes any changes to Compatibility Level on a SQL Server. 
This article also describes the 'Upgrade Geodatabase' tool, explaining that it checks the database to see if all the prerequisites are met. A chunk of Python code is supplied as example of upgrading geodatabases through Python tools.  Reading through the code tries to accommodate databases on SQLSERVER, ORACLE, POSTGRE, etc, but does not contain any SQL code to check/change the Compatibility level setting of a SQL SERVER database.
The article ends with the "Tip:" if your database compatibility level is 80 then you must update your database compatibility level. Then a table from Microsoft's technical article about the ALTER DATABASE command is pasted in that shows that compatibility level 80 is for SQL Server 2000 and compatibility level 100 is for SQL Server 2008. The table shows that Microsoft SQL Server versions from 2008 onward support Compatibility levels from 100 and up. (SQL Server 2016 supporting compatibility levels 100, 110, 120, and 130)

The above article contains a link to: ESRI Geodatabase system requirements
which contains no information from ESRI about Compatibility Levels of SQL Servers.  Merely that for the 10.7 release of ESRI, that the supported editions of SQL Server are: 2014 SP3 (64-bit); 2016 (64-bit); 2017 on Linux (64-bit); and 2017 (64-bit).

Interestingly, SQL Server 2008 is not listed as a supported edition for the 10.7 ESRI release. This makes me wonder what's going on if we're using SQL Server 2016 but our databases are set to Compatibility Level for SQL Server 2008. The above ESRI article indicates that SQL 2008 is not supported at the 10.7 release.

Hence my question is not the same as Mr. Lohr's.  Instead I ask: is there any benefit to updating our compatibility level away from 100 (i.e. SQL Server 2008). Since ESRI lists SQL Server 2014 SP3 as the oldest supported version, should we set our compatibility level to at least 120 (for SQL Server 2014). Or should we just update our database compatibility to the edition we're using and update our compatibility level to 130 to match?

From the MS article about the ALTER DATABASE command (see link above) it states: "Starting with compatibility mode 130, any new query plan affecting fixes and features have been intentionally added only to the new compatibility level. This has been done in order to minimize the risk during upgrades that arise from performance degradation due to query plan changes potentially introduced by new query optimization behaviors."  The article is quite exhaustive about the various difference between each compatibility level from 100 and up. Essentially half the article describes these changes.

So, specifically: as the Microsoft article states - if I update the compatibility level of my geodatabases from 100 to 130 this will cause MS SQL Server to recompile the ESRI stored procedures for enterprise geodatabase functionality. Will there be 'performance degradation' due to 'query optimization behaviors' that may exist in the stored procedures?

Asrujit_SenGupta · Answer

Maybe this discussion will help: https://community.esri.com/thread/122198