Schätzen der Größe eines nicht gruppierten Index

Artikel
06/25/2013

Befolgen Sie diese Schritte, um abzuschätzen, wie viel Speicherplatz zum Speichern eines nicht gruppierten Index erforderlich ist.

Berechnen Sie die Variablen zur Verwendung in Schritt 2 und 3.
Berechnen Sie den Speicherplatz, der zum Speichern der Indexinformationen in der Blattebene des nicht gruppierten Index verwendet wird.
Berechnen Sie den Speicherplatz, der zum Speichern der Indexinformationen in den inneren Knotenebenen des nicht gruppierten Index verwendet wird.
Addieren Sie die berechneten Werte.

Schritt 1.Berechnen der zu verwendenden Variablen in Schritt 2 und 3

Mit den folgenden Schritten können Sie die Variablen berechnen, mit denen die Größe des Speicherplatzes geschätzt wird, die zum Speichern der oberen Ebenen des Indexes erforderlich ist.

Geben Sie die Anzahl der Zeilen an, die die Tabelle enthalten wird:

Num_Rows = Anzahl der Zeilen in der Tabelle
Geben Sie die Anzahl der Spalten mit fester und mit variabler Länge im Indexschlüssel an, und berechnen Sie den Speicherplatz, der für deren Speicherung erforderlich ist:

Die Schlüsselspalten eines Indexes können Spalten fester und variabler Länge enthalten. Um die Größe der Indexzeilen auf der inneren Ebene zu schätzen, müssen Sie den Speicherplatz berechnen, der von jeder dieser Spaltengruppen innerhalb der Indexzeile belegt wird. Die Größe einer Spalte hängt von der Angabe für Datentyp und -länge ab.

Num_Key_Cols = Gesamtanzahl der Schlüsselspalten (fester und variabler Länge)

Fixed_Key_Size = Gesamtzahl der Bytes in allen Schlüsselspalten fester Länge

Num_Variable_Key_Cols = Anzahl der Schlüsselspalten variabler Länge

Max_Var_Key_Size = Maximale Bytegröße aller Schlüsselspalten variabler Länge
Berücksichtigen Sie auch den Datenzeilenlokator, der erforderlich ist, wenn der Index nicht eindeutig ist:

Wenn der nicht gruppierte Index nicht eindeutig ist, wird der Datenzeilenlokator mit dem Schlüssel des nicht gruppierten Indexes kombiniert, um einen eindeutigen Schlüsselwert für jede Zeile zu erstellen.

Wenn sich der nicht gruppierte Index über einem Heap befindet, ist der Datenzeilenlokator die Heap-RID. Die Größe beträgt dann 8 Byte.

Num_Key_Cols = Num_Key_Cols + 1

Num_Variable_Key_Cols = Num_Variable_Key_Cols + 1

Max_Var_Key_Size = Max_Var_Key_Size + 8

Wenn sich der nicht gruppierte Index über einem gruppierten Index befindet, dann ist der Datenzeilenlokator der Gruppierungsschlüssel. Bei den Spalten, die mit dem nicht gruppierten Index kombiniert werden müssen, handelt es sich um die im Gruppierungsschlüssel enthaltenen Spalten, die noch nicht in der Menge der nicht gruppierten Indexschlüsselspalten vorhanden sind.

Num_Key_Cols = Num_Key_Cols + Anzahl der Gruppierungsschlüsselspalten, die nicht in der Menge der nicht gruppierten Indexschlüsselspalten vorhanden sind (+ 1, wenn der gruppierte Index nicht eindeutig ist)

Fixed_Key_Size = Fixed_Key_Size + Gesamtanzahl der Byte in allen Generierungsschlüsselspalten fester Länge, die nicht in der Menge der nicht gruppierten Indexschlüsselspalten vorhanden sind.

Num_Variable_Key_Cols = Num_Variable_Key_Cols + Anzahl der Gruppierungsschlüsselspalten variabler Länge, die nicht in der Menge der nicht gruppierten Indexschlüsselspalten vorhanden sind (+ 1, wenn der gruppierte Index nicht eindeutig ist)

Max_Var_Key_Size = Max_Var_Key_Size + maximale Anzahl der Byte in den Gruppierungsschlüsselspalten variabler Länge, die nicht in der Menge der nicht gruppierten Indexschlüsselspalten vorhanden sind (+ 4, wenn der gruppierte Index nicht eindeutig ist)
Ein Teil der Zeile, der als NULL-Bitmuster bezeichnet wird, kann für das Verwalten der NULL-Zulässigkeit der Spalte reserviert sein. Berechnen Sie dessen Größe:

Wenn der Indexschlüssel Spalten enthält, die NULL-Werte zulassen (einschließlich erforderlicher Gruppierungsschlüsselspalten wie in Schritt 1.3 beschrieben), ist ein Teil der Indexzeile für das NULL-Bitmuster reserviert.

Index_Null_Bitmap = 2 + ((Anzahl der Spalten in der Indexzeile + 7) / 8)

Nur der ganzzahlige Teil des vorherigen Ausdrucks darf verwendet werden. Der Rest wird verworfen.

Wenn keine Schlüsselspalten vorhanden sind, die NULL-Werte zulassen, legen Sie Index_Null_Bitmap auf 0 fest.
Berechnen Sie die Größe der Daten variabler Länge:

Wenn der Indexschlüssel Spalten variabler Länge enthält (einschließlich erforderlicher Schlüsselspalten des gruppierten Indexes), müssen Sie ermitteln, wie viel Speicherplatz zum Speichern der Spalten innerhalb der Indexzeile verwendet wird:

Variable_Key_Size* = 2 + (Num_Variable_Key_Cols x 2) + *Max_Var_Key_Size

Die zu ***Max\_Var\_Key\_Size*** hinzugefügten Bytes dienen der Nachverfolgung jeder einzelnen Variablenspalte. Bei dieser Formel wird angenommen, dass alle Spalten variabler Länge zu 100 Prozent gefüllt sind. Wenn sich abzeichnet, dass ein niedrigerer Prozentsatz des Speicherplatzes für Spalten variabler Länge verwendet wird, können Sie den ***Max\_Var\_Key\_Size***-Wert mithilfe dieses Prozentsatzes anpassen, um einen genaueren Schätzwert für die Gesamtgröße der Tabelle zu erhalten.

Wenn keine Spalten variabler Länge vorhanden sind, legen Sie ***Variable\_Key\_Size*** auf 0 fest.

Berechnen Sie die Länge der Indexzeile:

Index_Row_Size = Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1 (für Zeilenüberschriftenaufwand einer Indexzeile) + 6 (für ID-Zeiger der untergeordneten Seite)
Berechnen Sie die Anzahl der Indexzeilen pro Seite (8096 freie Byte pro Seite):

Index_Rows_Per_Page = 8096 / (Index_Row_Size + 2)

Da sich eine Indexzeile nicht auf zwei Seiten erstreckt, muss die Anzahl der Indexzeilen pro Seite auf die nächste ganze Zeile abgerundet werden. Die Angabe 2 in der Formel bezieht sich auf den Eingang der Zeile in das Slotarray der Seite.

Schritt 2.Berechnen des Speicherplatzes, der zum Speichern der Indexinformationen in der Blattebene verwendet wird

Mit den folgenden Schritten können Sie den Umfang des Speicherplatzes schätzen, der zum Speichern der Blattebene des Indexes erforderlich ist. Sie benötigen die in Schritt 1 aufgezeichneten Werte, um diesen Schritt durchführen zu können.

Geben Sie die Anzahl der Spalten mit fester und mit variabler Länge auf Blattebene an, und berechnen Sie den Speicherplatz, der für deren Speicherung erforderlich ist:

Hinweis
Sie können einen nicht gruppierten Index erweitern, indem Nichtschlüsselspalten zusätzlich zu Indexschlüsselspalten eingeschlossen werden. Diese zusätzlichen Spalten werden auf der Blattebene des nicht gruppierten Indexes gespeichert. Weitere Informationen finden Sie unter Erstellen von Indizes mit eingeschlossenen Spalten.

Hinweis
Sie können varchar-, nvarchar-, varbinary- oder sql_variant-Spalten kombinieren, mit dem Ergebnis, dass die definierte Tabellengesamtbreite größer als 8.060 Byte ist. Die Länge jeder einzelnen Spalte unterliegt auch weiterhin der Beschränkung von 8.000 Byte für eine varchar-, varbinary- oder sql_variant-Spalte und von 4.000 Byte für nvarchar-Spalten. Die kombinierte Breite kann jedoch den Grenzwert von 8.060 Byte in einer Tabelle überschreiten. Dies gilt auch für die Zeilen auf Blattebene eines nicht gruppierten Indexes, die eingeschlossene Spalten enthalten.

