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

Статья
05/23/2023

Применяется к:SQL Server Azure SQL Database Azure, управляемому экземпляру SQL Azure

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

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

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

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

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

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

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

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

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

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

Max_Var_Key_Size — максимальный размер в байтах всех ключевых столбцов переменной ширины.
Учтите место для указателя на строку данных, который необходим, если индекс не является уникальным.

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

Если некластеризованный индекс является индексом для кучи, указателем на строку данных служит 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, если кластеризованный индекс не уникальный).
Часть строки, называемая битовой картой NULL, зарезервирована для управления свойством столбцов содержать неопределенные значения. Вычислите ее размер.

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

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

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

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

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

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.
Расчет размера индексной строки:

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

Index_Rows_Per_Page = 8096 / (Index_Row_Size + 2)

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

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

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

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

Заметка

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

Заметка

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

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

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

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

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

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

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

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

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

Leaf_Rows_Per_Page = 8096 / (Leaf_Row_Size + 2)

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

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

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

Num_Leaf_Pages = Num_Rows / (Leaf_Rows_Per_Page - Free_Rows_Per_Page)

Вычисленное количество страниц должно быть округлено в большую сторону до ближайшей целой страницы.
Расчет размера индекса (всего 8 192 байт на страницу).

Leaf_Space_Used = 8192 x Num_Leaf_Pages

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

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

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

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

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

Num_Index_Pages = ∑Level (Num_Leaf_Pages/Index_Rows_Per_Page^Level)where 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, что составляет число страниц, описанное в примере.
Расчет размера индекса (всего 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 и добавить его к общему размеру некластеризованного индекса.
Сжатие

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

Сведения о требованиях разреженных столбцов к месту на диске см. в разделе Use Sparse Columns.

См. также

Описание кластеризованных и некластеризованных индексов
Создание некластеризованных индексов
Создание кластеризованных индексов
Оценка размера таблицы
Оценка размера кластеризованного индекса
Оценка размера кучи
Оценка размера базы данных