Quellcode als Download auf www.windowsdeveloper.de datenbanken . Server-Side Traces Server-Side Traces des SQL Servers mit Hilfsprozeduren Werkzeug für den Notfallkasten Probleme in der Datenbank kommen immer im ungünstigsten Moment: Der nächtliche Import schlägt fehl, Datenbanktransaktionen sind plötzlich langsam, bei einem Benutzer treten Datenbankfehler auf, die zudem auf der Testumgebung nicht nachvollzogen werden können. Der SQL Server bietet mit den Server-Side Traces ein leistungsfähiges Werkzeug, das sich für Produktivsysteme sehr gut eignet. Der Einsatz wird mit vier Hilfsprozeduren vereinfacht, die in diesem Artikel vorgestellt werden. von Georg Lampart Das Tracing des SQL Servers bezeichnet man als SQL Trace. Es eignet sich z. B. für das Aufzeichnen von SQLAbfragen, Logins/Logouts bei Verbindungsproblemen, das Ermitteln von SQL Exceptions mit Fehlermeldung, für die detaillierte Ausgabe des Ablaufes in Stored Procedures/ Triggers, beim Deadlock Tracking. Es gibt zwei Anwendungsarten von SQL Trace: der bekannte Profiler, der eine GUI-gestützte Konfiguration zulässt, und die weniger eingesetzten Server-Side Traces. Der Profiler bietet sich vor allem für Entwicklungs- und Testumgebungen an, um schnell und einfach die Aktionen des SQL Servers oder der zugreifenden Applikationen aufzeichnen zu können. Auf Produktivumgebungen bieten sich hingegen Server-Side Traces an. Bei diesen ist ein Starten und Stoppen nur mit System Stored Procedures möglich. 30 SQL Trace Die wichtigsten Möglichkeiten von SQL Trace lassen sich mit dem Profiler [1] zeigen. Ein neuer Trace lässt sich via File | New Trace erstellen. Nach der Verbindung zum Zielserver erscheint ein Dialog für die Definition des Traces. Im ersten Register ist vor allem die Auswahl eines Templates als Ausgangskonfiguration zentral. Im zweiten Register zeigen sich die umfangreichen Möglichkeiten von SQL Trace (Abb. 1). Es stehen viele Events zur Verfügung; jeder kann viele Informationen liefern (Data Columns). Mit der Schaltfläche Filter unten rechts kann ein kleiner Dialog geöffnet werden. Mit diesen Filtern können die aufzuzeichnenden Events weiter eingeschränkt werden, zum Beispiel mit einem Applikationsnamen, einem Benutzernamen oder der Mindestdauer eines SQL Statements. 8.2012 Server-Side Traces . datenbanken Abb. 1: Profiler – TraceEigenschaften mit offenem Filterdialog Nach Auswahl der benötigten Events, Daten und Filter wird der Dialog mit der Run-Taste geschlossen und der Trace gestartet. Im Profiler werden die betroffenen Events fortlaufend ausgegeben. Für Entwicklungs- und Testumgebungen ist das eine praktische und schnelle Vorgehensweise, um Problemen auf die Spur zu kommen. Für Produktivsysteme hat der Profiler jedoch einige Nachteile: •Der Overhead des Profilers ist relativ hoch (das kann CPU, Memory, Speicherplatz und Netzwerkbandbreite betreffen [2]). •Es ist auf die Schnelle nicht sichtbar, wie der Speicherbedarf im temporären Verzeichnis durch die laufend aufgezeichneten Events zunimmt [2]. •Bei der Ausgabe im Profiler ist nicht sichergestellt, dass bei hohem Aufkommen alle Events lückenlos aufgezeichnet werden. Das resultiert aus der Verwendung des Rowset-Providers, der das nicht garantieren kann [3]. •Die unbeaufsichtigte beziehungsweise automatisierte Ausführung von Traces ist nicht oder nur schwierig zu bewerkstelligen, zum Beispiel wenn es darum geht, einen Batch-Job nachts um 3 Uhr protokollieren zu lassen. Server-Side Traces können diese Nachteile ausgleichen: Die Daten können unter Verwendung des File-Providers direkt in eine (lokale) Datei geschrieben werden. So wird der Overhead minimiert und es ist sichergestellt, dass die Events auch bei hohem Aufkommen lückenlos aufgezeichnet werden. Die maximale Größe der Datei kann vor dem Start festgelegt werden, der Speicherbedarf wird vorhersehbar. Traces können automatisch zu einem festgelegten Zeitpunkt oder wenn die Datei die maximale Größe erreicht angehalten werden. Server-Side Traces Die Basis, um Server-Side Traces zu definieren, zu starten und zu stoppen, bilden mehrere Systemprozeduren, die den Namensanfang sp_trace aufweisen. Der Profiler hilft hierbei, aus einer Trace-Definition ein SQL Script zu generieren, mit dem Server-Side Traces definiert und gestartet werden können. Dazu erstellen wir einen neuen Trace, beispielsweise mit dem Template TSQL, und drücken gleich auf Pause (da wir ja nichts aufzeichnen wollen). Via File | Export | Script Trace Definition | For SQL Server 2005 – 2008 R2 können wir das Script in eine Datei speichern. Listing 1 zeigt einen Ausschnitt des Scripts (Variablendeklaration, Endteil und einige Eventdefinitionen wurden entfernt). Kurz zusammengefasst wird mit sp_ trace_create der Trace in seiner Grundform definiert. Die folgenden sp_trace_setevent fügen die Events und Data Columns hinzu. Die Prozedur sp_trace_setstatus startet den Trace. Am Schluss wird die ID des gestarteten Trace ausgegeben, die für das Stoppen benutzt werden kann (weitergehende Informationen unter [4]). Können Sie aus den Zeilen in Listing 1 ablesen, aus welchen Events und 8.2012 Data Columns der Trace zusammengesetzt ist? Hier zeigt sich eines der Mankos dieser Scripts: •Die kryptischen Zahlen beim Aufruf von sp_trace_ setevent sagen nicht aus, welche Events, welche Daten benutzt werden. •Im Script muss einiges manuell angepasst werden, zum Beispiel der Dateiname, ein Endzeitpunkt oder die maximale Dateigröße. •Die Datei darf im Zielverzeichnis nicht existieren. Man sollte also darauf achten, eine Trace-Datei vor dem Start wegzukopieren oder mit jedem Trace-Start einen eindeutigen Dateinamen zu vergeben. •Fürs Stoppen des Traces muss man ein weiteres Script nehmen. Die ID des Traces muss man sich selber merken, was vor allem beim automatisierten Starten/Stoppen mit Zusatzaufwand verbunden ist. •Wenn man die Definition des Traces nachträglich anpassen will, muss man das Script nochmals als Ganzes exportieren. Dateiname und die anderen Anpassungen sind dann wieder einzupflegen. Listing 1 -- Please replace the text InsertFileNameHere, with an appropriate -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension -- will be appended to the filename automatically. exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL if (@rc != 0) goto error -- Set the events exec sp_trace_setevent @TraceID, 14, 1, @on exec sp_trace_setevent @TraceID, 14, 14, @on exec sp_trace_setevent @TraceID, 14, 12, @on -- (More calls with sp_trace_setevent were omitted) -- Set the trace status to start exec sp_trace_setstatus @TraceID, 1 -- display trace id for future references select TraceID=@TraceID goto finish www.windowsdeveloper.de 31 datenbanken . Server-Side Traces Die Server-Side Traces sind ein mächtiges Tool, leider gestaltet sich die Handhabung etwas umständlicher als erwartet. Gerade in Stresssituationen sollte man sich auf das Tracing konzentrieren können und sich nicht mit Scripts, kryptischen Zahlen, eindeutigen Dateinamen und TraceIDs herumschlagen müssen. Hilfsprozeduren Das hier vorgestellte Script umfasst eine Tabelle und vier Hilfsprozeduren (Tab. 1). Zusammengefasst können damit Trace-Definitionen in XML-Form als Vorlagen verwaltet sowie Server-Side Traces gestartet und gestoppt werden. Bevor wir mit der Schritt-für-Schritt-Anleitung beginnen können, sind folgende Vorarbeiten erforderlich: •Führen Sie das Installations-Script auf der master-Datenbank Ihrer Testumgebung aus (oder in einer anderen Datenbank, wenn Ihre Richtlinien das verbieten). •Starten Sie im Profiler einen neuen Trace, zum Beispiel mit dem Template TSQL, und drücken Sie unmittelbar danach die Pause-Taste, da wir wiederum keine Events aufzeichnen wollen. Jede Prozedur unterstützt mehrere Aktionen und verfügt über einen Hilfstext. Dieser beschreibt die verfügbaren Aktionen und dokumentiert die entsprechenden Parameter pro Aktion. Der Hilfstext wird mit der Aktion HELP im Meldungsfenster ausgegeben: EXECUTE dbo.spr_gla_monitoring_traces_manage @Action = 'HELP'; Listing 2 -- a) Rückgabe aller aktiven Traces EXECUTE dbo.spr_gla_monitoring_traces_manage @Action = 'LIST_RUNNING_TRACES'; -- b) Ausgabe des XML als Resultat EXECUTE dbo.spr_gla_monitoring_traces_capture @Action = 'GET_XML', @TraceId = 2; -- c) Speichern des XML mit einem Namen in der Vorlagen-Tabelle EXECUTE dbo.spr_gla_monitoring_traces_capture @Action = 'SAVE', @TraceId = 2, @TraceDefinitionName = N'TSQL'; -- d) Ausschnitt einer Trace-Definition als XML <TraceDefinition> <TraceEvents> <TraceEvent name="RPC:Starting" id="11"> <Columns> <Column name="BinaryData" id="2" enabled="1" /> <Column name="SPID" id="12" enabled="1" /> <Column name="StartTime" id="14" enabled="1" /> </Columns> </TraceEvent> <TraceEvent name="SQL:BatchStarting" id="13"> <Columns> <Column name="SPID" id="12" enabled="1" /> <Column name="StartTime" id="14" enabled="1" /> <Column name="TextData" id="1" enabled="1" /> </Columns> </TraceEvent> </TraceEvents> </TraceDefinition> Listing 2 zeigt, wie die Prozeduren eingesetzt werden. In Teil a wird mit der Aktion LIST_RUNNING_TRACES eine Liste aller aktuell gestarteten Traces ausgegeben. Wichtig in dieser Liste ist die Spalte id, mit der gestartete Traces referenziert werden können. Normalerweise werden Sie zwei Datensätze sehen: einen Trace (mit dem Typ INTERNAL bezeichnet), der vom SQL Server selber gestartet wird, sowie einen Trace (vom Typ custom), den Sie im Profiler erstellt und angehalten haben. In Teil b von Listing 2 sehen Sie, dass Sie mit der ID des zweiten Trace die Definition als XML ausgeben oder wie in c das XML mit einem Namen in die Vorlagentabelle speichern können. Die XML-Struktur sehen Sie in Teil d. Jedes Event und die zugewiesenen Data Columns werden sowohl im Klartext als auch mit der kryptischen Nummer hinterlegt. Mit dieser XML-Struktur sollte die Definition des Traces jederzeit nachvollziehbar sein. Weitere Aktionen können im Script mit den Beispielaufrufen nachvollzogen werden, beispielsweise gespeicherte Vorlagen abfragen, ändern oder löschen. Starten und Stoppen Nachdem Sie die gewünschten Trace-Definitionen als XML gespeichert haben, geht es nun darum, mit der Vorlage beziehungsweise den XML-Daten einen Trace zu starten und zu stoppen. Die Startprozedur nimmt den Namen der gespeicherten Vorlage (im Beispiel TSQL) entgegen (Listing 3). Zusätzlich sind der Speicherpfad sowie ein Identifier als Mindestinformation anzugeben. Tabelle/Prozedur Beschreibung gla_trace_definition Tabelle, in der Trace-Definitionen in XML-Form als Vorlagen hinterlegt werden können. spr_gla_monitoring_traces_capture Generiert aus der Definition eines aktiven Trace ein XML. Dieses kann als Vorlage in die Tabelle gla_trace_definition gespeichert oder als Resultat zurückgegeben werden. spr_gla_monitoring_traces_manage Mit dieser Prozedur können einerseits die aktiven Traces oder die gespeicherten Vorlagen ausgegeben werden. Zusätzlich können Vorlagen erstellt, geändert oder gelöscht werden. spr_gla_monitoring_traces_start Startet einen Trace mit einer Vorlage aus der Tabelle gla_trace_definition oder ad hoc mit einer XMLDefinition. spr_gla_monitoring_traces_stop Stoppt einen Trace anhand eines Identifiers, anhand einer Trace-ID oder beendet alle Traces, die mit diesen Hilfsprozeduren gestartet wurden. Tabelle 1: Bestandteile des Installationsskripts 32 8.2012 Server-Side Traces . datenbanken Den Identifier werden wir beim Stoppen näher betrachten. Die Startprozedur erledigt einige Aufgaben: Im zweiten Teil von Listing 3 wird der Stoppmechanismus gezeigt. Die Stoppprozedur benötigt nur den Identifier, der beim Starten definiert wurde (bei der Aktion STOP_ WITH_RUNNING_IDENTIFIER). Das hat den Vorteil, dass die SQL-Server-spezifische Trace-ID nicht bekannt sein muss. Natürlich kann man einen Trace auch mit der ID stoppen über die Aktion STOP_WITH_TRACEID. Als dritte Option können alle aktiven Traces gestoppt werden, die den Präfix im Dateinamen aufweisen (mit der Aktion STOP_ALL). Die Einschränkung mit dem Präfix gilt bei allen drei Aktionen, damit fremde Traces nicht aus Versehen beendet werden. Die Startprozedur wird mit weiteren Informationen ausgeführt; so können einerseits die maximale Dateigröße sowie der Endzeitpunkt mitgegeben werden, andererseits sind vier Filterparameter verfügbar, mit denen zusätzliche Einschränkungen zur Trace-Definition hinzugefügt werden können, zum Beispiel der Datenbankname. Listing 4 zeigt ein Beispiel, bei dem nur SQL-Abfragen protokolliert werden, die die Datenbank AdventureWorks2008R2 betreffen und deren CPU-Zeit mehr als eine Sekunde dauert (zu beachten ist, dass der CPU-Filter nur bei Events wirksam ist, die diesen Wert aufzeichnen, beispielsweise SQL:BatchCompleted. Der Trace muss also entsprechend definiert werden). Praxiseinsatz Was ist nötig, um die Server-Side Traces mit diesen Prozeduren in einer Produktivumgebung einzusetzen? •Definieren Sie die benötigten Trace-Definitionen auf einem Testsystem und generieren Sie das XML. Beachten Sie dabei die Optimierungsvorschläge bei TraceDefinitionen von Microsoft [5]. Mit der Prozedur dbo. spr_gla_monitoring_traces_manage und der Aktion SAVE_TRACE_WITH_XML können die XML-Definitionen in ein Script hinterlegt und auf einem anderen Server in die Vorlagentabelle gespeichert werden. •Die erstellten Scripts sind anschließend auf dem Produktivsystem zu installieren. Zusätzlich benötigen Sie ein Verzeichnis, in das die Dateien geschrieben werden 8.2012 Video Elemente Canvas API SVG JScript Geolocation CSS3 Kommunikation Audio HMTL5 IHR SCHNELLSTER WEG ZU HTML5 DAS NEUE HTML5 CAMP Bootcamp für zukünftige HTML5-Profis 3 me r issen mit er ngerten eiten Buchautoren als Trainer 4 Kurstage in 3 komprimiert Persönliches Rund um glücklich - Paket JETZT BUCHEN! Frühbucher sparen bis zu 200 € oder übernachten gratis TERMINE JEWEILS 3 TAGE ab 26. Juli in Berlin ab 24. Sept. in München EIN CAMP VON PPEDV AG HTML5Camp.de ppedv AG, Marktlerstr. 15b, 84489 Burghausen, Handelsregister Traunstein, HRB 12703, St.Nr. 131-45412 , Vorstand: Johannes Preishuber •Die Prozedur liest das XML automatisch aus der Tabelle aus und generiert daraus den Server-Side Trace. •Als Default wird die Dauer des Traces auf eine Stunde festgelegt und die maximale Größe auf 50 Megabyte. Es empfiehlt sich jedoch, diese Informationen explizit anzugeben. •Die Prozedur generiert den Dateinamen zusammengesetzt aus dem Zielpfad, einem Dateipräfix TraceStartedByGLA, dem oben genannten Identifier sowie einem Zeitstempel. Dadurch ist der Dateiname immer eindeutig, was bei unbeaufsichtigten/automatisierten Traces vorteilhaft ist. Mit dem Präfix lässt sich ermitteln, ob ein Trace von den Hilfsprozeduren erstellt worden ist. datenbanken . Server-Side Traces können. Der SQL-Server-Service-Account benötigt Schreibrechte. Es wird empfohlen, ein lokales Verzeichnis zu verwenden. •Zum Starten und Stoppen der Traces eignen sich SQLServer-Agent-Jobs sehr gut. Damit haben Sie bereits die Basis, um Traces manuell und zeitgesteuert/unbeaufsichtigt ausführen zu können. Zusätzlich wird mit jedem Jobstart/Fehler eine Historie geschrieben. Legen Sie einen Job für den Start des Traces an; Listing 4 kann als Basis für das zu hinterlegende SQL dienen. Einen weiteren Job erstellen Sie für das Stoppen des Traces. Zu Beginn wird es reichen, wenn der Job sämtliche Traces anhält (also mit der Aktion STOP_ALL). •Bevor Sie die Traces produktiv einsetzen, prüfen Sie, ob das Ziellaufwerk über genügend Speicherplatz verfügt. Sinnvoll ist es, den Trace zuerst eine kurze Zeit (beispielsweise fünf Minuten) laufen zu lassen. An der Größe der Datei können Sie hochrechnen, wie lange der Trace ungefähr läuft, bis die maximale Dateigröße erreicht ist. Vielleicht müssen Sie die maximale Größe erhöhen, die Trace-Definition vereinfachen oder wei- Listing 3 -- Starten des Server-Side Trace mit einer Vorlage und Mindestinformationen -- Der Identifier PerfTrace kann beim Stoppen verwendet werden anstelle der ID EXECUTE dbo.spr_gla_monitoring_traces_start @Action = 'START_WITH_NAME', @TraceDefinitionName = 'TSQL', @RunningTraceIdentifier = N'PerfTrace', @TraceFileSavePath = N'C:\temp\'; -- Stoppen des Traces mit dem Identifier PerfTrace EXECUTE dbo.spr_gla_monitoring_traces_stop @Action = 'STOP_WITH_RUNNING_IDENTIFIER', @RunningTraceIdentifier = 'PerfTrace'; Listing 4 -- Starten eines Trace mit erweiterten Informationen -- Den Endzeitpunkt vor Aufruf der Prozedur ermitteln DECLARE @StopTime DATETIME; SET @StopTime = DATEADD(HOUR, 12, CURRENT_TIMESTAMP); EXECUTE dbo.spr_gla_monitoring_traces_start @Action = 'START_WITH_NAME', @TraceDefinitionName = 'TSQL', @RunningTraceIdentifier = N'PerfTrace', @TraceFileSavePath = N'C:\temp\', @TraceFileMaxSizeInMB = 1024, @TraceFileStopTime = @StopTime, @FilterDatabaseName = N'AdventureWorks2008R2', @FilterCPU_GreaterThanEqual = 1000; tere Filterkriterien hinzufügen, damit die Protokollierung nicht zu früh abbricht. •Beobachten Sie bei den ersten Einsätzen das System und das Verhalten des Trace-Vorgangs. Bei Problemen können Sie so schnell handeln und den Trace notfalls manuell stoppen. Dann steht dem Einsatz von Server-Side Traces einzusetzen nichts mehr im Wege. Was passiert aber mit der Datei, die der Trace erzeugt hat? Auswertung Werten Sie die Datei möglichst nicht auf dem Produktivsystem aus. Kopieren Sie diese auf einen Testserver. Bei kleineren Dateien können Sie die Datei im Profiler mit File | Open | Trace File öffnen, dann werden alle aufgezeichneten Events angezeigt. Sie können in den Trace-Eigenschaften (File | Properties) Events ein-/ausschalten oder sogar Filter setzen. Nach dem Schließen des Eigenschaftendialogs werden dann nur noch die gewünschten Events angezeigt. Das hilft bei der Problemsuche. Bei großen Dateien lohnt es sich, die Daten mit SQL zu verarbeiten. Listing 5 zeigt den Zugriff auf die Datei. Damit haben Sie zur Auswertung der Events die ganze Power von SQL zur Verfügung. Empfehlenswert ist, wenn Sie die Daten mit SELECT … INTO in eine Tabelle laden. Danach können Sie Indizes setzen, aggregieren, filtern, nicht benötigte Events löschen etc. Fazit SQL Trace bietet viele Möglichkeiten, Probleme in der Datenbank und deren Gründe zu erkennen. Mit dem Profiler steht ein intuitives Programm zur Verfügung, um Traces zu definieren und Protokollierungen bei Entwicklungs- und Testumgebungen durchzuführen. Für Produktivsysteme sollten die Server-Side Traces verwendet werden. Die vorgestellten Hilfsprozeduren sollen die zum Teil umständliche Handhabung vereinfachen. Dadurch kann sich der DBA beziehungsweise der Entwickler auf die Problemlösung konzentrieren. Georg Lampart ist Senior Software Engineer bei der bbv Software Services AG. Seine Schwerpunkte liegen in der SQL-Server-Datenbankentwicklung sowie der .NET-Softwareentwicklung im Bereich Businessapplikationen. Er hat über zwölf Jahre Erfahrung im Design, der Programmierung und Optimierung von SQL-Server-Datenbanken. Sie erreichen ihn unter [email protected]. Links & Literatur Listing 5 SELECT * FROM dbo.fn_trace_gettable( N'C:\temp\TraceStartedByGLA__CPUTrace__20120101120000.trc', DEFAULT); 34 [1] SQL Server Profiler: http://bit.ly/LNCNXZ [2] Profiler Overhead: http://bit.ly/bMM0BX [3] Getting Started Profiler, Absatz nach Figure 2-2: http://bit.ly/JWQ0OJ [4] SQL-Ablaufverfolgung: http://bit.ly/L2heFH [5] Optimierung SQL-Ablaufverfolgung: http://bit.ly/LRCUFb 8.2012