Sie können varchar-, nvarchar-, varbinary- oder sql_variant-Spalten kombinieren, mit dem Ergebnis, dass die definierte Tabellengesamtbreite größer als 8.060 Byte ist. Die Länge jeder einzelnen Spalte unterliegt auch weiterhin der Beschränkung von 8.000 Byte für eine varchar-, varbinary- oder sql_variant-Spalte und von 4.000 Byte für nvarchar-Spalten. Die kombinierte Breite kann jedoch den Grenzwert von 8.060 Byte in einer Tabelle überschreiten. Dies gilt auch für die Zeilen auf Blattebene eines nicht gruppierten Indexes, die eingeschlossene Spalten enthalten.

Wenn der nicht gruppierte Index keine eingeschlossenen Spalten besitzt, verwenden Sie die Werte aus Schritt 1 einschließlich aller Änderungen, die ggf. in Schritt 1.3 vorgenommen wurden:

Num_Leaf_Cols* = *Num_Key_Cols

Fixed_Leaf_Size* = *Fixed_Key_Size

Num_Variable_Leaf_Cols* = *Num_Variable_Key_Cols

Max_Var_Leaf_Size* = *Max_Var_Key_Size

Wenn der nicht gruppierte Index keine eingeschlossenen Spalten besitzt, fügen Sie die entsprechenden Werte den Werten aus Schritt 1 einschließlich allen Änderungen, die ggf. in Schritt 1.3 vorgenommen wurden, hinzu. Die Größe einer Spalte hängt von der Angabe für Datentyp und -länge ab. Weitere Informationen finden Sie unter [Datentypen (Transact-SQL)](ms187752\(v=sql.110\).md).

***Num\_Leaf\_Cols*** = ***Num\_Key\_Cols*** + Anzahl der enthaltenen Spalten

***Fixed\_Leaf\_Size*** = ***Fixed\_Key\_Size*** + Gesamtzahl der Byte der enthaltenen Schlüsselspalten fester Länge

***Num\_Variable\_Leaf\_Cols*** = ***Num\_Variable\_Key\_Cols*** + Anzahl der enthaltenen Spalten variabler Länge

***Max\_Var\_Leaf\_Size*** = ***Max\_Var\_Key\_Size*** + Maximale Bytegröße der enthaltenen Spalten variabler Länge

Konto für den Datenzeilenlokator:

Wenn der nicht gruppierte Index nicht eindeutig ist, wurde der Aufwand für den Datenzeilenlokator bereits in Schritt 1.3 berücksichtigt. In diesem Fall sind keine zusätzlichen Änderungen erforderlich. Fahren Sie mit dem nächsten Schritt fort.

Wenn der nicht gruppierte Index eindeutig ist, muss der Datenzeilenlokator in allen Zeilen auf der Blattebene berücksichtigt werden.

Wenn sich der nicht gruppierte Index über einem Heap befindet, ist der Datenzeilenlokator die Heap-RID (Größe 8 Byte).

Num_Leaf_Cols = Num_Leaf_Cols + 1

Num_Variable_Leaf_Cols = Num_Variable_Leaf_Cols + 1

Max_Var_Leaf_Size = Max_Var_Leaf_Size + 8

Wenn sich der nicht gruppierte Index über einem gruppierten Index befindet, dann ist der Datenzeilenlokator der Gruppierungsschlüssel. Bei den Spalten, die mit dem nicht gruppierten Index kombiniert werden müssen, handelt es sich um die im Gruppierungsschlüssel enthaltenen Spalten, die noch nicht in der Menge der nicht gruppierten Indexschlüsselspalten vorhanden sind.

Num_Leaf_Cols = Num_Leaf_Cols + Anzahl der Gruppierungsschlüsselspalten, die nicht in der Menge der nicht gruppierten Indexschlüsselspalten vorhanden sind (+ 1, wenn der gruppierte Index nicht eindeutig ist)

Fixed_Leaf_Size = Fixed_Leaf_Size + Anzahl der Byte in allen Gruppierungsschlüsselspalten fester Länge, die nicht in der Menge der nicht gruppierten Indexschlüsselspalten vorhanden sind

