Audyt SQL Server przed sezonem szczytowym

Plan dwóch tygodni

Audyt przed szczytem ma inny cel niż pełny przegląd środowiska. Szukamy tych niedoskonałości, które przy większym ruchu zamienią się w przestój albo wyraźne spowolnienie, i zostawiamy czas na wdrożenie poprawek przed okresem zamrożenia zmian. Dlatego pracę dzielimy na cztery etapy.

Dane zbieramy w zwykłym tygodniu pracy, najlepiej obejmującym zamknięcie dnia lub inny cykliczny proces. Pomiar z dnia, w którym nic się nie dzieje, daje fałszywie spokojny obraz i prowadzi do poprawek w niewłaściwych miejscach.

Wynik pierwszego tygodnia to także punkt odniesienia. Zapisujemy czasy kluczowych zapytań, obciążenie procesora, oczekiwania i przyrost plików, żeby po wdrożeniu poprawek móc porównać stan przed i po zmianach. W czasie samego szczytu te same dane pozwalają szybko odróżnić normalny wzrost ruchu od rzeczywistego problemu.

Indeksy i statystyki

Indeksy są najczęstszym źródłem szybkich zysków, ale też najczęstszym źródłem zmian wprowadzanych bez zastanowienia. Każdy nowy indeks przyspiesza odczyt i jednocześnie spowalnia zapis, więc w systemach z dużą liczbą transakcji liczy się bilans, a nie liczba dodanych indeksów.

• Brakujące indeksy podpowiadane przez widoki dynamiczne zweryfikowane ręcznie, bo propozycje silnika bywają nadmiarowe i nakładają się na siebie
• Indeksy, które są wyłącznie aktualizowane i nigdy nie są czytane (sys.dm_db_index_usage_stats), oznaczone jako kandydaci do usunięcia, bo spowalniają zapis
• Zduplikowane i nakładające się indeksy na tych samych kolumnach
• Aktualność statystyk na dużych tabelach, w których automatyczna aktualizacja uruchamia się zbyt rzadko
• Zadania utrzymaniowe dopasowane do wielkości baz i dostępnych okien, zamiast przebudowy wszystkich indeksów co noc

Plany zapytań i oczekiwania

Najwięcej informacji daje połączenie dwóch źródeł: statystyk oczekiwań, które mówią, na co serwer czeka, oraz Query Store, który pokazuje, które zapytania zużywają najwięcej zasobów i czy ich plany zmieniały się w czasie.

• Query Store włączony na kluczowych bazach, z rozmiarem i trybem przechwytywania dopasowanym do obciążenia
• Ranking zapytań według czasu procesora, odczytów logicznych i czasu trwania z ostatnich tygodni
• Zapytania z regresją planu, czyli takie, które po skompilowaniu nowego planu działają wielokrotnie wolniej
• Procedury wywoływane z bardzo różnymi wartościami parametrów, podejrzane o problem z wrażliwością planu na parametry
• Dominujące typy oczekiwań, na przykład operacje dyskowe, blokady czy przydział pamięci dla zapytań, porównane z normą dla tego środowiska
• Ustawienia instancji, takie jak maksymalna pamięć serwera, MAXDOP i próg kosztu dla równoległości, ustawione świadomie, a nie pozostawione domyślnie

Wymuszenie dobrego planu w Query Store bywa szybką i odwracalną poprawką na czas szczytu, ale traktujemy je jako rozwiązanie przejściowe. Po sezonie wracamy do przyczyny, którą zwykle jest brakujący indeks, nieaktualne statystyki albo zapytanie napisane w sposób utrudniający pracę optymalizatorowi.

Kopie zapasowe i odtwarzanie

Kopia, której nie odtworzono, pozostaje tylko założeniem. Przed szczytem sprawdzamy przede wszystkim, czy z istniejących kopii da się odzyskać bazę w czasie akceptowalnym dla biznesu, bo zielony status zadania kopii tego nie potwierdza.

