Wydajność SQL Server

Wydajność SQL Server zależy od konfiguracji sprzętu, indeksów, statystyk i jakości napisanych zapytań.

Jak SQL Server wykorzystuje zasoby sprzętowe

Wydajność SQL Server zależy od tego, czy procesor, pamięć operacyjna i podsystem dyskowy mogą obsłużyć obciążenie generowane przez zapytania. Procesor wykonuje między innymi operacje sortowania, agregacji, filtrowania i łączenia tabel. SQL Server może używać planów równoległych, w których zadanie jest dzielone między kilka wątków. Równoległość nie przyspiesza jednak każdego zapytania: dla małych operacji koszt uruchomienia dodatkowych wątków może być większy niż uzyskana korzyść. Znaczenie mają między innymi ustawienia MAXDOP oraz cost threshold for parallelism, które wpływają na wybór planów równoległych.

Pamięć operacyjna jest wykorzystywana przede wszystkim przez bufor danych, w którym SQL Server przechowuje odczytane strony baz danych. Strona danych w SQL Server ma rozmiar 8 KiB, a osiem stron tworzy jeden extent o rozmiarze 64 KiB. Jeżeli potrzebne strony znajdują się już w pamięci, odczyt może zostać wykonany bez ponownego pobierania danych z dysku. Przy niedoborze pamięci część stron jest usuwana z bufora, co zwiększa liczbę operacji wejścia-wyjścia. Szczególne znaczenie ma także odpowiednia ilość wolnego miejsca na dyskach przechowujących pliki danych, pliki dziennika transakcji i pliki tymczasowej bazy tempdb.

Indeksy, statystyki i wybór planu wykonania

Indeks w SQL Server jest strukturą, która pozwala szybciej odszukać wiersze spełniające warunki zapytania. Indeks klastrowany określa fizyczną kolejność przechowywania danych tabeli w strukturze B-tree, natomiast indeks nieklastrowany przechowuje klucze indeksu oraz odwołania do danych. Indeks może ograniczyć liczbę odczytywanych stron, ale każdy dodatkowy indeks zwiększa koszt operacji INSERT, UPDATE i DELETE, ponieważ musi zostać odpowiednio zmodyfikowany. Nieprawidłowy lub nadmiernie rozbudowany zestaw indeksów może więc pogorszyć wydajność zapisów i zwiększyć zużycie miejsca.

Optymalizator zapytań SQL Server wybiera plan wykonania na podstawie między innymi statystyk opisujących rozkład wartości w kolumnach. Statystyki pomagają oszacować liczbę wierszy, które zwróci filtr lub połączenie tabel. Jeżeli statystyki są nieaktualne albo próbka danych nie odzwierciedla rzeczywistego rozkładu wartości, optymalizator może wybrać niewłaściwy operator, na przykład skanowanie dużej tabeli zamiast użycia selektywnego indeksu. Stan statystyk można sprawdzać i aktualizować poleceniami UPDATE STATISTICS lub sp_updatestats, przy czym częstotliwość aktualizacji powinna odpowiadać zmianom danych i charakterystyce konkretnej bazy.

Analiza rzeczywistego planu wykonania pokazuje, jakie operatory zostały faktycznie użyte oraz ile wierszy przetworzył każdy z nich. W SSMS można włączyć zbieranie rzeczywistych planów wykonania, a w samym zapytaniu użyć między innymi SET STATISTICS IO ON i SET STATISTICS TIME ON. Pierwsza opcja raportuje logiczne i fizyczne odczyty stron, a druga przedstawia czas procesora oraz czas upływający. Te dane pozwalają odróżnić problem obliczeniowy od problemu z pamięcią podręczną lub operacjami dyskowymi.

Zapytania, blokady i diagnostyka opóźnień

