Scroll

Extended Events er et must!

I min verden er Extended Events et must at kende til hvis du er DBA eller blot SQL nørd. Der er et væld af informationer vi kan få ved simpelthen at ”lytte” på events. De events vi vælger at logge bliver gemt enten i en buffer eller filer, hvorfra vi så med XQuery kan spørge ned i de forskellige events, der logges som XML.
Lad os sige, at vi gerne ville have noget auditing af de objekter der bliver oprettet, ændret og slettet på vores SQL Server. For eksempel vil vi gerne fange når der bliver oprettet en STORED PROCEDURE, et VIEW bliver ændret eller en funktion bliver slettet. Det er ingen sag med Extendet Events.
Vi kan oprette events enten via GUI i SSMS eller med et Script – vi kan også bruge GUI I SSMS og generere et script til oprettelse af vores Extended Event.
VI finder Extended Events i: Object exploren -> Management -> Extended Events:

Her har vi vores Extended Events listet under Sessions:

For at oprette en ny event: højreklik på Sessions og vælg New Session…

Det åbner GUI til Extended events:

Først skal vi give den et navn:

Det er muligt at benytte en af standartskabelonerne som udgangspunkt, men det gør vi ikke her. De kan dog være fine at kigge i, for at finde ud af hvordan det hele hænger sammen.
Herefter skal vi tage stilling til hvilke Events vi vil ”lytte” efter:

Vi kan søge efter Events skriv ”object_” i Event Library tekstboksen:

Hvis vi trykker på object_created, så kommer der en beskrivelse i boksen under:

Med object_created valgt, tryk på den lille pil og eventen overføres til Selected events:

Med object_created valgt i Selected events, tryk på knappen Configure i højre hjørne:

Vi overføres nu til en fane, hvor vi kan konfigurere eventen:

Global fields

Global fields giver os mulighed for at vælge hvilke oplysninger vi vil have med ud.
Jeg vælger database_id, sql_text og username, jeg vil jo gerne se hvem der sletter og opretter objekter på serveren 😊
I fanen Filter kan vi så begrænse hvad eventen skal lytte efter. For eksempel er vi kun interesserede i at lytte på en enkelt database på serveren, eller efter en bestemt brugers handlinger. I dette tilfælde er jeg kun interesseret i en bestemt type objekter, nemlig View, Proc (stored procedure), Function, USRTAB (tabel) og kun når det en Commit handling.
Tryk på ”Click here to add a clause”:

I field kan vi nu vælge fra en liste af muligheder:

Da jeg kun er interesseret i en bestemt type objekter, så vælger jeg object_type og i Value vælger jeg PROC:

Tryk igen på ”click here to add a clause” vælg igen object_type og i value Function, gentag for USERTAB og VIEW:

Nu skal vi ændre alle And/Or til OR for det skal være en af de 4 muligheder:

Nu mangler vi bare at vælge, at vi kun er intereesseret i Commit kommandoer, så jeg tilføjer en clause mere ddl_phase og i value vælger commit:

Nu er der kun en sidste ting tilbage og det er at gruppere de 4 object_type så de evalueres som en enhed hvor ddl_phase stadig skal være opfyldt.
Tryk på den første; hold shift nede og tryk på sidste object_type, slip shift og højreklik på de valgte rækker:

Vælg Group Clauses:

Gå tilbage til Select og gentag for object_altered og Object_deleted:

Gå i data Storage:

Her skal vi tilføje et sted hvor events skal gemmes; klik på Click here to add a target:

Her er der et udvalg af muligheder. Ring_buffer er en buffer af en given størrelse (som vi bestemmer) og event_file er en fil, der skrives i. Jeg vil ikke gå i dybden med de forskellige muligheder her, men jeg vælger event_file. Nu får vi mulighed for at sætte nogle properties på event_filen:

Det er vigtigt at SQL Serveren har adgang til den sti der vælges, ellers kan eventen ikke starte.
Slutteligt kan vi gå i Advanced:

Og sætte nogle properties på vores session, bl.a. Event retention mode, der er dog ude af scope for denne artikel, jeg lader det hele stå som default.
Tryk OK
Hvis du bliver spurgt om eventen skal startes med det samme, så siger du Yes, ellers kan du højreklikke på eventen og vælge start session:

Når eventen er startet, kommer der en lille grøn play symbol på eventen. Hvis du gerne vil se data, som det kommer i filen, kan du højreklikke på eventen og vælge Watch Live Data:

Så kommer de forskellige events i listen, som de bliver fanget:

Nu logges alle Objects af typen Procedure, Function, View og Tabel – hvilket giver dig muligheden for at gå ned og bede din kollega om kage når han/hun får slettet et objekt, der bruges.
Hvis du gerne vil Scripte eventen for lettere oprettelse på andre servere, så kan dette også gøres ved at højreklikke på eventen og vælge Script Session As -> CREATE to -> New Query Editor Window:

(Beklager Dark theme er ikke helt gennemført endnu i SSMS 😊)

SCRIPT:

CREATE EVENT SESSION [Object_create_modify] ON SERVER 
ADD EVENT sqlserver.object_altered(
    ACTION(sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
    WHERE (([package0].[equal_uint64]([object_type],(8272)) OR [object_type]=(8277) OR [object_type]=(8278) OR [object_type]=(20038)) AND [package0].[equal_uint64]([ddl_phase],(1)))),
ADD EVENT sqlserver.object_created(
    ACTION(sqlserver.database_id,sqlserver.sql_text,sqlserver.username)
    WHERE (([package0].[equal_uint64]([object_type],(8272)) OR [package0].[equal_uint64]([object_type],(8277)) OR [package0].[equal_uint64]([object_type],(20038)) OR [package0].[equal_uint64]([object_type],(8278))) AND [package0].[equal_uint64]([ddl_phase],(1)))),
ADD EVENT sqlserver.object_deleted(SET collect_database_name=(1)
    ACTION(sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
    WHERE (([package0].[equal_uint64]([object_type],(8272)) OR [object_type]=(20038) OR [object_type]=(8277) OR [object_type]=(8278)) AND [package0].[equal_uint64]([ddl_phase],(1))))
ADD TARGET package0.event_file(SET filename=N&#x27;C:\Temp\Object_create_modify.xel&#x27;,max_file_size=(250))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Som jeg skrev indledningsvis, kan vi med XQuery forespørge data – gennemgangen af scriptet kommer i en senere artikel. Men outputtet ser sådan her ud:

Dette var blot et eksempel på hvad Extended Events kan bruges til, og da SQL Profileren er depricated siden 2012 så er det Extended Events, der skal bruges for at fange nogle af de events, der er kommet til i SQL Serveren siden 2012. læs mere om extended events på docs https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events?view=sql-server-ver15.

Tak fordi du læste med. Som altid så er du velkommen til at kontakte mig eller en af mine dygtige kollegaer i Unit IT på tlf.: 88 333 333, hvis du har spørgsmål eller udfordringer med dit SQL miljø.

Tilbage til artikler