HOWTO: Deadlocks vermeiden

Vorwort

Ich hatte mal wieder das Vergnügen zu analysieren, warum sich auf einem MSSQL Server drei unterschiedliche Applikationen im Sekundentakt mit Deadlocks blockiert haben. Die Literatur dazu ist vielfältig, hat mir im Speziellen aber wenig geholfen. Daher fasse ich hier mal in einfachen Worten zusammen, wie ich die Deadlocks aufgelöst habe. Das Ganze hat keinen Anspruch auf Vollständigkeit oder korrektes „Wording“.

Voranalyse

Deadlocks können immer auftreten und stellen normalerweise kein Problem dar (fehlertolerante Anwendungen vorausgesetzt). Zu Problemen kommt es bei gehäuften Auftreten. Die Erkennung und Behandlung von Deadlocks kostet einfach Zeit. Im „schlimmsten“ Produktivfall dauerte es über 10 Sekunden bis ein Deadlock in der Anwendung aufschlug.

Wenn gleichzeitig mehrere Deadlocks auftreten, muss man diese nacheinander „auflösen“. Wenn man „Glück“ hat, lösen sich „Folgedeadlocks“ gleich mit auf. Um das „richtige“ Deadlock zu finden unterteile ich die Deadlocks in zwei Klassen:

  • Ablauf-Deadlock: der klassische Fall. Ein Begin Transation und Commit sind nötig. Diese gehe ich meistens erst im zweiten Schritt an.
  • Zugriffs-Deadlock: Diese Deadlocks werden dadurch verursacht wie dar SQL-Server auf die Daten zugreift. Immer wenn ein Entwickler sagt „Der gleiche Code produziert unter Oracle keine Deadlocks“, hat man so einen Kandidaten. Meistens kann man diese ohne größere Eingriffe in die Applikation auflösen.

Zuverlässiger erkennt man diese Zugriffs-Deadlocks jedoch an dem beteiligten QueryPlan. Dazu muss man wissen, dass der SQL-Server jedes Statement mit einer eigenen Transaction „sichert“ um konsistent auf die Datenbestände zuzugreifen. Als Konsequenz daraus folgt: man kann nicht auf Daten zugreifen (Delete, Update, Insert) ohne Locks zu erzeugen. Daraus folgt wiederum, Statements die viele IO-Ops haben (lesen, schreiben), erzeugen auch viele Locks, die Chance auf ein Deadlock steigen.

Konkret: Es wird folgendes Statement ohne explizite Transaktion von mehreren Prozessen konkurrierend ausgeführt.

In der Theorie (und bei Oracle) darf hier nie ein Deadlock auftreten. Im schlimmsten Fall aktualisieren alle Prozesse die gleichen Rows und der „letzte“ Prozess gibt „die neue Warheit“ vor. Unter MS-SQL hagelt es Deadlocks. Das Statement kann nicht in eine atomare Operation umgesetzt werden. Erst müssen die zu verändernden Rows gefunden und anschließend die Änderung durchgeführt werden. Um einen konsistenten Zugriff auf die Daten zu bekommen benutzt der SQL-Server Shared Locks. Wenn die Spalte „name“ nicht indiziert ist, wird es einen FullTableScan geben. Ein SharedLock „wandert“ durch die Tabelle (und in Abhängigkeit des verwendeten Isolationslevels bleiben die Locks bestehen), dort wo die Where-Clause matched gibt es ein UpdateLock und wenn es dumm kommt, blockieren sich dabei zwei Prozesse zu Tode.

Zusammenfassend formuliert: jede IO-Operation erzeugt ein Lock. Damit hat man gleich zwei Gründe diese zu vermeiden. Das bedeutet aber auch, dass die gleichen Maßnahmen zur Zugriffsoptimierung auch bei Deadlock-Problemen helfen können.

Statement tuning

Der erste Ansatz zielt auf die oben beschriebene IO-Ops/Lock-Problematik ab. Man lässt sich via Error Trace die Statements raus, die an den Deadlocks beteiligt sind und ermittelt sich die Kandidaten mit der höchsten IO-Last (entweder über die Cached Plans-Statistik oder über eine einfache Plananalyse). Diese Statements tuned man anschließend mit passenden Indizes.

Dummerweise macht einem der QueryOptimiser bei kleinen Tabellen einen Strich durch die Rechnung. Der Optimizer versucht (vereinfacht gesagt) die IO-Ops runter zu bekommen. Bei kleinen Tabellen kann es sinnvoller sein einen Full Table Scan zu machen, anstatt einen Index-Seek und Key-Lookup zu machen. Das hat mir schon ein paar mal den Tag versaut.