Num_Variable_Leaf_Cols = Num_Variable_Leaf_Cols + Anzahl der Gruppierungsschlüsselspalten variabler Länge, die nicht in der Menge der nicht gruppierten Indexschlüsselspalten vorhanden sind (+ 1, wenn der gruppierte Index nicht eindeutig ist)

Max_Var_Leaf_Size = Max_Var_Leaf_Size + Anzahl der Byte in den Gruppierungsschlüsselspalten variabler Länge, die nicht in der Menge der nicht gruppierten Indexschlüsselspalten vorhanden sind (+ 4, wenn der gruppierte Index nicht eindeutig ist)
Berechnen der Größe des NULL-Bitmusters:

Leaf_Null_Bitmap = 2 + ((Num_Leaf_Cols + 7) / 8)

Nur der ganzzahlige Teil des vorherigen Ausdrucks darf verwendet werden. Der Rest wird verworfen.
Berechnen Sie die Größe der Daten variabler Länge:

Wenn der Indexschlüssel Spalten variabler Länge enthält (einschließlich erforderlicher Gruppierungsschlüsselspalten wie zuvor in Schritt 2.2 beschrieben), müssen Sie ermitteln, wie viel Speicherplatz zum Speichern der Spalten innerhalb der Indexzeile verwendet wird:

Variable_Leaf_Size* = 2 + (Num_Variable_Leaf_Cols x 2) + *Max_Var_Leaf_Size

Die zu ***Max\_Var\_Key\_Size*** hinzugefügten Bytes dienen der Nachverfolgung jeder einzelnen Variablenspalte. Bei dieser Formel wird angenommen, dass alle Spalten variabler Länge zu 100 Prozent gefüllt sind. Wenn sich abzeichnet, dass ein niedrigerer Prozentsatz des Speicherplatzes für Spalten variabler Länge verwendet wird, können Sie den ***Max\_Var\_Leaf\_Size***-Wert mithilfe dieses Prozentsatzes anpassen, um einen genaueren Schätzwert für die Gesamtgröße der Tabelle zu erhalten.

Wenn keine Spalten variabler Länge vorhanden sind, legen Sie ***Variable\_Leaf\_Size*** auf 0 fest.

Berechnen Sie die Länge der Indexzeile:

Leaf_Row_Size = Fixed_Leaf_Size + Variable_Leaf_Size + Leaf_Null_Bitmap + 1 (für Zeilenüberschriftenaufwand einer Indexzeile) + 6 (für ID-Zeiger der untergeordneten Seite)
Berechnen Sie die Anzahl der Indexzeilen pro Seite (8096 freie Byte pro Seite):

Leaf_Rows_Per_Page = 8096 / (Leaf_Row_Size + 2)

Da sich eine Indexzeile nicht auf zwei Seiten erstreckt, muss die Anzahl der Indexzeilen pro Seite auf die nächste ganze Zeile abgerundet werden. Die Angabe 2 in der Formel bezieht sich auf den Eingang der Zeile in das Slotarray der Seite.
Berechnen Sie die Anzahl der reservierten freien Zeilen pro Seite anhand des angegebenen Füllfaktors:

Free_Rows_Per_Page = 8096 x ((100 - Fill_Factor) / 100) / (Leaf_Row_Size + 2)

Bei dem in der Berechnung verwendeten Füllfaktor handelt es sich nicht um einen Prozentwert, sondern um einen ganzzahligen Wert. Da sich eine Zeile nicht auf zwei Seiten erstreckt, muss die Anzahl der Zeilen pro Seite auf die nächste ganze Zeile abgerundet werden. Mit ansteigendem Füllfaktor werden mehr Daten auf jeder Seite gespeichert, und die Anzahl der Seiten nimmt ab. Die Angabe 2 in der Formel bezieht sich auf den Eingang der Zeile in das Slotarray der Seite.
Berechnen Sie die Anzahl der Seiten, die zum Speichern aller Zeilen benötigt werden:

Num_Leaf_Pages = Num_Rows / (Leaf_Rows_Per_Page - Free_Rows_Per_Page)

Die geschätzte Seitenanzahl muss auf die nächste ganze Seite aufgerundet werden.
Berechnen Sie die Größe des Indexes (insgesamt 8.192 Byte pro Seite):

