Поделиться через

  • Статья

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

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

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

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

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

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

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

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

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

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

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

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

    Num_Key_Cols = общее число ключевых столбцов (фиксированной и переменной длины)

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

    Num_Variable_Key_Cols = количество ключевых столбцов переменной длины

    Max_Var_Key_Size = максимальный размер в байтах всех ключевых столбцов переменной длины

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

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

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

    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

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

    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 процентов. Если предполагается, что для хранения столбца переменной длины будет использовано меньше места, то для более точного подсчета общего размера таблицы можно задать значение ***Макс_объем_перем_ключей*** как процент от максимально возможной длины.

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

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

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

  2. Расчет количества индексных строк на страницу (8 096 свободных байт на страницу):

    Index_Rows_Per_Page = 8096 / (Index_Row_Size + 2)

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

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

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

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

    ПримечаниеПримечание

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

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

    Если некластеризованный индекс не имеет никаких включенных столбцов, используйте значения из шага 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

Если некластеризованный индекс имеет включенные столбцы, добавьте соответствующие значения к значениям из шага 1, включая любые модификации из шага 1.3. Размер столбца зависит от типа данных и длины. Дополнительные сведения см. в разделе [Типы данных (компонент Database Engine)](ms187594\(v=sql.105\).md).

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

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

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

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

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

    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

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

    +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, если кластеризованный индекс неуникален)

  2. Вычислите размер битовой карты NULL:

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

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

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

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

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

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

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

    Leaf_Rows_Per_Page = 8096 / (Leaf_Row_Size + 2)

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

  3. Рассчитайте количество зарезервированных свободных строк страницы на основе указанного параметра коэффициента заполнения fill factor:

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

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

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

    Num_Leaf_Pages = Num_Rows / (Leaf_Rows_Per_Page - Free_Rows_Per_Page)

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

  5. Расчет размера индекса (всего 8 192 байт на страницу).

Leaf_Space_Used* = 8192 x *Num_Leaf_Pages

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

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

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

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

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

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

Num_Index_Pages* = ∑Level (Num_Leaf_Pages/Index_Rows_Per_PageLevel), где 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 + log25 (1000 / 25) = 3

***Num_Index_Pages*** = 1000/(253)+ 1000/(252) + 1000/(251) = 1 + 2 + 40 = 43, что составит число страниц, описанное в примере.
  1. Расчет размера индекса (всего 8 192 байт на страницу).

Index_Space_Used* = 8192 x *Num_Index_Pages

Шаг 4. Сложение полученных значений

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

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

Этот расчет не учитывает следующие факторы.

  • Секционирование

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

  • Страницы размещения

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

  • Значения LOB

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

  • Сжатие

    Размер сжатого индекса нельзя вычислить заранее.

  • Разреженные столбцы

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