Ein Beispiel: Eine kleine Tabelle (nur eine Page) wird zur Kommunikation zwischen Prozessen benutzt. Ein Index ist so eingerichtet, das jeder Prozess direkt auf die ihm zugeordneten Rows zugreifen kann. Beim Auslesen der „zu verarbeitenden Werte“ müssen alle Spalten selektiert werden. Aus diesem Grund war der QueryOptimizer auf normalem Weg nicht dazu zu bringen, den Index für die Where-Bedingung zu benutzten. Ein Umstellen der Abfrage nach der Art „SELECT PrimaryKey FROM Table WHERE“ mit anschließendem „SELECT * FROM Table WHERE PrimaryKey =“, um die Nutzung des Index „sanft“ zu „erzwingen“, war nicht möglich. Es blieb nur die Möglichkeit der Applikationen eine View unter zu jubeln, die mittels WITH(INDEX ( ))-QueryHint die Nutzung des Index erzwingt. In Folge dieser Optimierung ging die IO-Last um 20% hoch und die Ausführungszeit des Statements stieg auch um wenige MilliSekunden. Der QueryOptimizer hatte also Recht, den Index nicht zu nutzen ist performanter. Mit Index-Nutzung treten jedoch keine Deadlocks mehr auf (vorher gab es mehre Deadlock alle 10 Sekunden), der Gesamtdurchsatz über die Tabelle stieg dadurch um den Faktor 10. Die höhere IO-Last war damit zu ignorieren.

Eine weitere Möglichkeit der Optimierung ist die Separierung der Indizes. Das ganze zielt in die gleich Richtung wie der Ansatz mit dem Index-Erzwingen. Man führt mehr Indizes als eigentlich nötig um zu verhindern das ein Lese-Lock auf einem Index gelegt wird, der für den Schreibzugriff benötigt wird bzw. umgekehrt. Eine Änderung im Clustered-Index/RawTable soll sich nicht auf eine LeseOperation durchschlagen. Das ganze funktioniert nur wenn die beiden konkurrierenden Statements unterschiedliche Where-Clauses verwenden oder man eine Indexnutzung erzwingt.

Zugriffe und Abläufe optimieren.

Auch wenn die Entwickler es gerne leugnen, verneinen, ignorieren und unzählige Frameworks einführen und nutzen um es zu kaschieren, am Ende entscheiden sie mit ihrer Art des Zugriffs, wie gut der Datenzugriff erfolgen kann. Wenn man also alle Scan – Operationen beseitigt hat, Indizes separiert und optimiert hat und immer noch Deadlocks auftreten, ist es an der Zeit mit dem Entwickler zu Reden und die Abläufe und Datenzugriffe in der Applikation zu optimieren.

Relativ einfach aufzulösen sind Konstrukte der folgenden Art: Lese N Einträge aus einer Tabelle, markiere sie, verarbeite sie und quittiere die Verarbeitung. Im schlimmsten Fall alles in einer großen Transaktion. Es kann performanter sein, nur einen Datensatz zu lesen und zu markieren (inklusive COMMIT) und anschließend in einer neuen Transaktion die Verarbeitung durchzuführen und zu Quittieren. Da man immer nur einen Datensatz sperrt, ist die Chance eine Deadlocks geringer und der Durchsatz kann steigen.

In die Kategorie „schwer zu detektieren aber einfach zu lösen“ fällt die Optimierung der Lock-Reihenfolge bzw. in welcher Reihenfolge wird welche Resource blockiert. Dies kann durch einfaches umsortieren der Statements erfolgen oder mit LockHints (UPDLOCK, XLOCK). Es kann sinnvoll sein möglichst früh ein Update- oder Exclusive-Lock zu holen um spätere Verklemmungen oder gar Deadlocks zu verhindern. Einmal gefunden lassen sich diese Änderung meist ohne großen Aufwand in der Applikation umsetzten.

Richtig kompliziert wird es erst bei Zugriffsoptimierung.
Eins vorweg: eine Zugriffs-Optimierung kann man auch ohne Zutun der Entwickler umsetzten. Mittels View oder „QueryPlan-Forcing“ kann man der Applikation immer die Zugriffs-Variante unterschieben, die man für „optimaler“ hält. Allerdings muss man diese Optimierung bei jedem Software-Update erneut vornehmen. Sobald der Entwickler sich dieser Optimierung bewusst ist (und sie auch versteht) laufen die Updates wesentlich entspannter.

