Monitorowanie instancji i baz danych dostarcza informacji niezbędnych do diagnozowania przyczyn różnorodnych błędów i problemów wydajnościowych, a także do optymalizowania SQL Server’a. Nie jest łatwo zdefiniować optymalny poziom wydajności, gdyż jest to zazwyczaj kompromis między wieloma czynnikami związanymi z oprogramowaniem, sprzętem, wymaganiami biznesowymi, polityką firmy oraz innymi czynnikami związanymi ze środowiskiem, w którym działa system bazodanowy.
W małej firmie można łatwo zaakceptować wolne tworzenie raportów, podczas gdy w korporacji wszystkie opóźnienia, wąskie gardła, odczuwalne przez dużą liczbę użytkowników mogą znacząco wpływać na wyniki biznesowe. W takiej sytuacji problemy związane z przetwarzaniem są zazwyczaj nieakceptowalne i powinny zostać naprawione „na wczoraj”.
Nie wystarczy również tylko raz zoptymalizować działania SQL Server’a, należy je stale monitorować, ponieważ dane, schematy, zmiany konfiguracji często prowadzą do sytuacji, w której dodatkowe strojenie jest niezbędne. Typowym przykładem są nieaktualne statystyki – zapytanie, które dotychczas działało poprawnie nagle, bez racjonalnych przyczyn zaczyna wykonywać się bardzo wolno.
Aby utrzymywać optymalnie działającą instancję SQL Server’a, administrator systemu bazodanowego powinien proaktywnie, stale monitorować system, definiować trendy wydajności, określać typowe obciążenie dla różnorodnych miar SQL Server’a. Powinien także mieć możliwość otrzymania powiadomień o odchyleniach od stanu normalnego, aby podjąć właściwe działania.
Jakie metryki SQL Server’a należy monitorować?
Metryki wybrane do monitoringu zazwyczaj zależą od zdefiniowanych celów wydajnościowych, jednak jest zestaw typowych metryk, które dostarczają informacji wystarczających dla podstawowego rozwiązywania problemów wydajnościowych. Po analizie metryk typowych można przejść do metryk dodatkowych, odpowiednich do znalezienia przyczyny konkretnego problemu.
Do typowych monitorowanych metryk należą: wykorzystanie pamięci i procesora, ruch sieciowy oraz aktywność na dysku.
Oprócz monitorowania parametrów SQL Server’a, zaleca się monitorowanie parametrów dla określonej bazy, a także parametrów system operacyjnego.
Metryki, które zazwyczaj monitorujemy to: czas wykorzystania procesora, kolejka do procesora, strony odczytane i zapisane na sekundę, oczekiwana długość życia strony, pamięć serwera, współczynnik trafienia w cache, % użycia procesora, opóźnione zapisy, wykorzystanie sieci, podłączenia użytkownika, itd.
SQL Server dostarcza dwie wbudowane funkcjonalności, umożliwiające monitorowanie: Activity Monitor oraz Data Collector.
Activity Monitor
Activity Monitor śledzi tylko najważniejsze metryki wydajnościowe SQL Server’a. Aby je otrzymać, Activity Monitor wykonuje zapytanie do instancji SQL Server’a co 10 sekund. Wydajność jest monitorowana tylko gdy Activity Monitor jest otwarty, co czyni z niego narzędzie o minimalnym narzucie.
Metryki są widoczne w pięciu panelach: Overview, Processes, Resource Waits, Data File I/O, oraz Recent Expensive Queries.
Panel Overview przedstawia procent wykorzystania czasu procesora, liczbę czekających zadań, działania WE/WY w bazie w MB/sek. i liczba żądań wykonania batch’y.
Panel Processes zawiera informacje o działających w danym momencie procesach instancji SQL Server’a na każdej bazie danych. Należą do nich: używane konto, aplikacja oraz komputer, status zadań i komend, czas oczekiwania, itd. Informacje wyświetlane są w tabeli i mogą być filtrowane po określonej wartości w poszczególnych kolumnach.
Menu kontekstowe panelu Process pozwala skorzystać z funkcjonalności SQL Server Profiler, umożliwiającej głębszą analizę potencjalnych problemów, dla wybranego procesu.
Panel Resource Waits wyświetla czasy oczekiwania zadań SQL Server’a na różnego rodzaju zasoby: pamięć, kompilacje, sieć itd.
Czasy oczekiwania to m.in. ostatni czas oczekiwania, skumulowany czas oczekiwania, średni czas oczekiwania.
Następny panel Data File I/O przedstawia listę wszystkich plików baz danych: MDF, NDF i LDF, ich nazwy, ścieżki dostępu do plików, ostatnią aktywność odczytu i zapisu danych oraz czas odpowiedzi.
Ostatni z paneli Recent Expensive Queries wyświetla zapytania wykonane w ostatnich 30 sekundach, które wykorzystały najwięcej zasobów sprzętowych: zasobów procesora, pamięci, sieci. Menu kontekstowe pozwala otworzyć definicję zapytania w SQL Server Management Studio i wyświetlić jego plan wykonania.
Jak używać Activity Monitor’a
Activity Monitor otwiera się przy pomocy ikony Activity Monitor na pasku narzędziowym SQL Server Management Studio, przy pomocy skrótu klawiaturowego Ctrl+Alt+A lub przy pomocy menu kontekstowego instancji SQL Server’a w Eksploratorze Obiektów.
Activity Monitor śledzi tylko predefiniowany zbiór najważniejszych metryk wydajnościowych SQL Server’a. Nie ma możliwości monitorowania dodatkowych metryk, a metryki już monitorowane nie mogą być usunięte. Możliwe jest także monitorowanie w czasie rzeczywistym. Activity Monitor nie pozwala niestety zapamiętać historii metryk w celu późniejszej analizy. Dlatego wykorzystanie Activity Monitor’a ogranicza się do monitoringu bieżącego oraz do rozwiązywania podstawowych problemów, występujących w systemie bazodanowym. Aby wykonać dogłębną analizę i zoptymalizować wydajność działania SQL Server’a niezbędne jest narzędzie, w którym będzie można wybrać metryki, które należy obserwować, ustalać dla nich wartości progowe i zapamiętywać dane historyczne w celu późniejszej analizy.
Data Collector
Kolejnym wbudowanym SQL Server Management Studio narzędziem, służącym do monitorowania i optymalizacji wydajności jest Data Collector. Kolektor pozwala zbierać dane dla metryk wydajnościowych instancji SQL Server’a i zapamiętywać je w lokalnym repozytorium. Data Collector wykorzystuje istniejące komponenty SQL Server’a: Data Warehousing, SQL Server Agent i Integration Services.
Inaczej niż Activity Monitor, Data Collector pozwala zdefiniować metryki, które chcemy monitorować. Narzędzie oferuje trzy wbudowane zestawy metryk, które zawierają metryki najczęściej wykorzystywane. Dodatkowe metryki mogą być tworzone przy pomocy T-SQL’a lub dostarczonego API.
Jak używać Data Collector’a
Po pierwsze należy się upewnić, że zainstalowane są usługi SQL Server Integration Services oraz SQL Server Agent oraz, że Data Collector jest włączony.
- Rozwiń Management w Eksploratorze Obiektów w SQL Server Management Studio.
- W menu kontekstowym Data Collector’a wybierz opcję Configure Management Data Warehouse.
-
Zaznacz Set up data collection
-
Kliknij Next
-
Wybierz instancję SQL Server’a oraz bazę, która będzie używana jako hurtownia dla danych z monitoringu, a także lokalny folder, w którym będą przechowywane zbierane dane.
-
Kliknij Next i Finish.
Data Collector dostarcza 3 predefiniowane zestawy metryk, dostępne w folderze Management Eksploratora Obiektów, w węźle: Disk Usage, Query Statistics i Server Activity. Każdy zestaw ma oddzielny wbudowany raport.
Zbiór metryk Disk Usage zbiera dane dla plików bazy danych (MDF i NDF), logów transakcyjnych (LDF) i statystyk WE/WY.
Wbudowany raport Disk Usage dostępny jest w menu kontekstowym w Data Collection. Pokazuje wykorzystanie przestrzeni dyskowej dla plików danych, trendy wzrostu zajętości miejsca na dysku przez te pliki oraz średni przyrost dzienny.
Zbiór metryk Query Statistics zbiera dane o kodzie zapytań, statystykach, planach wykonania dla 10 najbardziej kosztownym zapytań.
Zestaw metryk Server Activity zbiera dane o wykorzystaniu procesora, pamięci, operacji WE/WY na dysku i wykorzystaniu sieci oraz czasach oczekiwania zadań wykonywanych w systemie bazodanowym.
Aby rozpocząć zbieranie danych należy skonfigurować Data Collection oraz je uruchomić, a następnie skorzystać z wbudowanych zestawów metryk i gotowych raportów. Dodawanie własnych metryk wykonywane jest przy pomocy kodowania, nie istnieje kreator ułatwiający to zadanie.
Jedną z zalet Data Collector’a jest fakt, że dane nie są zbierane non-stop, istnieje możliwość uruchomienia zbierania danych w oparciu o harmonogramy. Dzięki temu narzędzie nie dodaje dużego narzutu na system, na którym działa SQL Server. Minusem narzędzia jest niemożność wyboru bazy danych, która będzie monitorowana; monitorowane są wszystkie bazy danych wybranej instancji SQL Server’a, czego efektem jest większe obciążenie systemu oraz większe zużycie powierzchni dyskowej.
Funkcjonalność ta nie jest dostępna w wersjach SQL Server’a niższych niż 2008 i w edycjach innych niż Standard, Web, Enterprise i Business Intelligence.
Nie jest także dostępna – jak to było w przypadku Activity Monitor’a – opcja pozwalająca oglądać wyniki w postaci wykresów. Dostępna jest natomiast opcja zapamiętywania przechwyconych danych przez określoną liczbę dni. Dodawanie nowych metryk wymaga wiedzy programistycznej.
Dodatkowe informacje:
Activity Monitor
Kolektor Danych
Monitorowanie wydajności SQL Server’a
Tłumacz: Anna Lesniak
November 4, 2015
