Metody monitorowania i optymalizacji SQL Server Analysis Services (SSAS)

Written by on in Analysis Services, Business Intelligence, Programming, TIPS

Wprowadzenie

Każdy system informatyczny, aplikacja, usługa czy też produkt powinien być na bieżąco monitorowany i sprawdzany. Weryfikacja poprawności działania, wydajności czy zadowolenia klientów końcowych. Powodów i celów może być wiele. W tym poście zostaną omówione metody monitorowania usługi SQL Server Analysis Services oraz kostek wielowymiarowych. W kolejnych postach zostaną zaprezentowane konkretne przykłady wykorzystania tych technik. Posty powinny okazać się przydatne do samego monitorowania, ale również podczas audytu w chwili gdy przejmujemy projekt, oraz do lepszego zrozumienia działania SSAS.
Post ten i pozostałe, które ukażą się wkrótce, powinien być szczególnie przydatny dla osób, które:

  • pracują/zarządzają średniej wielkości bazami danych SSAS – setki gigabatów, lub dużymi bazami danych SSAS – terabajty, a nawet setki terabajtów danych kiedy nawet najmniejszy element/problem/decyzja może mieć duże znaczenie
  • chcą dowiedzieć się, kto tak naprawdę używa kostek i jak często oraz w jaki sposób zdobyć te informacje minimalizując dodatkowe obciążenie
  • chcą dowiedzieć się jak dana kostka jest używana, które elementy są faktycznie używane, a które nie
  • chcą dowiedzieć się dlaczego kostka jest tak duża i które grupy miar, wymiary, a nawet konkretne atrybuty i partycje mają największy wpływ na rozmiar bazy
  • starają się poprawić wydajność/zoptymalizować zapytania SSAS
  • starają się poprawić wydajność/zoptymalizować czas procesowania bazy wielowymiarowej
  • borykają się z lockami w SSAS (np. podczas procesowania kostki)

Ewentualnie podczas próby głębszego zrozumienia działania SQL Server Analysis Services. Zrozumienie w jaki sposób działa Analysis Services od środka oraz w jaki sposób go monitorować i sprawdzać jego wydajność powinna pomóc po pierwsze w poprawie działania serwera, ale również – być może – poprzez analizę użycia kostki pozwoli na refaktoryzację i w efekcie jeszcze większą poprawę wydajności. W niniejszym poście zrzuty ekranu zostały zamieszczone w celach poglądowych i nie stanowią swoistego tutoriala.
Z racji, że post jest dość długi w celu przejścia do konkretnego tematu można skorzystać z odnośników:

Pliki kostek analitycznych

Dość popularnym mitem jest sposób przechowywania danych w kostkach analitycznych. Faktem jest, że wszelkie dane kostki SSAS zapisuje w plikach XML dla metadanych oraz plikach binarnych dla danych. W ogromnej ilości plików. Pliki te można znaleźć w folderze (ścieżka domyślna):

C:\Program Files\Microsoft SQL Server\<MSAS<WERSJA_SQL_SERVER>.<NAZWA_INSTANCJI>>\OLAP\Data\<NAZWA_BAZY_DANYCH_SSAS>.<NUMER_BAZY_DANYCH*>.db

Numer bazy danych jest automatycznie generowany w chwili utworzenia bazy danych, ale może ulec zmienia np. podczas przywrócenia bazy danych. Dodatkowy plik z metadanymi dla bazy danych znajduje się w katalogu (ścieżka domyślna):

C:\Program Files\Microsoft SQL Server\<MSAS<WERSJA_SQL_SERVER>.<NAZWA_INSTANCJI>>\OLAP\Data\<NAZWA_BAZY_DANYCH_SSAS>.<NUMER_BAZY_DANYCH*>.xml