Im Kern geht es auch in dieser Kategorie darum den Zugriff auf Daten zu separieren. Dabei unterteilt man Vertikale und Horizontale Partitionierung.

  • Vertikale Partitionierung: Eine große Tabellen (viele Rows) wird in mehre kleine Tabellen zerlegt. Ziel ist, das die Partitionen so angelegt sind, dass die Applikation nur selten mehrere Partitionen selektieren muss. Ein Beispiel für so etwas sind Auftragsdaten. In einem System gibt es meist 3 Arten von Auftragsdaten: Die Vorschau, die aktiven Aufträge und die archivierten Aufträge. Die Vorschau und die aktiven Aufträge halten sich mengenmäßig meist in Grenzen, die Archivdaten explodieren hingegen meistens. Die BuisinessLogic wird aber fast immer nur die aktiven Aufträge selektieren. Selbst der Benutzer wird sich hauptsächlich um die aktiven Aufträge kümmern und nur selten auf die Archivdaten zugreifen. Die Folge: Es existieren unnötige große Indizies, die Datenzugriffe erfolgen über viel zu große Datenmengen, die Latenz geht hoch, die Chance für Deadlocks steigen. Sinniger ist es die Archivdaten von den aktiven Daten zu trennen (zwei oder drei Tabellen) und die Datenbestände ganz bewusst in der BL zu überführen (delete aus der QuellTabelle – Insert in die FolgeTabelle) und nur im Sonderfall alle Daten zu Selektieren.
  • Horizontale Partitionierung: Gerade moderne Frameworks (z.B. das EntityFramework) verleiten dazu Daten aus unterschiedlichen logischen Kontexten physikalisch in einer Entität abzulegen (Schlagwort: Vererbung und Discriminator). Die Folge sind „sehr Breite“ Tabellen.
    Ein Beispiel: Eine (kleine, Hobby) Autovermietung führt in einer Tabelle die Basis-Fahrzeugdaten (Baujahr, Typ, Modell …), Lebensdaten (letzter Kilometerstand, letzte Reparatur, aktueller Benutzer …) als auch (weil es so Fancy ist) die aktuellen Telemetriedaten (GPS – Speed, Coords, …). Das sind eine Reihe von Zugriffen aus unterschiedlichen Kontexten mit vollkommen unterschiedlicher Zugriffs-Art und Frequentierung.

    • Die Basis-Daten werden einmal Geschrieben, defacto nie geändert und maximal gelöscht, dafür aber sehr häufig in großen Blöcken gelesen (Auflistung der Fahrzeuge)
    • Die Lebensdaten werden regelmäßig aktualisiert und gezielt (über Fahrzeugkennung – indiziert) ausgelesen.
    • Die Telemetriedaten werden permanent (scheiß auf Datenvolumen) geschrieben und nur im Sonderfall ausgelesen.

    Das kann man (bei großen Tabellen) kaum unter einen Hut bringen. Sinnvoller ist es die Daten in unterschiedliche Tabellen Auszulagern und über „1 zu 1“-Beziehung zu verknüpfen. So schlagen die häufigen Updates nicht auf die häufigen Reads durch (und umgekehrt).

Einen Nachteil hat die gezielte optimieren jedoch. Man muss aufpassen, dass man eine Seite nicht übervorteilt. Wenn man die Zugriffe einer Applikation stark optimiert hat (über indizies oder Zugriffsanweisungen), kann es passieren, das die konkurrierende Applikation „verhungert“. Also immer beide Seite Betrachten und optimieren.

TSQL Deadlocks tracen lassen

Wer mal wieder beim MSSQL Server Deadlocks nachspüren muss, hat mehrere Möglichkeiten. 0ft wird der SQL Profiler empfohlen. Ich persönlich finde das ErrorTrace des SQLServers sinnvoller. Dazu muss man das Deadlock-Tracing über das folgende Statement aktivieren:

Anschließend wird bei jedem Deadlock im ErrorLog der komplette Deadlock-Graph ausgegeben. Anders als im Profiler stehen hier aber zusätzliche Informationen zur Verfügung. Extrem nützlich dabei ist die Auflistung der beteiligte Indizes und Sperr-Objekte und die Statements die von dem Deadlock betroffen sind. Bei komplexen Statements oder StoredProcedure bekommt man so die „Line of Code“ die die Probleme verursacht.

Android: Multithreading mit AsyncTask und Deadlocks

Wenn man für Android Anwendungen entwickelt kommt man sehr schnell mit dem AsyncTask in Kontakt. Immer dann, wenn etwas länger dauern kann, soll man diese Klasse benutzten, so der Tenor in fast allen Tutorials, Blog-Einträgen und Offiziellen Developer-Guides. Was seltener zur Sprache kommt ist folgender Umstand: unterschiedliche AsyncTask-Instanzen sind nicht unabhängig voneinander.  Standardmäßig laufen alle AsnycTask auf einem „Executor“, einem ThreadPool. Jeh nach AndroidVersion ist dieser auf Serielle Verarbeitung eingestellt (ein WorkerThread) oder stellt fünf WorkerThreads bereit.

