Akademia SQL - Część 4: Kompresja kopii zapasowej     Akademia SQL     Akademia SQL - Część 6: Atrybut FILESTREAM

Akademia SQL - Część 5: Extended Events Udostępnij na: Facebook

Autor: Damian Widera

Opublikowano: 23 września 2008

Zawartość strony
 Extended Events – informacje podstawowe   Extended Events – informacje podstawowe
 Extended Events – laboratorium   Extended Events – laboratorium
 Extended Events – referencje   Extended Events – referencje

Extended Events – informacje podstawowe

SQL Server 2008 udostępnił administratorom baz danych bardzo wydajny i nieobciążający mechanizm przechwytywania zdarzeń – tzw. rozszerzone zdarzenia (Extended Events - XE). Rozszerzone zdarzenia wykorzystują mechanizm Event Tracing for Windows (ETW), zaś swoja specyfiką przypominają dziennik zdarzeń systemu Windows. Po części rozszerzone zdarzenia można traktować jako liczniki monitora wydajności (perfmon). Przechwytywanie zdarzeń nie jest jedyną cechą mechanizmu rozszerzonych zdarzeń, bo w jego ramach, po przechwyceniu odpowiedniego zdarzenia, można także uruchomić dynamicznie określoną akcję lub dostarczyć do tego zdarzenia dane.

Głównymi zaletami mechanizmu zdarzeń rozszerzonych są:

  1.  Ścisła integracja ze zdarzeniami systemu Windows – administratorzy mogą śledzić jądro systemu Windows od momentu, kiedy działał będzie serwer SQL

  2.  Niski koszt przechwytywania zdarzeń, co czyni mechanizm XE idealnym rozwiązaniem także dla dużych systemów produkcyjnych

  3.  Krótki czas diagnozowania problemów występujących w serwerze baz danych, takich jak np. wąskie gardła – zamiast pracochłonnego przeglądania czy analizowania plików zarejestrowanych narzędziem SQL Server Profiler.

  4.  Natychmiastowa agregacja danych pochodzących ze zdarzeń, która odbywa się w pamięci procesu, co z kolei pozwala na zaszycie w tej pamięci pewnej logiki, która w odpowiedni sposób zareaguje na uzyskany wynik

  5.  Konfigurowalność – administrator może utworzyć sesję zbierającą dane zależne od środowiska, w którym została ta sesja utworzona. Sesje mogą zostać utworzone za każdym razem, gdy zostaje uruchomiony serwer SQL

Zdarzenia mogą być przechwytywane w tzw. sesjach. Sesje możne tworzyć za pomocą polecenia CREATE EVENT SESSION. Polecenie to pozwala wybrać z paczek (package) przechwytywane zdarzenia, określić maksymalną ilość pamięci przydzielaną sesji, określić opcje przechwytywania i przechowywania zdarzeń, akcje podejmowane w reakcji na zdarzenia.

Dostępne są trzy paczki:

  1.   sqlos – oferuje niskopoziomowe zdarzenia interakcji z systemem operacyjnym,

  2.   sqlserver – oferuje zdarzenia odpowiadające w większości licznikom monitora systemowego,

  3.   package0 – zawiera typy danych, operatory porównania, akcje (podejmowane w odpowiedzi na zdarzenia), mapy (odpowiednik enumeracji w językach programowania, np. tryb I/O dla pliku), docelowe obiekty (targets), które mogą być w jakikolwiek sposób związane z przechwytywanymi zdarzeniami (dzięki tym obiektom można zdefiniować np. zapisywanie zdarzeń do plików).

Domyślnie, po zdefiniowaniu, wszystkie sesje śledzenia (przechwytywania) są zatrzymane. W celu uruchomienia sesji śledzenia rozszerzonych zdarzeń administrator musi uruchomić polecenie ALTER EVENT SESSION...STATE=START. Analogicznie, zatrzymanie sesji to kwestia wykonania polecenia ALTER EVENT SESSION...STATE=STOP. Można także bez zatrzymywania sesji usunąć ją poleceniem DROP EVENT SESSION.

