Оценка размера некластеризованного индекса

Применимо к:SQL ServerAzure SQL База данныхAzure SQL Управляемый экземплярSQL База данных в Microsoft Fabric

Чтобы оценить объем пространства, необходимого для хранения некластеризованного индекса, можно выполнить следующие шаги.

  1. Вычислить переменные, используемые на шагах 2 и 3.

  2. Рассчитать пространство, используемое для хранения информации об индексе на уровне листьев некластеризованного индекса.

  3. Рассчитать пространство, используемое для хранения информации об индексе на нелистовых уровнях некластеризованного индекса.

  4. Сложите полученные значения.

Шаг 1. Вычисление переменных для использования в шагах 2 и 3

Можно использовать следующие шаги, чтобы вычислить переменные, используемые для оценки объема пространства, необходимого для хранения верхних уровней индекса.

  1. Укажите количество строк в новой таблице:

    • Num_Rows = число строк в таблице.

  2. Укажите количество столбцов переменной и фиксированной длины в ключе индекса и рассчитайте объем необходимого места для их хранения:

    Ключевые столбцы индекса могут включать в себя столбцы постоянной и переменной длины. Чтобы вычислить размер строки индекса на внутреннем уровне, нужно рассчитать, сколько места занимает в строке индекса каждая из этих групп столбцов. Размер столбца зависит от типа данных и длины.

    • Num_Key_Cols = общее количество ключевых столбцов (фиксированной и переменной ширины).
    • Fixed_Key_Size = общий размер в байтах всех ключевых столбцов фиксированной ширины.
    • Num_Variable_Key_Cols = количество ключевых столбцов переменной ширины.
    • Max_Var_Key_Size — максимальный размер в байтах всех ключевых столбцов переменной ширины.

  3. Учтите место для указателя на строку данных, который необходим, если индекс не является уникальным.

    Если некластеризованный индекс не является уникальным, то указатель на строку данных будет объединен с ключом некластеризованного индекса, чтобы получить уникальное ключевое значение для каждой строки.

    Если некластеризованный индекс является над кучей, указателем строки данных является RID (РИД) кучи. Его размер составляет 8 байт.

    • Num_Key_Cols = Num_Key_ColsNum_Key_Cols + 1
    • Num_Variable_Key_Cols = Num_Variable_Key_Cols + 1
    • = Max_Var_Key_SizeMax_Var_Key_Size + 8

    Если некластеризованный индекс строится поверх кластеризованного, указателем строки данных является ключ кластеризации. Столбцы, которые необходимо объединить с ключом некластеризованного индекса, — это столбцы в ключе кластеризации, которые еще не присутствуют в наборе некластеризованных ключевых столбцов индекса.

    • Num_Key_Cols = Num_Key_Cols + количество ключевых столбцов кластеризации, не вошедших в набор ключевых столбцов некластеризованного индекса (+ 1, если кластеризованный индекс не уникален).

    • Fixed_Key_Size = Fixed_Key_Size + общий размер в байтах ключевых столбцов кластеризации фиксированной ширины, не вошедших в набор ключевых столбцов некластеризованного индекса.

    • Num_Variable_Key_Cols = Num_Variable_Key_Cols + число ключевых столбцов кластеризации переменной ширины, не вошедших в набор ключевых столбцов некластеризованного индекса (+ 1, если кластеризованный индекс не уникальный).

    • Max_Var_Key_Size = Max_Var_Key_Size + максимальный размер в байтах ключевых столбцов кластеризации переменной ширины, не вошедших в набор ключевых столбцов некластеризованного индекса (+ 4, если кластеризованный индекс не уникальный).

  4. Часть строки, известная как битовая карта NULL, может быть зарезервирована для управления пустыми значениями столбцов. Вычислите ее размер.

    Если в ключе индекса есть столбцы, допускающие значения NULL, включая любые необходимые ключевые столбцы кластеризации, как описано в шаге 1.3, то часть строки индекса должна быть зарезервирована для битовой карты NULL.

    • Index_Null_Bitmap = 2 + ((количество столбцов в строке индекса + 7) / 8).

    Следует использовать только целую часть предшествующего выражения. Остаток выбросьте.

    Если нет ключевых столбцов, допускающих значения NULL, установите для параметра Index_Null_Bitmap значение 0.

  5. Вычислите размер данных переменной длины.

    Если есть столбцы переменной длины в ключе индекса, включающие какие-либо необходимые кластеризованные индексные ключевые столбцы, определите, сколько пространства используется для хранения этих столбцов в строке индекса:

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

    Байты, добавленные в Max_Var_Key_Size , предназначены для отслеживания каждого столбца переменной. В этой формуле предполагается, что все столбцы переменной длины заполнены 100 процентов. Если вы предполагаете, что для хранения столбца переменной ширины будет использовано меньше места, для более точного подсчета общего размера таблицы можно изменить значение Max_Var_Key_Size на коэффициент заполнения места (в %).

    Если нет столбцов переменной ширины, установите для параметра Variable_Key_Size значение 0.

  6. Расчет размера индексной строки:

    • Index_Row_Size = Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap+ 1 (для служебных данных строки заголовка индекса) + 6 (для указателя на идентификатор дочерней страницы).

  7. Вычислите количество строк индекса на страницу (8096 бесплатных байтов на страницу):

    • Index_Rows_Per_Page = 8096 / (Index_Row_Size + 2)

    Так как строки индекса не охватывают страницы, количество строк индекса на страницу должно быть округлено до ближайшей целой строки. Число 2 в формуле относится к записи строки в массиве слотов страницы.