Na potrzeby tego wpisu nie znajduje się w nim raczej nic interesującego. W folderze bazy danych znajdziemy:

  • Plik: <NAZWA_ŹRÓDŁA_DANYCH>.<ds>.xml – zawiera definicję źródła danych
  • Plik: <NAZWA_WIDOKU_ŹRÓDŁA_DANYCH>.<dsv>.xml – zawiera definicję widoku źródła danych
  • Pliki: <NAZWA_WYMIARU>.<NUMER>.dim.xml – zawiera metadane dla wymiaru, jego definicję jak i listę wszystkich plików, które zawierają dane dla tego wymiaru.
  • Pliki: <NAZWA_KOSTKI>.<NUMER>.cub.xml – zawiera metadane dla kostki, jego definicję jak i listę wszystkich plików, które zawierają dane dla kostki (pozwalających połączyć grupy miar z wymiarami)
  • Foldery: <NAZWA_WYMIARU>.<NUMER>.dim – zawiera pliki z danymi z wymiarów
  • Foldery: <NAZWA_KOSTKI>.<NUMER>.cub – zawiera pliki z metadanymi i definicjami grup miar oraz zawiera podfoldery zawierające dane dla partycji grup miar oraz dane potrzebne do połączenia danych z plików dla grup miar (partycji) z danymi z plików dla wymiarów.

Te dwa ostatnie foldery, które zawierają dane kostki, mogą okazać się bardzo interesujące podczas monitorowania oraz audytu kostki. Oczywiście nie trudno zgadnąć, że im większy wymiar lub im większa tabela faktów tym większa będzie ilość plików, natomiast należy również pamiętać, że oprócz samych wartości z bazy danych Analysis Services generuje pliki potrzebne do powiązania miar i wymiarów, dane dla hierarchii czy też agregacji. Analiza rozmiarów plików i folderów pozwala lepiej zrozumieć faktyczny rozmiar bazy analitycznej oraz rozmiar poszczególnych wymiarów i grup miar.

Zobacz post: “SSAS analiza plików bazy analitycznej”

Na temat samych plików kostki wielowymiarowych, ich generowania, budowy oraz powiązań pomiędzy nimi powstanie osobny wpis, który skupi się na szczegółach.

Logi serwera SSAS

Niezależnie od tego czy opisana w następnym punkcie usługa Flight Recorder jest włączona lub wyłączona Analysis Services zapisuje szczególnie ważne z jego punktu widzenia logi w dodatkowym pliku: msmdsrv.log (nazwa domyślna), który można znaleźć w folderze (ścieżka domyślna):

C:\Program Files\Microsoft SQL Server\<MSAS<WERSJA_SQL_SERVER>.<NAZWA_INSTANCJI>>\OLAP\Log\

Plik ten zawiera informacje o

  • czasie kiedy usługa została uruchomiona lub zatrzymana
  • zakleszczeniach i blokadach, które mogą wystąpić np. podczas procesowania
  • błędach krytycznych, które spowodowały np. zatrzymanie usługi
  • inne krytyczne błędy

Jak widać zapisywane są tutaj raczej błędy szczególnie istotne oraz dotyczące samej usługi. W przypadku – na przykład – błędów procesowania błędy te nie zostaną tutaj zapisane. Logowanie jest domyślnie włączone i nie da się go wyłączyć. Warto natomiast zwrócić uwagę na wielkość i ilość plików. Opcje te można skonfigurować w ustawieniach serwera.

  • Log\File – nazwa pliku z logami
  • Log\MaxFileSizeMB – limit rozmiaru pliku, domyślnie 0, czyli bez limitu
  • Log\MaxNumberOfLogFiles – limit plików (po przekroczeniu limitu rozmiaru zostanie utworzony nowy plik), domyślnie 0 i logi zapisywane są w jednym pliku
  • Log\MessageLogs – zakres logów, które chcemy zapisywać. Tak naprawdę nie ma informacji o innych kategoriach i Microsoft zaleca nie zmieniać tych ustawień bez wcześniejszego kontaktu z nimi
  • LogDir – folder w którym zapisywane są logi (nie tylko główny log usługi, ale domyślnie również te opisane poniżej)

Wartość “yes” w kolumnie “Restart” informuje o tym, że po zmianie ustawienia niezbędny jest restart usługi oraz, że plik ten wbrew niektórym postom nie jest czyszczony w chwili restartu usługi.

W przypadku dużej ilości baz danych i kostek lub ewentualnych problemów warto również zapoznać się z metodami opisanymi tutaj:
* https://thomaslarock.com/2015/01/how-to-recycle-the-sql-server-analysis-services-msmdsrv-log-file/
* https://www.sqlservercentral.com/Forums/Topic625205-146-1.aspx

Analiza tego plika logów oraz jego monitorowanie może być przydatne w sytuacji nieplanowanego zatrzymania usługi, problemu z wielkością samego pliku lub w przypadku występowania zakleszczeń.

