Tag: "tracing"

Performance overhead of tracing with Extended Event targets vs SQL Trace under CPU Load

Leistungseinbußen beim Tracing, Extended Event Ziele gegen SQL Trace unter CPU Last

(DE)

Inspiriert durch den Blog Post Post “Measuring Observer Overhead of SQL Trace vs. Extended Events” von Jonathan Kehayias, war ich neugierig, ein paar weitere Benchmarks durchzuführen. Die Resultate habe ich bereits in meinen SQL Server Master-Class Workshops zu XEvents und auf der PreCon des SQLSaturday #230 präsentiert. Nun teile ich sie endlich hier.

Vor allem folgende Aspekte interessierten mich:

  • Was ist der Performance-overhead einer Extended Event Session selber – ohne die Verarbeitung und des Dispatchens zu den Targets
  • Wie verhält sich das im Vergleich zu SQL Trace – auch das ohne Overhead für ein Ziel (Keine Datei, kein Profiler: gar kein Konsument)

Da ich einmal das Setup bereit hatte, habe ich mich entschieden, alle anderen Ziele ebenfalls aufzunehmen, sowie einige grundlegende Options-Variationen, um einen kompletten Überblick zu erhalten, der in sich vergleichbar ist.

(EN)

Inspired by the Blog Post “Measuring Observer Overhead of SQL Trace vs. Extended Events” by Jonathan Kehayias, I was curious to do some more benchmarking.
I have already presented the results in my SQL Server Master-Class workshops on XEvents as well as at the PreCon of the SQLSaturday #230. Now I am finally sharing them with you here.

I was interested in the following aspects, in particular:

  • What is the Performance-overhead of an Extended Event Session itself – not including the processing/dispatching for the target
  • How does this compare to the old-school SQL Trace – again with no Target overhead (No File, no Profiler: no consumer at all)

Besides that, once I had the setup ready, I decided to include all the other targets and some basic option-variations, to have a complete overview that is comparable in itself.

Ich entschied mich für ein System, das bereits unter CPU Druck stehtdie CPU Verwendung liegt fast vollständig bei 100%.
Unter diesen Umständen hoffte ich die maximalen Auswirkungen von Extended Events („Erweiterte Ereignisse“) als auch SQL Trace (Zur Erinnerung: SQL Profiler basiert auf SQL Trace) zu erhalten. Man könnte meinen, dass eine E/A(I/O)-lastige Arbeitslast noch mehr beeinflusst werden würde, aber das hängt an den Targets für Extended Events. – 4 davon gehen lediglich in den Arbeitsspeicher, gegenüber einem File-Target (welches man ohnehin auf ein Nicht-Daten-Volume legen sollte). Und da ich auch den reinen Overhead der Trace-Technologien (ohne Ziel) messen möchte, macht das noch mehr Sinn. Abgesehen davon kann I/O in der Regel meist recht einfach getuned werden, indem man auf ein ansonsten unverwendetes und schnelles Device schreibt, wohingegen es für CPU nicht so einfach ist, die Kosten unsichtbar zu halten. Das im Hinterkopf zu behalten hilft, die Resultate, die hier präsentiert werden zu evaluieren und sie in den rechten Kontext zu rücken.

I also decided for a test of a system that’s already under CPU pressure - so the Processor Usage is almost constantly hitting 100%.
Under these circumstances I was hoping to get the maximum impact of Extended Events as well as SQL Trace. (Remember: SQL Profiler is based on SQL Trace) One could think that an I/O driven workload would be even more influenced, but that depends on the targets of Extended Events. - 4 of which only go to memory, vs. one file target (which one should put onto a non-data volume anyways). And since I also want to measure the pure overhead of the tracing technologies (without any target), this makes even more sense.
Besides that, I/O usually can be tuned quite easily by writing to an otherwise idle and fast device, whereas for CPU it is not as simple to keep the costs invisible. Keeping this in mind helps to evaluate the results presented here and bringing them into the right context.

 

Test Umgebung

“Einfach aber physikalisch”

1 Windows 2008 R2 Server mit SQL Server 2012 SP1. Keine laufenden Dienste abseits des minimal Notwendigem, um Seiten-Effekte zu verhindern.
Ich habe auch die Default-Trace sowie die system_health_session gestoppt.
Der Distributed Replay-Client wurde für alle Test local ausgeführt, und Ja, das hat Effekt auf den Server, aber das war gleich für alle Tests. Alle Tests wurden 3 Mal wiederholt (einige sogar öfter), um sicherzustellen, dass der Durchschnitt/Median valide ist.
Der Server, wie gesagt, war physikalisch (das kommt praktisch, wenn man Benchmarks mit einer Gesamtzeit von ~50 Stunden durchführt.): 1 Dual Core Intel Xeon mit 8 GB RAM (was bei Weitem genug war), Daten Volumen und Volumen für Trace-Targets auf separate Controllers.

Die Arbeitslast war ein Mix von meist kurzen (Sub-Sekunden) aber auch einigen Abfragen mit mehreren Sekunden Laufzeit, (Ad Hoc und gespeicherte Prozeduren), um eine einigermaßen realistische Decision-Support-System Last zu simulieren – jedoch ohne Datenänderungen.
Die Baseline Workload dauerte 00:24:24 Minuten. Die Statistiken blieben für alle Workload-Zyklen identisch. Die verwendete Datenbank war AdventureWorks2012.

Test Environment

“Simple but physical”

1 Windows 2008 R2 Server with SQL Server 2012 SP1. No running services above the minimum required, to avoid side-effects.
I also shut down the default trace as well as the system_health_session.
The Distributed Replay-client was run locally for all tests, so yes, this has effect on the server, but it was equally for all tests. All tests have been repeated 3 times (some even more) to make sure the average/median is valid.
The server, as I already said, was physical (which comes in handy when you are running benchmarks for a total of ~50 hours): 1 Dual Core Intel Xeon with 8 GB of RAM (which was by far enough), Data Volumes and Volumes for Trace-Targets on separate Controllers.

