Was tun Sie, um sicherzustellen, dass ein neuer Index nicht verlangsamt Abfragen?

Wenn wir einen neuen Index hinzufügen oder entfernen, um etwas zu beschleunigen, können wir am Ende etwas anderes verlangsamen. Um gegen solche Fälle zu schützen, mache ich nach dem Erstellen eines neuen Index folgende Schritte:

  1. starten Sie den Profiler,
  2. Führen Sie ein SQL-Skript, das viele Abfragen enthält, die ich nicht verlangsamen möchte
  3. Laden Sie den Trace aus einer file in eine Tabelle,
  4. analysiere CPU, liest und schreibt aus der Spur gegen die Ergebnisse aus den vorherigen Läufen, bevor ich einen Index hinzugefügt oder gelöscht habe.

Das ist eine Art von automatisierte und Art von tut was ich will. Allerdings bin ich mir nicht sicher, ob es einen besseren path gibt, es zu tun. Gibt es ein Werkzeug, das tut, was ich will?

Bearbeiten 1 Die Person, die zum Abschluss meiner Frage gestimmt hat, könntest du deine Gründe erklären?

Bearbeiten 2 Ich googled up, aber nicht finden, was das erklärt, wie das Hinzufügen eines Index kann verlangsamen auswählt. Allerdings ist dies eine bekannte Tatsache, also sollte es irgendwo etwas geben. Wenn nichts auftaucht, kann ich später noch ein paar Beispiele schreiben.

Bearbeiten 3 Ein solches Beispiel ist das: zwei Spalten sind sehr korreliert, wie Höhe und Gewicht. Wir haben einen Index auf der Höhe, der nicht selektiv genug für unsere Abfrage ist. Wir fügen einen Index auf Gewicht hinzu und führen eine Abfrage mit zwei Bedingungen aus: ein Bereich auf der Höhe und eine Reichweite auf Gewicht. weil der Optimierer sich der Korrelation nicht bewusst ist, unterschätzt er die Kardinalität unserer Abfrage grob.

Ein anderes Beispiel ist das Hinzufügen eines Index auf die zunehmende Spalte, wie zB OrderDate, kann eine Abfrage mit einer Bedingung wie OrderDate> SomeDateAfterCreatingTheIndex ernsthaft verlangsamen.

Letztlich was du verlangst, kann man umformulieren wie 'Wie kann ich sicherstellen, dass die Abfragen, die bereits einen optimalen, schnellen Plan verwenden, nicht in einen schlechteren Ausführungsplan optimiert werden?' .

Ob der Plan aufgrund von Parameter Sniffing, Statistik Update oder Metadaten Änderungen (wie das Hinzufügen eines neuen Index) die beste Antwort, die ich weiß, um den Plan stabil zu halten ist Plan Führer . Bereitstellung von Planleitfäden für kritische Abfragen, die bereits gute Ausführungspläne haben, ist wahrscheinlich der beste path, um den Optimierer zu zwingen, den guten, validierten Plan zu nutzen. Weitere Informationen finden Sie unter Anlegen eines festen Abfrageplans an einen Planleitfaden :

Sie können einen festen Abfrageplan an einen Planleitfaden vom Typ OBJECT oder SQL anwenden. Plan-Guides, die einen festen Abfrageplan anwenden, sind nützlich, wenn Sie über einen vorhandenen Ausführungsplan wissen, der besser als der vom Optimierer für eine bestimmte Abfrage ausgewählte ausgeführt wird.

Die üblichen Warnungen gelten für jeden möglichen Missbrauch eines Merkmals, das verhindert, dass der Optimierer einen Plan verwendet, der tatsächlich besser als der Planführer sein kann.

Wie wäre es mit dem folgenden Ansatz:

  • Sichern Sie die Ausführungspläne aller typischen Abfragen.
  • Nachdem Sie neue Indizes angewendet haben, überprüfen Sie, welche Ausführungspläne geändert wurden.
  • Testen Sie die performance der Abfragen mit geänderten Plänen.

Von der Seite "Query Performance Tuning"

Verbessere Indizes

Diese Seite hat viele hilfreiche Schritt-für-Schritt-Tipps, wie Sie Ihre Indizes für die beste performance zu stimmen, und was zu beobachten (Profiling).

Wie bei den meisten performancesoptimizationstechniken gibt es Kompromisse. Zum Beispiel, mit mehr Indizes, SELECT Abfragen werden möglicherweise schneller laufen. Allerdings werden die DML- (INSERT-, UPDATE- und DELETE-) Operationen erheblich verlangsamen, da bei jedem Vorgang weitere Indizes gepflegt werden müssen. Wenn also Ihre Abfragen meist SELECT-statementen sind, können weitere Indizes hilfreich sein. Wenn Ihre Anwendung viele DML-Operationen durchführt, sollten Sie mit der Anzahl der Indizes, die Sie erstellen, konservativ sein.

Andere Ressourcen:

Allerdings ist es wichtig zu bedenken, dass nicht gruppierte Indizes verlangsamen die dataänderung und Einfügung process, so Indizes sollten auf ein Minimum gehalten werden

Fragmentierte Indizes und Tabellen in SQL server können die Anwendungsleistung verlangsamen. Hier ist eine gespeicherte Prozedur, die fragmentierte Indizes in SQL servern und databaseen findet.

OK . Zuerst aus, Index verlangsamt zwei Dinge (mindestens)

-> insert / aktualisieren / löschen: Index rebuild

-> Abfrageplanung: "soll ich diesen Index verwenden oder nicht?"

Jemand erwähnte, dass der Abfrageplaner eine weniger effiziente Route einnehmen könnte – das soll nicht passieren.

Wenn dein Optimierer ist sogar halb anständig, und Ihre Statistiken / Parameter korrekt, gibt es keine Möglichkeit, es wird den falschen Plan zu wählen.

So oder so, in deinem Fall (mssql) kannst du dem Optimierer kaum vertrauen und musst immer noch jedes Mal überprüfen.

Was du gerade tust, sieht ganz gut aus, du solltest einfach dafür sorgen, dass die data, die du siehst, relevant sind, dh echte Use Case-Abfragen im richtigen Verhältnis (das kann eine Welt des Unterschiedes machen).

Um dies zu tun, empfehle ich immer, ein Benchmarking-Skript zu schreiben, das auf echten Gebrauch basiert – durch logging von Produktions-env. Abfragen, ein bisschen wie ich hier gesagt habe:

Komplette db Schema Transformation – wie man umgeschriebene Abfragen testing?