Als Entwickler kann man dies aber auch vorgeben oder gleich einen eigenen ThreadPool mitgeben. Das Problem bleibt aber das gleiche: man kommt wahnsinnig schnell in die Versuchung zwei AsyncTask logisch zu verschalten oder aus einem Task einen mehrere Task zu erzeugen und auf deren Ergebnisse zu werten (scatter – gather). Die Anzahl der WorkerThreads legt nun fest wie schnell das ganze in die Hose geht. Bei einem WorkerThread braucht es solange bis der erste „wartende“ AsyncTask anfängt zu warten, danach kommt kein andere AsyncTask mehr zum Zug. Bei zwei oder mehr WorkerThreads kann es immer gut gehen oder zu der Situation kommen, dass alle Threads auf Tasks warten die noch gar nicht ausgeführt werden. Ein klassisches logisches DeadLock. Die Anwendung „hängt“, Android bekommt es nicht mit, weil der UI Thread nicht blockiert ist und der User schimpft.

Das ganze kann man umgehen indem man nicht „unbegrenzt“ auf ein Ergebnis wartet (AsyncTask.get mit Zeitspanne aufrufen), die Abhängigkeiten anderes gestaltet oder mehrere unterschiedliche ThreadPools verwendet. Das Grundproblem ist aber, ein einmal erzeugter AsyncTask(.execute) wird genau einmal ausgeführt. Man kann ihn nicht vom Stack nehmen, nicht wieder einqueuen oder sonst wie an der Ausführungsreihenfolge drehen.

Glücklicherweise stellt Google die Klasse im Rahmen seiner OpenSource-Strategie die AsyncTask-Quelldatei zur Verfügung, so dass man sich relativ einfach seine eigene AsyncTask-Variante nach eigenem Bedarf zusammenbauen kann.

Ich hab das z.B gemacht um einen dedizierten Thread zu haben, bei dem immer wieder Task eingequeued werden können und sequenziell in einer definierten Reihenfolge (FIFO) abgearbeitet werden.

Sources: RepeatingTask

Grundsätzlich bleibt aber gültig: wer Multithreading betreibt, weiß was er da tut oder kommt in die Hölle.

OutOfMemory – Exception die Zweite

Während des Feintunings an der GalDroid-App bin ich auf einen interessanten Unterschied zwischen der GridView und der Gallery Komponente gestoßen.

Erstere versorgt die Adapter-Implementierung mit einer Referenz auf vorher genutzte Elemente. Die Gallery Komponente macht dies nicht. Das ist dann blöd, wenn man sich darauf verlässt, dass man über diese Referenz alte Bitmaps aufräumt oder gar Hintergrund-Threads abbrechen kann/muss.

Wer also einen Adapter an die Gallery bindet, muss sich selbst um die „alten“ Referenzen  kümmern. In einem ersten Schritt hab ich dafür gesorgt, dass zu einem Zeitpunkt nur ein Bitmap dekodiert wird (syncronized-Block).  Das gibt dem GC genug Zeit, Platz zu schaffen wen möglich. Daneben nutze ich eine LinkedList/Queue von WeakReferences auf die AsyncTask für das Laden und Dekodieren der Bilder. Überschreitet die Länge dieser Queue einen Schwellwert, werden die ersten Threads abgebrochen.

Dadurch wird keine Bilder mehr unnötig geladen und es spart gleichzeitig Ram.

Sourcecode:

Android – BitmapFactory und OutOfMemory-Exception

OutOfMemory-Exceptions sind immer eine unangenehme Sache. Trehten sie einmal auf, kann man nicht mehr reagieren. Das Programm wird einfach vom Stack geworfen und dem User eine unschöne Fehlermeldung vorgesetzt. Für den Entwickler ist die Fehlersuche langwierig, da das vermeintliche „Opfer“ selten der Verursacher ist. Der produzierte StackTrace ist schlicht unbrauchbar.

Die OOM-Exceptions trehten gehäuft im zusammenhang mit der BitmapFactory auf. Der Grund ist einfach, Bilder brauchen viel Platz…

Wenn man im Web nach Beispielen  für eine Bilder-Galerie sucht, bekommt man eigentlich immer das gleiche Beispiel. Ein paar kleine Bilder, lokale Resourcen, werden über eine Klasse die von BaseAdapter abgeleitet an ein über-gelagertes UI-Element gereicht. um dort zur Anzeige gebracht zu werden.

Bringt man jedoch Bilder auf einem Tablet zur Anzeige, werden schon die Bild-Dimensionen größer, nicht nur die Auflösungen. Auf einem 10 Zoll Tablet mit 1280×800 und mehr Bildpunkten braucht ein Bild in VollBild-Auflösung oder mehr schon mehrere MegaByte Ram, alleine zum Vorhalten der Bildinformationen. Dank der komprimierung brauchen Bilder als File wesentlich weniger Speicher, als die dann Tatsächlich im Ram belegen. Ein JPEG in 1920*1080 Aufgelöst und 500KB Dateigröße braucht mehr als 6MB Ram. Ein paar solcher Bilder im Ram abgelegt und die Dalvik-VM grüßt mit einer OutOfMemory-Exception.

