Zmiana compatibility level w SQL Server – wskazówki

Zmiana compatibility level w SQL Server

Ostatnimi czasy wspierałem administratora baz danych podczas zmiany “compatibility level” dla jednej z baz hurtowni danych. Konkretnie zmiana dotyczyła przejścia z trybu 110 (zgodny z SQL Server 2012) na tryb 120 (zgodny z SQL Server 2014). Baza działała w trybie zgodnym z SQL Server 2012, natomiast Server został już wcześniej “upgradowany” do SQL Server 2014. W tym poście chciałbym podzielić się z problemami oraz wskazówkami dotyczącymi tej zmiany. Przedstawię swój plan pracy oraz poszczególne etapy oraz problemy, które napotkałem. Wskazówki mogą się okazać szczególnie przydatne w przypadku baz hurtowni danych, które uczestniczą w procesie ETL.

Teoria

Każda wersja SQL Server przynosi wiele zmian oraz nowości. Jako, że silnik bazy danych również ulega zmianie, systemy, które mają zostać migrowane do nowej edycji, mogą zacząć działać inaczej niż dotychczas. Oczywiści z założenia nowsza wersja powinna być lepsza i szybsza, natomiast może się również okazać, że po zmianie niektóre zapytania będą działać znacznie wolniej. “Compatibility level”, czyli tryb zgodności pozwala na zminimalizowanie takich ryzyk i zapewnia wsteczną kompatybilność. Warto zaznaczyć, że tryb zgodności jest ustawiany na poziomie pojedynczej bazy danych.

Opis środowisko

Zgodnie z tym co wspomniano powyej moja sytuacja wygldała następujaco:

  • Baza danych w charakterze hurtowni danych
  • Baza danych wykorzystywana w procesie ETL obsługiwanym przez SSIS
  • Bardzo duża ilość procedur składowanych w tym dużo długich i skomplikowanych
  • Wiele dużych tabel, większość z liczbą wierszy równą kilkuset milionów wierszy
  • Zainstalowany wcześniej SQL Server 2014 i ustawiony tryb zgodności z SQL Server 2012 (120)
  • Parametry serwera dobre, nie będę podawał szczegółowej specyfikacji
  • Proces ETL działał stabilnie i czas ładowania nie ulegał dużym wahaniom, mniej niż 15 minut różnicy pomiędzy najwolniejszym i najszybszym uruchomieniem w ciągu ostatnich kilku miesięcy
  • Plan zakładał przełączenie bazy w tryb zgodności zgodny z SQL Server 2014

Plan oraz szczegóły pracy

Przygotowanie do zmiany

  1. Sprawdzenie szczegółów technicznych oraz zmian pomiędzy wersjami – na samym początku powinniśmy sprawdzić szczegóły techniczne oraz zapoznać się ze zmianami pomiędzy poszczególnymi wersjami. Informacje te dostarcza Microsoft na stronach MSDN: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level oraz https://msdn.microsoft.com/en-us/library/dn673537.aspx Po tej lekturze, znając własną bazę oraz znając charakter naszych danych powinniśmy wiedzieć czego się spodziewać.
  2. Sprawdzenie potencjalnych błędów – przeglądniecie Internetu w poszukiwaniu typowych problemów. Oczywiście nie będziemy pierwszymi developerami, którzy dokonują migracji lub przełączają tryb zgodności, więc dobrym pomysłem jest zapoznać się z potencjalnymi problemami, które zostały już opisane.
  3. Sprawdzenie kompatybilności kodu – wykorzystanie Microsoft Data Migration Assistant. Microsoft przygotował narzędzie, które pozwoli nam sprawdzić kod bazy danych oraz jego zgodność pomiędzy wersjami SQL Servera. Narzędzie dostępne jest tutaj: https://www.microsoft.com/en-us/download/details.aspx?id=53595

Jako wynik działania aplikacji uzyskamy listę potencjalnych błędów oraz wskazówek jak je rozwiaząć. W moim przypadku zostałem poinformowany o następujących przypadkach:

  • użycie starego typu złączeń w kilku zapytaniach (“Unqualified Join(s)”)
  • użycie typów danych, które wkrótce mogą przestać być wspierane, np. TEXT (“Deprecated data types TEXT, IMAGE or NTEXT”)
  • użycie ORDER BY w niewłaściwy sposób (“ORDER BY specifies integer ordinal”)
  • odwołania do nieudokumentowanych widoków systemowych (“Remove references to undocumented system tables”)

Ostrzeżeń tych było zaledwie kilka i żaden z tych ostrzeżeń nie został oznaczony jako przypadek krytyczny, czyli taki, który mógłby sprawić, że kod po przełączeniu przestałby działać (“BreakingChange”)

