Wykres Gantt aktywności SQL Server

Gantt report for MS SQL Server

Wstęp

 

Narzędzi oraz sposobów monitorowania poszczególnych obiektów w SQL Server jest wiele i wiele zostało już na ten temat napisane. W tym poście chciałbym pokazać jeden ze sposobów graficznej analizy aktywności różnych obiektów działającej na serwerze Microsoft SQL Server, który – mam nadzieję – nie jest tak bardzo oczywisty. Idea tego postu to przygotowanie wykresu Gantt (https://pl.wikipedia.org/wiki/Diagram_Gantta), który zobrazuje aktywność uruchamianych procedur, pakietów SSIS oraz zadań SQL Server Agent. Taki wykres powinien okazać się przydatny do ogólnej analizy tego, co dzieje się na serwerze, ale również powinien pomóc dokładniej zrozumieć które zadania są wykonywane równocześnie i jakie zależności mogą występować. Graficzna prezentacja powinna ułatwić oraz przyspieszyć ewentualną analizę problemów. Rzeczony wykres zostanie przygotowany z wykorzystaniem języka R, natomiast dane zostaną przygotowane po stronie SQL Server jako procedura składowana.

Logi dla procedur składowanych

Śledzenie czasów wykonania procedur składowanych w Microsoft SQL Server nie jest rzeczą trywialną. Co prawda możemy skorzystać z widoku systemowego sys.dm_exec_procedure_stats, który udostępnia informacje o statystykach wykonywania procedur, natomiast należy pamiętać, że zawiera on informacje tylko o tych procedurach, które zostały załadowane do pamięci cache. W chwili, kiedy procedura jest usuwana z cache widok ten nie zwróci statystyk dla tej procedury. Innymi sposobami mogłoby być skonfigurowanie SQL Server Profiler, Extendend Events bądź wykorzystanie logów dodatkowych narzędzi do monitorowania Microsoft SQL Server. W przypadku hurtowni danych wydaje mi się jednak, że dobrym pomysłem jest stworzenie własnego mechanizmu do gromadzenia informacji o początku i zakończeniu wykonywania procedur. W przypadku dużych rozwiązań i dużej liczby skomplikowanych procedur dobrym pomysłem może być nawet rozbudowanie takiego mechanizmu oraz logowanie informacji ze znakiem czasowym dla kluczowych miejsc w środku procedur. Dla procesu ETL i dużych procedur logowanie takie nie spowoduje zauważalnego narzutu, a mogą okazać się bardzo pomocne podczas optymalizacji oraz monitorowania. Niezależnie od tego postu warto rozważyć implementację takich logów dla procesów ładowania hurtowni danych. Poniżej przykład takiego rozwiązania poczynając od tabeli, która posłuży do przechowywania logów:

W przykładzie znajduje się dodatkowa kolumna packageRunID do której możemy zapisywać execution_id z pakietu SSIS. W tym przypadku zakładamy, że procedura jest wywoływana z pakietu SQL Server Integration Services i jako parametr przekazywany jest @packageRunID, który następnie pozwoli powiązać konkretne wykonanie procedury z konkretnym wykonaniem pakietu w logach SSISDB. Następnie możemy dodać dwie procedury, które będą odpowiedzialne za logowanie czasu startu i zakończenia procedury.

Dla każdej procedury wystarczy teraz dodać na początku oraz końcu kodu procedury odwołanie do nowo utworzonych procedur. Poniżej przykład:

Przykładowe wykonanie testowej procedury. Warto zauważyć, że pierwsze wywołanie spowoduje błąd, ponieważ parametr dla klauzuli TOP nie może być ujemny.

Dzięki zaimplementowanemu mechanizmu jesteśmy w stanie przygotować proste zapytanie oraz sprawdzić czas wykonywania procedur.

Powyższe wywołanie procedur zaloguje następujące informacje:

Oczywiście zarówno tabelę, jak i procedury z logowaniem można dowolnie rozbudowywać i dodać dodatkowe informacje, natomiast nawet w takiej formie powinny dostarczyć wielu cennych informacji. Na potrzeby tego postu oraz wykresu taki mechanizm dostarczy informacji o czasie wykonywania procedur.

Logi dla SQL Server Integration Services Packages

Czasy wykonywania pakietów w SQL Server Integration Services można przechwycić na kilka sposób. Moglibyśmy przygotować własny mechanizm logowania, użyć logowania dostępnego w SSIS i zapisywać informacje do tabeli sysssislog, natomiast najprostszym sposobem wydaje się przygotowanie zapytania na bazie SSISDB. Na przykład:

Przykładowy wynik zapytania:

Zapytanie zwróci statystyki o czasie wykonania poszczególnych pakietów. Moglibyśmy również oczywiście dodać dodatkowe informacje dostępne w bazie SSISDB.

Logi dla SQL Server Agent

W przypadku SQL Server Agent również wystarczy przygotować stosunkowo proste zapytanie (tym razem do bazy msdb). Na przykład:

Przykładowy wynik zapytania:

Zapytanie to zwróci informacje o konktrenych “Job-ach” i ich zadaniach oraz dodatkowe informacje, które ułatwią ewentualną, dalszą analizę.

Procedura jako źródło danych do wykresu

Wykorzystując powyższe zapytania możemy wreszcie przygotować procedurę, która pobierze statystyki dla procedur składowanych, SSIS oraz SQL Server Agent i zwróci jako pojedynczy rezultat.

Procedura jest stosunkowo prosta i w dużym skrócie łączy dane z powyższych zapytań, natomiast wszelkie dodatkowe informacje, które są unikalne dla poszczególnych obiektów są konsolidowane do jednego atrybutu. Na potrzeby skryptu do generowania wykresu za pomocą ROW_NUMBER generowany jest unikalny identyfikator wiersza. Do procedury zostało dodane kilka parametrów:

  • @startDate – data początkowa logów, które powinny znaleźć się na wykresie
  • @endDate – data końcowa logów, które powinny znaleźć się na wykresie
  • @getSSISPackages – zwróć/nie zwracaj logów dla SSIS
  • @getStoredProcedures – zwróć/nie zwracaj logów dla procedur składowanych
  • @getAgentJobs – zwróć/nie zwracaj logów dla SQL Server Agent
  • @durationMin – dolny limit czasu trwania poszczególnego obiektu w sekundach. Ustawienie tego parametru na 60 spowoduje, że procedura zwróci tylko i wyłącznie te logi dla których czas wykonania był dłuższy niż 1 minuta (60 sekund)

Wykres

Powyższa procedura posłuży jako źródło danych dla wykresu. Wykres będzie generowany za pomocą skryptu R i zostanie w nim wykorzystane biblioteka plotly:

Idea samego skryptu została zaczęrpnięta z http://moderndata.plot.ly/gantt-charts-in-r-using-plotly/ Kod został zmodyfikowany w następujących miejscach:

  • pobieranie dane z bazy danych
  • prezentowanie dane z dokładnością do 1 sekundy
  • obsługa różną ilości źródeł logów (resources)
  • dynamicznie ustalanie rozmiaru wykresu
  • zmiana wyglądu wykresu (marginesy, rozmiar obiektów)
  • debugowowanie

Przykładowy wykres wygenerowany przez powyższy skrypt może wyglądać następująco:

Jak widać na załączonym zrzucie, wykres przedstawia oś czasu oraz poszczególne obiekty, które były wykonywane na serwerze. Dzięki wykorzystaniu biblioteki plotly wykres jest dynamiczny oraz można dowolnie zaznaczać dany fragment (dany moment), aby lepiej przyjrzeć się obiektom, które wówczas były wykonywane, a po najechaniu na poszczególny obiekt jesteśmy w stanie zobaczyć jego szczegóły.

Wykres można eksportować do pliku HTML, a pełny przykład dostępny jest tutaj: ganttMSSQLServer
Wszystkie kody źródłowe oraz przykładowy wykres dostępny jest na GitHub: https://github.com/seequality/seequality_gantt_for_sqlserver

Slawomir Drzymala
Follow me on

Slawomir Drzymala

Still playing with data and .NET technologies
Slawomir Drzymala
Follow me on

Leave a Comment

Your email address will not be published. Required fields are marked *