Extended Events czyli szukanie zapytań w SQL

Załóżmy, że codziennie wieczorem ktoś uruchamia jakieś zapytania do naszej bazy powodując np. zwiększone obciążenie serwera. Aby znaleźć winowajcę możemy wykorzystać narzędzie Extended Events.
Sposób konfiguracji opisałem w oddzielnym artykule (http://micek1968.pl/?p=3632).
Dziś skupimy się na konfiguracji aby wyszukiwało wykonywane „duże” zapytania.

Wchodzimy we właściwości naszej sesji Extended Events.


Przechodzimy do zakładki Event. proponuję w tym miejscu wybrać z biblioteki zdarzeń tylko „SQL_STATEMENT_COMPLETE”. W zupełności to wystarczy do naszych poszukiwań.

Przechodzimy w prawo do konfiguracji zdarzeń, a następnie do zakładki filtrowania.

Interesują nas tylko „duze” zapytania więc wybieramy zapytania które zwracają więcej niż 50 wierszy. Wybieramy z listy rozwijanej „LAST_ROW_COUNT”

a następnie podajemy wartość powyżej której ma wyszukiwać.

Przy ponownym otwarciu ustawień znak „>” zmienia się automatycznie w wartość „greater_than_uint64”

Zatwierdzamy ustawienia i uruchamiamy Sesję Extended Events

Następnie wykonujemy (testowo) kilka zapytań na bazie Northwind. Nie ma tam dużo wierszy ale wszystkie zapytania zwracające więcej niż 50 wierszy zostaną zarejestrowane.

SELECT * FROM [Northwind].[DBO].[ORDER_DETAILS] 
SELECT * FROM [Northwind].[DBO].[ORDERS]
SELECT * FROM [Northwind].[DBO].[PRODUCTS]
SELECT * FROM [Northwind].[DBO].[CUSTOMERS]

Następnie zamykamy sesję

I wyświetlamy zapisane dane

Jak widać nasze zapytania zostały zarejestrowane:

Widać kto to wykonywał, z jakiej komputera, ile wierszy zwróciło, treść zapytania i wiele innych rzeczy.
Wyszukiwanie możemy za pomocą filtra oczywiście ograniczyć do jednej bazy i wielu innych różnych parametrów.

Problemem się staje kiedy to puściliśmy rejestrację zdarzeń do Extended Events np na cały dzień i zarejestrowało nam kilkaset tysiecy zapytań do bazy. Jak znaleźć wśród tego interesujące nas zapytanie.
Możemy się wspomóc odczytem zarejestrowanych danych z plików poprzez zapytanie t-sql.

W naszym przykładzie zdarzenia są zapisane w pięciu plikach, ale może być ich więcej i o większej pojemności.

Najprostszym zapytanie zwracającym zarejestrowane wyniki będzie

select * from sys.fn_xe_file_target_read_file('C:\Temp\MySession_*.xel', null, null, null)

Zwróci to nam wszystkie zarejestrowane zdarzenia. Mozemy ograniczyć się do pojedynczych plików wpisując ich nazwę.

Jeśli np wiemy, że zapytanie dotyczy konkretnej bazy mozemy dodać warunek
WHERE event_data like '%Orders%'
Zapytanie zwróci tyle zdarzenia dotyczące tabeli ORDERS.

Wszelkie dane dotyczące danego zdarzenia znajdują się w kolumnie EVENT_DATA i EVENT_DATA_XML (zapisane w formacie XML).

Możemy bardziej uporządkować wyświetlane dane

SELECT
object_name,
file_name,
file_offset,
event_data,
'CLICK_NEXT_CELL_TO_BROWSE_XML RESULTS!'
AS [CLICK_NEXT_CELL_TO_BROWSE_XML_RESULTS],
CAST(event_data AS XML) AS [event_data_XML]
-- TODO: In ssms.exe results grid, double-click this xml cell!
FROM
sys.fn_xe_file_target_read_file(
--'C:\Temp\MySession_1_0_132878711812050000.xel',
'C:\Temp\MySession_1_0_*.xel',
null, null, null
)
WHERE event_data like '%Orders%'

Poniższe zapytanie zwraca nam zapisane zdarzenia w bardziej czytelnej postaci.

select
n.value('(@name)[1]', 'varchar(50)') as event_name,
n.value('(@package)[1]', 'varchar(50)') AS package_name,
n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp],
n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement,
n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name,
n.value('(data[@name="duration"]/value)[1]', 'int') as duration,
n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cpu,
n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
n.value('(data[@name="logical_reads"]/value)[1]', 'int') as logical_reads,
n.value('(data[@name="writes"]/value)[1]', 'int') as writes,
n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count,
n.value('(data[@name="last_row_count"]/value)[1]', 'int') as last_row_count,
n.value('(data[@name="line_number"]/value)[1]', 'int') as line_number,
n.value('(data[@name="offset"]/value)[1]', 'int') as offset,
n.value('(data[@name="offset_end"]/value)[1]', 'int') as offset_end
from (select cast(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file('C:\Temp\MySession_*.xel', null, null, null)) ed
cross apply ed.event_data.nodes('event') as q(n)
-- WHERE n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') like '%Orders%'

Jeśli nas interesuje tylko tabela ORDERS musimy odznaczyć ostatni wiersz z warunkiem WHERE.

A na na koniec taka mała porada, że jeśli chcielibyśmy zarejestrować tylko zapytania wykonywane w określonym przedziale czasowym (np między 2 i 3 w nocy najprościej powinniśmy utworzyli dwa joby: jeden uruchamiający a drugi zatrzymujący naszą sesję (nie ma możliwości ustawienia tego we właściwościach sesji).
W kroku joba nalezy umieścić polecenie uruchamiające/zatrzymujące naszą sesję

ALTER EVENT SESSION event_session_name ON SERVER STATE = {START | STOP}

Ten wpis został opublikowany w kategorii Microsoft SQL. Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz