nicht gruppierter index für die primary key-Spalte?

Habe ich einen Primärschlüssel in einer Tabelle (sagen ContactID). SQL Server automatisch erstellt und verwaltet einen gruppierten INDEX auf diese Spalte. Bei mir lief der Tuning Advisor (gegen ein performance-trace), schien es zu empfehlen einen anderen INDEX in der gleichen Spalte - ein NICHT GRUPPIERTER index auf die contactID Spalte. Wie wird diese Hilfe - da gibt es bereits ein gruppierter index auf der Spalte?

  • Sind alle anderen Spalten enthalten die in den neuen index? War der neue index sortiert die gleiche Weise wie die anderen?
  • Kannst du das script für den der empfohlene index?
InformationsquelleAutor user2736158 | 2013-11-18
  1. 3

    Wenn die Abfrage tuning-Berater empfehlen, die einen nicht gruppierten index für den Primärschlüssel, es wird auch empfehlen, einen gruppierten index für eine andere Spalte (oder Spalten).

    Primärschlüssel ist eine Einschränkung, kein index. Die Annahme von MS SQL Server ist, dass der primary key ist auch eine wichtige Art, wie die Daten aus der Tabelle abgerufen (via 'wo ContactID = 2' oder auf einen join zwischen Tabellen auf ContactID). Diese Annahme bedeutet, dass ein gruppierter index wird automatisch angelegt, auf die Spalten, die den Primärschlüssel bilden. Es gibt andere Gründe für dieses Verhalten als gut, aber lassen wir es einfach halten für jetzt.

    Nun, wenn die meisten Abfragen in der Tabelle auf Kontakt Vorname (ContactFirstName Bereich) so etwas wie 'Wo ContactFirstName WIE" Muh%', dann SQL server wird empfohlen, ändern Sie den gruppierten index von ContactID zu ContactFirstName da eine Tabelle kann nur 1 gruppierten index. Die Primary Key-Einschränkung immer noch vorhanden sind (und verhindern, dass dupe Zeilen), aber die Daten in der Tabelle werden physisch bestellt ContactFirstName.

    Die Arbeitslast, verbraucht die tuning-Berater wird diktieren, was die tuning-Berater empfiehlt. Die tuning-Berater auch nur mit einem Prozent die höchste Ressource Abfragen in der Arbeitslast, nicht die gesamte Arbeitslast in die Bestimmung.

    • Das macht Sinn. Dank
    InformationsquelleAutor Evadman
  2. 9

    In sehr speziellen Umständen, wie die Tabelle mit ContactID ist groß, und/oder die Zeilen sind groß (z.B. viele große varchars), scan des Gruppierten Indexes kann sehr IO/memory-intensive, verglichen mit ContactID als Clustered Index UND Non Clustered Index.

    Wenn eine Abfrage erfordert einen scan von der Tabelle, ContactID, und es gibt nur einen Gruppierten Index auf ContactID, dann die gesamte Zeile von Daten, die vom Datenträger gelesen wird. Allerdings, wenn Sie haben auch einen Nicht Gruppierten index auf ContactID, dann nur die ContactID vom Datenträger gelesen wird.

    Dies hat zu tun mit der Art und Weise Clustered vs. Non-Clustered auf der Festplatte gespeichert sind. Clustered-Indizes gespeichert sind, ContactID, aber alles der Reihe werden auch Daten gespeichert, die mit es. Sagen, jede Zeile ist groß genug, um eine Seite nach oben (8KB), dann Scannen 100,000,000 Zeilen erfordert 800,000,000 kb disk-io.

    Einem Nicht Gruppierten index würde nur speichern ContactID in seiner "Zeile". Vorausgesetzt, ContactID ist 8 Byte (bigint), dann 1000 Zeilen des Non Clustered index können passen in eine Seite (8 KB). Nun, scan-100,000,000 Zeilen, die mit "ContactID", nur erfordert (100,000,000 /1000 * 8) = die 800.000 KB disk-io.

    Wenn die Abfrage wird analysiert, namens ziemlich Häufig, die 800.000 KB im Vergleich zu 800,000,000 KB ist signifikant.

    Jedoch, wie Evadman vorgeschlagen, die Tuning Advisor ist nur auf der Suche auf eine bestimmte workload. In den meisten Fällen eine zusätzliche Non Clustered index werden nur zusätzliche Arbeit, für Einfügungen/Löschungen.

    Real life Beispiel, ich arbeite mit einer Tabelle, die viele varchars. Der Gruppierte Index ist 5521 MBs. Es gibt verschiedene Abfragen, die aufgerufen werden, viele Male pro Sekunde, das am Ende dabei partial-scan des Gruppierten Index Spalte (nennen wir es P_ID). Ein Nicht Gruppierter index auf P_ID ist 211 MB (26x kleiner als Gruppierte Idx). Dies führte zu einem deutlichen Rückgang bei der Ausführung der Abfrage benötigte Zeit sowie Last auf dem Datenträger und Speicher.

    Bonus: die Abfrage, um herauszufinden, die Größe von Gruppierten und Nicht Gruppierten Indizes

    DECLARE @TableName VARCHAR(200)
SET @TableName = 'NAME_OF_YOUR_TABLE'

SELECT
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    8 * SUM(a.used_pages) AS 'Indexsize(KB)',
    (8 * SUM(a.used_pages)) / 1024 AS 'Indexsize(MB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p 
    ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a 
    ON a.container_id = p.partition_id
WHERE OBJECT_NAME(i.object_id) = @TableName
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id
    • Danke.Das war auch hilfreich.
    • Gutes Beispiel. Die nicht-gruppierten index, der die ContactID wäre als "covering index"; der index hat alle erforderlichen Daten, die der Anfrage genügen. ContactID klingt wie ein ansteigender Zahl um eine Zeile identifiziert, so ist es wahrscheinlich nicht sinnvoll, für eine Anwendung von selbst. Aber wenn in einer anderen Tabelle einen Fremdschlüssel von "ContactID", das bedeutet, dass jedes insert/update in die andere Tabelle werden auch brauchen, um zu überprüfen, dass die ContactID vorhanden ist. Da nur ContactID wäre erforderlich, dass die Abfrage einen abdeckenden index auf ContactID würde befriedigen. So, überspringen Sie den scan von einem großen gruppierten index.
    InformationsquelleAutor Sully
Schreibe einen Kommentar