Jak monitorować bazy danych i instancje SQL Server’a

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.

  1. Rozwiń Management w Eksploratorze Obiektów w SQL Server Management Studio.
  2. W menu kontekstowym Data Collector’a wybierz opcję Configure Management Data Warehouse.
  3. Zaznacz Set up data collection

  4. Kliknij Next

  5. 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.

  6. 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.

ApexSQL Monitor

ApexSQL Monitor jest oprogramowaniem do monitorowania SQL Server’a, które monitoruje system operacyjny, SQL Server, wydajność baz danych w czasie rzeczywistym, dzięki ponad 30 licznikom. Pozwala użytkownikom zdecydować, które instancje i bazy danych SQL Server’a należy monitorować., wybrać metryki dla każdej bazy, instancji SQL Server’a i systemu, a także określić wartości progowe, przekroczenie których wywołuje alerty. ApexSQL Monitor pokazuje mierzone liczniki na wykresach, a dodatkowo przechowuje wartości metryk w centralnym repozytorium, co umożliwia późniejsze ich analizowanie.

Przechwycone dane dla liczników wydajnościowych oraz zapytań są przestawione na wykresach na sześciu domyślnych zakładkach. Istnieje także możliwość tworzenia własnych zakładek.

Zakładka Overview pokazuje najważniejsze informacje o SQL Server’rze oraz jego konfigurację, czasy oczekiwania oraz liczniki takie jak współczynnik trafienia w cache, pełne skany na sekundę, opóźnione zapisy na sekundę i żądania blokad na sekundę.

Overview tab - image 1

Overview tab - image 2

Zakładka System performance przedstawia metryki dla system operacyjnego: kolejkę do procesora, wykorzystanie procesora, wykorzystanie sieci, wykorzystanie pliku wymiany, średnią liczbę bajtów na zapis/odczyt, średnią i bieżącą kolejkę do dysku, średni czas zapisu/odczytu, liczbę zapisów/odczytów na sekundę, wolne miejsce na dysku, dostępną pamięć.

System performance tab

Kolejna zakładka SQL Server performance pokazuje metryki związane z wydajnością instancji SQL Server’a; są to metryki takie jak: liczba żądań wykonania batch’y, kompilacje i rekompilacje na sekundę, transakcje działające najdłużej, pełne skany tabel, przeszukiwania indeksów, opóźniony zapis (lazy writes), żądania blokad, zakleszczenia (deadlocks) na sekundę, połączenia użytkownika, pamięć serwera, współczynnik trafienia w cache, oczekiwana długość życia strony, liczba stron czytanych/zapisywanych i podział strony na sekundę.

SQL Server performance tab

Zakładka Database performance pokazuje szczegóły, dotyczące rozmiarów bazy danych oraz jej logu transakcyjnego, liczbę transakcji na sekundę.

Database Performance tab

Zakładka Query waits przedstawia w postaci wykresów informacje o zapytaniach z najwyższym czasem oczekiwania dla wybranego okresu czasu. Istnieje także możliwość analizy szczegółów pojedynczego lub dla grupy zapytań oraz przejrzenia dla nich planów wykonania zapytania.

Query waits tab

Zakładka Query performance przedstawia w postaci wykresów informacje o zapytaniach, które wykorzystały najwięcej zasobów dla wybranego okresu czasu, umożliwiając prezentację zapytań według średniego czasu wykorzystania procesora, średniego czasu wykonania oraz operacji WE/WY na sekundę. Użytkownik może także określić dla ilu zapytań będą wyświetlane powyższe informacje.

Query performancetab

Użytkownicy mają możliwość definiowania własnych zakładek (Custom tabs) i decydowania jakie metryki – istotne z ich punktu widzenia będą na nich prezentowane po uruchomieniu ApexSQL Monitor’a. Dzięki zakładkom zdefiniowanym przez użytkownika można na jednym ekranie monitorować najważniejsze metryki dla systemu, instancji SQL Server’a i bazy danych.

Jak używać ApexSQL Monitor’a

  1. Uruchom ApexSQL Monitor
  2. Kliknij przycisk Add w menu, aby dodać instancję SQL Server’a, którą chcesz monitorować. Wszystkie monitorowane instancje będą pokazywane w liście instancji po lewej stronie okna aplikacji.
  3. Aby skonfigurować właściwe metryki, kliknij Metrics w menu. Metryki mogą być skonfigurowane dla urządzenia, bazy danych, instancji SQL Server’a i systemu operacyjnego.

    Metrics tab

  4. Na tym samym ekranie można skonfigurować alerty. Aby włączyć uruchamianie alertów, wybierz opcję Alerting i podaj wartość progową lub użyj progu określonego na podstawie wartości bazowej (baseline), jeśli jest ona liczona.

  5. Po zdefiniowaniu metryk i alertów kliknij Apply.

Zdefiniowany alert jest wskazany ikoną wyświetloną za instancją SQL Server’a na liście Servers. Aby zobaczyć listę i szczegóły dla aktywnych alertów, kliknij na Alerts w menu. Lista pokazuje metryki wydajnościowe, czas uruchomienia i zakończenia oraz liczbę wystąpień na każdym poziomie ważności.

Alerts information

ApexSQL Monitor jest łatwym w konfiguracji i użyciu narzędziem, nie wymagającym umiejętności programistycznych. Umożliwia monitoring na różnym poziomie szczegółowości i – w przeciwieństwie do wbudowanych w SQL Server narzędzi – pozwala na wybranie metryk oraz baz danych, które chcemy monitorować. Różne zestawy metryk mogą być zdefiniowane dla wybranej instancji SQL Server’a, wybranej bazy, urządzenia i systemu operacyjnego. Zmniejsza to narzut na działający system, powodowany przez proces monitorujący.

Inaczej niż Activity Monitor czy Data Collector, ApexSQL Monitor pokazuje metryki w czasie rzeczywistym w postaci graficznej, przechowuje dane historyczne w centralnym repozytorium, umożliwia prezentowanie danych historycznych w postaci wykresów. Pozwala także na łatwe definiowanie Alertów i ustalanie ich wartości progowych na poziomie ważności niski, średni i wysoki.

Dodatkowe informacje:
Activity Monitor
Kolektor Danych
Monitorowanie wydajności SQL Server’a

Tłumacz: Anna Lesniak

November 4, 2015