Compatibility Level of WEBCON BPS databases – good practices

Starting with WEBCON BPS 2022.1.1, the lowest supported MS SQL Server version is 2014.

Due to this, when installing/updating WEBCON BPS, the Compatibility Level for databases is set to 120 – which corresponds to SQL Server 2014. This setting is also used when installing WEBCON BPS with newer versions of SQL Server (2016, 2019…).

When installing BPS databases on SQL server higher than 2014, changing the Compatibility Level of WEBCON BPS databases from SQL Server 2014 to a higher level is not recommended.

All development and testing when producing WEBCON BPS is done on databases set to Compatibility Level 120. Only this setting guarantees flawless system operation.

Changing the Compatibility Level by the database administrator to a level corresponding to the SQL server (e.g. SQL Server 2019) is technically possible. However, it should be noted that such a switch may cause the engine processing queries to act differently. This may lead to performance changes (both positive and negative), as well as changing how certain parts of WEBCON BPS work.

In the Compatibility Level documentation, Microsoft suggests that databases of existing applications should use the Compatibility Level for which they were designed and optimized, even if that level is lower than that of the installed SQL server.

For pre-existing databases running at lower compatibility levels, as long as the application doesn’t need to use enhancements that are only available in a higher database compatibility level, it is a valid approach to maintain the previous database compatibility level.

In situations where the database has a Compatibility Level lower than that of the SQL server version, concerns may arise about any security vulnerabilities that were present in earlier versions of Microsoft SQL Server. However, this topic was also addressed in the documentation – the Compatibility Level setting affects the backward compatibility of processing queries for the specific database, but it does not affect the behavior of the SQL server itself.

The database compatibility level setting provides backward compatibility with earlier versions of SQL Server in what relates to Transact-SQL and query optimization behaviors only for the specified database, not for the entire server.

Examples of known issues caused by changing the Compatibility Level of a WEBCON BPS database to higher than 120

Scalar function inline

When switching Compatibility Level to SQL Server 2019 or newer (150+), the SQL query optimizer will start to activate scalar function “inlining” in each query to such a database.

https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining

Scalar functions defined in WEBCON BPS databases mostly perform conversion operations or format data on individual values. There are no subqueries or arithmetic operations on multiple columns of the table passed as entry parameters. “Inlining” such functions in WEBCON BPS databases does not improve performance. On the contrary, due to them being used extensively by SQL queries generated from e.g. application reports, “inlining” will increase the size of these queries to the point where the performance will drop – and in some cases, prevent them from being executed altogether.

Due to this, the option to inline scalar functions should be deactivated in WEBCON BPS databases.

If WEBCON BPS databases have their Compatibility Level set to 150 or higher, turn off TSQL_SCALAR_UDF_INLINING (Scalar UDF Inlining) for the system to work correctly.

This option is turned on by default when switching the Compatibility Level to SQL Server 2019.

Switching to a new Cardinality Estimator

When switching the Compatibility Level to SQL Server 2016 or higher, the SQL query optimizer will start using the newer Cardinality Estimator.

https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server

This should improve the query execution time in the vast majority of cases, but may slow them down in some.

An example of such a case where performance might decrease is a query (or view) using JOIN to compare columns of different types with implicit type conversion (e.g.: the ON part contains int and nvarchar type columns without an explicit CAST).

Such an implicit conversion is allowed in SQL, but the new Cardinality Estimator might generate a slower query plan.

The solution to this is to add an explicit column conversion (CAST) to the same type in all comparisons, which in turn requires a comprehensive analysis of all views in the database and queries generated by the application (e.g. those configured in Designer Studio).