Tracing mit Extended Events in Azure SQL Database

Eine Schritt-für-Schritt-Anleitung.

Ob Developer, Administrator oder einfach Support für eine Azure-SQL-Database-basierte Anwendung – es gibt da diese Momente, wenn die von Azure bereitgestellten Dashboard-Features nicht ausreichen und man eine gründliche Fehlersuche bestimmter auftretender Statements durchführen muss.

Manchmal benötigt man dazu:

besseres Filtern an der Quelle

und die Fähigkeit, seine Daten

zu gruppieren
zu aggregieren
in den gesammelten Daten zu dicen/zu filtern

…um problematische Queries zu finden.

Oder man ist an Ereignissen interessiert, die nicht die Queries selbst sein mögen, um solche Fragen zu beantworten wie z.B.:

“Was hat die Verwendung von Adaptive Query-Plans verhindert?”
- Event: adaptive_join_skipped
“wie genau lief der Tupel-Mover-Prozess für meinen Columnstore Index?”
- Mögliche interessante Ereignisse:
  - columnstore_tuple_mover_begin_compress
  - columnstore_tuple_mover_begin_delete_buffer_flush
  - columnstore_tuple_mover_compression_stats
  - columnstore_tuple_mover_delete_buffer_flush_requirements_not_met
  - columnstore_tuple_mover_delete_buffer_truncate_requirements_not_met
  - columnstore_tuple_mover_delete_buffer_truncate_timed_out
  - columnstore_tuple_mover_delete_buffer_truncated
  - columnstore_tuple_mover_delete_buffers_swapped
  - columnstore_tuple_mover_end_compress
  - columnstore_tuple_mover_end_delete_buffer_flush
oder, wann trat der Garbage Collector meiner memory-optimierten Tabelle auf und wieviele Zeilen hat er entfernt?”
- Mögliche interessante Ereignisse:
  - gc_base_generation_evaluation
  - gc_base_generation_updated
  - gc_cycle_completed
  - gc_notification

Wenn ihr dabei gehofft hattet, SQLTrace/Profiler zum Tracing zu verwenden, solltet ihr allein beim Blick auf diese Liste erkennen, dass es keine Chance für dieses alte, überholte Werkzeug aus dem letzten Jahrzehnt in der Azure SQL Database gibt. 😉