The workload was a mixture of mostly short (sub-second) but also several queries of several second runtime, (Ad Hoc and stored procedures) to simulate a somehow realistic decision-support-systems’ load – no data changes though.
The baseline workload took 00:24:24 minutes. The statistics stayed the same for all workloads cycles. The database used was AdventureWorks2012.

 

Trace-Konfigurationen

Ich habe mich für eine einfache, aber nicht unübliche Trace entschieden, die typischerweise viele Daten sammelt.
Die Trace sammelt nur 2 Ereignisse:

Trace-Configurations

I decided for a simple but not uncommon trace that typically collects a lot of data. The trace collects only 2 events:

  • Lock acquired
  • Lock released

 

Ausgefiltert: System-Sitzungen und andere Datenbanken als die, die hier unter Last steht.

Gesammelte Daten:

Filtered out: System-sessions and other databases than the one under load here.

CollectedData:

 

  • Lock:Released   BinaryData
  • Lock:Released   DatabaseID
  • Lock:Released   TransactionID
  • Lock:Released   SPID
  • Lock:Released   ObjectID
  • Lock:Released   Mode
  • Lock:Released   BigintData1
  • Lock:Released   IntegerData2
  • Lock:Released   ObjectID2
  • Lock:Released   Type
  • Lock:Released   OwnerID
  • Lock:Released   IsSystem
  • Lock:Acquired   BinaryData
  • Lock:Acquired   DatabaseID
  • Lock:Acquired   TransactionID
  • Lock:Acquired   SPID
  • Lock:Acquired   Duration
  • Lock:Acquired   ObjectID
  • Lock:Acquired   IsSystem
  • Lock:Acquired   Mode
  • Lock:Acquired   BigintData1
  • Lock:Acquired   IntegerData2
  • Lock:Acquired   ObjectID2
  • Lock:Acquired   Type
  • Lock:Acquired   OwnerID

Das gab mir den Vorteil, einen validen Test für alle Extended Event Targets, die bereitgestellt werden, zu haben (lediglich ETW-Tracing wurde ausgelassen) - speziell das Histogramm und die „Ereignispaarbildung“ (Pair-Matching)(Die wenigen Ereignisse von Lock-Escalation störten mich bewusst nicht).

Die folgenden Trace Technologien und -Ziele wurden verwendet:

This gave me the advantage to have a valid test for all Extended Event Targets that are provided (only ETW-Tracing was left out) - specifically histogram and Pair Matching (I did not care about the few occasions of lock escalation on purpose).

The following Trace technologies and -Targets were used:

 

  • XEvent Trace, Target: None
  • XEvent Trace, Target: Ring Buffer
  • XEvent Trace, Target: File
  • XEvent Trace, Target: Counter
  • XEvent Trace, Target: Histogram
    • Here I chose to filter on lock acquired and to bucketize on the lock mode
  • XEvent Trace, Target: Pair Matching
    • Guess, what I matched here ;-)
  • SQL Trace, Target: None (I had to trick a little bit to get this to work, but I made sure, the behavior of this unsupported configuration did not distort the results: It’s just a SQL Trace with no Provider processing, so all events are lost by nature.)
  • SQL Trace, Target: File
  • SQL Profiler, remotely

Für jedes Ziel der Extended Events habe ich 4 Varianten getestet, basierend auf den Sitzungsoptionen:

For each Extended Event Target I tested 4 variations, based on the session options:

  • Single event loss
  • No event loss
  • Multiple event loss
  • Single event loss with causality tracking on

Alle anderen Optionen verwendeten die Standardwerte für diese Tests.

All other options were using the defaults for these tests.

 XEvent_PerfComparison_sessions

Picture: the 24 prepared traces

Die Ergebnisse

Ich habe die Gesamtlaufzeit für die Workload sowie Batches pro Sekunde und CPU Zeit % gemessen.

Und hier ist die vollständige Tabelle mit den Ergebnissen:

The Results

I measured the total time for the workload to take as well as batches per second and CPU time %.

And here is the complete Table of Results:

 

 XEvent_PerfComparison_Runtime_Avg_EvSec

EL = Ereignisverlust. S = Verlust einzelner Ereignisse, N = Kein Ereignisverlust, M = Verlust mehrerer Ereignisse

CT = Kausalitätsverfolgung (Causality Tracking) Ein

EL = Event Loss. S = Single event loss, N = No event loss, M = Multiple event loss

CT = Causality Tracking On

 

Um Zeit und Platz zu sparen, konzentriere ich mich auf die Benchmarks mit den Optionen single event loss und no event loss ohne Causality Tracking. Tatsächlich waren die Kosten von Causality Tracking weniger als 1% für alle Ziele.

Hier ist daher das komprimierte Ergebnis:

For the sake of saving space and time, I will focus on the benchmarks with the options single event loss and no event loss without causality tracking. In fact, the cost of causality tracking was less than 1% for all targets.

So this is the more condensed result:

 

 XEvent_PerfComparison_Runtime_S_N_Avg_EvSec

Was wir demnach sagen können, ist:
(Erinnern wir uns daran: alles läuft unter Hoher CPU Last. Das ist die Grundlage aller Ergebnisse.)