Zmiana

  1. Wybór środowiska
    Zmiana powinna zostać szczegółowo zaplanowana. Według mnie najlepszą metodą jest zacząć od najniższego środowiska (DEV) i następnie przechodzić w górę (TEST, PREPROD, PROD). Oczywiście nie jest powiedziane, że problemy po zmianie będą identyczne na każdym środowisku, natomiast będziemy mogli się zorientować co może nas czekać przy najważniejszej zmianie, czyli zmianie na produkcji. Inna sprawa, że wszystkie środowiska muszą zostać przełączone. Musimy natomiast pamiętać, że deweloperzy, którze będą pisać nowe rzeczy powinni zadbać o kompatybilność swojego kodu między wersjami oraz o dodatkowe testy wydajnościowe.
  2. Poinformowanie zespołu
    Jak zaznaczono wyżej każda osoba, która pracuje z bazą danych na każdym środowisku dla którego ma być przeprowadzona zmiana powinien o tym wiedzieć. Po pierwsze, aby zadbać o kompatybilność nowego kodu, a po drugie, aby poinformować, że bieżący kod może działać wolniej lub inaczej niż było to do tej pory. Powinniśmy również zrozumieć czy inne zespoły, narzędzia lub serwisy nie korzystają z tych baz danych i w razie konieczności poinformować zainteresowanych o możliwych problemach.
  3. Zmiana trybu
    Przeprowadzenie samej zmiany jest bardzo proste i sprowadza się do kilku kroków.

    1. Zmiana trybu
      Aby wyświetlić bieżący tryb dla bazy danych można wykorzystać następujące zapytanie:

      Aby zmienić tryb zalecanym jest najpierw przełączyć się w tryb “SINGLE_USER”, następnie zmienić tryb kompatybilności i powrócić do “MULTI_USER”

      Jest to zalecana technika i Microsoft ostrzega, że bez przełączenia się w tryb “SINGLE_USER” niektóre zapytania mogą korzystać z niewłaściwych planów. Jak widać, sama zmiana jest bardzo prosta i ogranicza się do wykonania kilku komend.
    2. Czyszczenie planów zapytania
      Niektóre miejsca w sieci podają, że w przypadku zmiany trybu zgodności procedury według potrzeby będą oznaczone do rekompilacji automatycznie. Z moich obserwacji wynika, że jest to nieprawda, lub nie zawsze tak jest. Moje testy wskazały, że mimo wszystko najbezpieczniejszą opcją będzie, zaraz po zmianie,  wyczyszczenie całego cache’a. 


      Można użyć zapytania, które po prostu oznaczy wszystkie procedury do rekompilacji w przypadku następnego uruchomienia – do znalezienia w Internecie. Wydaje mi się natomiast, że wyczyszczenie zupełnie cache’a w tym przypadku będzie również odpowiednie. W moim przypadku bez oznaczenia procedur i bez wyczyszczenia cache’a, po zmianie, proces ETL miał ogromne problemy. Dla niektórych procedur, które powinny wykonywać się około 3 minut, czas wykonywania uległ zmianie do kilku godzin. Po ponownej zmianie do poprzedniego trybu procedury działały poprawnie. Po wyczyszczeniu cache’a natomiast zdecydowana większość procedur posiadała spodziewany czas wykonywania – oprócz przypadków opisanych poniżej.


Obserwacja i monitorowanie

Bardzo ważnym jest umiejętne sprawdzanie oraz monitorowanie zmiany, aby być w stanie szybko sprawdzać czas wykonywania procedur i zapytań oraz parametrów serwera. Oprócz standardowych technik dobrze sprawdza się przygotowanie odpowiednich zapytań i wykresów – na przykład z wykorzystaniem języka R. Posiadając taki szablon można w prosty i szybki sposób porównać różne ładowania hurtowni. Przykład poniżej:

Wykres pokaże różnice w kolejnych ładowaniach dla poszczególnych obiektów. Dzięki filtrowi można pokazać tylko te obiekty dla których różnica pomiędzy najszybszym i najwolniejszym wykonaniem była większa niż X minut. Kolory pokazują ładowania przed i po zmianie trybu zgodności.

Kolejny przykład przyda się w przypadku porównywania pojedynczych procedur lub pakietów:

W tym przypadku wykres pokazuje poszczególne wykonania procedury. Znowu z podziałem na wywołania przed i po zmianie.

Oczywiście, aby wykorzystać te skrypty trzeba posiadać odpowiednie logowanie oraz przygotować odpowiednie zapytania, natomiast pozwoli to zaoszczędzić sporo czasu podczas monitorowania.

Plan awaryjny

Zmiana może spowodować, że niektóre zapytania lub procedury znacznie spowolnią. Szczególnie przed zmianą na serwerze produkcyjnym trzeba mieć zatem plan B.

  1. Źródło problemu – na samym początku musimy zdać sobie sprawę, że coś jest nie tak. Oczywiście w pierwszej kolejności musimy monitorować podstawowe charakterystyki pracy serwera oraz monitorować to, co aktualnie dzieje się na serwerze. Możemy też wykorzystać różne narzędzia (np. RedGate) lub własne rozwiązania do monitorowania oraz logowania. W tym przypadku będziemy szukać zapytań, które po zmianie stały się wolniejsze.
  2. Zmiana trybu dla pojedynczej procedury  – w przypadku, gdy pojedyncza procedura wykonuje się wolno możemy skorzystać z flagi i wymusić tryb dla całej procedury.
  3. Zmiana trybu dla pojedynczego zapytania – możemy również wymusić konkretny tryb dla pojedynczego zapytania

    Dzięki temu, po zmianie, możemy wymusić poprzedni tryb zgodności tylko dla wybranych obiektów, natomiast wszystkie pozostałe będą korzystać z nowej wersji silnika.

