Artikel als pdf lesen - bbv Blog

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