SQL Server 2008 przechowuje wszystkie istotne informacje o rozszerzonych zdarzeniach w 8 widokach katalogowych, z których najważniejsze to:

  1.   sys.dm_xe_packages – zwraca listę paczek (paczka jest identyfikowana przez nazwę i GUID),

  2.   sys.dm_xe_objects – zwraca listę obiektów zawartych w paczkach,

  3.   sys.dm_xe_object_columns – zwraca listę kolumn opisujących obiekty znajdujące się w paczkach.

Oprócz tego do dyspozycji administratorów są dynamiczne widoki zarządzane (DMV), które umożliwiają monitorowanie sesji przechwytywania rozszerzonych zdarzeń:

  1.   sys.dm_xe_sessions – zwraca informacje o aktywnych sesjach

  2.   sys.dm_xe_session_targets – zwraca informacje o docelowych obiektach sesji

  3.   sys.dm_xe_session_events – zwraca informacje o zdarzeniach w sesjach

  4.   sys.dm_xe_session_event_actions – zwraca informacje o akcjach w sesjach

  5.   sys.dm_xe_map_values – zwraca odpowiedniki tekstowe map

  6.   sys.dm_xe_session_object_columns - zwraca informacje o konfiguracji sesji

Istnieje co najmniej kilka scenariuszów, w których zastosowanie mechanizmu rozszerzonych zdarzeń wydaje się doskonałym rozwiązaniem:

  1.  rozwiązywanie problemów z zakleszczaniem (deadlocks)

  2.  rozwiązywanie problemów wynikających z nadmiernego zużycia zasobów procesora

  3.  diagnozowanie problemów z pamięcią serwera

  4.  powiązanie zdarzeń występujących na serwerze SQL ze zdarzeniami z systemu operacyjnego

Podsumowując wątek teoretyczny można ocenić, iż mechanizm XE jest jednym z najpotężniejszych, jakie kiedykolwiek mieli administratorzy baz danych do swojej dyspozycji. Na tym mechanizmie zbudowano np. inną funkcjonalność – Database Audit.

 Do początku strony Do początku strony

Extended Events – laboratorium

Celem laboratorium będzie wykonanie dwóch zadań. Celem pierwszego z nich będzie praktyczne zapoznanie się z obiektami tworzącymi mechanizm rozszerzonych zdarzeń. W ramach tego zadania pokazane zostaną dynamiczne widoki zarządcze, które odpowiadają za przechowanie składowych elementów systemu. W drugim zadaniu, które będzie zarazem przykładem praktycznym, pokażemy w jaki sposób można szybko odnaleźć zapytania, które trwają dłużej niż zadany okres czasu. Więcej zastosowań można znaleźć w Internecie, a najciekawsze odnośniki opisujące tę tematykę zostały podane na zakończenie lekcji.

Zadanie 1

  1.  Uruchom SQL Server Management Studio (SSMS) i połącz się z serwerem EVALUATION używając uwierzytelnienia WINDOWS.

  2.  Otwórz nowe okno zapytania (klawisze skrótu CLTR+N lub przycisk New query znajdujący się na głównym pasku narzędzi). W oknie wpisz poniższe zapytanie, które wyświetli podstawowe informacje o paczkach dostępnych w mechanizmie rozszerzonych zdarzeń:

use master

go

SELECT * FROM sys.dm_xe_packages

  3.  Po uruchomieniu zapytania z punktu 2 otrzymujemy informacje o trzech paczkach zainstalowanych domyślnie w SQL Server 2008 oraz jednej dodatkowej, która jest związana z mechanizmem Database Audit:

  4.  Paczki zawierają obiekty, które można wylistować za pomocą poniższego zapytania. Paczka może zawierać każdy obiekt, wszystkie obiekty lub być pusta.

SELECT DISTINCT object_type

FROM sys.dm_xe_objects

ORDER BY object_type

Lista obiektów jest następująca:

  5.  W kolejnym przykładzie pokazano, jakie akcje są możliwe do zdefiniowania w ramach każdej paczki.

DECLARE @typ_obiektu varchar(20) = 'action'



