Problemy wydajnościowe w bazach relacyjnych MS SQL są dość częstą bolączką developerów, którzy na nich pracują. Samych przyczyn takich problemów może być mnóstwo.
Każdy z nas zna te przypadki, gdy osoby z tzw. biznesu zgłaszają, że ich raporty nie działają płynnie, codzienne procesy się zablokowały i nie ma nowych danych w aplikacji biznesowej lub nawet aktualizacja danych działa zbyt wolno niż jest to oczekiwane. Wówczas pojawia się pytanie: od czego powinno się zacząć taką weryfikację?
W poniższym artykule postaram się przybliżyć podstawowe podejścia, które pozwolą na sprawniejsze wyszukanie możliwych przyczyn takiej sytuacji.
#1 Co dzieje się w bazie?
Na początku warto zapoznać się z procedurą „sp_whoisactive”.
Jest to procedura stworzona przez Adama Machanic, która naprawdę bardzo ułatwia działanie na bazach danych i może być utworzona w naszej bazie przez administratorów.
Gdy już mamy do niej dostęp wówczas możemy sprawdzać chociażby takie informacje jak:
- jakie aktualnie zapytanie jest uruchomione na bazie i od kiedy,
- jaki jest plan zapytania,
- czy są jakieś sesje z zapytaniami, które się blokują.
Pierwszą metodą, z której ja chętnie korzystam jest sprawdzanie za pomocą powyższej procedury, co w międzyczasie dzieje się na serwerze. Czasami gdy uruchomione przeze mnie zapytanie się zacina, przyczyną bywa to, że inna osoba lub jakiś ustawiony schedule uruchamia kod, który blokuje dostęp do obiektów. Zdarza się również tak, że są zasoby na serwerze są wysycane przez długo działający proces.
#2 Sprawdzenie kodu i logów
Gdy już wykluczymy powyższe czynniki zewnętrzne, to kolejnym etapem jest znalezienie tego kawałka kodu, który powoduje spadek wydajności.
Jeśli mamy dostępne logowanie wykonywanych się procedur/procesów, to warto się temu przyjrzeć.
Czasami kod, który będziemy przeglądać, może mieć logowanie informacji po konkretnych operacjach. Przeglądając logi, możemy więc wychwycić, która część kodu zajmuje najwięcej czasu. Dodatkowo w logach możemy też sprawdzać, czy w danym momencie wykonywania się kodu działo się też coś równolegle. Jeśli tak, to to już może dać nam wskazówki, że na wydajność sprawdzanego kodu mogą mieć też wpływ inne procesy, których nie wychwyciliśmy w punkcie #1. Jeśli zaś takich logów nie mamy, to niestety musimy odpytywać kod po kolei i sprawdzać, w którym miejscu spada jego wydajność.
#3 Czerp informacje z planu zapytania
Gdy znajdziemy wąskie gardło w kodzie, czyli taką część kodu, która powinna z założenia wykonywać się szybciej, niż aktualnie to robi, to możemy zacząć jego sprawdzanie i testowanie przyczyn zaistniałej sytuacji.
Ale uwaga! Ważne jest by pamiętać, że jeśli problem występuje na środowisku produkcyjnym, to nie zawsze jest możliwe testowanie kodu 1:1 – mam tu na myśli głównie ten kod, który modyfikuje dane. Wtedy możemy omyłkowo zmienić te dane lub – testując kod możemy wpłynąć na wydajność działania serwera. Warto więc mieć na uwadze, co chcemy testować oraz to, że warto początkowo spróbować odtworzyć całą sytuację na kopii obiektów np. na środowisku testowym.
Sprawdzanie kodu możemy zacząć od weryfikacji planu zapytania, który włączamy z poziomu programu SQL Server Management Studio. Możemy użyć opcji „Display Estimated Execution Plan”, który pokaże plan zapytania, jaki prawdopodobnie zostanie użyty podczas wykonywania się kodu. Dodatkowo możemy użyć opcji „Include Live Query Statistics”, która pokaże bieżący plan zapytania, gdy uruchomimy kod.
W przypadku drugiej opcji – gdy po chwili od uruchomienia kolejne kroki planu nie będą się wykonywać, to warto poszukać miejsca, w którym zapytanie utknęło – będzie tam nadal przerywana linia. To może dać nam informację dotyczącą np. tabeli, z której w nieefektywny sposób są pobierane dane.
Gdy już jesteśmy przy planie wykonania kodu, szczególną uwagę warto zwrócić na takie informacje jak:
- rozmiar tabeli,
- połączenia tabel,
- agregacje.
Jeśli chodzi o rozmiar tabeli – wydajność może ulec zmianie, gdy Twoje zapytanie odpytuje jedną lub wiele tabel z milionową lub większą liczbą rekordów. Połączenia tabel mogą również dostarczać pewnych informacji, na przykład o możliwości występowania duplikatów w połączeniach, co może skutkować nieoczekiwanym zwracaniem dużych ilości danych. Twoje zapytanie będzie prawdopodobnie powolne też wtedy, gdy połączy dwie tabele w sposób znacznie zwiększający liczbę wierszy w zestawie zwracanym. Warto zatem wiedzieć, jakiego rodzaju złączenia występują i kiedy powinny się pojawić. Co więcej, pewne informacje można znaleźć sprawdzając też agregacje. Złączenie kilku wierszy w celu utworzenia pojedynczego wyniku wymaga więcej przetwarzania niż tylko pojedyncze pobieranie tych wartości. Powinieneś zrozumieć więc, jakie agregacje widzisz w planie wykonania i czasami, czy są jakieś sposoby na uproszczenie kodu, który próbujesz wykonać.
#4 Kiedy problemem jest ilość danych
Z drugiej strony jest jeszcze kilka innych powodów słabej wydajności kodu, które warto sprawdzić, aby usprawnić wykonywanie się zapytań.
W pierwszej kolejności sprawdź, czy nie brakuje indeksów lub czy są one być może nieprawidłowo zdefiniowane. Czasami indeksy są nieaktualne i wymagają aktualizacji. Sprawdź także, czy do systemu nie napłynęło więcej danych niż zwykle. Być może występuje właśnie typowy okres, w którym ilość danych wzrasta, ale może to być również wystąpienie pewnych problemow w powiązanych systemach, które zaczęły wysyłać zbyt dużo danych, niż oczekiwano. Zwróć uwagę też na sposób przesyłania danych pomiędzy obiektami. Przykładowo, używane wcześniej tabele tymczasowe lub zmienne mogą już nie wystarczać, a ładowanie do nich danych może być nieefektywne przy większej ilości danych.
To jest też dobre miejsce by zastanowić się, czy ilość danych, jaką obecnie przetwarzamy jest słuszna.
To znaczy: czy to, że w raporcie, do którego zaczytujemy codzienne dane, musimy za każdym razem brać dane za ostatnie 5 lat? Czy ktokolwiek taką ilość danych analizuje? Być może wystarczyłoby zredukować te dane na poziomie ich zaczytywania, zmienić zakres tych danych na mniejszy, a tym samym szybkość zaczytywanych danych zwiększyłaby się diametralnie.
#5 Kluczowe wyrażenia
Warto też poszukać w samym kodzie kluczowych słów, które mogą znacząco obniżyć efektywność kodu. Wymienię tutaj kilka z nich, z którymi spotykam się najczęściej:
- DISTINCT – kiedy widzimy to słowo w kodzie, warto się mu bliżej przyjrzeć, ponieważ czasami służy ono do szybkiego zatuszowania istnienia duplikatów, być może nawet bez zrozumienia, jak działa kod. W efekcie może to spowodować nieoczekiwane zachowanie się kodu.
- UNION – różni się ono znacznie od UNION ALL, ponieważ pozwala eliminować duplikaty w łączonych danych. Czyli tak samo jak w powyższym przykładzie – należy sprawdzić, czy zostało ono poprawnie użyte w kodzie.
- SELECT * – oznacza „dodaj wszystkie kolumny z każdej połączonej tabeli do wyniku” i może skutkować pobraniem zbyt dużej ilości danych na raz. Dobrą praktyką jest unikanie tego sposobu odczytu danych z tabeli w kodzie docelowym.
- WHERE LIKE '%abc%’ – przeszukiwanie długich ciągów tekstowych w ten sposób może spowolnić działanie kodu. Sprawdź, czy taki warunek jest potrzebny, a jeśli tak – sprawdź, czy możesz napisać kod od nowa bez jego użycia i szybciej uzyskać te same wyniki.
- wiele warunków „OR” w klauzuli WHERE – SQL Server nie przetwarza warunków „OR” w ramach jednej operacji. Zamiast tego ocenia każdy składnik „OR” osobno, co z kolei może prowadzić do słabej wydajności. Jeśli podzielimy zapytanie, SQL Server będzie mógł skorzystać chociażby z indeksów, a zapytanie zostanie zoptymalizowane. Warto więc dzielić zapytania, a następnie scalać ich wyniki, zamiast wykonywać 1 zapytanie z wieloma rozłącznymi warunkami.
Popraw wydajność zapytań!
Powyższe wskazówki to podstawy, od których można zacząć swoją przygodę z badaniem wydajności kodu SQL. Im więcej będziesz zdobywać doświadczenia w tym obszarze, tym łatwiej i szybciej będziesz mógł znajdować przyczyny takich sytuacji. W zależności od miejsca, w którym rozpoczniesz weryfikację kodu, możemy obrać różne techniki weryfikacji, niemniej jednak za każdym razem warto przejść te podstawowe etapy sprawdzania kodu.
Chętnie pomożemy Ci w optymalizacji zapytań!