Dazu kommt Fall einer Web Galerie die Bilder nicht lokal vorhanden sind, sondern bei Bedarf von einem WebServer geladen werden. Was einfach klingt, stellte sich schnell als vermeintliches Memory-Leak heraus. Im Grunde dreht sich alles um folgende Methode:

Mit dieser Methode werden die Views/darzustellenden Bilder zur Laufzeit abgefragt. Im einfachsten Fall sind das ImageViews die ein Bitmap auf den Bildschirm zeichnen.

Der augenscheinlichste Ansatzpunkt zum Speicher sparen ist, dass man eine vorher benutze View zur „Wiederverwendung“ übergeben bekommt (cachedView). Die CachedView kann man auch benutzen, der Resourcengewinn ist bei großen Bildern aber nicht „von Interesse“. In Anbetracht der xMB pro Bild, kommt es auf die paar Byte für die ImageView Struktur kaum an. Kommt jedoch ein AsyncTask zum ein Einsatz, um die Bilddaten „im Hintergrund“ herunterzuladen und in ein Bitmap zu decodieren, dann ist diese cachedView immens wichtig. Slided der User schneller durch die Galerie, als die Bilder aus dem Netz herunter geladen werden können, werden AsyncTask erzeugt, die „sinnlos“ Bilder im Hintergrund herunter laden und decodieren. Bei kleinen Bildern und einem Lokalen Cache, ist das nicht mal schlecht. Einmal gecached, werden die Bilder das nächste mal aus der lokalen Quelle geladen. Bei den großen Bildern frisst das parallele Decodieren schlicht zu viel Speicher.

Bei der GalDroid-Entwicklung hat es sich nicht als sinnvoll herausgestellt, die Anzahl der Threads zu kontrollieren. Der overhead, die Downloads zu queuen, stand in keinem Verhältnis zum Nutzen oder der Wartezeit des Users. Ich hab mir in der CachedView einfach den Task gemerkt, der das Bild herunterlädt und diesen bei Bedarf abgebrochen. So sind bei einer VollBild-Anzeigen selten mehr als vier Bilder parallel in Bearbeitung.

Eine Ausnahme gibt es noch: Ist das Layout des UI-Elements nicht statisch fixiert, werden die Dimension geändert, oder hängen die Dimensionen gar von den darzustellenden Inhalt ab, wird das 0-te Element mehrfach abgefragt um die benötigten Dimension zu bestimmen. Das ist in sofern übel, da es 5-7 mal hinter einander passierte, ohne das jeweils eine cachedView übergeben wird. Wieder: bei kleinen Bildern unschön, bei großen Bildern grüßt die OutOfMemory-Exception.

Das Problem kann man umgehen, in dem man mehr speichert. Bei der GalDroid wird einfach ein Feld von WeakReferences angelegt, in dem jedes je angefragte ImageView gespeichert wird. Die Logik ist simpel – wird ein Element mehrfach kurz hinter einander abgefragt, sollte es in diesem Speicher zu finden sein. Wird es länger Zeit nicht genutzt, räumt der GC auf und die WeakReference zeigt auf NULL.

Sollte nach diesen Maßnahmen immer noch OOM-Exceptions auftreten, kann man noch Bitmap.recycle aufrufen, sobald eine ImageView aus dem Sichtbereich des Users verschwindet. Das ist aber „frickelig“, da nicht immer klar ist, wann etwas „nicht mehr gesehen werden kann“. Das Recycle gibt sofort den Speicher des Bitmaps wieder frei, ohne den GC aufzurufen. Allerdings muss man so jedes mal das Bitmap neu laden, wenn es wieder in den Sichtbereich kommt.

Sollte das alles nicht helfen, kann man auf den Allocation Tracker zurückgreifen. Dieses Tool ist in dem ADT für Eclipse enthalten und listet jedes erstellte Objekt zur Laufzeit auf.

Android – App bekommt kein Zugriff aufs Internet

Da ich gerade meine ersten Gehversuche in der Android-Entwicklung mache, bin ich auch gleich in eine fiese Falle getappt.

Android hat ein „schickes“ Rechtemanagement. Jede App/Anwendung bekommt einen eigenen User und diesem wird über das Manifest das recht auf Ressourcen zugeteilt. Soweit so bekannt. Ich ging davon aus, dass wenn eine Ressource angesprochen wird, die nicht freigegeben ist, eine entsprechende SecurityException geworfen wird. Dem ist leider nicht so.