(ja, es klingt ein wenig harsch, aber es hilft, dabei daran zu denken, dass XEvents damals mit SQL 2008 eingeführt wurden, und dass SQLTrace-Technologie seit SQL 2012 im Koma liegt und darauf wartet, dass der Stecker herausgezogen wird, als XE einen höherwertigen GUI und alle verpassten Events erhielt. Mehr dazu könnt ihr hier lesen: https://andreas-wolter.com/extended-events-vs-sql-trace-im-vergleich/ )

Erste Schlussfolgerung: Die Architektur von SQLTrace wird einfach nicht von Azure SQL DB unterstützt.

Jetzt also weiter mit Extended Events.

Wenn man sich via SQL Server Management Studio mit seiner Datenbank von Azure verbunden hat, sieht man “Extended Events” innerhalb der Datenbankreichweite.

Von dort aus kann man das GUI verwenden, um seine Session zu erstellen, genau wie On-Premise.

Ihr werdet merken, dass die Option, den Live Target Viewer zu starten, ausgegraut ist. Das liegt daran, dass dieses Target, und noch nicht einmal das reine “event_stream”- und “compressed_history”-Ziel, in Azure nicht unterstützt wird.

Kausalitätstracking (Causality Tracking), was auf bestimmten privaten “Actions” basiert, wird jedoch unterstützt.

Ihr werdet auch bemerken, dass es weniger Vorlagen gibt als On-Premise. Vor allem fehlen die “Profiler-Äquivalente”, aber auch einige andere.

Aber wen kümmert es? … Ich selbst habe noch nie eine davon verwendet, seit sie im SQL Server 2012 eingeführt wurden. Das DDL ist so schon einfach anzuwenden.

Als nächstes wählt man seine Ereignisse, Filter und, falls nötig, Actions.

Zum Zeitpunkt des Schreibens bot das letzte Update von SQL Azure DB vom 27. März 2018 343 tracebare Ereignisse an.

Das Interessante bei den Filtern/Prädikaten ist, dass es aus dem “sqlazure”-Paket einige Azure-spezifische Prädikate gibt:

Die Liste der speziellen Azure-Prädikate ist – ab heute:

Predicate	Description
logical_database_name	Get current database name.
logical_server_name	Get current server name.
is_azure_connection	Get whether current session is an azure connection.
is_managed_instance	Get whether this is Managed Instance.
local_partition_id	Get the current local partition id.
app_name	Get the app name of the current partition.
table_group_name	Get the table group name of the current partition.
low_key	Get the low key of the current partition.
high_key	Get the high key of the current partition.
worker_last_error_number	Get the last error number from the current worker.
logical_database_guid	Get current database logical GUID.
physical_database_guid	Get current database physical GUID.
instance_partition_id	Get current sqlserver instance winfab partition id.
instance_replica_id	Get current sqlserver instance winfab replica id.
instance_rg_size	Collect instance RG container size.

Diese können sich als sehr nützlich erweisen!

Dann muss man sich für ein Ziel entscheiden. Hier sind wir wirklich eingeschränkt: alles, woraus wir wählen können, ist:

event_file
event_counter
ring_buffer

Jetzt bin ich nicht sicher, ob diese Auswahl so viel Sinn ergibt. Warum event_counter-Target, warum nicht histogram oder pair_matching? Aus den umfassenden Benchmarks, die ich gemacht habe (Leistungseinbußen beim Tracing, Extended Event Ziele gegen SQL Trace unter CPU Last) weiß ich, dass das Counter-Ziel wirklich nicht weniger Overhead hat als das File-Target. Und außerdem kann, technisch gesehen, ein File-Target einem leicht eine Zählung geben. Was ich nicht verstehe, ist, warum Microsoft nicht das pair_matching Ziel zur Verfügung gestellt hat. Das nämlich hat Fähigkeiten, die kein anderes Ziel hat! Und es ist gar nicht komplizierter zu unterstützen. Ich kann nur vermuten, dass es auf reale Anwendungsfälle basiert, und ich finde nur sehr wenige Leute, die die Vorteile dieses speziellen Ziels kenne. Schade.

Das Ring-Buffer-Target kann für weitere Analyse via XQuery geparst werden. Hier habe ich ein Beispiel: Import von Extended Events Ereignisdateien und Parsen von Deadlock-Graphen

Das File-Target in Azure SQL Database

Während das File-Target am einfachsten On-Premise zu verwenden ist, gibt es mit der Azure SQL Database einige Hindernisse. Das liegt daran, dass man dort kein Konzept des lokalen Speichers hat.

Man muss einen Pfad zu einem Storage Container verwenden. Und dafür braucht man ein Storage Account und muss man der Datenbank irgendwie erlauben, es zu beschreiben.

Die Schritte zum Einrichten eines File-Targets sind:

Einen Storage Account einrichten
Einen Container erstellen
1. a. Pfad erhalten
2. b. Shared Access Signature (rwl) erhalten
Einen Master-Verschlüsselungsschlüssel auf der Azure SQL DB erstellen
Anmeldedaten in der Datenbank erstellen, um auf Storage Account zuzugreifen
Die Extended Event Session mit der kompletten Ziel-URL konfigurieren
Die Session starten
Die Daten analysieren

Schritt 1) Einen Storage Account einrichten

Für dies alles kann man Powershell verwenden.
Aber ich werde die manuelle Herangehensweise via das Azure Portal zeigen. Dort geht man zu Storage Accounts und erstellt einen neuen. Unbedingt darauf achten, “general purpose v2” auszuwählen, und nicht v1 storage. Für die anderen Optionen wählt ihr eure individuellen Anforderungen aus.

Schritt 2) Einen Container erstellen

Im Storage Account gibt es die Option, Container zu erstellen:

Wenn dies erledigt ist, muss man:

Einen Pfad und eine Shared Access Signature erhalten

Ab hier schlage ich vor, dass ihr Azure Storage Explorer verwendet – oder für alles Powershell, um weitere Ordner (optional) zu erstellen und die Shared Access Signature (SAS) zu erhalten.