Jakość zapytania wpływa na ilość danych przetwarzanych przez SQL Server. Warunki filtrujące kolumny powinny być zgodne z typami danych, a funkcje stosowane bezpośrednio na kolumnach mogą ograniczyć możliwość użycia indeksu. Znaczenie ma również unikanie niepotrzebnego pobierania kolumn za pomocą SELECT *, ograniczanie liczby zwracanych wierszy oraz właściwe definiowanie warunków JOIN. Parametryzowane zapytania pomagają ponownie wykorzystywać plany wykonania i ograniczają ryzyko niepożądanego tworzenia wielu podobnych planów dla różnych wartości parametrów.

Opóźnienie nie zawsze wynika z samego czasu obliczeń. Transakcje utrzymujące blokady przez długi czas mogą zatrzymywać inne operacje odczytu lub zapisu. SQL Server udostępnia między innymi widoki dynamicznego zarządzania, takie jak sys.dm_exec_requests, sys.dm_tran_locks i sys.dm_os_waiting_tasks, które pomagają identyfikować oczekujące sesje, blokujące transakcje i typy oczekiwań. W przypadku blokad należy sprawdzić czas trwania transakcji, kolejność modyfikowania tabel oraz poziom izolacji. Samo zwiększenie liczby procesorów nie usunie opóźnienia spowodowanego przez blokadę.

Do oceny obciążenia można wykorzystać Query Store, który przechowuje informacje o zapytaniach, planach i statystykach ich wykonania, o ile funkcja jest włączona dla danej bazy. Query Store ułatwia wykrycie regresji, czyli sytuacji, w której to samo zapytanie zaczyna korzystać z gorszego planu albo wykonuje się dłużej po zmianie danych. Diagnostyka powinna obejmować porównanie czasu procesora, czasu całkowitego, liczby odczytów, liczby wykonanych wierszy oraz częstotliwości uruchamiania zapytania. Pojedynczy wolny pomiar nie wystarcza do potwierdzenia trwałego problemu.

Konserwacja i bezpieczne przywracanie sprawności

Regularna konserwacja baz SQL Server obejmuje kontrolę wzrostu plików, stanu dziennika transakcji, statystyk, fragmentacji indeksów oraz wykorzystania przestrzeni w tempdb. Fragmentacja indeksu może zwiększać liczbę odczytów, ale jej usuwanie nie powinno być wykonywane bez pomiaru. Operacje ALTER INDEX ... REORGANIZE i ALTER INDEX ... REBUILD mają różny koszt oraz różne wymagania dotyczące zasobów. Decyzja o ich zastosowaniu powinna uwzględniać rozmiar indeksu, poziom fragmentacji, rodzaj obciążenia i dostępne okno serwisowe.

Plik dziennika transakcji służy do zapewnienia możliwości odtworzenia operacji zgodnie z modelem odzyskiwania bazy. Jego nagły wzrost może wynikać między innymi z długiej transakcji, braku kopii dziennika w modelu FULL albo operacji wymagającej dużej ilości miejsca. Zmniejszenie pliku poleceniem DBCC SHRINKFILE nie jest ogólną metodą poprawy wydajności i wykonywane cyklicznie może prowadzić do ponownego wzrostu pliku oraz fragmentacji. Najpierw należy ustalić przyczynę wzrostu, dobrać rozmiar początkowy i skonfigurować przyrosty plików w sposób ograniczający częste automatyczne rozszerzenia.

Przed zmianą indeksów, ustawień instancji lub planów wykonania należy zebrać pomiary bazowe i sprawdzić wpływ modyfikacji na reprezentatywnym obciążeniu. Diagnostyka powinna rozróżniać błędny plan, brak indeksu, nieaktualne statystyki, blokowanie, opóźnienia dyskowe i niewystarczającą pamięć. Takie podejście pozwala dobrać zmianę do rzeczywistej przyczyny, a następnie potwierdzić jej efekt przez ponowne porównanie planów, czasów wykonania, oczekiwań i liczby odczytywanych stron.