What we can tell from that, is:
(Remember: Everything is running under High CPU Load, so that’s the base of all results.)

  • Eine blanke XEvent Session alleine, ohne Targets, hat bereits einen spürbaren Einfluss von ~15 % Leistungsverlust.
  • Der Aufschlag („Overhead“) der künstlich erzeugten (nicht unterstützten) Version einer blanken SQL Trace, ohne den Aufschlag eines Rowset oder File Providers („Target“), ist nicht allzu weit von dem einer Extended Events Session ohne dem Dispatchen zu einem Target entfernt (~19% gegen ~15%).
  • Der Ringpuffer (Ring Buffer) ist das XE-Target mit dem größten Overhead (~40%). (!)
  • Das Ereignisdateiziel (“File-Target”) hat, wie erwartet, wirklich den geringsten Overhead, aber dieser beläuft sich immer noch auf 25%.
  • Die Unterschiede zwischen den Targets Ereigniszähler („Counter“), Histogramm und Ereignispaarbildung („Pair Matching“) sind irrelevant.
    • Ja, sogar der simple Counter hat einen höheren Overhead als das File Target.
    • Das File Target mit No Event Loss zu konfigurieren, fügt fast den vierfachen Overhead hinzu.
    • Die Unterschiede für die anderen Targets zwischen single, multiple und sogar no event loss ist in diesem Szenario fast negierbar.
    • Verglichen mit Extended Events ist der Overhead durch eine SQL Trace fast schon “brutal”: Er vervierfacht die Gesamtlaufzeit.
    • Und noch mehr: Tracing mit dem SQL Profiler ist in solch einem Szenario schlicht ein Ding der Unmöglichkeit: Ich habe 21 Stunden eingetragen, aber das ist tatsächlich eine Schätzung: Ich beendete diese Test-Zyklen nach rund 3 Stunden (man möge mir verzeihen, aber es ist schlicht Zeitverschwendung...), zählte die beendeten Events und rechnete aus, wie lange es noch gedauert hätte, grob – aber tatsächlich eher optimistisch. Profiler ist, wie ich es immer sage, ein “No-Go” um für längere Zeiträume zu tracen, vor allem, wenn die Performance ohnehin bereits leidet.
 
  • A plain XEvent Session itself, with no Targets, already does have a noticeable impact of ~15 % performance decrease.
  • The overhead of the artificial (not supported) version plain SQL Trace, without the overhead of a rowset or file provider (aka target), is not too far off the Extended Events Session without the dispatching to any target (~19% versus ~15%).
  • The Ring Buffer is the XE-Target with the greatest overhead (~40%). (!)
  • The File-Target really does have the lowest overhead as expected, but it’s still 25%.
  • The differences between the Targets Counter, Histogram and Pair Matching are irrelevant.
    • Yes, even the simple Counter has a higher overhead than the File Target.
    • Configuring the file target with no event loss adds approximately 4 times as much performance overhead.
    • The difference for the other targets between single, multiple and even no event loss is mostly negligible in this scenario.
    • Compared to Extended Events, the overhead through SQL Trace is almost “brutal”: It quadruplicated the total runtime.
    • And there's even more: Tracing with SQL Profiler is absolutely impossible for such a scenario: I filled in 21 hours, but that’s actually an estimate: I stopped those test cycles after around 3 hours (forgive me, but it’s just a waste of time...), counted the finished events and calculated how much else it would have taken, roughly – in an actually rather optimistic manner. Profiler, as I always say, is a “no go” for tracing longer periods of time, especially if performance is already at stake.

Ein schönes Bild der SQL Trace Architektur findet sich in den BOL: msdn.microsoft.com/en-us/library/hh245121.aspx
Die Extended Events Architektur wird hier bildlich gezeigt: msdn.microsoft.com/en-us/library/bb630318%28v=sql.105%29.aspx

You have a nice picture of the SQL Trace Architecture in BOL: msdn.microsoft.com/en-us/library/hh245121.aspx
The Extended Events Architecture is depicted here: msdn.microsoft.com/en-us/library/bb630318%28v=sql.105%29.aspx

 

Hinzufügen von Filtern

Wie ändert das Hinzufügen von Filtern den Beobachter-Overhead?

Es ist wichtig, zu wissen, was ich als Filter verwendet habe: Die database_id / source_database_id.
- Tatsächlich habe ich einen Filter verwendet, der niemals “true” zurückgeben würde, indem ich einen Filter auf database_id = 77 angelegt habe – da keine Sitzung/Abfrage/Sperre je auf dieser nicht-existenten Datenbank lag.
Warum habe ich das gemacht? – Das ist ein rein künstlicher Test/Benchmark. Was mich interessiert, ist der reine Overhead des Tracen, und nicht, was passiert, wenn ein bestimmter Prozentsatz der Aktivität nicht protokolliert wird. (Das könnte vielleicht etwas für einen anderen Benchmark sein. :-))

Adding Filters

How does adding filters change the observer overhead?

Now it is important to note what I actually used as a filter: The database_id / source_database_id.
- I essentially added a filter that would never return “true” by adding a filter on database_id = 77 – since no session/query/lock was on this non-existing database.
Why did I do that? – This is a totally artificial test/benchmark. I am interested what the pure overhead of tracing is, and not what happens if a certain percentage of activity is not logged. (That might be something for a different benchmark. :-))

Dieses Mal konzentriere ich mich nur auf „single“ und „no event loss“.

Die Resultate des gefilterten Tracing, ohne tatsächlich irgendwelche Events zu speichern/protokollieren, ist wie folgt:

This time I only focused on “single” and “no event loss”.

The results of Filtered Tracing, without actually storing/logging any events is as follows:

 

 XEvent_PerfComparison_Runtime_Filtered_Avg

  • Wie man sehen kann, fällt der Overhead der reinen XEvent Session ohne Target von ~ 15% auf 9% bzw. ~ 16,5% gg. 12,5 % (No Event loss)
  • Die anderen Targets zeigen in diesem Setup tatsächlich die größten Unterschiede:
    • Das Counter Target mit single event loss profitiert am Meisten von dem Filter und fällt von ~33,5 auf 8,4 % Overhead.
    • Nur Ring Buffer und Pair Matching zeigen keinen Unterschied zwischen single und no event loss.
    • SQL Trace mit und ohne rowset Provider (Datei) als Target haben fast den gleichen Overhead – keine Überraschung, wenn man die Architektur kennt. Das ist tatsächlich ein weiterer risen Unterschied, der für Extended Events spricht, wo Filters viel früher in der Trace berücksichtigt werden.
    • Das der Overhead von SQL Trace ohne Target und zugleich aller Events herausgefiltert sogar höher ist, als der Overhead ohne Target aber auch ohne Filter (von dem vorhergehenden Test-Zyklus), muss an dem Verarbeiten des Ausfilterns selber liegen. Ich werde mich damit aber nicht weiter befassen, da das Setup weder dokumentiert noch unterstützt ist.
      Und in meinen Augen ist “SQL Trace“ sowieso „tot“ ;-) – Okay, ich übertreibe hier ein wenig, aber ich bin auch ganz offen: Für SQL Server 2012 gibt es fast keinen Grund mehr zu dessen Verwendung. – Benchmarks mithilfe von Distributed Replay durchzuführen, ist so ein Grund – ist das nicht ironisch?