Die DalvikVM lässt die Anwendung einfach ins Leere laufen. In meinem Fall hatte ich vergessen anzumelden, dass meine App zugriff aufs Netz benötigt. Die Folge war eine IOException mit dem Hinweis „Unable to resolve host “<mein dnsname>” No address associated with hostname“. Es hat ne Weile gebraucht, biss ich die Ursache gefunden hatte. Es bedarf eines Eintrages in der Manifest.xml

Tomcat und Eclipse unter Ubuntu zum laufen bekommen.

Unter Ubuntu wird die Tomcat (wie unter Linux üblich) verteilt über mehrere Verzeichnisse installiert. Leider kommt damit das Eclipse-Plugin nicht so klar. Es bringt beim konfigurieren immer folgende Fehlermeldung.

Um diesen Fehler zu beheben einfach folgende Befehle ausführen:

WPF Performant viele Controls darstellen.

Wer unter WPF eigene Controls erzeugt wird sich früher oder später mit der Frage der Performance auseinander setzten müssen. Besonders wenn das eigene UserControl mehr als 100 mal auf die Oberfläche darstellt. Spätestens dann muss man sich gut überlegen, wo man in den WPF-ToolKit Baum einsteigt.

Zuerst muss man als alter DirectDraw oder WindowsForms Entwickler umdenken. Anders als früher zeichnet man nicht mehr „immidiate“. Früher wurde man vom Betriebssystem informiert, wenn sich was am Zeichenbereich geändert hat und man musste „mit der Hand am arm“ neuzeichnen. Bei WPF wird der Spieß umgedreht. Man übergibt dem WPF Subsystem eine Reihe von zu zeichnenden Objekten und wird dann nicht weiter belästigt. Das Subsystem kümmert sich um alles, was das Neuzeichnen, Überzeichnen und das Transformieren betrifft.

Die leichteste Komponente die man zeichnen kann ist eine irgendwie geartete Geometrie. Leicht im Sinne von Overhead, der vom WPF-Subsystem erzeugt wird. Will man „schnell“ viele ObjeKte Zeichnen ist es die einfachste Methode alles in einen DrawingContext/DrawingVisual zu zeichnen. Mittels ein oder mehrerer Geometries kann man die komplexesten Objekte zeichnen. Leider hat dieses Verfahren das Problem, dass man sich um alles selber kümmern muss. Es gitb keine „MouseEvents“ oder ähnliches für die Geometries. Desweiteren kann man ein DrawingVisual nur im ganzen neu zeichnen. Will man einzelne Bereiche aktualisieren muss das ganze Bild neu gezeichnet. Das kann bei vielen Objekten zu einem Problem werden.

Der bessere Weg ist da einen DrawingContainer zu nutzen. Dabei wird stellt jede darzustellende Objekt ein Geometrie dar, die in ihr eigenes DrawingVisual gezeichnet wird. Dieses wird dann in einen DrawingContainer eingebettet. Das erhöht den Overhead ein wenig, bietet dafür aber den Vorteil, dass einzelne Bereiche des Bildes neu gezeichnet werden können, ohne dass andere DrawingVisuals davon betroffen sind. Um HitTesting und „MouseEvents“ muss man sich erneut selber kümmern.

Kommt es auf Performance nicht an, kann man auch von einem UserControl oder ähnlichem ableiten und diesem ein DrawingVisual als Child verpassen. Hier hat man alle Vorzüge die das WPF so an EventHandling Bereit hält, leider geht damit auch ein erheblicher Performanceverlusst ein her.

SQL Execution Plan Caching

Bei einem TSQL-Server kann es von Zeit zu Zeit zu „seltsamen“ Performance-Einbrüchen kommen. Dieser Posting beschäftige sich mit einem der Gründe: SQL – Execution Plan Caching.

Bei modernen Datenbanken wird die Schicht der Datenabfrage von der Schicht der Datenbeschaffung getrennt. Hatte früher die Formulierung eines SQL-Befehls Einfluss auf die Art wie das DBMS die Daten beschafft, ist der gleicher Befehl heute eher als „Wunsch“ welche Daten zu selektieren sind. Wie diese schlussendlich beschafft werden, bleibt dem DBMS überlassen. Der Sprung von SQL-Statement auf tatsächliche I/O-Operationen wird gemeinhin als „Execution Plan“ bezeichnet.

Ein solcher ExecutionPlan wird für jede Abfrage erzeugt die an das DBMS gestellt wird. Bei der Erstellungen eines solchen Plans werden alle Informationen die dem DBMS zur Verfügung stehen (Statistiken, Datenbankstruktur, etc) für die Optimierung der Datenabfrage genutzt. Es werden Suchalgorithmen und Daten-Aggregations–Funktionen ausgewählt. Anschließend wird der ExecutionPlan ausgeführt und die Abfrage beantwortet. Um bei Wiederholung einer Abfrage Zeit zu sparen, wird ein einmal erzeugter ExecutionPlan gecached und später wiederverwendet.