Flight Recorder

Flight Recorder to kolejny mechanizm, który pozwala na logowanie zdarzeń w SSAS. Mechanizm ten wykorzystuje funkcjonalności SQL Server Profiler i zapisuje logi w postaci pliku ze zdarzeniami (trace file). Największą zaletą jest to, że plik ten może zostać przez nas później otwarty i odtworzony w takiej samej kolejności zdarzeń za pomocą SQL Server Profiler. Dzięki temu można analizować logi z serwera produkcyjnego na innej maszynie. Flight Recorder umożliwia przechwytywanie następujących zdarzeń:

  • Audit Login
  • Audit Logout
  • Audit Server Starts and Stops
  • Command Begin
  • Command End
  • Discover Begin
  • Discover End
  • Error
  • Notification
  • Query Begin
  • Query End
  • Query Subcube
  • Server State Discover Begin
  • Server State Discover Data
  • Server State Discover End

Zdarzenia logowane są zgodnie z definicją, która znajduje się w (ścieżka domyślna):

C:\Program Files\Microsoft SQL Server\<MSAS<WERSJA_SQL_SERVER>.<NAZWA_INSTANCJI>>\OLAP\bin\flightrecordertracedef.xml

Ciekawy post o definicji można znaleźć tutaj: http://byobi.com/2016/01/ever-wondered-whats-captured-in-the-ssas-flight-recorder/

Plik z definicją to zwykły plik XML i sama definicja może być modyfikowana w celu zmiany interesujących nas zdarzeń do logowania, chociaż Microsoft tego nie zaleca i jest to praktycznie nie udokumentowane. Zarówno plik z definicją jak i inne właściwości są konfigurowalne z poziomu ustawień serwera SSAS

  • Log\FlightRecorder\Enabled – włącza/wyłącza usługę Flight Recorder
  • Log\FlightRecorder\FileSizeMB – limit wielkości plików, które generuje SSAS
  • Log\FlightRecorder\LogDurationSec – limit czasu trwania pojedynczego logu
  • Log\FlightRecorder\SnapshotDefinitonFile – lista typów żądań, które będzie wysyłał Profiler
  • Log\FlightRecorder\SnapshotFrequencySec – właściwość, która kontroluje jak często SSAS/Profiler będzie wysyłał żądania w celu przechwycenia zdarzeń
  • Log\FlightRecorder\TraceDefinitionFile – adres do pliku z definicją zdarzeń do śledzenia

Zgodnie z powyższymi ustawieniami SSAS będzie rejestrował zdarzenia w pliku FlightRecorderCurrent.trc do czasu, aż nie przekroczy limitu wielkości lub limitu czasu. Jeżeli to się stanie SSAS zmieni nazwę tego pliku na FlightRecorderBack.trc, utworzy nowy plik FlightRecorderCurrent.trc i znowu zacznie logować aż do przekroczenia limitu wielkości lub czasu. Jeżeli plik FlightRecorderBack.trc już istnieje SSAS usunie stary plik i podmieni z nowymi zdarzeniami. Dlatego też warto pamiętać, że faktyczna liczba miejsca potrzebnego na dysku jest dwa razy większa niż ta podana w parametrze oraz, że logi będą obejmować czas dwa razy dłuży niż ten podany we właściwościach.

Zdarzenia przechwycone przez Flight Recorder można otworzyć w SQL Server Profiler, a nawet odtworzyć te same zdarzenia na innym serwerze w celach diagnostycznych.

This slideshow requires JavaScript.

Należy również pamiętać, że Flight Recorder domyślnie jest włączony na serwerze i na serwerach produkcyjnych powinien on zostać wyłączony w celu zapewnienia najlepszej wydajności i redukcji niepotrzebnego obciążenia. Na serwerze produkcyjnym powinien on być włączany tylko wtedy, gdy zachodzi taka realna potrzeba.

Flight Recorder to źródło wielu informacji dla administratorów serwera. Szczególnie użyteczny powinien okazać się wtedy, gdy serwer “wydaje się” być wolny, nastąpił nagły spadek wydajności lub podczas okresowego sprawdzania działania. Pamiętać należy jednak o tym, że na serwerze produkcyjnym nie powinien on być nieustannie włączony.

Query Log

