Tryb zgodności baz danych WEBCON BPS (Compatibility Level) – dobre praktyki

Facebooktwitterpinterestlinkedinmail

Począwszy od wersji WEBCON BPS 2022.1.1 najniższą wspieraną wersją MS SQL Server jest wersja 2014.

W związku z tym podczas instalacji/migracji WEBCON BPS,  Compatibility Level dla baz danych jest ustawiany na wartość: 120 – odpowiadającą SQL Server 2014. Takie ustawienie jest stosowane również w przypadku instalacji WEBCON BPS na nowszych wersjach SQL Serwera (2016, 2019…).

W przypadku instalacji baz BPS na serwerze SQL nowszym niż 2014, zmiana Compatibility Level baz danych WEBCON BPS z SQL Server 2014 na wyższy poziom nie jest zalecana.
Wszystkie prace programistyczne, testy poprawności działania oraz testy wydajnościowe przy produkcji systemu WEBCON BPS są wykonywane na bazach z ustawionym Compatibility Level 120.
Tylko w takim ustawieniu baz danych jest gwarantowane bezbłędne działanie systemu.

Zmiana Compatibility Level przez administratora baz danych na poziom odpowiadający wersji serwera SQL (np. na SQL Server 2019) jest technicznie możliwa. Trzeba jednak mieć świadomość, ze takie przełączenie może powodować inne działanie silnika przetwarzania zapytań. Może to doprowadzić do zmian wydajnościowych (zarówno korzystnych jak i niekorzystnych), a nawet do niepoprawnego działania niektórych części systemu WEBCON BPS.

Microsoft w dokumentacji Compatibility Level uznaje za właściwe podejście, aby bazy danych istniejących aplikacji wykorzystywały Compatibility Level do których zostały zaprojektowane i przystosowane, również wtedy, gdy jest on niższy niż wersja zainstalowanego serwera SQL.

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.

W przypadku, gdy baza danych posiada Compatibility Level niższy niż wersja serwera SQL, mogą pojawić się obawy o podatności w zabezpieczeniach (security vulnerabilities) jakie występowały we wcześniejszych wersjach Microsoft SQL Server. Jednak ten temat również został wyjaśniony w dokumentacji – ustawienie Compatibility Level dotyczy tylko kompatybilnej wstecz obsługi przetwarzania zapytań dla konkretnej bazy danych, ale nie wpływa na zachowanie samego serwera SQL.

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.

Dokumentacja Compatiblity Level

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15#remarks

W przypadku przełączenia Compatibility Level dla bazy danych, Microsoft zaleca, aby była to operacja dobrze zaplanowana i odpowiednio przeprowadzona. Microsoft udostępnia odpowiednie narzędzia, które pomagają w poprowadzeniu tego procesu:

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15#best-practices-for-upgrading-database-compatibility-level

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/compatibility-certification?view=sql-server-ver15#compatibility-levels-and-database-engine-upgrades

 

Przykładowe znane problemy wynikające ze zmiany Compatibility Level baz danych WEBCON BPS na poziom wyższy niż 120.

Inline funkcji skalarnych

W przypadku przełączenia Compatibility Level na SQL Server 2019 lub nowszy (150+), optymalizator zapytań SQL serwera zacznie włączać Inline funkcji skalarnych w każdym zapytaniu do takiej bazy danych.

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

Funkcje skalarne zdefiniowane w bazach WEBCON BPS w większości wykonują operacje konwersji lub formatowania na pojedynczych wartościach. Nie ma tu podzapytań lub operacji arytmetycznych na kilku kolumnach z tabeli, przekazanych jako parametry wejściowe. „Inlineowanie” takich funkcji w bazach WEBCON BPS nie poprawia wydajności zapytań. Natomiast ze względu na to, że są one używane w dużej ilości w zapytaniach SQL generowanych np. przez raporty aplikacji, rozmiar takiego zapytania znacznie wzrastać podczas „inlineowania”, co prowadzi do pogorszenia wydajności tych zapytań, a nawet braku możliwości ich uruchomienia.

Ze względu na powyższe „inlineowanie” funkcji skalarnych powinno być wyłączone w bazach WEBCON BPS.

W przypadku przełączenia baz WEBCON BPS na poziom 150 lub wyższy, należy wyłączyć opcję TSQL_SCALAR_UDF_INLINING (Scalar UDF Inlining), aby system mógł działać poprawnie.

Opcja ta jest domyślnie włączana przy zmianie Compatibility Level na SQL Server 2019.

Zabezpieczenie w instalatorze WEBCON BPS
Aby zabezpieczyć działanie systemu przed nierozważną zmianą Compatibility Level na SQL Server 2019 lub wyższy, od wersji WEBCON BPS 2022.1.2.31 w trakcie procesu aktualizacji systemu do wersji wyższych, weryfikowany jest Compatibility Level oraz stan parametru TSQL_SCALAR_UDF_INLINING. Jeśli Compatibility Level bazy danych (dotyczy baz Config, Content, Archiwum) jest ustawiony na SQL Server 2019 i równocześnie parametr TSQL_SCALAR_UDF_INLINING jest włączony wówczas dla tej bazy danych parametr TSQL_SCALAR_UDF_INLINING jest wyłączany (w podsumowaniu instalacji użytkownik jest o tym informowany).

Przełączenie na nowy Cardinality Estimator

W przypadku przełączenia Compatibility Level na SQL Server 2016 lub nowszy, optymalizator zapytań SQL Serwera zacznie wykorzystywać nowszy Cardinality Estimator.

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

Poprawia on czas wykonywania zapytań w większości przypadków, ale może również spowodować wolniejsze wykonywanie zapytań.

Przykładem pogorszenia wydajności jest np. zapytanie (lub widok) wykorzystujące w złączeniach JOIN porównywanie kolumn różnych typów z niejawną konwersją typów (np. w części ON jest podana kolumna typu int i typu nvarchar bez jawnego CASTa).
Taka niejawna konwersja jest dopuszczalna w SQLu, ale nowszy Cardinality Estimator może wygenerować w takim wypadku wolniejszy plan zapytania.
Rozwiązaniem w takim wypadku jest dodanie jawnej konwersji kolumn (CAST) na ten sam typ we wszystkich porównaniach, co wymaga gruntownej analizy wszystkich widoków w bazie i zapytań generowanych przez aplikację (np. skonfigurowanych w Designer Studio).