Ich habe einen Primärschlüssel in einer Tabelle (zB ContactID). SQL Server erstellt und verwaltet automatisch einen gruppierten INDEX für diese Spalte. Als ich den Tuning Advisor (gegen einen Performance-Trace) ausführte, schien es einen anderen INDEX in derselben Spalte zu empfehlen - einen NICHT CLUSTERED-Index in der contactID-Spalte. Wie wird das helfen - da es bereits einen Clustered Index für die Spalte gibt?
Wenn der Abfrageoptimierungsratgeber einen nicht gruppierten Index für den Primärschlüssel empfiehlt, empfiehlt er auch einen gruppierten Index für eine andere Spalte (oder andere Spalten).
Ein Primärschlüssel ist eine Einschränkung, kein Index. Die Annahme von MS SQL Server ist, dass der Primärschlüssel auch eine wichtige Art ist, wie Daten aus der Tabelle abgerufen werden (über 'wo ContactID = 2' oder eine Verknüpfung zwischen Tabellen auf ContactID). Diese Annahme bedeutet, dass ein Clustered-Index automatisch auch für die Spalten erstellt wird, aus denen der Primärschlüssel besteht. Es gibt andere Gründe für dieses Verhalten, aber lassen Sie uns das für jetzt einfach halten.
Wenn nun die meisten Abfragen für die Tabelle im Kontaktname (Feld ContactFirstName) in etwas wie 'Where ContactFirstName LIKE' Muh% 'stehen, wird SQL Server empfohlen, den Clustered-Index von ContactID auf ContactFirstName seit einer Tabelle zu ändern kann nur 1 Clustered-Index haben. Die Primärschlüsseleinschränkung wird weiterhin bestehen (und Dublettenzeilen verhindern), aber die Daten in der Tabelle werden physisch nach ContactFirstName sortiert.
Die Arbeitslast, die vom Optimierungsberater verbraucht wird, legt fest, was der Optimierungsberater empfiehlt. Der Optimierungsberater verwendet für die Ermittlung auch nur einen Prozentsatz der höchsten Ressourcenabfragen von der Workload, nicht die gesamte Workload.
Unter bestimmten Umständen, wie zB die Tabelle, die ContactID enthält, groß ist und / oder die Zeilen groß sind (dh viele große Varchare), kann das Scannen des Clustered Index sehr IO / speicherintensiv sein, verglichen mit ContactID als Clustered Index UND als nicht geclusterter Index.
Wenn eine Abfrage einen Scan der Tabelle nach ContactID erfordert und es nur einen Clustered-Index für ContactID gibt, wird die gesamte Datenzeile vom Datenträger gelesen. Wenn Sie jedoch einen nicht gruppierten Index für ContactID haben, wird nur die ContactID von der Festplatte gelesen.
Dies hängt damit zusammen, wie Clustered vs Non Clustered auf der Festplatte gespeichert werden. Clustered-Indizes werden von ContactID gespeichert, aber alle Zeilendaten werden auch mit ihr gespeichert. Nehmen wir an, jede Zeile ist groß genug, um eine Seite (8KB) aufzunehmen, und dann werden 100.000.000 Zeilen gescannt, so dass 800.000.000 kb Platte benötigt werden.
Ein Non Clustered-Index würde nur ContactID in seiner "Zeile" speichern. Angenommen, ContactID ist 8 Byte (bigint), dann können 1000 Zeilen des Non Clustered Index in eine Seite (8KB) passen. Das Scannen von 100.000.000 Zeilen nach ContactID erfordert nur (100.000.000 / 1000 * 8) = 800.000 KB Festplatte.
Wenn die analysierte Abfrage ziemlich häufig aufgerufen wird, sind 800.000 KB im Vergleich zu 800.000.000 KB signifikant.
Wie jedoch Evadman vorgeschlagen hat, betrachtet der Tuning Advisor nur eine bestimmte Arbeitslast. In den meisten Fällen ist ein zusätzlicher Nicht-Cluster-Index nur eine zusätzliche Arbeitslast für Einfügungen / Löschungen.
Beispiel für ein echtes Leben, ich arbeite mit einem Tisch, der viele Varchare enthält. Der Clustered Index beträgt 5521 MB. Es gibt mehrere Abfragen, die oft in der Sekunde aufgerufen werden, die zu Teil-Scans der Clustered-Index-Spalte führen (nennen wir sie P_ID). Ein Non Clustered-Index für P_ID ist 211 MB (26x kleiner als Clustered Idx). Dies führte zu einer erheblichen Verringerung der Abfrageausführungszeit sowie der Belastung von Festplatte und Arbeitsspeicher.
Bonus: Abfrage, um die Größe von gruppierten und nicht gruppierten Indizes zu ermitteln
%Vor%Tags und Links sql-server performance sql-server-2008 indexing