Napotkane problemy

Osobiście spotkałem się z następującymi problemami:

  1. Zmienne tabelaryczne – o zmiennych tabelarycznych napisano i powiedziano już naprawdę wiele. W tym przypadku zmienna tabelaryczna przechowywała tylko kilka wierszy, natomiast w przypadku kilku procedur powodowało to ogromne problemy dla nowej wersji silnika.
    Przykładowy fragment kodu wyglądał tak:

    Następnie zmienna tabelaryczna była wykorzystywana w kolejnych zapytaniach, aby przefiltrować wiersze. Po zmianie niektóre procedury znacznie spowolniły. Lepszym wariantem będzie wykorzystanie tabeli tymczasowej:

    poniżej plany zapytania:

    Zamiana zmiennych tabelarycznych na tabele tymczasowe przyniosła zauważalne zmiany w niektórych procedurach.

  2. Niewłaściwe “hinty”
    Z pewnych względów niektóre “hinty” przestały działać i powodowały spadek wydajności. Zauważyłem problemy z:

    Po usunięciu tych “hintów” zapytania zaczęły znowu działać poprawnie.

  3. Niepokrywające indeksy
    Zmiana trybu poprawności uwypukliła również niepokrywające indeksy. Poniższy przykład prezentuje plan zapytania, dla dokładnie takiego samego zapytania (OLD, NEW). W przypadku “compatibility mode” ustawionego dla SQL Server 2014 zapytanie znacznie spowolniło.

    Problemem okazał się niepokrywający indeks. Będąc dokładnym to tabela posiadała prawidłowy indeks, natomiast zapytanie nie wykorzystywało wszystkich kolumn indeksów tak jak powinno. Po zapewnieniu odpowiedniego zapytania czas wykonywania uległ skróceniu.

  4. Intra-Query Parallel Thread Deadlocks
    W przypadku jednej z procedur wystąpił również problem z Intra-Query Parallel Thread Deadlocks, czyli problem z wielowątkowością, co pokazał odpowiedni deadlock. Deadlocki zostały wychwycone przez narzędzia do monitorowania, natomiast możemy również wykorzystać, na przykład, Extended Events:

    Do sprawdzenia wyników:

    Poniżej schemat “deadlocka”:

    Rozwiązaniem problemy w tym przypadku była zmiana zapytania na bardziej wydajne. Alternatywnie moglibyśmy skorzystać z ustawienia MAXDOP dla tego zapytania.

  5. Problem z Cross Apply
    Nowy “Cardinality Estimator” z pewnych względów zaczął mieć również problemy z cross apply. Pomijając treść samego zapytania po zamianie cross-apply na dodatkowe zapytanie oraz tabelę tymczasową zapytanie znacząco przyspieszyło.

    Powyżej statystyki dla zapytania z cross-apply oraz dla nowej wersji zapytania.

  6. Problem ze zmianą typu danych dla partycji
    Po zmianie trybu zgodności poniższe zapytanie zwracało błąd:

    Problem dotyczył konwertowania wartości funkcji z typu SQL_Variant na datę.

    W rozwiązaniu problemu pomógł Adam Machanic:

    Co ciekawe, przed zmianą, procedury z podobnym kodem nigdy nie zwróciły błędu, natomiast po zmianie procedury zawsze kończyły się błędem.

  7. Merge
    Odnośnie mergy planowana jest dłuższa seria… Z całą pewnością natomiast niektóre procedury z klauzulą “Merge” po zmianie trybu zgodności z 110 na 120 spowolniły. Dla niektórych zmiana była dość znaczna i zapytania trzeba było przepisać.

Podsumowanie

Zmiana trybu zgodności dla SQL Server jest zmianą bardzo prostą i szybką, natomiast w niektórych sytuacjach może przysporzyć wielu problemów. W celu zniwelowania potencjalnych problemów w środowisku produkcyjnym trzeba mieć dobry plan, dobrze rozumieć potencjalne problemy i wiedzieć jak na nie reagować. Mam nadzieję, że ten post oraz “mój” plan pomoże Wam podczas Waszych przygód ze zmianą trybu zgodności lub instalacją nowej wersji SQL Server.

Dodatek

Od wersji SQL Server 2016 warto również przyjrzeć się ALTER DATABASE SCOPED CONFIGURATION https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql

Slawomir Drzymala
Follow me on

Slawomir Drzymala

Still playing with data and .NET technologies
Slawomir Drzymala
Follow me on

Leave a Comment

Your email address will not be published. Required fields are marked *