• Model odzyskiwania zgodny z wymaganym RPO, ponieważ baza w modelu prostym nie pozwoli odtworzyć danych do wybranego momentu
• Harmonogram kopii pełnych, różnicowych i dziennika transakcji spójny z RPO
• Test odtworzenia największej bazy na osobnym serwerze, z pomiarem rzeczywistego czasu
• Regularne uruchamianie DBCC CHECKDB, żeby uszkodzenie stron nie trafiło niezauważone do wszystkich kopii
• Kopie przechowywane poza serwerem i poza domeną, w której pracuje instancja
• Alert o nieudanej lub pominiętej kopii kierowany do osoby, która na niego zareaguje

Czas odtworzenia porównujemy z RTO uzgodnionym z biznesem. Jeśli odtworzenie największej bazy z kopii pełnej i kilkudziesięciu kopii dziennika trwa dłużej, niż firma może czekać, sama kopia nie wystarczy i trzeba rozważyć częstsze kopie różnicowe, szybszy magazyn albo replikę. Przy okazji sprawdzamy ciągłość łańcucha kopii dziennika, bo jedna ręczna kopia wykonana poza harmonogramem potrafi go przerwać w najmniej odpowiednim momencie.

Miejsce na dziennik, tempdb i dyski

W szczycie najczęściej kończy się miejsce. Dziennik transakcji rośnie szybciej przy większej liczbie zapisów, długich transakcjach i opóźnionych kopiach dziennika, a automatyczny wzrost pliku w nieodpowiednim momencie potrafi na chwilę wstrzymać zapis w całej bazie.

• Przyczyna braku ponownego wykorzystania dziennika (log_reuse_wait_desc) sprawdzona dla największych baz
• Automatyczny wzrost plików ustawiony w megabajtach o rozsądnej wielkości, a nie w procentach
• Liczba wirtualnych plików dziennika (VLF) pod kontrolą, bez tysięcy drobnych segmentów po latach małych przyrostów
• Tempdb z kilkoma plikami danych o równej wielkości i z zapasem miejsca na szczytowe sortowania i tabele tymczasowe
• Prognoza wolnego miejsca na dyskach na cały sezon, z uwzględnieniem kopii zapasowych i dziennika
• Alerty na zajętość dysków i dziennika z progiem, który daje czas na reakcję przed zatrzymaniem zapisu

Warto też sprawdzić, co poza samą aplikacją zatrzymuje skracanie dziennika. Długo otwarta transakcja z zapomnianej sesji, opóźniona replika w grupie dostępności albo zatrzymana replikacja transakcyjna sprawiają, że dziennik rośnie mimo regularnych kopii. W zwykłym tygodniu kończy się to większym plikiem, w szczycie może skończyć się brakiem miejsca na dysku.

Co zamrozić przed szczytem

Ostatnie dni przed sezonem to czas stabilizacji. Po wdrożeniu poprawek zamrażamy konfigurację instancji, harmonogramy zadań i zmiany schematu, a każdy wyjątek wymaga zgody osoby odpowiedzialnej za ciągłość działania. Zespół dyżurny dostaje krótką instrukcję: które wskaźniki obserwować, jakie progi oznaczają problem, jakie działania są dozwolone bez eskalacji i kogo powiadomić w nocy.

• Lista zmian wdrożonych w czasie audytu, z opisem sposobu wycofania każdej z nich
• Okres zamrożenia zmian ogłoszony zespołom aplikacji i dostawcom zewnętrznym
• Instrukcja dla dyżuru z progami alertów i ścieżką eskalacji
• Zaplanowane zadania utrzymaniowe przesunięte poza godziny największego ruchu
• Potwierdzony kontakt do osoby decyzyjnej po stronie biznesu na wypadek przestoju

Po sezonie warto wrócić do listy ryzyk z pierwszego tygodnia. Część poprawek była tymczasowa, a dane zebrane w szczycie są najlepszym materiałem do planowania pojemności na kolejny rok.

Sezon zbliża się szybciej niż przegląd bazy?

Przeprowadzimy audyt SQL Server w dwa tygodnie i zostawimy listę poprawek uporządkowaną według wpływu na ryzyko.

Porozmawiaj z ekspertem