Am 14. September 2017 haben wir eine überarbeitete Fassung unserer Datenschutzrichtlinie veröffentlicht. Wenn Sie video2brain.com weiterhin nutzen, erklären Sie sich mit diesem überarbeiteten Dokument einverstanden. Bitte lesen Sie es deshalb sorgfältig durch.

SQL Server: Performance-Optimierung

Wie schnell sind 100.000 SELECTs?

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Wie lange dauern 100.000 zufällige SELECTs und was beeinflusst das Ergebnis? Dieser Film schafft Klarheit.
13:22

Transkript

Vielleicht sogar wichtiger als das Einfügen der Daten, ist natürlich das Lesen der Daten. Auch dafür habe ich einen kleinen Test gebaut, der auf die gerade befüllten Tabellen aufsetzt, und zwar möchte ich aus den Tabellen 100.000 zufällige Zeilen jeweils auslesen, und entsprechend messe die Zeit die notwendig ist, das für die einzelnen Indizes durchzuführen. Allerdings ohne den Heap, warum? Weil der Heap dafür schlicht und ergreifend viel, viel zu langsam ist. Nicht, dass ich ein ungeduldiger Mensch wäre, aber ich habe beim ersten Test schon gesehen, das 10.000 Selects 1.504 Sekunden gebraucht haben, das sind 25 Minuten, fast eine halbe Stunde, und ich denke, es macht keinen Sinn wirklich diesen Test sinnvoll laufen zu lassen. Man könnte zwar hochrechnen, oder man könnte ihn in der Tat wirklich laufen lassen, aber letztendlich macht es, glaub ich, wirklich keinen Sinn den Heap da entsprechend laufen zu lassen, um zu sehen, dass er besonders langsam ist, weil das ist die Erwartung. Aber letztendlich muss beim Suchen einer Zeile der komplette Heap durchlaufen werden, und das ist natürlich relativ aufwendig, und dafür ist es sicherlich auch nicht gedacht. Insofern, den lasse ich aus den Tests raus um das Ganze nicht zu verzerren, sondern ich gehe einfach hin und werde alle anderen Indizes entsprechend verwenden und ich zeige Ihnen mal mit welchen Skripten ich den Test durchführen werde. Hier im SQL Server Management Studio habe ich schon das entsprechende Skript geöffnet. Gehen wir kurz mal durch. Ich benötige letztendlich ein paar Variablen, unter anderem auch maxcounter, da lege ich fest, wie viele Leseoperationen ich durchführen möchte, dann benötige ich eine Reihe von dummy Variablen, in denen ich die Inhalte der einzelnen Spalten einlesen kann. Sinn und Zweck ist es keinen Select zu machen, was die Daten zurück an den Client liefert, sondern quasi in diese Variablen hinein, so dass ich nicht das Problem habe, dass mir eine mögliche Netzwerkverbindung die Ergebnisse verfälscht. Dann frage ich ab, wie viel Zeilen in der entsprechenden Tabelle sind, via CHECKPOINT und DBCC DROPCLEANBUFFERS wird wieder das Transaktionsprotokoll geschrieben, der Buffer wird entsprechend, respektive der Cache geleert, dann merke ich mir den Startzeitpunkt, und in einer WHILE-Schleife gehe ich hin und lese einen zufälligen Wert, beziehungsweise erzeuge mir einen zufälligen Wert für die ID über die ich entsprechenden Wert dann entsprechend einschränken werde, so dass ich sagen kann, wenn ich 10.000 Zeilen habe, dann habe ich eine ID-Wertspanne von 1 bis 10.000, das ist da die Annahme, und letztendlich kann ich dann die Zeile mit dieser Anweisung dann hier in die Variablen laden und quasi an der Stelle nichts mit diesen Variablen machen, wie gesagt es sind nur dummys damit ich keine Rückgabe habe. Für den Fall, dass es einzelne IDs nicht geben sollte wäre das auch egal, weil das würde natürlich auch den Index letztendlich fordern, um rauszufinden ob es einen Wert gibt oder nicht, wie gesagt, hier habe ich von 1 bis 10.000.000 jeden Wert genau einmal, dann erhöhe ich den aktuellen counter, gebe jeden 100. Wert an der Stelle aus, es kann auch jeder 1000te sein, je nachdem wie ich das haben möchte, das ist also dieser Wert nach dem Modulo, das Prozentzeichen ist ein Modulo, und dann wird entsprechend ein PRINT ausgegeben, und wenn die Schleife einmal komplett durchlaufen ist, das heißt, ich entsprechend viele Durchläufe absolviert habe wie in maxcounter steht, dann wird die Dauer entsprechend ausgegeben, also erst mal berechnet, wie viele Millisekunden waren es, und das entsprechend ausgegeben. So, das führe ich jetzt entsprechend natürlich nicht aus, sondern ich werde das ausführen für NonclusteredIndex und Co, das heißt, dann sehen Sie mich in ein paar Sekunden wieder, beziehungsweise das Ergebnis in ein paar Sekunden, wenn ich alle Tests so weit durchgeführt habe, und dann besprechen wir mal, wie das entsprechend zu verstehen ist. So, und da sind sie auch schon die Endergebnisse. Klar zu sehen wer der Verlierer und wer die Gewinner sind. Erst einmal in der Mitte, Clustered Columnstore Index ist sowohl mit 8 Kernen als auch mit einem Kern, also 8 Kerne blau, 1 Kern MaxDrop1 gelb, entsprechend sicherlich die langsamste Variante, das ist auch ganz gut zu verstehen, weil letztendlich müssen natürlich die ganzen Informationen erst einmal entschlüsselt werden, dann kann ein Vergleich durchgeführt werden und Ähnliches, das heißt, das ist durchaus die langsamste Variante an der Stelle, wie gesagt, aber vor Augen halten, dass hier die Kompression schlicht die beste war, das heißt, für ein Archivsystem, das kann man schon mal vorneweg nehmen, ist glaube ich, Clustered Columnstore Index, nicht unbedingt die schlechteste Wahl an der Stelle. Schnell sind sowohl Non clustered als auch Clustered Index, das sind die beiden Varianten links, die tun sich eigentlich kaum was, also der Clustered Index ist ein bisschen schneller, da kann der Server ein bisschen optimieren noch, beim Non clustered Index hat er immer noch mit der relativ groben Struktur eines Heaps zu kämpfen, aber nichtsdestotrotz ist es so, dass beide Ergebnisse relativ nah beieinander liegen und man schon fast die Nachkommastellen bemühen muss, um da einen großen Unterschied zu sehen. Richtig, richtig schnell sind allerdings die InMemory Optimized Tabellen, und die liegen mit unter 1 Sekunde für 100.000 Leseversuche, das heißt, das ist eine ganze Menge und das ist auch die Stärke dieser Tabellen, das heißt, wenn ich auf die Daten zugreifen möchte und diese irgendwie berechnen oder verdichten, filtern, sortieren und Ähnliches, habe ich mit InMemory in vielen Fällen durchaus einen riesigen Performance Zuwachs. Wenn man das mal in Relation stellt, die schnellste andere Variante für ein 8 Kern-System ist das Clustered Index, ist der Clustered Index, und das ist, wenn ich das mal so sehe, ich weiß es nicht, das mit Sicherheit fast schon 15 - 16 fache ich habe es nicht genau ausgerechnet. Prozentual gesehen ist das natürlich ein riesen Wert an der Stelle. Man muss aber auch ein bisschen berücksichtigen, dass das eigentlich ein bisschen unfair ist an der Stelle, weil zum Beispiel Memory Optimized mit einer Durability für das Schema and Data bedeutet, dass die Daten natürlich in den Speicher gelesen werden, aber optimalerweise natürlich genau dann, wenn die Datenbank online genommen wird, das heißt, wenn ich die Datenbank starte, in dem Moment und meistens bedeutet das gleichzeitig den Start der Instanz, beziehungsweise Start der Instanz bedeutet den Start der Datenbank, dann werden die Daten eingelesen, das dauert natürlich eine Weile, und das ist hier das, was natürlich rausfällt, letztendlich erzeuge ich damit kaum bis sehr wenig I/O Traffic an der Stelle, weil ich schlicht und ergreifend natürlich nichts mehr laden muss. Und das erklärt auch warum natürlich die beiden InMemory Optimized Tabellen wirklich, ja, Kopf an Kopf die gleichen Ergebnisse liefern schlicht und ergreifend. Durchaus sehr beeindruckend was da möglich ist an der Stelle. Das heißt, wenn ich wirklich schnell auf Daten zugreifen möchte, und durchaus genug Speicher in der Maschine habe, das ist da so ein bisschen die Krux an der Sache, kann ich das durchaus mit dieser Art von Tabellen machen, der Nachteil ist nämlich, wenn ich nicht genügend Speicher habe, bedeutet das im Wesentlichen, dass die Datenbank nicht gestartet werden kann, und der Server schlicht und ergreifend auf Memory ist an der Stelle. Die Werte für die gelben Balken habe ich übrigens diesmal nicht gemessen, indem ich den Server umkonfiguriert habe, so, und ich habe der Abfrage per Option mitgeteilt dass ich möchte, dass es nur ein einziger Kern ist, das heißt, keine Parallelisierung stattfindet an der Stelle. Und das sieht so aus. Es ist genau das gleiche Skript, bis auf einen kleinen Unterschied, beziehungsweise pro Ausführungsblock natürlich ein kleiner Unterschied, und zwar hier, OPTION (MAXDOP 1) und hier kann ich jeden Wert angeben von dem ich möchte, dass die maximale Parallelisierung entsprechend genau mit dem konfigurierten Wert ihr Maximum erreicht an der Stelle. Also MAXDOP 1 bedeutet keine Parallelisierung und damit habe ich halt sichergestellt, dass die Ergebnisse relativ nah an den liegen, als wenn ich den Server auf einen Kern umstellen würde. So, die Frage ist, was das Ergebnis maßgeblich beeinflusst. Zum einen natürlich die Parallelisierung, damit natürlich die CPU, es ist ein Unterschied ob nur eine CPU, nur ein Thread an einer Aufgabe arbeitet, oder ob es entsprechend mehrere sind, keine Frage. Was das Ergebnis auf jeden Fall nicht beeinflusst, das ist normalerweise Standardantwort wenn man sagt Speicher, CPU oder CPU und Speicher, nein der Speicher ist es in dem Fall nicht, weil schlicht und ergreifend der Cache mit DBCC DROPCLEANBUFFERS sowieso frei gemacht, geleert wurde an der Stelle. Insofern, das kann es nicht sein, natürlich darf es nicht so wenig Speicher sein, dass der Server an sich, unter aller Sau, viel zu langsam läuft, sondern da muss natürlich eine gesunde Menge an Speicher vorhanden sein. Also, ich denke auf jeden Fall wird es die Parallelisierung sein. Man muss da noch anmerken, dass es natürlich schon ein Unterschied ist, ob ich den Server auf eine CPU reduziere, oder ob ich MAXDOP 1 benutze, weil im ersten Fall werden alle Aufgaben letztendlich die der SQL Server durchführen muss, von dieser einen CPU durchgeführt, die dem SQL Server ja eigentlich nur sagt, passe auf, benutze durchaus eine CPU deiner Wahl, aber parallelisiere nichts, das heißt, da ist immer nur ein Thread dabei, der entsprechend arbeitet, insofern, es macht schon einen Unterschied letztendlich, wenn Sie das in Ihrem eigenen Szenarium messen, dann reicht es denke ich, das eine oder andere Verfahren zu nehmen, nicht zu mischen, so wie ich es gemacht habe, aber ich wollte Ihnen auch zeigen, welche Möglichkeiten Sie da denn zur Zeit haben, und entscheiden Sie sich für ein Vorgehen, und halten Sie das letztendlich durch an der Stelle. Also, die Parallelisierung ist definitiv etwas was das Ergebnis beeinflusst. Dann die Breiten der Zeilen, das bedeutet, je mehr Speicher für eine Zeile benötigt wird, desto ungünstiger, weil das bedeutet, dass mehr Daten gelesen werden müssen. Fangen wir mal an, zum einen den Non clustered und Clustered Indizes, dort werden die Informationen zeilenweise in 8 Kilobyte Seiten auf dem I/O System gespeichert und letztendlich bedeutet das, es wird auch in 8 Kilobyte Einheiten gelesen, das wiederum bedeutet, je mehr Zeilen ich in 8 Kilobyte unterbringen kann, um so besser. Es spielt nicht eine Rolle, ob ich alle Spalten zurückliefere, weil letztendlich die Informationen werden so oder so gelesen, also der Unterschied ist dann für die I/O Systeme gleich null, 8 Kilobyte werden gelesen, egal wie viel ich von diesen 8 Kilobyte benötige an der Stelle. Für Clustered Columnstore Indizes sieht das ein bisschen anders aus, und zwar dreht sich die ganze Situation. Dort werden die Informationen spaltenweise abgelegt, das bedeutet, je mehr Zeilen, und zwar je mehr Zeilen gesehen auf eine Spalte, ich in 8 Kilobyte reinbringe, um so besser natürlich an der Stelle, und damit habe ich natürlich im Wesentlichen die gleiche Aussage, nur jetzt nicht mehr horizontal pro Zeile oder Zeilen, sondern vertikal pro entsprechende Spalte an der Stelle. Es spielt keine Rolle für die InMemory Optimized Tables, weil letztendlich müssen die Daten zwar in den RAM, das ist richtig, aber sie werden dann in den RAM gelegt, wenn die Datenbank gestartet wird, insofern dauert der Prozess halt schneller oder kürzer, letztendlich aber für den Zugriff spielt das keine Rolle, weil da sind die Daten auf jeden Fall im Speicher. Und natürlich wichtig, das Netzwerk. Theoretisch könnte ich natürlich auch meine Medien irgendwo im Netzwerk liegen haben, irgendwo im SAN liegen haben beispielsweise auch, und dann spielt das natürlich schon eine Rolle wie schnell diese Technologie ist an der Stelle. Letztendlich aber wird es auf jeden Fall zwischen dem Client und dem Server eine Art Netzwerk geben, zumindest in den meisten Fällen, und letztendlich bedeutet das dann wieder, dass ich natürlich, je mehr Daten ich anfordere, natürlich auch um so mehr das Netzwerk belaste, und da macht es schon einen Unterschied, ob ich einen einzelnen Wert nur zurückliefern möchte, oder ob ich eine ganze, ausgewachsene Tabelle mit allen Spalten, wo möglicherweise noch Plots oder Ähnliches drin sind, zurückliefere. Das haben wir bei den Tests jetzt hier an der Stelle ausgeklammert, das heißt, das beeinflusst unser spezielles Ergebnis jetzt eigentlich nicht, aber letztendlich, das Netzwerk ist sehr oft, oder wird sehr oft übersehen, ist aber extrem wichtig, wenn es darum geht wirklich zu beurteilen: Warum ist meine Anwendung so langsam? Letztendlich jeder Zugriff auf den Server kostet natürlich Geld und und in dem Sinne "Geld", in Anführungsstrichen, natürlich Netzwerk Performance in dem Sinne, das spielt natürlich eine Rolle. Hier in dem Beispiel speziell nicht, weil wir absichtlich nicht zurückgeben, das wollte ich an der Stelle ausgenommen haben, dafür gibt es diese dummy-Variablen. Und ich habe somit also auf jeden Fall aber einen dritten Posten, den man nicht aus den Augen lassen sollte, was denn das Ergebnis von solchen Statements letztendlich beeinflusst. Über was ich jetzt hier nicht groß rede ist natürlich, ich darf natürlich kein Prädikat, also kein WHERE haben, was schlicht und ergreifend zu keinem passenden Index in irgendeiner Form ausgeführt werden muss. Das macht natürlich keinen Sinn an der Stelle. Darum geht es aber nicht. Alle Statements, alle Select Statements sind so aufgebaut, dass sie mit ihrem Prädikat genau auf die Spalte geht, die auch mindestens indexiert ist, um möglichst gute Ergebnisse zu erzielen.

SQL Server: Performance-Optimierung

Lernen Sie den Umgang mit Indizis und Tools, um die Leistungsfähigkiet Ihrer SQL Server Datenbank effektiv zu erhöhen.

3 Std. 20 min (32 Videos)
Derzeit sind keine Feedbacks vorhanden...
 
Hersteller:
Software:
Exklusiv für Abo-Kunden
Erscheinungsdatum:04.05.2017

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!