Im Storage Explorer könnt ihr nach Rechtsklick auf den Container von dort aus weitermachen:

1804_Azure_Explorer_container_properties

Die Access Policy erstellen und Shared Access Signature erhalten:

1804_Azure_Storage_Explorer_Access_Policy

Berechtigungen: rwl

1804_Azure_Explorer_get_Shared_Access_Signature

Neben der URL braucht ihr noch die Zeichenfolge nach dem Fragezeichen, was euer Shared Access Key ist. Er wird so ähnlich aussehen wie der hier:

st=2018-04-07T17%3A00%3A00Z&se=2018-06-10T13%3A45%3A00Z&sp=rwl&sv=2017-04-17&sr=c&sig=XDQNl%2BLg7aTadsdwSk9zFT2Ri127Ad1v%2Fg59yJfRR2mE%3D

Wenn ihr den habt, könnt ihr zurück zu SSMS gehen.

Schritt 3) Einen Master Encryption Key erstellen

Bevor wir ein Credential mit dem Shared Access Key erstellen können, müssen wir einen Database-Master-Key haben, um diese Entität zu schützen.

Mit diesem einfachen Befehl erstellt ihr einen Master-Key:

CREATE MASTER KEY

Für unsere Zwecke braucht man kein Passwort festzulegen. Dieser Server wird von der Microsoft Azure Plattform betrieben.

Schritt 4) Ein Credential erstellen

Jetzt müssen wir das Credential erstellen, das die Shared Access Signature enthalten wird, um auf unseren vorbereiteten Storage Account zuzugreifen.
Anmerkung: Dieser Befehl wird nur auf der Azure SQL DB funktionieren:

Wie ihr seht, könnt ihr mehrere Credentials auf eurer Datenbank erstellen.

Schritt 5) Extended Event Session konfigurieren

Gebt den Dateinamen in diesem Format an: https://StorageAccount.blob.core.windows.net/Container/FileName.xel

Ich empfehle euch, den Datenbanknamen in den Dateinamen einzubauen, es sei denn, ihr wollt einen Container für jeden haben. In meinem Beispiel wird er also sein: https://sarpedonstore.blob.core.windows.net/azuredemos/xetrace/Workload_DB_XE_Azure_Demo.xel

Im GUI könnt ihr die Credentials sehen, die es gibt:

1804_Azure_SQL_DB_Extended_Event_File_Target

Das DDL-Statement für die Session sieht so aus:

Schritt 6) Session starten, Workload laufen lassen, Session stoppen

Selbsterklärend 😉
(ihr könnt dafür GUI oder T-SQL oder Powershell verwenden)

Schritt 7) Daten analysieren

Wenn ihr einmal fertig seid und die erfassten Daten analysieren wollt, gibt es 2 Möglichkeiten: SSMS Extended Events Viewer, oder T-SQL unter Verwendung von sys.fn_xe_file_target_read_file.

Verwendet man die fn_xe_file_target_read_file-Funktion, werdet ihr feststellen, dass im Gegensatz zum lokalen File Target die Storage Container URL nur exakte Dateinamensübereinstimmungen akzeptiert – keinen Platzhalter. Das macht das Arbeiten mit dem File-Target noch zusätzlich ineffizient.

1804_Azure_SQL_DB_fn_xe_file_target_read_file

Hier zeige ich auch, wie man das File Target parst: Import von Extended Events Ereignisdateien und Parsen von Deadlock-Graphen

Aber aufgrund dieser Einschränkung empfehle ich, die Dateien auf einen speziellen Monitoring Server (auf Azure) herunterzuladen und von dort aus weiterzumachen.

Datei auf einen Monitoring Server herunterladen

Zum Herunterladen der Datei empfehle ich, den Azure Storage Explorer zu verwenden.

1804_Azure_Storage_Explorer_download_file

Jetzt könnt ihr die Datei lokal in SSMS analysieren.

Die ersten Male wird dieser Vorgang sehr umständlich und langsam vonstattengehen, bis ihr eine Routine und Automatisierung für alle diese Schritte entwickelt. Ich hoffe, dass euch diese Anleitung zu einem schnellen Start mit Extended Events auf der Azure SQL Database verhilft.