SELECT OB.name as [nazwa obiektu]

 ,OB.description as [opis obiektu]

     ,PCK.name as [nazwa paczki]

FROM sys.dm_xe_objects OB

JOIN sys.dm_xe_packages PCK

ON PCK.guid=OB.package_guid

WHERE object_type=@typ_obiektu

ORDER BY PCK.name,OB.name

W wyniku uruchomienia powyższego zapytania otrzymano następującą listę akcji z podziałem na paczki:

  6.  Zamieniając słowo ‘action’ dla parametru @typ\_obiektu na jeden z listy uzyskanej w punkcie 4 można dokładnie zapoznać się z obiektami budującymi paczki a co za tym idzie lepiej i dokładniej poznać mechanizm rozszerzonych zdarzeń.

  7.  Ostatni krok polega na wypisaniu wszystkich kolumn definiujących obiekt ‘ lock_acquired’ w ramach paczki sqlserver:

SELECT OC.name    as [nazwa kolumny]

,OC.column_type as [typ kolumny]

,OC.description as [opis]

,OC.type_name  as [typ]

,PCK.name   as [paczka]

FROM sys.dm_xe_object_columns OC

JOIN sys.dm_xe_packages PCK

ON PCK.guid=OC.object_package_guid

WHERE object_name='lock_acquired'

ORDER BY OC.name

  8.  Po uruchomieniu powyższego zapytania otrzymujemy następującą informacje:

Zadanie 2

  1.  Zadanie numer 2 ma na celu pokazanie, jak praktycznie wykorzystać informacje płynące z mechanizmu zdarzeń rozszerzonych. Wykonana zostanie sesja, w ramach której zostaną przechwycone informacje o procedurach, które trwają dłużej niż określony okres czasu.

  2.  Uruchom SQL Server Management Studio (SSMS) i połącz się z serwerem EVALUATION używając uwierzytelnienia WINDOWS.

  3.  W nowym oknie utwórz zapytanie, które utworzy sesję przechwytywania zdarzeń rozszerzonych oraz testową procedurę. Wszystkie operacje będą wykonywane w kontekście bazy danych AdventureWorks.

use AdventureWorks

go



--sprawdzenie, czy sesja ‘MyLongQueries’ już istnieje  

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE NAME='MyLongQueries')

  DROP EVENT SESSION MyLongQueries 

  ON SERVER



--sprawdzenie, czy testowa procedura ‘usp_cpu_hog’ już istnieje  

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_cpu_hog]') AND type in (N'P', N'PC'))

  DROP PROCEDURE [dbo].[usp_cpu_hog]

GO

  4.  Sesja może zostać utworzona dynamicznie, co pozwala na budowanie skryptów ją tworzących w locie – np. na konkretne żądanie aplikacji klienckiej czy diagnostycznej.

--deklaracja sesji – w kontekści bazy danych AdventureWorks. 

--Procedura musi działać powyżej 2 sekund

declare @sqlText varchar(MAX)





select @sqlText = 

 'CREATE EVENT SESSION MyLongQueries ' +

 'ON SERVER ' +

 'ADD EVENT sqlserver.sql_statement_completed ( ' +

 'ACTION (sqlserver.sql_text) ' +

 'WHERE sqlserver.database_id = ' + cast(db_id('AdventureWorks') as varchar(4)) + ' ' +

 'AND duration > 20000' +

 ') '



exec(@sqlText)

GO

  5.  Dodawanie elementów/obiektów do sesji przechwytywania może odbywać się także po jej utworzeniu, ale przed uruchomieniem. W poniższym przykładzie dodany został odbiorca zdarzenia, który przechowa informację o zdarzeniu ‘sql_statement_completed’ , a konkretniej – otrzymamy nazwy procedur składowanych wraz z parametrami uruchomieniowymi (source = ‘sqlserver.sql_text’)

--dodanie odbiorcy zdarzenia 

ALTER EVENT SESSION MyLongQueries

ON SERVER

ADD TARGET package0.synchronous_bucketizer ( 

    set filtering_event_name='sqlserver.sql_statement_completed', 

        source_type=1, 

        source='sqlserver.sql_text' )

        