Für diesen Test habe ich SQL Profiler nicht ausgeführt, Pardon. Sie wissen bis hierhin wahrscheinlich schon, warum. ;-)

  • As one can see, the overhead of the pure XEvent session without target drops from ~ 15% to 9% respectively ~ 16.5% vs. 12.5 % (no Event loss)
  • The other targets do in fact show more differences in this setup:
    • The Counter Target with single event loss benefits most of the filter and drops from ~33.5 to 8.4 % overhead.
    • Only Ring Buffer and Pair Matching show no difference between single and no event loss.
    • SQL Trace with and without rowset provider (file) as target have almost the same overhead – no surprise if you know the architecture. This is in fact another huge difference which counts for Extended Events, where filters get honored much sooner within the Trace.
    • That the overhead of SQL Trace without target and at the same time all events being filtered out is actually higher than the overhead without target but no filtering (from the former test cycle) must be caused by the processing of for filtering out. I will not dive more into this though, since this setup is not documented and or supported.
      And in my eyes, “SQL trace is dead” anyways ;-) – okay, I am exaggerating a bit here, but I am also just being frank: For SQL Server 2012 there is almost no need to use it any more. – Running benchmarks using Distributed Replay is such a reason – isn’t that ironic?

I did not run SQL Profiler for this Test, forgive me. You probably know why by now. ;-)

 

Wait-Types für Extended Events

Ein weiterer Aspekt, der mich interessierte, waren die XEvent Wait-Typen, die auftreten würden, wenn man Extended Event Sessions ausführt. (Die Warte-Statistiken sind oft die Basis für Performance-Analysen)

Allgemein sieht man folgende:

Wait-Types for Extended Events

Another aspect I was interested in were the specific XEvent wait-types which would occur when running Extended Event sessions.
(Wait-Statistics are often the base for performance analysis)

In general, you will see the following:

 

Beim Starten einer Sitzung:

When starting a session:

PREEMPTIVE_XE_TARGETINIT
PREEMPTIVE_XE_CALLBACKEXECUTE
PREEMPTIVE_XE_SESSIONCOMMIT

Beim Beenden einer Sitzung:

When stopping a session:

PREEMPTIVE_XE_SESSIONCOMMIT
XE_BUFFERMGR_ALLPROCESSED_EVENT
PREEMPTIVE_XE_CALLBACKEXECUTE

 Während Sessions aktiv sind, sieht man:

While running sessions you will see:

XE_DISPATCHER_WAIT  - From BOL: Occurs when a background thread that is used for Extended Events sessions is waiting for event buffers to process. - You should be able to safely ignore this unless you believe a problem is occurring with processing of events for async targets. Since this works on a queue you can have bursts of high wait times especially when no XEvent sessions are active.

XE_TIMER_EVENT – From BOL: Used to indicate a background task is waiting for "expired" timers for internal Xevent engine work. - You should be able to safely ignore this one. Just used by the Xevent engine for internal processing of its work. If something was possibly wrong with Xevent processing you might see if this thread ever "wakes up"

Beim Starten eines File-Targets sieht man außerdem:

When starting the File target you will also see:

PREEMPTIVE_XE_TARGETINIT

Wenn man eine Sitzung mit der No Event Loss Option ausführt, sieht man gegebenenfalls:

If you run a session with the no event loss option you might see

XE_BUFFERMGR_FREEBUF_EVENT - which by BOL means: An Extended Events session is configured for no event loss, and all buffers in the session are currently full. This can indicate that the buffers for an Extended Events session are too small, or should be partitioned.

 

So. Ich hoffe das war interessant. Man kann noch weitere und andere Schlüsse aus den Ergebnissen ziehen. Immer im Hinterkopf zu behalten, ist, dass das ein sehr spezielles Szenario ist, wo keine CPU Reserven zur Verfügung stehen, so das der Trace/Beobachter-Overhead sich manifestieren muss – keine Chance, als eben (CPU) Ressourcen wegzunehmen.

So, I hope this was interesting for you. You may draw more and other conclusions out of these results. Remember though: this is a very specific scenario with no CPU reserves, so the Tracing/observer overhead has to show up – no choice but to take away (CPU) resources.

 

Happy Tracing

 

Andreas

PS: I just discovered that my MCM and SQLSkills-class buddy Steven Ormrod also has recently blogged about the performance overhead from a SQL Trace to remote file share on production here: stevenormrod.com/2013/06/remote-trace-slowing-down-server/

Survey: Which Tracing and Analysis-Tools do you use for SQL Server?

 Umfrage: Welche Tracing und Analyse-Tools verwendet ihr für SQL Server?

(DE)
In meiner Session „Hasta la vista Profiler – Tracing with Extended Events“, die ich nun seit über einem Jahr bei diversen Gelegenheiten gehalten habe, habe ich die Zuhörer meist gefragt, welche Monitoring-Tools sie bisher verwendeten.