Шаг 2. Рассчитайте пространство, используемое для хранения сведений об индексе на лиственном уровне.

Используйте следующие шаги, чтобы оценить требуемый объём памяти для хранения листового уровня индекса. Для выполнения этого шага необходимо сохранить значения, сохраненные на шаге 1.

  1. Укажите количество столбцов фиксированной и переменной длины на уровне листа и рассчитайте необходимое пространство для их хранения.

    Можно расширить некластеризованный индекс, включив неключевые столбцы в дополнение к ключевым столбцам индекса. Эти дополнительные столбцы сохраняются только на листовом уровне некластеризованного индекса. Дополнительные сведения см. в статье Создание индексов с включенными столбцами.

    Note

    Можно сочетать столбцы varchar, nvarchar, varbinaryили sql_variant , в результате чего общая ширина определенной таблицы превысит 8 060 байт. Длина каждого из этих столбцов должна быть в пределах 8 000 байт для столбцов типа varchar, varbinaryили sql_variant и 4 000 байт для столбцов типа nvarchar . Однако их объединенные ширины могут превышать 8060-байтового ограничения в таблице. Это также применимо к листовым строкам некластеризованного индекса, у которых включены столбцы.

    Если некластеризованный индекс не содержит включенных столбцов, используйте значения из шага 1, включая любые изменения, определенные на шаге 1.3:

    • Num_Leaf_Cols = Num_Leaf_ColsNum_Key_Cols
    • = Fixed_Leaf_SizeFixed_Key_Size
    • Число_Переменных_Листовых_Столбцов = Число_Переменных_Ключевых_Столбцов
    • Max_Var_Leaf_Size = Max_Var_Key_Size

    Если некластеризованный индекс имеет включенные столбцы, добавьте соответствующие значения к значениям из шага 1, включая любые модификации из шага 1.3. Размер столбца зависит от типа данных и длины. Дополнительные сведения см. в разделе Типы данных.

    • 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 + максимальный размер в байтах включенных столбцов переменной ширины.

  2. Учтите место для указателя на строку данных.

    Если некластеризованный индекс не уникален, затраты на указатель строки данных уже рассматривались на шаге 1.3, а дальнейшие изменения не требуются. Перейдите к следующему шагу.

    Если некластеризованный индекс уникален, указатель на строку данных должен учитываться во всех строках на листьевом уровне.

    Если некластеризованный индекс расположен поверх кучи, указателем на строку данных служит RID для кучи (размером 8 байт).

    • Num_Leaf_Cols = Num_Leaf_ColsNum_Leaf_Cols + 1
    • = Num_Variable_Leaf_ColsNum_Variable_Leaf_Cols + 1
    • Max_Var_Leaf_Size = Max_Var_Leaf_SizeMax_Var_Leaf_Size + 8

    Если некластеризованный индекс строится поверх кластеризованного, указателем строки данных является ключ кластеризации. Столбцы, которые необходимо объединить с ключом некластеризованного индекса, — это столбцы в ключе кластеризации, которые еще не присутствуют в наборе некластеризованных ключевых столбцов индекса.

    • Num_Leaf_Cols = Num_Leaf_Cols + количество ключевых столбцов кластеризации, не вошедших в набор ключевых столбцов некластеризованного индекса (+ 1, если кластеризованный индекс не уникальный).

    • Fixed_Leaf_Size = Fixed_Leaf_Size + число ключевых столбцов кластеризации фиксированной ширины, не вошедших в набор ключевых столбцов некластеризованного индекса.

    • Num_Variable_Leaf_Cols = Num_Variable_Leaf_Cols + число ключевых столбцов кластеризации переменной длины, не входящих в набор ключевых столбцов некластеризованного индекса (+ 1, если кластеризованный индекс не уникальный).

    • Max_Var_Leaf_Size = Max_Var_Leaf_Size + размер в байтах ключевых столбцов кластеризации переменной ширины, не вошедших в набор ключевых столбцов некластеризованного индекса (+ 4, если кластеризованный индекс не уникальный).

  3. Рассчет размера битовой карты NULL:

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

    Следует использовать только целую часть предшествующего выражения. Остаток выбросьте.

  4. Вычислите размер данных переменной длины.

    Если есть столбцы переменной длины (ключевые или включенные), включая какие-либо необходимые ключевые столбцы кластеризации, как было описано в шаге 2.2, то определите, сколько пространства используется для хранения столбцов в строке индекса:

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

    Байты, добавленные в Max_Var_Key_Size , предназначены для отслеживания каждого столбца переменной. В этой формуле предполагается, что все столбцы переменной длины заполнены 100 процентов. Если вы предполагаете, что для хранения столбца переменной ширины будет использовано меньше места, то для более точного подсчета общего размера таблицы можно изменить значение Max_Var_Leaf_Size на коэффициент заполнения места (в %).

    Если нет столбцов переменной ширины (ключевых или включенных), установите для параметра Variable_Leaf_Size значение 0.

  5. Расчет размера индексной строки:

    • Leaf_Row_Size = Fixed_Leaf_Size + Variable_Leaf_Size + Leaf_Null_Bitmap + 1 (для служебных данных строки заголовка индекса).

  6. Вычислите количество строк индекса на страницу (8096 бесплатных байтов на страницу):

    • Leaf_Rows_Per_Page = 8096 / (Leaf_Row_Size + 2)

    Так как строки индекса не охватывают страницы, количество строк индекса на страницу должно быть округлено до ближайшей целой строки. Число 2 в формуле относится к записи строки в массиве слотов страницы.

  7. Рассчет количества зарезервированных пустых строк на странице на основании указанного коэффициента заполнения :

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

    Коэффициент заполнения, используемый в расчете, должен быть целым значением, а не процентным соотношением. Так как строки не охватывают страницы, количество строк на страницу должно быть округлено до ближайшей целой строки. По мере роста коэффициента заполнения на каждой странице хранится больше данных и меньше страниц. Число 2 в формуле относится к записи строки в массиве слотов страницы.

  8. Вычислите количество страниц, необходимое для хранения всех строк:

    • Num_Leaf_PagesNum_Rows / (Leaf_Rows_Per_Page Free_Rows_Per_Page)

    Вычисленное количество страниц должно быть округлено в большую сторону до ближайшей целой страницы.

  9. Вычислите размер индекса (8192 общих байт на страницу):

    • Используемое_пространство_листьев (Leaf_Space_Used) = 8192 x Количество_листовых_страниц (Num_Leaf_Pages)