Um zu sehen welche ExecutionPlans gerade im Cache vorgehalten werden, kann folgender Befehl genutzt werden.

Wie bei allen Cache und Statistik-Funktionen, gibt es eine Kehrseite der Medaille. Es kann passieren, dass all die schöne Optimierung ins Lehre läuft oder ein Plan verwendet wird, der für die aktuelle Abfrage völlig ungeeignet ist. Wie schon bei dem TOP-Problem dreht sich auch hier alles darum, dass das DBMS den „falschen“ ExecutionPlan benutzt.

„Falsch“ bedeutet in diesem Zusammenhang, dass I/O – Operatoren benutzt werden, die für die anfallenden Datenmengen nicht optimal sind. Dafür gibt es normalerweise nur drei Gründe:

  • Man weißt das DBMS dazu an: Es gibt mehrere Möglichkeiten das DBMS auf die falsche Fährte zu schicken. Entweder zwingt man es mittels QueryHints dazu oder gibt gleiche ganze ExecutionPlans vor.
  • Die Statistik ist falsch: der unwahrscheinlichste Fall, sei aber dennoch Aufgeführt. Ändert sich bei einer Tabelle mehr als 20%+500 Datensätze (Update, Delete, Insert) wird die Statistik der Tabelle neu erzeugt. Bei 10000 Datensätzen müssten sich also 2500 Datensätze ändern, bis es zu einem Update in der Statistik gibt. Im schlimmsten Fall muss sich nur ein Datensatz ändern um bei einem Folge-Join zu einer explodierenden Datenmenge zu sorgen. In-the-Wild ist mir ein solches „Szenario“ aber noch nie unter gekommen.
  • Parameter Sniffing/Spoofing + ExecutionPlan Caching: Für sich alleine genommen ist jede der beiden Technologien sinnvoll und hilfreich. In Kombination kann es jedoch zu erheblichen „Verklemmungen“ kommen. „Parameter Sniffing“ bezeichnet den Versuch des Query-Optimizers einen Execution Plan auf einen bestimmten Parameter-Tupel zu optimieren. Wird die Abfrage immer nur mit den gleichen Parametern ausgeführt oder nur einmal, beschleunigt das die Abfrage. Sorgt einer der Parameter z.B. dafür das eine große Tabelle auf wenige Datensätze reduziert wird, kann sich so die Lead-Tabelle ändern. Nachteilig wird das ganze wenn dieser Plan gecached wird. Bei der zweiten Ausführung der Abfrage wird der alte Execution Plan genutzt. Im schlimmsten Fall ist dieser falsch optimiert und führt zu unnötigen I/O – Operationen.

Gegen falsche Statistiken lässt sich recht einfach vorgehen. Man veranlasst einen Rebuild der entsprechenden Statistik oder erzeugt gleich alle Statistiken neu.

Mit einem Statistik-Rebuild werden auch alle gecachten Execution-Pläne verworfen die von der Statistik abhängen.

Gegen Parameter-Sniffing hilft das leider nur temporär. Abfragen die mehrere „optimale“ Execution Pläne haben, sollten gesondert behandelt werden. Es gibt prinzipiell fünf Möglichkeiten das Problem endgültig zu beheben.

  • der „sicherste“ weg: Wenn eine Abfrage in unterschiedlichen Kontexten genutzt wird, dupliziert man die Abfrage und ändert die Signatur ein wenig. Somit werden mehrere Abfragen gestellt, die getrennt optimiert werden.
  • Kann man das Problem in eine StoredProcedure auslagern, kann man dort ein „ConditionalTree“ aufbauen. Also die Übergabeparameter analysieren und entsprechende Sub-StoredProcedures aufrufen. Die wiederum auf den speziellen Fall optimiert werden.
  • Man kann auch das Parameter Sniffing unterdrücken. Dazu muss man aber ebenfalls auf Stored Procedures zurückgreifen.

    Die Parameterzuweisung kann vom Optimizer nicht vorhergesehen werden, deswegen wird der anschließende Select auf den allgemeinen Anwendungsfall optimiert.
  • Kann man den Anwendungsbereich eines SELECTs von vornherein einschränken oder eingrenzen, kann man QueryOptimizer anweisen auf diesen Bereich hin zu optimieren.

  • Es gibt fälle wo alle bisher genannten Lösungsvarianten nicht greifen, da die Abfragen nicht getrennt werden können und die einzelnen Abfrage-Ziele so unterschiedlich sind, dass eine allgemeine Optimierung keinen Vorteil bringt. In diesem Fall kann man das DBMS anweisen, den ExecutionPlan nicht zu cachen bzw keine gecachten Plane zu nutzen.

    In diesem Fall wird für diesen SELECT immer ein neuer ExecutionPlan erzeugt und nach der Ausführung sofort verworfen. So wird immer der „optimale“ ExecutionPlan genutzt. Dafür geht jetzt immer Zeit für das Erzeugen des Plans drauf.