Kolejną funkcjonalnością, którą udostępnia Analysis Services jest Query Log. Po włączeniu tego mechanizmu zapytania, które uruchamiają lub generują (przez aplikacje klienckie) użytkownicy będą logowane do tabeli w bazie danych. W celu skonfigurowania tej opcji należy udać się do ustawień serwera.

  • Log\QueryLog\CreateQueryLogTable – prawda lub fałsz, służy do utworzenia tabeli logowania
  • Log\QueryLog\QueryLogConnectionString – dane do połączenia się z bazą danych, gdzie chcemy zapisać logi, po kliknięciu w puste pole można wybrać kreator połączenia
  • Log\QueryLog\QueryLogSampling – próbkowanie, opcja ta pozwala na logowanie co n-tego zapytania do SSAS, pozwala na ograniczenie wielkości tabeli wynikowej
  • Log\QueryLog\QueryLogTableName – nazwa tabeli, która zostanie utworzona w celu przechowywania logów

Po konfiguracji we wskazanej bazie danych zostanie utworzona tabela i kolejne zapytania do bazy OLAP będą tam logowane. Warto zwrócić uwagę, że jeżeli połączenie do bazy danych zostanie skonfigurowane, natomiast właściwość “Log\QueryLog\CreateQueryLogTable” będzie ustawiona na ‘False” Analysis Services będzie zakładał, że tabela już istnieje i będzie starał się logować zapytania.

Przykładowa zawartość tabeli może wyglądać następująco:

SELECT 
	[MSOLAP_Database] ,
	[MSOLAP_ObjectPath] ,
	[MSOLAP_User] ,
	[Dataset] ,
	[StartTime] ,
	[Duration]
FROM [dbo].[OlapQueryLog];
  • MSOLAP_Database – nazwa bazy danych SSAS
  • MSOLAP_ObjectPath – ścieżka do partycji
  • MSOLAP_User – użytkownik, który wykonuje zapytanie
  • MSOLAP_Dataset – ciąg znaków, który opisuje, które wymiary i atrybuty zostały wykorzystane do wykonania zapytania
  • MSOLAP_StartTime – czas uruchomienia zapytania
  • Duration – czas trwania zapytania w milisekundach

Warto zauważyć, że jeżeli w jednym zapytaniu odwołujemy się do dwóch partycji (np. dwóch miar z dwóch różnych grup miar) to w tabeli zostaną dodane dwa wpisy. Warto również dodać, że jak widać na załączonym zrzucie ekranu dane, które są logowane nie są zbyt użyteczne dla nas samych. Analysis Services natomiast wykorzystuje dane z takiej tabeli w trakcie korzystania z “Usage-Based Optimization Wizard” podczas tworzenia agregacji. Aby skorzystać z tej funkcjonalności musimy właśnie najpierw uruchomić to logowanie. Mimo wszystko jednak sama nazwa użytkownika, nazwa partycji z której użytkownik korzysta oraz czas trwania zapytania daje dużą porcję informacji o korzystaniu z kostki. Do samego logowania użytkowników poleciłbym raczej inne metody, natomiast jeżeli tak czy tak logujemy te informacje to można z nich skorzystać. Analiza ich powinna pomóc w zrozumieniu:

  • kto korzysta z kostki
  • kto korzysta z jakich partycji (grup miar)
  • kto wykonuje najcięższe zapytania

Error log

Monitorować można również procesowanie danych. Oczywiście najprawdopodobniej każda aplikacja pozwoli nam przechwycić błędy z procesowanie jeżeli takowe wystąpią, natomiast SSAS zapewnia własną, wbudowaną metodę ich logowania nazywaną Error log. Aby utworzyć plik logowania dla procesowania można skorzystać z kreatora z poziomu Management Studio.

This slideshow requires JavaScript.

Teraz, w przypadku, gdy procesowanie wymiaru zwróci błędy zostaną one zapisane w podanym pliku. W przypadku gdy procesowanie zakończy się sukcesem plik będzie pusty. Warto pamiętać, że tak skonfigurowany plan procesowania można zapisać jako skrypt i w skrypcie pojawi się informacja o miejscu gdzie powinny zostać zapisane błędy z procesowania.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ErrorConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
    <KeyErrorLogFile>C:\Users\sldr\Desktop\ssasErrorLog.log</KeyErrorLogFile>
    <KeyDuplicate>ReportAndContinue</KeyDuplicate>
    <NullKeyConvertedToUnknown>ReportAndContinue</NullKeyConvertedToUnknown>
  </ErrorConfiguration>
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
      <Object>
        <DatabaseID>AdventureWorksDW2014Multidimensional-EE</DatabaseID>
        <DimensionID>Dim Sales Territory</DimensionID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

