Monitoring procedur natywnie kompilowanych w In-memory OLTP

NativeStoredProceduresMonitoring

Obiekty natywnie kompilowane z całą pewnością mogą dać nam przyrost wydajności związany z faktem, iż cała faza optymalizacji i kompilacji następuje w momencie ich tworzenia. Wynikiem procesu optymalizacji jest plan wykonania, który będzie wykorzystywany przy każdym wywołaniu procedury, aż do momentu wystąpienia rekompilacji. Mamy do dyspozycji pewne możliwości monitorowania statystyk wywołania procedur natywnie kompilowanych, które są domyślnie wyłączone i to właśnie o tym chciałbym dziś parę słów napisać.

Na samym początku stwórzmy sobie bazę danych o nazwie Hekaton_demo, którą będziemy używać. Baza ta nie charakteryzuje się niczym specjalnym i została stworzona przy pomocy poniższego skryptu:

W następnym kroku stworzymy sobie nietrwałą tabelę in-memory  o nazwie UserSession, którą następnie wypełnimy 100 tysiącami rekordów używając pętli. Tabela ta będzie posiadała indeks typu hash na polu SessionID będącym jednocześnie kulczem głównym całej tabeli ze wskazaniem 180 tysięcy bucketów na wartości:

W kolejnym kroku stwórzmy sobie procedurę natywnie kompilowaną, która będzie zwracała identyfikator użytkowników, którzy byli przypisani do podanych jako parametr numerów sesji:

Mając już zestaw testowy wywołajmy sobie naszą procedurę dla określonych numerów sesji przy włączonym jednocześnie Actual Execution Plan:

Wszystko wydaje się w porządku jednak nasz aktualny plan wykonania nie został dołączony. Dzieje się tak dlatego, że narzędzie to nie jest dla nas dostępne dla procedur natywnie kompilowanych! Gdy chcemy zobaczyć jaki plan został wykorzystany możemy użyć Estimated Execution Plan, który dla naszej procedury przedstawia się następująco:

Mamy troszeczkę ograniczony wachlarz możliwości monitorowania problemów wydajnościowych bez aktualnego planu wykonania, ale na powyższym zrzucie już możemy zaobserwować kilka przydatnych informacji na temat sposobu wykonania naszej procedury. Przede wszystkim nie został wykorzystany stworzony przez nas indeks – dzieje się tak ze względu na fakt, iż indeks typu Hash nie obsługuje operacji opartych na zakresach takich jak właśnie BETWEEN. Indeks tego typu służy niemal wyłącznie do tzw. POINT LOOKUP czyli wyszukiwania wierszy na podstawie warunku z operatorem równości na selektywnych kolumnach np. WHERE SessionID=1. Przechodząc dalej na naszym planie widzimy, iż wystąpił DISTINCT SORT, który jest odzwierciedleniem operacji SELECT DISTINCT naszego zapytania.

Gdy podejrzymy sobie właściwości operatora SELECT zobaczymy tam właściwie jedną użyteczna informację – chodzi mianowicie o powód dlaczego nasz plan nie jest wykonywany równolegle:

W taki właśnie sposób mamy dowód na to, że przetwarzanie równoległe nie jest dostępne dla opisywanego typu procedur…

Wiemy, że nasz indeks nie może być wykorzystywany ponieważ nie obsługuje operacji takiej jaka występuje w naszym zapytaniu. Do dyspozycji mamy również drugi typ indeksu – chodzi mianowicie o indeks typu RANGE. Nic nie stoi na przeszkodzie aby mieć dwa indeksy różnego typu na tej samej kolumnie – w in-memory OLTP nie ma koncepcji indeksu pokrywającego – każdy indeks zawiera wskazania bezpośrednio do wiersza danych! Dodajmy sobie zatem taki indeks do naszej tabeli:

Następnie podejrzyjmy estymowany plan wykonania przy wywołaniu naszej procedury:

Tym razem dzięki nowemu indeksowi mamy Index Seek, czyli nie musimy skanować całej tabeli. W momencie dodania indeksu procedura została rekompilowana dzięki czemu plan został zbudowany na nowo. W przypadku gdy zaaktualizowane zostaną statystyki nasza procedura nie zostanie rekompilowana – musimy to zrobić ręcznie używając sp_recompile. Pamiętajmy, że w przypadku występowania parametrów to optymalizator przyjmuje, że ich wartość jest nieznana i niejako uśrednia liczebność tak więc wpływ statystyk na kształt planu będzie miał duże znaczenie w przypadku gdy mamy do czynienia ze skośnym rozkładem częstości.