Wie man mit TOP den SQL Server lahmlegt

Eine „häufig“ genutzte Funktionalität des TSQL Befehlssatzes ist das TOP Statement. Es schränkt das Ergebnis Resultset auf eine vorgegebene Anzahl an Zeilen ein. Sinnvoll ist das ganze wenn man mehre gleichwertige Treffer in einer Ergebnissmenge hat, und nur einen Teil davon braucht.

Beispiel: von 1000 Läufern werden nur die selektiert, die die Strecke in einer vorgegebene Zeit zurück gelegt haben, sortiert nach der benötigten zeit aufsteigend. Aufs Treppchen kommen jedoch nur drei Läufer.

Der TSQL – Select dazu:

Die landläufige „Halbwahrheit“ dazu ist, dass TOP X nicht in den Execution Plan eingreift. Folglich der Select normal ausgeführt wird und nur die Ergebnis-Menge auf die ersten X Reihen beschränkt wird. Das ist gefährliches Halbwissen! Hier sind zwei Bilder die „eindrucksvoll“ belegen, dass man sich mit einem TOP den ganzen Server lahmlegen kann.

Select ohne TOP
Select ohne TOP
Select Top 100
Select Top 100

Dem geübten SQL-Auge dürfte sofort (wenn man die Bilder in groß betrachtet) auffallen das ohne TOP Hash-Match-Joins genutzt werden, wohingegen bei dem TOP 100 Nested-Loop-Joins genutzt werden. Was man im Bild nur erahnen kann, ist der Umstand, das mit dieser Konstellation jeder MS-SQL Server in die Knie zu bekommen ist.

Nested-Loops skalieren ganz beschissen, nämlich linear. Was kein Problem ist, wenn die zu bearbeitende Treffermenge klein ist. Hier ist der Problem begraben. Dem TOP entnimmt der QueryOptimizer eine Information, eine Vorhersage wie viele Treffer zu erwarten sind. Der Select wird nicht ausgeführt und dann die Treffermenge beschnitten, sondern der Select wird solange ausgeführt bis die gewünschte Treffermenge erreicht ist oder der Select keine Ergebnisse mehr liefert.

Normalerweise ist dieses „Denken“ auch richtig. Wenn die Treffermenge normalerweise 10k Trefferr enthält und darauf ein TOP 1 angewendet wird ist es Schwachsinnig erst die 10K Treffer zu ermitteln und dann 9999 davon weg zu schmeißen.

Umgekehrt kann man sagen, das für einen Treffer selten ein kompletter Full-Table-Scann bei allen beteiligten Tabellen benötigt wird. So wird je nach „Where“-Bedingung aus der Lead-Tabelle der erste Datensatz ermittelt und so lange an die Folgeoperationen durch gereicht, bis er in der Treffermenge landet oder Aufgrund einer Bedingung ausscheidet. Das wird solange wiederholt bis de gewünschte Trefferzahl erreicht wurde. Im Beispiel benötigte es im „Gutfall“ für einen Treffer 70 „Reads“ aus der Lead-Tabelle (die Tabelle ganz rechts).

Was passiert aber, wenn Aufgrund einer Sortierung die komplette Lead-Tabelle gelesen werden muss? Das bekommt der Optimizer mit und wechselt sofort auf Hash-Match-Joins. Leider erkennt der Optimizer nicht, dass es auch andere Gründe geben kann, warum die Treffermenge rapide hochgehen kann. Der ungünstigste Fall ist folgender:
Die letzte Operation vor dem „TOP“-Operator führt einen Filter aus, der alle gelieferten Datensätze ausschließt. So müssen alle vorherigen Tabellen komplett gescannt werden und alle ermittelten Datensätze durch die Nested-Loops Operatoren bearbeitet werden. Bei Tabellen mit 10k und mehr Datensätzen sprengt das jeglichen verfügbaren Speicher. Der Server geht augenblicklich in die Knie. Wenn man Glück hat, ist die Instanz in CPU und Speicherverbrauch eingeschränkt, so dass die anderen Instanzen unbeeinflusst bleiben. Wenn nicht, dürfte man seinem Kunden gegenüber in Erklärungsnot geraten…

Die Gegenmaßnahmen sind recht einfach:

  • Ausschließen das es zu solchen Extrem-Situationen kommt, wo ein Datensatz erst den ganzen ExecutionPlan durchlaufen muss um dann ausgeschlossen zu werden.
  • Keep it Simple – Selects mit Top sollten ohne große Where-Bedingung auskommen.
  • Dem QueryOptimizer Hilfestellung über query-Hints geben.