Przykładowy plik może wówczas wyglądać następująco:

6/25/2017 3:40:08 PM: Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DimSalesTerritory', Column: 'SalesTerritoryCountry', Value: 'France'. The attribute is 'Sales Territory Country'. Errors in the OLAP storage engine: The record was skipped because the attribute key is a duplicate. Attribute: Sales Territory Country of Dimension: Sales Territory from Database: AdventureWorksDW2014Multidimensional-EE, Record: 5.
6/25/2017 3:40:08 PM: Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DimSalesTerritory', Column: 'SalesTerritoryCountry', Value: 'France'. The attribute is 'Sales Territory Country'. Errors in the OLAP storage engine: The record was skipped because the attribute key is a duplicate. Attribute: Sales Territory Country of Dimension: Sales Territory from Database: AdventureWorksDW2014Multidimensional-EE, Record: 5. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while the 'Sales Territory Country' attribute of the 'Sales Territory' dimension from the 'AdventureWorksDW2014Multidimensional-EE' database was being processed.

Metoda ta może okazać się przydatna do przechwytywania i logowania błędów z procesowania, natomiast w przypadku procesowania wielu wymiarów czy całej kostki metoda ta jak można doczytać w różnych postach może nie zapisać wszystkich napotkanych błędów i informacje zawarte w pliku mogą okazać się niewystarczające.

SQL Server Profiler

Profiler jako narzędzie do analizy oraz optymalizacji powinno być znane niemalże każdemu. Podobnie jak dla SQL Server również i dla Analasis Services możemy przechwytywać zdarzenia serwera.

This slideshow requires JavaScript.

SQL Server Profiler to jednej z najpotężniejszych narzędzi jakie możemy użyć. Dostarcza mnóstwo inforamcji, które mogą okazać się przydatne podczas:

  • optymalizacji zapytań – swoisty plan wykonania zapytania dla SSAS
  • sprawdzania oraz optymalizacji agregacji
  • sprawdzenie oraz optymalizacja procesowania
  • analiza locków oraz deadlocków

Możliwości wykorzystania SQL Server Profiler jest tak wiele, że prawdopodobnie w przyszłości pojawią się wpisy o konkretnych przypadkach użycia.

Extended Events

Począwszy od SQL Server 2016 Analysis Services może być również monitorowany oraz analizowany za pomocą Extendend Events. Można by powiedzieć, że oficjalnie, ponieważ “nieoficjalnie” można było używać go już wcześniej co opisał Chris Webb tutaj: https://blog.crossjoin.co.uk/2012/05/05/using-xevents-in-ssas-2012/ Liczba wskaźników, które możemy śledzić nie różni się znacznie od tej dostępnej w SQL Profiler, więc tak naprawdę wybór pomiędzy tymi narzędziami będzie należał raczej do preferencji użytkownika. Oczywiście pamiętając o ogólnych wadach i zaletach korzystania z obu rozwiązań. Na przykład Extendend Events z reguły powoduje mniejsze obciążenie

This slideshow requires JavaScript.

Ciekawe porównanie i podsumowanie odnośnie SQL Server Profiler i Extendend Events dla SSAS napisał wcześniej już wspomniany Chris Webb tutaj: https://blog.crossjoin.co.uk/2016/04/18/profiler-extended-events-and-analysis-services/

Performance Monitor

Windows Performance Monitor (perfmon) jest to narzędzie to uruchamiania, zarządzania i przeglądania tak zwanych liczników wydajnościowych (Performance Counters). Liczniki te zawierają informację odnośnie jednego konkretnego wskaźnika w danej chwili. Dzięki Performance Monitor można monitorować i sprawdzać wydajność systemu, aplikacji i usług, w tym także Analysis Services. Narzędzie to jest integralną częścią systemu Windows, aby rozpocząć z nim pracę wystarczy go uruchomić oraz dodać interesujące nas wskaźniki.

