Akademia SQL - Część 5: Extended Events
Autor: Damian Widera
Opublikowano: 23 września 2008
Zawartość strony
Extended Events – informacje podstawowe | |
Extended Events – laboratorium | |
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
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
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 |