Ich möchte diese Stelle einmal nutzen, um einmal breit zu erheben, welche Werkzeuge von SQL Server Professionals zur Protokollierung und Analyse von Performance-Problemen verwendet werden. Um ein differenzierteres Bild zu erhalten, ist auch eine Einordnung in „Administrator“, und „Entwickler“ sicherlich interessant.

Die Ergebnisse werde ich auf dem kommenden SQLSaturday #230 am 12. Juli in St. Augustin auf der PreCon, „From SQL Traces to Extended Events. The next big switch.“, die ich zusammen mit Mladen Prajdic gebe, präsentieren, und später mit Sicherheit noch auf dem PASS Camp sowie bei weiteren Gelegenheiten der deutschen und auch internationalen PASS - und natürlich hier in meinem Blog selber. Die Antworten können natürlich auch anonym abgegeben werden.

Im Folgenden liste ich eine Reihe von geläufigen Tools auf.

Bitte gebt Eure Stimme einfach als Kommentar wie im folgenden Beispiel ab - ich gebe ALLE Antworten (außer reinem Spam und Werbung) frei, auch Oracle-Tools ;-)

(EN-US)
In my session Session „Hasta la vista Profiler – Tracing with Extended Events“, that I have been presenting at several occasions, I used to ask the audience, which tools they used for monitoring so far.

I would like to use this platform to survey on a broader range, which tools are being used by SQL Server professionals for logging and analyzing performance-problems. In order to get a differentiated result, a classification in “Administrator” and “Developer” is certainly also interesting.

The results will be presented first at the upcoming SQLSaturday #230 on the 12th of Juli in St. Augustin/Germany at the PreCon, From SQL Traces to Extended Events. The next big switch.“, which I will be giving together with Mladen Prajdic gebe, and later on certainly also at the PASS Camp and other occasions of the German and international PASS - and of course also here in my blog itself. You can of course also keep your comments anonymously.

In the following I am listing a series of common tools.

Please simply provide your vote as a comment like in the following example – I will publish ALL answers (except plain spam/ads), even Oracle-Tools ;-)

„Administrator and/or Developer“
A 3
B 1
D 2
L 0

Dabei stehen die Zahlen für // The numbers stand for:

3: fast immer // almost all the time
2: manchmal // sometimes
1: selten // rarely
-1: das Tool ist mir unbekannt // I haven't hear of this tool

- Die Auswahl “unbekannt” auf Anregung eines Lesers (Danke!).

Werkzeuge, die nie verwendet werden gerne einfach weglassen.

Und das ist die Auswahl an Tools:

The choice “unknown” at a reader’s suggestion (Thank you!).

Tools that are not used can be simply left out.

And these are the choices of tools:

A) ClearTrace
B) Datenbankoptimierungsratgeber // Database Engine Tuning Advisor
C) Dynamic Management Views (DMV's)
D) Event Notifications
E) Extended Events unter 2008/R2
F) Extended Events unter 2012
G) Management Datawarehouse (MDW) / Data Collection Reports
H) PAL
I) PerfMon
J) RML Utilities / ReadTrace
K) SQL Diag
L) SQL Profiler
M) SQL Trace
N) Software von Drittherstellern – siehe auch O) // Third-Party Tools - also see O)
O) Andere // Other: …

- Die Reihenfolge ist alphabetisch und soll nichts implizieren :-)

Meine Liste enthält ausschließlich mit SQL Server gelieferte, sowie codeplex-Tools, es können aber auch andere angegeben werden. (Punkt „O“)

Mir ist natürlich völlig bewusst, das auch die SQL Server Version und ggf. Edition Einfluss auf die zur Verfügung stehende Auswahl hat, aber ich möchte die Umfrage auch nicht zu komplex gestalten. Das Ziel, einen Eindruck über die Verbreitung der Tracing-Gewohnheiten zu erreichen, wird so sicherlich erreicht werden können :-)

Vielen Dank an dieser Stelle schon einmal für die Beteiligung - ich bin sicher, dass es auch viele andere Community-Mitglieder gern sehen, was andere so für ihre Arbeit einsetzen.

- The order is alphabetical and not supposed to imply anything :-)

My list contains solely tools shipped with SQL Server and from codeplex, but feel free to add others (point “O”)

I am totally aware that also the SQL Server version and possibly edition have an influence on the choices available, but I also do not want to make the survey all too complex. The aim, to gain an impression on the prevalence and practices of tracing-habits will certainly be reached like that, too :-)

Thank you very much for participating – I am sure that many members of the SQL Server Community are also interested to see, what others use for their work.

Andreas

Extended Event File Target size vs SQL Server Trace trace file - a comparison

No big science, more out of curiosity this time..

The Extended Events File Target for SQL Server saves data using xml, which as is well known, is a bit “chatty”. A student in my recent SQL Server Master-Class workshop on extended events came up with the question for how much (more) space he would have to account for using Extended Events with a file target. Although this depends greatly on the specific events and possibly actions, selected, I was a bit curious myself and decided for a small test.

Both, the old and deprecated SQL Server Trace and Extended Events can save the data in a file, so it’s easy to compare what difference in size the new format will make.

I set up a SQL Server Trace that is almost identical to an Extended Events Trace. (You will see why “almost”.)

I had to choose a very simple Trace, so the customizable columns of extended events would not make the comparison unequal and ended up with a trace that captures SP:Starting/SP:Completed with the following columns:

You will see why I collect Source/DatabaseID twice later on.
Of course I used a lightweight Server-Trace, although for the purpose of this comparison it would not have mattered.

The SQL Trace definition:

exec sp_trace_setevent@TraceID, 43, 3, @on
exec
sp_trace_setevent@TraceID, 43, 5, @on
exec
sp_trace_setevent@TraceID, 43, 12, @on
exec
sp_trace_setevent@TraceID, 43, 13, @on
exec
sp_trace_setevent@TraceID, 43, 22, @on
exec
sp_trace_setevent@TraceID, 43, 28, @on
exec
sp_trace_setevent@TraceID, 43, 34, @on
exec
sp_trace_setevent@TraceID, 43, 48, @on
exec
sp_trace_setevent@TraceID, 43, 62, @on
exec
sp_trace_setevent@TraceID, 42, 3, @on
exec
sp_trace_setevent@TraceID, 42, 5, @on
exec
sp_trace_setevent@TraceID, 42, 12, @on
exec
sp_trace_setevent@TraceID, 42, 22, @on
exec
sp_trace_setevent@TraceID, 42, 28, @on
exec
sp_trace_setevent@TraceID, 42, 34, @on
exec
sp_trace_setevent@TraceID, 42, 62, @on 

declare @intfilter int
declare
@bigintfilter bigint

 set @intfilter = 7

exec sp_trace_setfilter@TraceID, 62, 0, 0, @intfilter

As you might see the trace includes a filter, which is for a specific database ID.

The Extended Event Trace session looks like that:

CREATE EVENT SESSION [ModulesStartEnd_ToFile]
ON
SERVER
ADD
EVENT sqlserver.module_start(
    WHERE ([source_database_id]=(7))),

ADD
EVENT sqlserver.module_end(
    WHERE ([source_database_id]=(7)))

ADD
TARGET package0.event_file
   
(SET filename=N'R:\Tracing\XE\ModulesStartEnd.xel', max_file_size=(10240))
WITH
(MAX_MEMORY=
4096 KB, EVENT_RETENTION_MODE=NO_EVENT_LOSS,  MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF)

You may know, that Extended Events include certain columns by default, and for module_start/end, this includes offset and offset_end.
 

Those two columns are not available for SP_Staring/SP:Completed in SQL Trace. Since they are both integers, I decided to include another column, DatabaseID, into the SQLTrace. SQL Trace also includes the SPID by default, which cannot be deselected, therefore those two columns should equalize it.
Both traces were started before the workload which ran for a while. At the end, the same number of events have been logged by both technologies in parallel.

SQL Trace event count:

XEvent Trace event count:



100644 + 100644 = 201288, so both captured the exact same events. :-)

So, and now to the final question: what size are the files?

See yourself:

Size in Megabytes:


 
(The numbers in MB are the real size, whereas windows explorer shows the size on disk.)
That’s a difference of 5.32MB or in other words 29.13%.

And this is what one single module_start-event for a function call in XEvents looks like:

<eventname="module_start"package="sqlserver"timestamp="2013-06-08T18:41:48.780Z">
<
dataname="source_database_id"><value>7</value></data>
<
dataname="object_id"><value>103671417</value></data>
<
dataname="line_number"><value>1</value></data>
<
dataname="offset"><value>0</value></data>
<
dataname="offset_end"><value>-1</value></data>
<
dataname="object_type"><value><![CDATA[TF]]></value></data>
<
dataname="object_name"><value><![CDATA[ufnGetContactInformation]]></value></data>
<dataname="statement"><value></value></data>
</
event>

The content is self-explanatory, as xml is supposed to be, and the overhead in size is no surprise at all.

Keep in mind that this post is purely on comparing file sizes, and not performance or features. There are good reasons that SQL Trace & Profiler have been deprecated, and Extended Events in SQL Server 2012 overcomes SQL Trace & Profiler by far, in performance as well as in flexibility/usability.
For a performance overhead comparision check out my recently published benchmark blog post: "Performance overhead of tracing with Extended Event targets vs SQL Trace under CPU Load".

So whenever performance matters, remember to set session options appropriately and if the amount of events is high, do not use your slowest volume for the file target - same as for all other tracing activities anyways.

happy tracing,

Andreas

Tracing Analysis Services (SSAS) with Extended Events – Yes it works and this is how

Tracing Analysis Services mit Extended Events – Ja, es geht, und zwar so

or: “Hasta la vista, Profiler”... ;-)

(en)
One of the features new in Analysis Services 2012 is the support of the SQL Server Extended Events Framework.

While there is no GUI support for that, yet, it is however possible to set up a XEvent session via DDL commands - just like it was in the “old days” with SQL Server 2008/ 2008 R2, until 2012 brought the GUI.