This slideshow requires JavaScript.

Dla SSAS dostępne są następujące liczniki:

  • Cache – statystyki dotyczące agregacji i użycia przez nie pamięci cache
  • Connection – statystyki dotyczące połączeń
  • Data Mining Prediction – statystyki dotyczące wydajności procesowania modeli DM
  • Data Mining Model Processing – statystyki dotyczące zapytań DM począwszy od ilości zapytań DMX aż po liczbę predykcji jaką SSAS potrafi zwrócić
  • Locks – “locki”, “deadlocki” oraz “waitsy”
  • MDX – statystyki dotyczące wydajności zapytań MDX. Bardzo dużo informacji o tym w jaki sposób SSAS intepretuje wykonywane zapytanie.
  • Memory – statystyki użycia pamięci RAM przez serwer SSAS
  • Proactive Caching – podstawowe statystyki dotyczące Proactive Caching
  • Processing Aggregations – statystyki odnośnie tworzenia (procesowania) agregacji (plików agregacji) w SSAS
  • Processing Indexes – statystyki odnośnie tworzenia (procesowania) indeksów (plików dla indeksów) w SSAS
  • Processing – statystyki dotyczące procesowania danych – zarówno czytania danych z bazy danych jak i zapisu danych do pliku
  • Storage Engine Query – statystyki silnika zapytań SSAS. Znowu informacje, które pozwalają analizować w jaki sposób realizowane są zapytania SSAS oraz skąd fizycznie Analysis Services pobiera dane (cache, agregacje, pliki, etc)
  • Threads – statystyki odnośnie wątków – zarówno dla wykonanych zapytań jak i dla procesowania

Szczegółowy opis wszystkich Performance Counters dla SSAS znajdziemy na MSDN: https://docs.microsoft.com/en-us/sql/analysis-services/instances/performance-counters-ssas

Performance Monitor może być szczególnie przydatny podczas analizy oraz optymalizacji zapytań i procesowania.

Widoki systemowe SSAS

Podobnie jak w SQL Server w Analysis Services dostępny jest szereg widoków systemowych, które zawierają mnóstwo informacji szczególnie przydatnych podczas monitorowania oraz sprawdzania działania bazy danych SSAS. Najważniejsze z punktu widzenia tego wpisu zostaną omówione poniżej, natomiast pełna lista dostępna jest na MSDN https://docs.microsoft.com/en-us/sql/analysis-services/instances/use-dynamic-management-views-dmvs-to-monitor-analysis-services a szczegółowy opis wraz z przypadkami użycia zostanie opublikowany w osobnym poście.

DISCOVER_COMMAND_OBJECTS – statystyki aktualnie wykonywanych zapytań. Informacje o konkretnych obiektach, które musiały zostać odczytane, aby wykonać zapytanie oraz podstawowe statystyki jak użycie procesora, informacje o przeczytanych oraz zapisanych danych i liczba zwróconych wierszy. Nie znajdziemy tutaj jednak samej treści zapytania
DISCOVER_COMMANDS – zapytania, które obecnie są wykonywane na serwerze i ewentualnie zapytania, które zostały wcześniej wykonane, ale połączenia nadal są otwarte. Znajdziemy treść zapytania, które zostało wykonane oraz czas jego wykonywania, informacje o przeczytanych oraz zapisanych danych i liczbę jaką zostało one wykonane.
DISCOVER_CONNECTIONS – otwarte połączenia do serwera. Zapytanie zwraca listę otwartych połączeń z informacjami o użytkowniku, aplikacji, rozpoczęciu połączenia, czasu trwania połączenia, czasie ostatniego połączenia, czasie trwania ostatniego połączenia, czasie bezczynności oraz informacje o ilości odebranych i wysłanych danych.
DISCOVER_MEMORYUSAGE – pamięć użyta dla różnych obiektów kostki, w tym konkretnych plików i konkretnych atrybutów wymiarów.
DISCOVER_OBJECT_ACTIVITY – statystyki wykorzystania zasobów maszyny dla różnych obiektów kostki od czasu uruchomienia usługi. Dane nie obejmują poszczególnych atrybutów wymiarów jak w przypadku MEMORYUSAGE, natomiast znajdziemy tutaj więcej informacji. Między innymi zużycie procesora, ilość odczytanych i zapisanych danych oraz informację o tym jak wiele razy było odwołanie do danego obiektu.
DISCOVER_OBJECT_MEMORY_USAGE – statystyki dotyczące zużycia pamięci według poszczególnych obiektów kostki w tym konkretnych atrybutów wymiarów
DISCOVER_SESSIONS – bieżące sesje SSAS. Informacje o użytkowniku, czasie trwania sesji, czasie bezczynności, ostatnim zapytaniu i liczbie zapytań oraz podstawowe statystyki dotyczące wykorzystanych zapytań