Leaf_Space_Used* = 8192 x *Num_Leaf_Pages

Schritt 3.Berechnen des Speicherplatzes, der zum Speichern der Indexinformationen in den inneren Knotenebenen verwendet wird

Befolgen Sie diese Schritte, um abzuschätzen, wie viel Speicherplatz zum Speichern der Zwischen- und Stammebenen des Indexes erforderlich ist. Sie benötigen die in Schritt 2 und 3 errechneten Werte, um diesen Schritt durchführen zu können.

Berechnen Sie die Anzahl der inneren Knotenebenen im Index:

Non-leaf Levels = 1 + log Index_Rows_Per_Page (Num_Leaf_Pages / Index_Rows_Per_Page)

Runden Sie diese Zahl auf die nächste ganze Zahl auf. Dieser Wert schließt die Blattebene des nicht gruppierten Index nicht mit ein.
Berechnen Sie die Anzahl der inneren Knotenseiten im Index:

Num_Index_Pages* = ∑Ebene (Num_Leaf_Pages/Index_Rows_Per_PageEbene), dabei gilt: 1 <= Level <= *Ebenen

Runden Sie jeden Summanden auf die nächste ganze Zahl auf. Stellen Sie sich als einfaches Beispiel einen Index vor, bei dem ***Num\_Leaf\_Pages*** = 1000 und ***Index\_Rows\_Per\_Page*** = 25 gilt. Die erste Indexebene über der Blattebene speichert 1.000 Indexzeilen, dies bedeutet eine Indexzeile pro Blattseite. Dabei passen 25 Indexzeilen auf eine Seite. Dies bedeutet, dass 40 Seiten zum Speichern dieser 1.000 Indexzeilen erforderlich sind. Die nächste Ebene des Indexes muss 40 Zeilen speichern. Dies bedeutet, dass zwei Seiten erforderlich sind. Die letzte Ebene des Indexes muss zwei Zeilen speichern. Dies bedeutet, dass eine Seite erforderlich ist. Daraus ergeben sich 43 innere Knotenseiten im Index. Wenn Sie diese Werte in den oben genannten Formeln verwenden, erhalten Sie folgendes Ergebnis:

***Non-leaf\_Levels*** = 1 + log25 (1000 / 25) = 3

***Num\_Index\_Pages*** = 1000/(253)+ 1000/(252) + 1000/(251) = 1 + 2 + 40 = 43. Dies ist die Anzahl der im Beispiel beschriebenen Seiten.

Berechnen Sie die Größe des Indexes (insgesamt 8.192 Byte pro Seite):

Index_Space_Used* = 8192 x *Num_Index_Pages

Schritt 4.Addieren der berechneten Werte

Addieren Sie die Werte, die in den beiden vorherigen Schritten erzielt wurden:

Größe des nicht gruppierten Indexes (in Byte) = Leaf_Space_Used + Index_Space_used

In dieser Berechnung wird Folgendes nicht berücksichtigt:

Partitionierung

Der Speicherplatzaufwand aus der Partitionierung ist geringfügig, seine Berechnung jedoch komplex. Er muss nicht berücksichtigt werden.
Zuordnungsseiten

Mindestens eine IAM-Seite wird zum Nachverfolgen der für einen Heap zugeordneten Seiten verwendet, der Speicherplatzaufwand ist jedoch nur minimal. Außerdem ist kein Algorithmus verfügbar, um deterministisch genau zu berechnen, wie viele IAM-Seiten verwendet werden.
LOB-Werte (Large Object)

Der Algorithmus zum Berechnen des genauen Speicherplatzes, der zum Speichern der Werte der LOB-Datentypen varchar(max), varbinary(max), nvarchar(max), text, ntext, xml und image erforderlich ist, ist komplex. Es ist ausreichend, einfach die durchschnittliche Größe der erwarteten LOB-Werte zu addieren, diese mit Num_Rows zu multiplizieren und das Ergebnis dann zur Gesamtgröße des nicht gruppierten Indexes zu addieren.
Komprimierung

Sie können die Größe eines komprimierten Index nicht im Vorfeld berechnen.
Spalten mit geringer Dichte

Informationen zu den Speicherplatzanforderungen von Spalten mit geringer Dichte finden Sie unter Verwenden von Spalten mit geringer Dichte.