GO

  6.  We wstępie teoretycznym podano, że sesja po utworzeniu jest domyślnie wyłączona. Należy więc przed rozpoczęciem zbierania informacji ustawić stan sesji na ‘START’.

ALTER EVENT SESSION MyLongQueries

ON SERVER

STATE=START

  7.  Wykonaj skrypt z pliku proc.sql w kontekście bazy Adventure Works. Plik ten znajduje się na dysku w katalogu C:\SQLAdmin\XEvent\Proc.sql. Plik ten pozwala utworzyć testową procedurę składowaną usp_cpu_hog.

  8.  Procedurę należy uruchomić dla różnych parametrów wejściowych:

use AdventureWorks

go

EXEC usp_cpu_hog 20,20

go

EXEC usp_cpu_hog 100,100

go

EXEC usp_cpu_hog 400,400

go

EXEC usp_cpu_hog 800,800

go

  9.  Wykonaj skrypt z pliku LongQueries.sql w kontekście bazy Adventure Works. Plik ten znajduje się na dysku w katalogu C:\SQLAdmin\XEvent\LongQueries.sql.

Otrzymane wyniki pokazują, że dla pewnych parametrów wejściowych testowa procedura składowana przekracza czas 2 sekund i ta informacja zostaje przechwycona i zapisana przez mechanizm rozszerzonych zdarzeń:

<BucketizerTarget truncated="0" buckets="256">

  <Slot count="1" trunc="1">

    <value>EXEC usp_cpu_hog 400,400</value>

  </Slot>

  <Slot count="1" trunc="1">

    <value>EXEC usp_cpu_hog 800,800</value>

  </Slot>

</BucketizerTarget>

Omawiany skrypt pokazuje, w jaki sposób wynik zdarzenia zaprezentować w formacie XML, ale nic nie stoi na przeszkodzie, aby dane zostały wyprowadzone w formie tekstowej.

  10.  Zakończ sesję przechwytywania rozszerzonych zdarzeń za pomoc polecenia:

ALTER EVENT SESSION MyLongQueries

ON SERVER

STATE=STOP

 Do początku strony Do początku strony

Extended Events – referencje

Dodatkowe informacje na temat rozszerzonych zdarzeń można znaleźć w Internecie:

[1] Co nowego w silniku bazodanowym SQL Server 2008 July CTP (CTP 4), cz. II

[2] Blog Boba Beauchmin - XEvent in SQL Server 2008

[3] MSDN Webcast: SQL Server 2008 Advanced Troubleshooting with Extended Events (Level 200)

[4] Books Online - Introducing SQL Server Extended Events


  Damian Widera, Project Manager & Team Lead (MCT, MCITP – DBA, MCSD.NET)
Od 8 lat zajmuje się projektowaniem, tworzeniem i wdrażaniem aplikacji wykorzystujących platformę .NET, SQL Server oraz Oracle. Obecnie pracuje jako project manager dla LGBS Polska. Pracował także jako trener, programista, administrator baz danych, twórca domumentacji oraz analityk biznesowy. Aktywnie współpracuje z polskim oddziałem Microsoft publikując atykuły, webcasty oraz porady z zakresu SQL Server na stronach TechNet. Jest współautorem książki „Serwer SQL 2008. Administracja i programowanie”.

Speaker na wielu konferencjach, m.in. Microsoft Heroes Happen Here, C2C, European PASS Conference, Microsoft Technology Summit, Energy Launch, TechED. Od 2004 r. posiada certyfikaty firmy Microsoft: MCT, MCITP–DBA oraz MCSD.NET. Jest współtwórcą oraz liderem jednej z najwiekszych grup pasjonatów SQL Server w Polsce – Śląskiej Regionalnej Grupy Microsoft (PLSSUG Katowice). Od listopada 2008 jest prezesem Polish SQL Server Users Group (PLSSUG) w Polsce. W styczniu 2009 nagrodzony tytułem MVP w kategorii SQL Server.
 Do początku strony Do początku strony

Akademia SQL - Część 3: Transparent Data Encryption     Akademia SQL     Akademia SQL - Część 5: Extended Events