Шаг 3. Рассчитайте пространство, используемое для хранения информации об индексе на не-листовых уровнях.

Выполните эти шаги для оценки пространства, требуемого для хранения промежуточных и конечного уровней индекса. Для выполнения этого шага необходимо сохранить значения из шагов 2 и 3.

  1. Вычислите количество нелистовых уровней индекса:

    • Неконечные уровни = 1 + log(Index_Rows_Per_Page) (Num_Leaf_Pages / Index_Rows_Per_Page)

    Данное значение округляется в большую сторону до ближайшего целого числа. Это значение не включает уровень листа некластеризованного индекса.

  2. Вычислите количество неконечных страниц индекса:

    • Num_Index_Pages = ∑Level (Num_Leaf_Pages/Index_Rows_Per_Page^Level), где 1 <= Level <= Levels

    Округлите каждое слагаемое вверх до ближайшего целого числа. Рассмотрим в качестве простого примера индекс, где Num_Leaf_Pages = 1000, а Index_Rows_Per_Page = 25. Первый уровень индекса над листовым уровнем хранит 1000 строк индекса, по одной строке индекса на каждый листовой уровень, и 25 строк индекса могут уместиться на каждой странице. Это означает, что для хранения этих 1000 строк индекса требуется 40 страниц. Следующий уровень индекса должен хранить 40 строк. Это означает, что ему необходимо 2 страницы. Последний уровень индекса должен хранить 2 строки. Это означает, что ему необходима 1 страница. Это соответствует 43 неконечным страницам индекса. Использование этих значений в предыдущих формулах приводит к следующему результату:

    • Non-leaf_Levels = 1 + log(25) (1000 / 25) = 3.

    • Num_Index_Pages = 1000/(25^3) + 1000/(25^2) + 1000/(25^1) = 1 + 2 + 40 = 43, что составляет число страниц, описанное в примере.

  3. Вычислите размер индекса (8192 общих байт на страницу):

    • Используемое_пространство_индекса = 8192 x Количество_страниц_индекса

Шаг 4. Всего вычисляемых значений

Необходимо сложить результаты, полученные на двух предыдущих шагах.

Размер некластеризованного индекса (в байтах) = Leaf_Space_Used + Index_Space_used

Этот расчет не учитывает следующие условия:

  • Секционирование: затраты на пространство от секционирования минимальны, но сложны для вычисления. Не важно, чтобы это было включено.

  • Страницы выделения: Для отслеживания страниц, выделенных куче, используется по крайней мере одна страница IAM, но накладные расходы на пространство минимальны, и нет алгоритма, позволяющего точно определить необходимое количество страниц IAM.

  • Большие значения объектов (LOB): алгоритм, определяющий, сколько пространства будет использоваться для хранения типов данных varchar(max), varbinary(max), nvarchar(max), text, ntext, xml и image значений, является сложным. Достаточно просто добавить средний размер ожидаемых LOB-значений, умножить на Num_Rows, и добавить это к общему размеру некластеризованного индекса.

  • Сжатие: невозможно предварительно вычислить размер сжатого индекса.

  • Разреженные столбцы: сведения о требованиях к пространству разреженных столбцов см. в разделе "Использование разреженных столбцов".

Связанный контент

  • Last updated on