Zmiana poziomu zgodności bazy danych

Zmiana poziomu zgodności bazy wpływa na zachowanie optymalizatora i dostępność nowszej składni T-SQL.

Co oznacza poziom zgodności bazy danych w SQL Server

Poziom zgodności bazy danych w Microsoft SQL Server określa, z jakim zestawem zachowań języka T-SQL oraz mechanizmów optymalizatora zapytań baza ma współpracować. Jest to właściwość konkretnej bazy, a nie całej instancji. Dzięki temu na jednym serwerze mogą działać bazy ustawione na różnych poziomach zgodności, o ile dany silnik obsługuje wymagane wartości. Poziom można sprawdzić zapytaniem SELECT name, compatibility_level FROM sys.databases;.

Wartość poziomu zgodności jest zapisywana jako liczba. Dla SQL Server 2016 typowa wartość to 130, dla SQL Server 2017 — 140, dla SQL Server 2019 — 150, a dla SQL Server 2022 — 160. Sama obecność konkretnej liczby nie oznacza jednak, że baza używa wszystkich funkcji dostępnych w danej wersji silnika. Ostateczny zakres możliwości zależy również od wersji SQL Server, edycji produktu oraz ustawień bazy i instancji.

Wpływ na optymalizator zapytań

Zmiana poziomu zgodności może zmienić plany wykonywania zapytań, ponieważ nowsze poziomy włączają nowsze zachowania optymalizatora. Dotyczy to między innymi estymacji liczby wierszy, doboru operatorów, kolejności wykonywania operacji, użycia indeksów oraz decyzji o równoległości. Zmiana nie gwarantuje automatycznej poprawy szybkości: jedno zapytanie może wykonać się szybciej, a inne wolniej, jeśli nowa estymacja będzie mniej trafna dla konkretnego rozkładu danych.

Poziom zgodności wpływa także na wersję estymatora kardynalności używaną przez optymalizator. W praktyce przejście z poziomu 110 do 120 wiąże się z przejściem na nowszy model estymacji, chyba że administrator zastosuje odpowiednie ustawienie, wskazówkę zapytania albo mechanizm planu wymuszonego. Dlatego przed zmianą należy przeanalizować najważniejsze zapytania, zwłaszcza te korzystające z dużych tabel, złożonych warunków filtrujących, wielu sprzężeń oraz statystyk o nierównomiernym rozkładzie wartości.

Bezpieczna weryfikacja obejmuje porównanie rzeczywistych i przewidywanych liczności w planach, czasu CPU, czasu trwania, liczby odczytanych stron oraz zużycia pamięci. Pomocne są Query Store, historia planów i testy na kopii bazy. Jeżeli po zmianie pojedynczy plan okaże się niekorzystny, problem można rozpatrywać osobno, zamiast natychmiast obniżać poziom zgodności całej bazy.

Składnia T-SQL i sposób wykonania zmiany

Poziom zgodności zmienia się poleceniem ALTER DATABASE, na przykład: ALTER DATABASE NazwaBazy SET COMPATIBILITY_LEVEL = 160;. Wartość 160 jest właściwa dla SQL Server 2022, natomiast na starszym silniku należy użyć poziomu obsługiwanego przez tę wersję. Przed wykonaniem polecenia trzeba sprawdzić zarówno bieżący poziom, jak i wersję silnika, ponieważ ustawienie nieobsługiwanej wartości zakończy się błędem.

Zmiana poziomu zgodności nie konwertuje danych, nie przebudowuje automatycznie indeksów i nie zmienia schematu tabel. Nie jest również tym samym co aktualizacja silnika SQL Server. Baza może zostać przywrócona na nowszym serwerze i nadal działać z niższym poziomem zgodności, aby ułatwić etapowe testowanie. Takie rozwiązanie nie zapewnia jednak dostępu do wszystkich funkcji nowej wersji i nie powinno być traktowane jako docelowa konfiguracja bez analizy wymagań aplikacji.

Podczas planowania zmiany należy sprawdzić procedury składowane, widoki, funkcje, wyzwalacze i zapytania generowane przez aplikację. Niektóre konstrukcje języka są dostępne dopiero przy określonym poziomie zgodności, a inne zależą bezpośrednio od wersji silnika. Po zmianie należy wykonać testy kompilacji i testy funkcjonalne. Istotne jest także sprawdzenie zapytań dynamicznych, ponieważ ich tekst może być budowany poza kodem bazy i ujawnić problem dopiero podczas konkretnej operacji użytkownika.

Kontrola ryzyka przy zmianie w środowisku produkcyjnym

Przed zmianą warto zapisać aktualną konfigurację oraz przygotować możliwość powrotu do poprzedniego poziomu poleceniem ALTER DATABASE NazwaBazy SET COMPATIBILITY_LEVEL = poprzednia_wartość;. Nie zastępuje to kopii zapasowej, ale pozwala szybko cofnąć zmianę zachowania optymalizatora. Kopia zapasowa jest potrzebna jako zabezpieczenie ciągłości, natomiast sama zmiana poziomu zgodności nie powinna modyfikować zawartości tabel.

Po wdrożeniu należy obserwować czas wykonywania zapytań, blokady, zakleszczenia, zużycie CPU, pamięć oraz zmiany planów zapisane w Query Store. Szczególną uwagę trzeba zwrócić na raporty, zadania cykliczne i procedury uruchamiane rzadko, ponieważ mogą nie wystąpić w krótkim teście ręcznym. Jeśli problem dotyczy tylko jednego zapytania, można rozważyć korektę statystyk, indeksu, zapytania lub konkretnego planu, zamiast zmieniać ustawienie całej bazy.

Diagnostyka powinna obejmować również zgodność sterowników i bibliotek aplikacyjnych z wersją SQL Server. Błąd składni, zmiana typu danych zwracanego przez zapytanie albo inny plan wykonania może ujawnić się w warstwie aplikacji, mimo że sama baza odpowiada poprawnie. W przypadku środowiska produkcyjnego zmianę najlepiej przeprowadzać w zaplanowanym oknie, z udokumentowanym poziomem wyjściowym, scenariuszem testowym i jasno określonym warunkiem wycofania.