W porządku, wiemy jak wyświetlać estymowany plan wykonania, a co z aktualnymi statystykami wykonania? Tutaj odpowiedź może Was zdziwić ponieważ aktualne statystyki wykonania nie są domyślnie zbierane ze względu, iż mogło by to negatywnie wpłynąć na wydajność wykonania. Zachowanie to możemy zmienić wywołując odpowiednie procedury. Pierwszą z nich jest sp_xtp_control_proc_exec_stats, którą możemy wywołać w następujący sposób:

Następnie wywołajmy naszą procedurę:

W dalszym kroku odpytajmy sys.dm_exec_procedure_stats przefiltrowując ten widok w taki sposób aby pokazywał jedynie procedury natywnie kompilowane z bieżącej bazy:

Widok ten zawiera skumulowane informacje takie jak ilość wywołań, czy też statystyki czasowe związane z wykonaniem całej procedury. Oczywiście procedura może zawierać więcej niż jedno zapytanie – w momencie gdy chcemy widzieć bardziej granularne dane możemy to zrobić włączając odpowiednią procedurę – tym razem sys.sp_xtp_control_query_exec_stats. Zanim przetestujemy ten mechanizm zmodyfikujmy naszą procedurę tak aby zawierała dwa zapytania:

Następnie włączmy kolekcjonowanie statystyk i wywołajmy nasze zapytanie:

Wywołajmy naszą procedurę:

Odpytajmy widok systemowy sys.dm_exec_query_stats:

Statystyki mogą być dla nas przydatne – jednakże nie powinniśmy mieć ich zawsze włączonych ze względu na fakt, iż tak jak wspomniałem mają one wpływ na wydajność (niewielki ale zawsze). Aby sprawdzić czy kolekcjonujemy dane czy też nie wystarczy użyć konstrukcji jak została przedstawiona poniżej tj. z parametrem ustawionym jako output – dzięki temu otrzymamy 1 gdy kolekcjonowanie jest włączone lub 0 gdy nie jest:

Aby móc kontrolować ten mechanizm musimy pamiętać o tym, że musimy być członkami sysadmin gdyż jest to ustawienie na poziomie instancji, a nie pojedynczej bazy danych. Mamy jednak możliwość włączenia kolekcji dla pojedynczej procedury – ustawienie to jest niezależne od ustawienia na poziomie instancji. Oprócz samych statystyk, do monitorowania procedur natywnie kompilowanych możemy użyć nowej funkcjonalności wprowadzonej w SQL Server 2016 czyli Query Store (o którym miałem okazję opisać w dwóch artykułach tutaj oraz tutaj). Możemy go włączyć następującym poleceniem ALTER DATABASE:

Następnie wywołajmy nasze zapytanie:

Dane zostały zapisane w specjalnych tabelach mechanizmu Query Store do których mamy dostęp poprzez widoki dynamiczne:

Powyższy SELECT zwrócił dane na temat samego procesu kompilacji w podziale na pojedyncze zapytania :

Do dyspozycji mamy również statystyki samego wykonania:

Tutaj mamy do dyspozycji całkiem sporo informacji jak np. ile wierszy zwracało zapytanie czy jego czas trwania. Może to być przydatne w przypadku gdy chcemy zlokalizować najbardziej kosztowne procedury i zapytania wchodzące w ich skład.

Ostatnią metodą o jakiej chciałbym wspomnieć jeśli chodzi o monitoring procedur natywnie kompilowanych jest sesja Extended Events. Przykładowa sesja tego mechanizmu została przedstawiona poniżej:

Daje nam ona możliwość śledzenia dosyć dokładnych informacji na temat czasu trwania wykonania procedury czy chociażby dane o użytkowniku, który wykonał daną procedurę – oczywiście możliwości wykorzystania powyższych informacji jest dużo więcej. Poniżej przykładowy zrzut danych XEvents podejrzany za pomocą podglądu na żywo:

Jak możecie zauważyć monitorowanie procedur natywnie kompilowanych nieco różni się od monitorowania tradycyjnych modułów. Niestety mamy dużo mniej możliwości,a brak możliwości podejrzenia np. aktualnego planu wykonania skutkuje tym, że sam troubleshooting może być nieco utrudniony. Istnieją jednak pewne możliwości i należy zdawać sobie z nich sprawę i wybrać ten sposób, który będzie dla nas najwygodniejszy i najlepiej spełniał nasze oczekiwania.

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

Latest posts by Adrian Chodkowski (see all)

Leave a Comment

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