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

Der Aufbau und das Mengengerüst für die folgenden Tests

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Dieser Film stellt die Datenbank für die in den nächsten Videos verwendeten Performance-Messungen vor. Dies schließt auch das Befüllen der Tabelle und das Messen der dafür benötigten Zeit ein.
13:45

Transkript

So, dann schauen wir uns doch mal an, was welcher Index bezüglich Performance denn kann. Und dafür habe ich einen kleinen Index Prüfstand aufgebaut. Vorneweg, es geht nicht darum absolute Zahlen zu ermitteln, das ändert sich einfach von Hardware zu Hardware, und auf vielen Servern ist ja nicht nur der SQL Server installiert, das heißt, da läuft auch noch vieles im Hintergrund. Das würde so ein reines Messen wirklich sehr, sehr schwierig machen. Was aber erkennbar ist durch die Zahlen, die ich Ihnen zeigen werde ist, welcher Index möglicherweise wo seine Stärken und seine Schwächen hat. Weil das lässt sich quasi doch relativ leicht rausdestillieren an der Stelle. Und wenn Sie das Ganze nachvollziehen auf Ihrer Hardware, dann dürfte von der Tendenz und von den Größenunterschieden, es durchaus ähnlich zu dem sein, was ich Ihnen gleich zeigen werde. Als Mengengerüst für den Test habe ich eine Datenbank aufgesetzt mit einer Tabelle deren Größe jetzt nicht ganz so klein, aber auch nicht unendlich groß ist, also die durchaus im guten Mittelfeld liegt, mit knapp 10 Mio. Zeilen. Die Datenbank selber ist mit dem Recovery Mode Simple eingestellt, das bedeutet das Transaktionsprotokoll wird nach jeder Transaktion wieder schön zurückgefahren, damit stelle ich sicher, dass mir das Transaktionsprotokoll während der Tests nicht unbedingt überläuft an der Stelle. Dann habe ich die Datenbank ausreichend dimensioniert, ungefähr 80%, ein bisschen mehr, sind noch frei und damit erspare ich mir das Problem, dass während des Einfügens möglicherweise die Datenbank vergrößert werden muss, was durchaus bemerkbar wäre, und ehrlich gesagt für den einzelnen Test natürlich auch ein bisschen unfair, weil die Tests vorher haben die Datenbank befüllt und er muss dann, also der Test, muss dann entsprechend genau diese Vergrößerung durchführen, das macht keinen Sinn, insofern von vorneherein mit einer vernünftigen Größe begonnen. Dann werde ich aus der Quelltabelle, also aus dem ProduktverzeichnisQuelle, entsprechend alle Daten in die einzelnen Tabellen importieren, und die einzelnen Tabellen haben dann Clustered Index, Non clustered Index sind Memory Optimized und Ähnliches, so dass sie dann in fortfolgenden Abfrage Skripten sehen, was quasi ungefähr wie viel Zeit benötigt, und um das Ganze so ein bisschen grafisch auch darzustellen habe ich entsprechende Diagramme dann vorbereitet. Als Vorbereitung habe ich in der entsprechenden Datenbank die einzelnen Tabellen mit den gewünschten Indizes versehen, ich mache es ein bisschen breiter, damit man es mal bisschen besser sehen kann, relativ lange Namen. Sie sehen also hier Produktverzeichnis- ClusteredColumnstoreIndex, ProduktverzeichnisClusteredIndex und so weiter, und so fort. Und mit dem Skript legen Sie die einzelnen Indizes in der Tat erst einmal an, genauer gesagt für diese drei. Für mich selber brauche ich keinen Vorbereitungsskript, also keinen Index in irgendeiner Form, keinen Schritt den ich ausführen muss am Anfang. Und die InMemory-Tabellen, die ich hier unten habe, die müssen auf jeden Fall einen Index haben, also den kann ich nicht erst sozusagen im Nachhinein anlegen, das würde nicht funktionieren. Das heißt, die Datenbank, so wie sie ist, kann ich mir jetzt mal die Größe anschauen, ich kann hier auf die Datenbank klicken, rechte Maustaste, "Reports", dann kann ich sagen und zum Beispiel Und dann sehe ich zum Beispiel erst einmal die Tabellen mit ihrer Größe, die sind momentan leer, insofern nicht besonders überraschend. Hier in meiner Quelltabelle befinden sich 10.000.000 Zeilen, und ich kann mir einen anderen Report anschauen, und da kann ich sagen Memory Usage By Memory Optimized Objects. Wenn ich hier runterscrolle sehe ich meine beiden InMemory Optimized Tables, und hier ist entsprechend auch nicht besonders viel Inhalt drin, weil auch da natürlich noch nichts eingefügt wurde. So, dann schließe ich die beiden Reports wieder. Und ich kann natürlich noch mal kontrollieren, ob ich auch wirklich diese drei Indizes richtig angelegt habe, wie gesagt die 3 Schritte habe ich jetzt nicht ausgeführt die sind nur für Sie da, damit Sie sehen, wie entsprechend die Indizes erzeugt wurden, und das Sie sehen, hier bei NONCLUSTERED und CLUSTERED INDEX muss ich die Spalten mitangeben, beim CLUSTERED COLUMNSTORE INDEX darf ich sie noch nicht mal angeben, also die Syntax variiert da ein bisschen, das heißt, ich kann diese Abfrage ausfüllen, und damit sehe ich zum Beispiel welche drei Indizes hier vorhanden sind, Das geht wieder über [sys] [indexes]. Ich kann, wenn ich kontrollieren möchte auch schauen ob es Primary Keys gibt, in der Tat gibt es die auch, aber für die beiden InMemory Tabellen. Die müssen noch einen Primary Key haben, das ist ebenfalls eine technische Voraussetzung. Und ich kann mir das Ganze auch mit einer etwas komplexeren Abfrage noch einmal anschauen. Und da sehe ich auch, welche primary keys vorhanden sind. Es gibt immer verschiedene Möglichkeiten die einzelnen Informationen abzurufen. Und ich kann natürlich jetzt hier noch mal die INDEXES verwenden. Kann hier einschränken über OBJECTPROPERTY, ob es eine 'UserTable' ist oder nicht, das fehlte mir, es dauert ein bisschen, also ich weiß nicht, ob die vom System ist oder ob das eine Tabelle von mir ist. Das kann ich über diese Einschränkung relativ leicht rausbekommen, und da sehe ich auch, wie die entsprechenden Tabellen heißen, und welcher Index entsprechend vorhanden ist. Und Sie sehen hier zum Beispiel interessanterweise InMemory hat für hier ein HEAP an der Stelle. Okay, damit ist so ein bisschen die Übersichtlichkeit hoffentlich gegeben, das heißt, Sie können abfragen welche Tabelle, welchen Index hat und momentan sehen Sie, dass das alles so definiert ist, wie es auch der Name der Tabelle verspricht, allerdings gibt es noch keine Daten innerhalb der Tabellen und das soll jetzt gleich kommen. Das nächste Skript ist für das Befüllen der einzelnen Tabellen zuständig. Ich nehme also immer die Daten aus der Quelltabelle, also dem ProduktverzeichnisQuelle, und kopiere sie in die einzelnen Tabellen mit den unterschiedlichen Indizes, messe dabei die Zeit und kann das später auf einem Diagramm zusammenfassen. Während ich jetzt hier rede markiere ich schon mal den ersten Block, führe den aus, und erzähle Ihnen kurz, was genau passiert, und zwar, CHECKPOINT sorgt dafür, dass entsprechend das Transaktionsprotokoll auf die Platte gesichert wird, mit DBCC DROPCLEANBUFFERS wird der Bufferpool, also der Cache geleert. Danach möchte ich, dass ich, wenn ich in die entsprechenden Tabellen Werte einfüge, dass ich auch die Identitäswerte beschreiben darf, also die AutoWerte, dann möchte ich im Wesentlichen die STATISTICS TIME ON haben, das heißt, ich möchte die ausgeführten Zeiten wirklich sehen, und Sie sehen, die Anweisung ist unten schon zu Ende. Dann gibt es ein INSERT Statement mit einem SELECT von der Quelltabelle in die Zieltabelle, und danach schalte ich entsprechend die Zeitstatistiken wieder aus. Und auch das IDENTITY_INSERT entsprechend wird wieder zurückgedreht, so dass ich die entsprechenden Identitätswerte nicht beschreiben kann mehr. So, das Ergebnis ist schon da und es ging relativ fix, das heißt, der SQL Server braucht genau dafür, um 10 Millionen Zeilen aus einem Heap in einen Heap zu kopieren, weil das ist das was technisch stattgefunden hat, knapp 21 Sekunden, bisschen mehr 21,5. Ich habe die Tests jetzt schon mehrfach gemacht, und es dreht sich ungefähr immer um diesen Wert. Sie sehen aber hier unter anderem auch, dass die CPU Zeit und die gesamt verstrichene Zeit, also elapsed time, nahezu, na ja, fast identisch sind und das bedeutet, dass genau diese Operation auf mehreren CPU Kernen kaum schneller ist, als wenn Sie nur einen Kern haben an der Stelle. Das zeige ich Ihnen auch noch, ich werde die ganzen Tests quasi im Hintergrund für Sie noch ein zweites Mal machen, und zwar dann nicht mit acht Kernen, so wie mein E7 das hier hergibt, sondern entsprechend genau nur mit einem Kern, um da zu sehen, welche Art der Indizes von möglichst starken oder von möglichst vielkernigen CPUs profitieren. Und das kann man machen, indem man zum Beispiel hingeht und sich hier die Eigenschaften des SQL Servers greift, auf Processors geht und diesen Haken hier oben wegsetzt und dafür dann hingeht, und einen der anderen Kerne hier auswählt an der Stelle. Das sollte nicht für eine dauerhafte Einstellung sein, ich beschneide dem SQL Server die Hardware damit künstlich, aber damit kann ich sicherstellen, dass er auch nur einen Kern benutzt. Ich habe theoretisch auch die Möglichkeit, das per Statement zu machen, Letztendlich möchte ich aber wirklich, dass der gesamte SQL Server wirklich dann nur auf einem Kern läuft und nicht nur die Teile die für die Ausführung genau dieser einzelnen Anweisungen sind. So, ich breche das mal ab. Und, Sie sehen hier, wie gesagt das Ergebnis, und das Gleiche passiert dann für eine andere Tabelle, zum Beispiel "NonclusteredIndex", danach entsprechend in den "clusteredIndex" und so weiter, und so fort und die einzelnen Zeiten werden gemessen, dann werde ich die Zeiten, wie gesagt, noch mal mit genau einem CPU Kern messen, und dann kann man, denke ich, schon relativ genau sehen, welcher Index beim Einfügen der Daten die Nase möglicherweise vorne hat, und welcher Index da ein bisschen langsamer ist. So, ich führe jetzt die Tests aus, und wenn die abgeschlossen sind, sehen wir uns mit der Grafik in wenigen Sekunden wieder. So, und da bin ich auch schon wieder. Ich habe den Test jetzt mehrfach laufen lassen, einmal mit einem, einmal mit mehreren Kernen. Der blaue Balken ist für 8 Kerne, wie es, wie gesagt, der E7 hergibt und "MaxDrop1", das ist der gelbe Balken, steht für die Verwendung eines einzelnen Kerns an der Stelle. So, und Sie sehen schon, der Unterschied ist teilweise durchaus schon mal sichtbar. Ich sehe zum Beispiel, das ein "Clustered Index" durchaus von mehr Kernen profitiert, während beispielsweise ein "Heap", wenn man es mal genau misst, sogar mit einem Kern schneller ist als mit 8 Kernen, der Unterschied ist allerdings relativ gering. Wie gesagt, bedenken Sie, dass da 10 Mio. Zeilen eingefügt werden, insofern das Ganze natürlich auch so eine gewisse extreme Größe hat an der Stelle. Das würden Sie höchstwahrscheinlich nur dann machen, wenn Sie ein Daten wirklich kopieren wollen oder mal schauen, wie schnell denn welcher Index ist. So, als Quintessenz sehe ich erst einmal, dass der "Heap" durchaus das Schnellste ist, danach kommt der Clustered Index, Non clustered Index, die geben sich nicht wirklich etwas. Ich denke, wenn man es auf anderer Hardware laufen lässt, kann das durchaus sein, dass es da ein bisschen unterschiedlich sogar noch ist. Und dann, relativ langsam ist der Clustered Columnstore Index mit 46 und 49 Sekunden, der ist sozusagen der Langsamste an der Stelle. Und die InMemory Varianten liegen, na ja, knapp zwischen den herkömmlichen Indizes und dem Clustered Columnstore Index, da tut sich aber letztendlich nicht viel. Vielleicht hier beim Memory Optimized, wo Daten und Schema persistiert werden, 32 und 39 Sekunden, da sehen Sie halt, da tut es ein bisschen was mehr Kerne zu haben. Dann interessant ist natürlich der Speicherbedarf auf den I/O Systemen. Und Sie sehen die Indizes, sei es jetzt ein Heap, Non clustered Index oder Clustered Index, die tun sich kaum was, die liegen sehr dicht beieinander. Ja, auch die Memory Optimized Table ist da noch relativ nah dran mit 2315 MB für die Datenmenge. Nur der Clustered Columnstore Index schafft es hier schon bei relativ moderaten Daten und vor allen Dingen bei Daten, die zufällig zusammengewürfelt sind, also die so ein bisschen rosa Rauschen darstellen, dennoch eine relativ gute Komprimierung hinzulegen, das heißt, ungefähr 50%, ein bisschen weniger. Das heißt, wenn ich viele Daten habe und wenig Plattenplatz dafür ausgeben möchte, ist das eine ganz gute Geschichte. Lassen Sie sich jetzt nicht davon täuschen, dass Memory Optimized nur für das Schema, entsprechend natürlich mit 0 MB auf den I/O Systemen zuschlägt, da wird nichts persistiert, das wäre seltsam wenn es irgendwie anders wäre, insofern, na ja, den besten Kompressionsfaktor hat der Clustered Columnstore Index und die anderen Indizes, muss man sagen, tun sich kaum was. Wobei man da zwischen den InMemory Optimized eigentlich unterscheiden muss als Gruppe, und den herkömmlich Disk Space Indizes und die letzten oder die ersten drei von links aus gesehen, Heap, Non clustered Index und Clustered Index, sind letztendlich relativ nah beieinander. So, wie sieht es im Speicherbedarf aus? Und die Frage kann ich eigentlich nur stellen, für die Memory Optimized Tables, das heißt diejenigen Tabellen, die wirklich im Speicher gehalten werden, weil die anderen Tabellen werden quasi in 8 Kilobyte Sektionen, sogenannten "pages file", bei Bedarf von der Platte in den RAM geladen und entsprechend auch dort wieder entfernt, wenn sie eine gewisse Zeitlang nicht benutzt werden, und wenn entsprechend der Server diesen Speicher irgendwie meint, anders besser ausgeben zu können. Also da ist es eher so ein durchgehender Posten, also ich kann schlecht dann fragen, wie viel Speicher benötigt die Tabelle? Ich könnte höchstens fragen, wie viel Speicher belegt die Tabelle genau in diesem Moment, aber nicht wie viel die komplette Größe der Tabelle wirklich im Speicher benötigt. Das kann ich nur bei InMemory Optimized sinnvoll stellen diese Frage, und Sie sehen, beide Varianten sind natürlich gleich auf, im Speicher ergibt sich da ein homogenes Bild, da gibt es keinen Unterschied, was die Größe betrifft, das sind entsprechend 2315 auf beiden Seiten.

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!