Widoki systemowe są dostępne z poziomu zapytania MDX. Przykładowo:

select 
	OBJECT_AGGREGATION_HIT
	, OBJECT_AGGREGATION_MISS
	, OBJECT_CPU_TIME_MS
	, OBJECT_DATA_VERSION
	, OBJECT_HIT
	, OBJECT_ID
	, OBJECT_MISS
	, OBJECT_PARENT_PATH
	, OBJECT_READ_KB
	, OBJECT_READS
	, OBJECT_ROWS_RETURNED
	, OBJECT_ROWS_SCANNED
	, OBJECT_VERSION
	, OBJECT_WRITE_KB
	, OBJECT_WRITES
from $system.DISCOVER_OBJECT_ACTIVITY

Dla niektórych widoków niezbędne jest podanie filtrów, aby ograniczyć zwracane dane. Przykładowo:

Select 
DATABASE_NAME
, CUBE_NAME
, MEASURE_GROUP_NAME
, PARTITION_NAME
, AGGREGATION_NAME
, AGGREGATION_SIZE
from SYSTEMRESTRICTSCHEMA 
(
	$system.DISCOVER_PARTITION_STAT 
	, [CUBE_NAME] = 'Adventure Works'
	, DATABASE_NAME = 'AdventureWorksDW2014Multidimensional-EE'
	, MEASURE_GROUP_NAME = 'Internet Sales'
	, PARTITION_NAME = 'Internet_Sales_2011'
)

W przypadku braku zdefiniowania filtrów zostanie zwrócony błąd.

Jak już wspomniano wyżej widoki systemowe zawierają mnóstwo przydatnych informacji. Poprzez skonstruowanie bardzo prostego pakietu w SSIS oraz systematycznemu pobieraniu informacji z nich jesteśmy w stanie poznać:

  • kto i kiedy korzysta z kostek?
  • które miary, wymiary, a nawet konkretne atrybuty są wykorzystywane, a które nie
  • czy powinniśmy dodać dodatkowe agregacje

Zobacz post: “Kto korzysta z bazy analitycznej SSAS?” 
Zobacz post: “Analiza użycia poszczególnych obiektów bazy analitycznej SSAS”

Dodatkowe narzędzia i metody

Do analizy oraz monitorowania Analysis Services można również użyć gotowych – darmowych lub płatnych – narzędzi. Najpopularniejsze to:

  • BI Sentry for Analysis Services – https://www.sentryone.com/platform/ssas-performance-monitoring
  • SSAS Stored Procedure Project – https://asstoredprocedures.codeplex.com/
  • https://asactivityviewer2012.codeplex.com/

Powyższe narzędzia wykorzystują oczywiście powyższe metody do gromadzenia danych i monitorowania Analysis Services.

Warto również pamiętać, że jeżeli wyżej wymienione metody nie dostarczą nam wiedzy której potrzebujemy Analysis Services posiada API oraz biblioteki dla platformy .NET oraz PowerShell, gdzie możemy uzyskać jeszcze więcej informacji o serwerze i bazach analitycznych.

Zobacz post: “Wykorzystanie C# i PowerShell do monitorowania SSAS“

Podsumowanie

SQL Server Analysis Services jak i kostki analityczne mogą być monitorowane oraz sprawdzane za pomocą wielu narzędzi oraz pod różnym kątem. Niezależnie czy planujecie przygotować system do monitorowania SSAS, przeprowadzacie audyt bieżącego rozwiązania czy też staracie się lepiej zrozumieć jak działa Analysis Services mam nadzieję, że ten post Wam to ułatwi. W kolejnych postach zaprezentowane zostaną konkretne przykłady wykorzystania opisanych technik.

 

 

 

Slawomir Drzymala
Follow me on
Latest posts by Slawomir Drzymala (see all)

Leave a Reply