Since I have been asked a lot at my sessions on Extended Events on how it is done in Analysis Services, and the Books Online sample code is not really working (“Use SQL Server Extended Events (XEvents) to Monitor Analysis Services
http://msdn.microsoft.com/en-us/library/gg492139.aspx”), I will show a quick example here.

The following code creates a session to collect the deadlocks events from the Analysis Services Instance:

(de)
Eines der in Analysis Services 2012 neuen Features ist die Unterstützung des SQL Server Extended Events Frameworks.

Obwohl dafür noch keine grafische Unterstützung da ist, ist es jedoch möglich eine XEvent Session über DDL Kommandos aufzusetzen - genau wie in den alten Zeiten” mit SQL Server 2008/ 2008 R2, bis 2012 die GUI brachte.

Da ich im Zuge meiner Sessions zu Extended Events häufig gefragt wurde, wie das bei Analysis Services funktioniert, und das Books Online Beispiel nicht wirklich funktioniert (“Use SQL Server Extended Events (XEvents) to Monitor Analysis Services
http://msdn.microsoft.com/en-us/library/gg492139.aspx”), zeige ich hier ein kurzes Beispiel.

Der folgende Code erzeugt eine Session um Deadlock Events von einer Analysis Services Instanz mitzuschneiden:

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"
mlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
xmlns:ddl3="http://schemas.microsoft.com/analysisservices/2003/engine/3"
xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"
xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"
xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
    <
ObjectDefinition>
        <
Trace>
            <
ID>Sarpedon AS Trace Demo</ID>
            <
Name>Sarpedon AS Trace Demo</Name>
            <
ddl300_300:XEvent>                <event_session name="SQL_AS_XE" dispatchLatency="10" maxEventSize="4" maxMemory="4" memoryPartitionMode="none">
                    <
event package="AS" name="Deadlock" />
                    <
target package="Package0" name="event_file">
<
parameter name="filename" value="D:\SQLData\SarpedonASDeadlockTrace.xel" />
                    </
target>
                </
event_session>
            </
ddl300_300:XEvent>
        </
Trace>
    </
ObjectDefinition>
</
Create>

As one can see, the definition like session configuration and targets, is quite similar to SQL Server, since it is in fact based on the same architecture.
The package containing the events is "AS", whereas targets come from our well known "Package0". The one familiar with XEvents will know, what this implicates - more maybe in a later post.

Via the internal system view $system.discover_traces, we can see the active traces on the instance: the “FlightRecorder” which is still using the old-style Tracing technology (I wonder when Microsoft will add a new one just like system_health in SQL Server) and my sample session. You will also note, that the XEvent session’s trace file name is not visible here.

Wie man sehen kann, ist die Definition wie Session-Konfiguration und Targets recht ähnlich zu SQL Server, da es tatsächlich auf der selben Architektur basiert.
Das Package welches die Events enthält, ist "AS", wohingegen die Targets aus dem bereits bekannten "Package0" stammen. Wer bereits mit XEvents vertraut ist, wird wissen, was das impliziert - mehr vielleicht in einem späteren Post.

Über die interne Systemsicht $system.discover_traces können wir die aktiven Traces auf der Instanz sehen: der “FlightRecorder”, der noch die alte Tracing Technik verwendet (Ich frage mich, wann Microsoft eine Neue, wie die system_health in SQL Server hinzufügen wird), und meine Beispiel-Sitzung. Man sieht auch, das der Trace Dateiname der XEvent-Session hier nicht sichtbar ist.

 Trace Sessions

To access the collected data one can easily stop and delete the session by name as follows:

Um auf die gesammelten Daten zuzugreifen, kann man die Trace session wie folgt bequem über den Namen beenden und löschen:

 <Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"
xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    <
Object>
        <
TraceID>Sarpedon AS Trace Demo</TraceID>
    </
Object>
</
Delete>

The collected data can be viewed, aggregated and filtered as normal with the Extended Events Viewer in Management Studio.
Here a sample with query runtimes:

Die gesammelten Daten lassen sich dann wie gewohnt über den Extended Events Viewer in Management Studio ansehen, aggregieren und filtern.
Hier ein Beispiel mit Query-Laufzeiten:

XEvent Viewer

In the detail pane on the bottom you can notice, that I turned on causality tracking here. Hence the activity ID /GUID correlate activity.

Im Detailbereich kann man sehen, das ich hier auch “Kausalitätstracking” eingeschaltet habe. Daher die activity ID/GUI um Aktivitäten zu korrelieren.

So as you see, for a fact, the Analysis Services engine has been extended to be using the Extended Events architecture for better performing and more flexible Tracing.
I do expect some more GUI-support for Managing XEvents in Analysis Services in the next major release.


Have fun, playing around with the sample. :-)

From now on there is no excuse any more, to burden an Analysis Server that is already on its knees with Profiler...

Wie man sehen kann, sind die Analysis Services tatsächlich erweitert worden um die Extended Events Architektur für performanteres und flexibleres Tracing zu verwenden.
Mit dem nächsten Major-Release des SQL Server erwarte ich mehr GUI-Unterstützung  für die Verwaltung von XEvents in Analysis Services.

Viel Spaß beim Herumspielen mit dem Beispiel. :-)

Ab jetzt gibt es keine Entschuldigung mehr, einen Analysis Server, der bereits auf den Knien ist, weiter mit dem Profiler zu belasten...

                      “Hasta la vista, Profiler” ;-)

Hopefully by MCM buddy and friend Reeves Smith will soon write his promised post on Tracing Analysis Services, maybe with a Performance Comparison.
”So, now you have to get going, Reeves!” :-D

Hoffentlich wird mein MCM Kollege und Freund seinen versprochenen Post über XEvent Tracing Analsis Services bald einlösen – vielleicht mit einem Performance-Vergleich.
”So, nun musst Du aber loslegen, Reeves!” :-D

Meanwhile I’d like to refer you to this article from another fellow MCM, Jonathan Kehayas, where you can see the enormous difference in terms of negative performance-impact of tracing via Profiler and SQL Trace vs XEvents:

Bis dahin verweise ich gerne auf diesen Artikel eines andern MCM Kollegen, Jonathan Kehayas, wo man den gewaltigen Unterschied des negativen Performance-Einflusses von Tracing mittels profiler aund SQL Trace gegenüber Extended Events sieht:

 www.sqlperformance.com/2012/10/sql-trace/observer-overhead-trace-extended-events

Update: I conducted an excessive benchmarking on Extended Events and SQL Trace & Profiler myself now. The results ar now public and can be found here:

Update: Ich habe nun selber ein exzessives Benchmarking zu Extended Events und SQL Trace & Profiler durchgeführt. Die Ergebnisse sind hier nun auch öffentlich und können hier gefunden werden:

www.insidesql.org/blogs/andreaswolter/2013/08/performance-overhead-tracing-extended-events-sql-trace-under-cpu-load

 

Andreas

 

Hinweis: es wird noch im 1. HJ 2013 einen zweiten Termin für die Master-Class Seminare zu Extended Events geben (13. und 14. Juni). 

Die nächste Möglichkeit ist am 22.11. bzw. 25.11.2013!

http://www.sarpedonqualitylab.com/SQL_Master-Classes.htm

SQL Server Master-Class Seminare – Für Alle, die es genau wissen wollen – Start im Mai mit Extended Events

Mein Erster Blog Post hier ist teils in eigener Sache, auf der anderen Seite aber für Euch und alle, die Wiss- und Lernbegierig in Sachen SQL Server sind:

Ich werde häufig darauf angesprochen, ob ich nicht mal ein Buch schreiben möchte, um mein Wissen so weiterzugeben, oder Deep-Dive Seminare anbiete, wie es einige andere Kollegen in den USA tun.

Um ein Buch zu schreiben, habe ich leider nicht die Zeit. Ein komplettes Buch würde mich zu sehr von der Praxis abhalten - und Praxis ist das A und O - zumal es ständig neue Dinge zu lernen gibt.

In Seminaren und Vorträgen hingegen, hat man einen kurzen Zyklus, und sieht den Erfolg direkt. Abgesehen davon, das ich mein Wissen gerne weitergebe, macht die Interaktion einfach Spaß.

Ich habe mir überlegt, und mich auch unter Kollegen umgehört, was an anderen Angeboten, die ich selber oder andere kennengelernt haben, störte, und kam zu folgenden Kern-Punkten:

  1. Reine Präsentation mit zwar Deep-Dive Inhalten, aber kaum eine Chance, diese mit Übungen direkt vor Ort zu festigen (sehr typisch für die USA, ob PreCon-“Workshops” oder Seminare, das ist eher immer Vortragsstil und Demos, aber keine echten “Hands-On”) – und nach der Schulungswoche hat man oft keine Zeit mehr dafür.
  2. Viele Themenkomplexe innerhalb einer oder mehrer Wochen, die man gar nicht alle mehr benötigt, aber Bestandteil der 5 Tage sind. – Und diese 5 Tage sind dann auch komplett blockiert.
  3. Weite und damit teure Anreise.

Und nun ist es soweit: ab diesem Sommer biete ich eine eigene Deep-Dive Trainingsreihe, “SQL Server Master-Classes” an.

 SQL Server Master-Classes

Und was ist hier anders?:

  1. Es sind immer Übungen und auch Zeit für diese eingeplant, so dass man das gerade erlernte gleich ausprobieren kann. In den SQL Server Master-Classes, die “Workshop” in der Bezeichnung tragen, beträgt der Praxisanteil ca. 40%.
  2. Server Master-Classes sind jeweils 1 - max. 3-tägige Veranstaltungen, die jeweils einen Themenkomplex abdecken.
    Z.B. 1 Tag “Concurrency” mit allem, was zum Thema Transaktions-Isolation, Locking & Blocking gehört, oder 2 Tage “Indexe, Statistiken und Partitionierung”, denn das Thema ist ein einem Tag nicht zu schaffen. Beides lässt sich aber einzeln buchen, obwohl es insgesamt natürlich zum Thema “Performance” gehört.
  3. “Learn from a Master” - Zum einen sind dies die ersten regulär angebotenen offenen Seminare mit einem Certified Master (MCM + MCT) als Instruktor in und aus Deutschland
    Microsoft-Certified-Master
    - zum anderen kann ich für die in Frankfurt laufenden Seminare einen absoluten Knüllerpreis für Bahnfahrer anbieten: 99,- Euro für Deutschlandweite An- und Abreise. (Details dazu auf der webseite: www.sarpedonqualitylab.com/SQL_Master-Classes.htm)
  4. Nur um Sicherzugehen: der Vergleich mit MOC-Kursen wird gar nicht erst versucht. Hier geht es nicht nur um Best Practices, sondern es soll erlernt werden, was dahinter steckt, um den BESTEN Weg zu ermitteln, und nicht die “allgemeine Best Practice”.
  5. Natürlich wird sich auch ganztägig um das leibliche Wohl gesorgt. Die Seminare finden in einer hochwertigen Location statt, und ein Mittagsmenü sowie Pausensnacks (Kaffee, Tee, Kuchen uä.) und “Getränke-Flat” gehören dazu.

Und nun zu den Inhalten:

Folgende Themenkomplexe sind derzeit vorbereitet:

  • Workshop Tracing mit Extended Events in SQL Server (1 Tag)
  • Workshop Fortgeschrittene Techniken für Tracing mit mit Extended Events in SQL Server (1 Tag)
  • Concurrency - Transaktionen, Isolation Level und Sperren (1 Tag)
  • Indexe, Statistiken und Partitionierung (2 Tage)
  • Optimierung von Prozeduren und Funktionen (1 Tag)
  • Performance und Analysetechniken & -Tools + Workshop (2 Tage)
  • Baselining & Benchmarking (1 Tag)
  • Sicherheitsworkshops (Essentials, Vertiefung für Entwickler, Vertiefung für Administratoren, je 1 Tag)
  • Wiederherstellungsstrategien und Techniken (1 Tag)
  • Workshop Hochverfügbarkeit (2 Tage)
  • Beyond Relational mit SQL Server - Filestream, FileTable, FullTextSearch, Geospatial (1 Tag)
  • Workshop Replikation (1 Tag)

Und los geht es mit XEvents:

  • Workshop Tracing mit Extended Events in SQL Server (1 Tag)
    • Frankfurt am Main, 17.5.2013
  • Workshop Fortgeschrittene Techniken für Tracing mit mit Extended Events in SQL Server (1 Tag)
    • Frankfurt am Main, 18.5.2013

Bis zum 29.3. läuft noch der Super-Early-Bird, und bis zum 22.4. der Early-Bird!
Für den ersten Termin gibt es keine freien Plätze mehr. Daher haben wir einen 2. Termin zum 13. und 14.Juni gepant. Anmeldungen sind ab jetzt möglich. (dafür gibt es auch wieder einen Early-Bird-Tarif)
Mitglieder der PASS Deutschland e.V. erhalten einen Rabatt von 10%

Hier geht es zur aktuellen Liste und Anmeldung: www.sarpedonqualitylab.com/SQL_Master-Classes.htm

happy learning,

Andreas

------------------------------------------------------------------------

Andreas Wolter | Microsoft Certified Master SQL Server

MCT, MCITPDD, MCITPBID, MCITPDA, MCDBA, MCSA, MCTS
Sr Technical Consultant & Architect Datenbanken & BI

Sarpedon Quality Lab

1 2