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

  • Статья

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

  1. Рассчитать пространство, используемое для хранения данных в неконечных уровнях некластеризованного индекса.
  2. Рассчитать пространство, используемое для хранения данных на конечном уровне некластеризованного индекса.
  3. Найти общую сумму рассчитанных значений.

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

ms190620.note(ru-ru,SQL.90).gifВажно!
Сохраните значения, используемые на этом шаге, для дальнейшего использования на шаге 2.

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

  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
    Число_перем_ключ_столбцов = Число_перем_ключ_столбцов + 1
    Макс_объем_перем_ключей = Макс_объем_перем_ключей +8
    Если некластеризованный индекс строится поверх кластеризованного, указателем строки данных является ключ кластеризации. Столбцы, которые должны быть объединены с ключом некластеризованного индекса, — это те столбцы в ключе кластеризации, которые еще не присутствуют в наборе столбцов ключа некластеризованного индекса.
    Num_Key_Cols = Num_Key_Cols + количество столбцов ключа кластеризации, не вошедших в набор столбцов ключа некластеризованного индекса (+ 1, если кластеризованный индекс неуникален)
    Объем_фикс_ключей = Объем_фикс_ключей + общий размер в байтах столбцов ключа кластеризации фиксированной длины, не вошедших в набор столбцов ключа некластеризованного индекса
    Число_перем_ключ_столбцов = Число_перем_ключ_столбцов + количество столбцов ключа кластеризации переменной длины, не вошедших в набор столбцов ключа некластеризованного индекса (+ 1, если кластеризованный индекс неуникален)
    Число_перем_ключ_столбцов = Число_перем_ключ_столбцов + максимальный размер в байтах столбцов ключа кластеризации переменной длины, не вошедших в набор столбцов ключа некластеризованного индекса (+4, если кластеризованный индекс неуникален)
  4. Часть строки, называемая битовой картой NULL, зарезервирована для управления свойством столбцов содержать неопределенные значения. Вычислите ее размер.
    Если в ключе индекса есть столбцы, которые могут содержать неопределенные значения, включая любые необходимые ключевые столбцы кластеризации, как описано в шаге 1.3, то часть строки индекса должна быть зарезервирована для битовой карты NULL.
    Index_Null_Bitmap = 2 + ((количество ключевых столбцов, которые могут содержать значения NULL + 7) / 8)
    Следует использовать только целую часть предшествующего выражения. Остаток должен быть отброшен.
    Если нет ключевых столбцов, которые могут содержать неопределенные значения, то установите параметр Index_Null_Bitmap в 0.
  5. Вычислите размер данных переменной длины.
    Если есть столбцы переменной длины в ключе индекса, включающие какие-либо необходимые кластеризованные индексные ключевые столбцы, определите, сколько пространства используется для хранения этих столбцов в строке индекса:
    Variable_Key_Size* = 2 + (Num_Variable_Key_Cols x 2) + *Max_Var_Key_Size Формула исходит из предположения о том, что все столбцы переменной длины заполнены на 100%. Если предполагается, что будет использовано меньше места для столбца переменной длины, то можно изменить значение Max_Var_Key_Size в процентах для более точного подсчета размера всей таблицы.
    Если в таблице нет столбцов переменной длины, установите параметр Variable_Data_Size в 0.
  6. Рассчитайте размер индексной строки:
    Index_Row_Size = Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1 (служебный для заголовка индексной строки) + 6 (для указателя идентификатора страницы-потомка)
  7. Вычислите количество строк индекса на страницу (8 096 свободных байт на страницу):
    Index_Rows_Per_Page = 8096 / (Index_Row_Size + 2)
    Так как индексная строка не может переходить с одной страницы на другую, общее количество строк на страницу необходимо округлить до ближайшего меньшего целого значения. Значение 2 в формуле соответствует записи строки в массиве областей памяти страницы.
  8. Рассчитайте количество уровней в индексе:
    Levels = 1 + log Index_Rows_Per_Page (Num_Rows / Index_Rows_Per_Page)
    Учтите, что это значение не включает конечный уровень некластеризованного индекса.
  9. Вычислите количество страниц в индексе:
    Число_индексных_стр = ?Уровень (Индексных_строк_на_стр)Уровень — 1
    где 1 <= Level <= Levels
    В качестве простого примера рассмотрите индекс, где общее количество строк индекса, необходимых на конечном уровне, равно 1000, а на одну страницу могут войти 10 строк индекса. Это означает, что для хранения 1000 строк требуется 100 страниц. Следующий уровень индекса должен хранить 100 строк. Это означает, что ему необходимо 10 страниц. Последний уровень индекса должен хранить 10 строк. Это означает, что ему необходима 1 страница. Использование этих чисел в предыдущих формулах предоставляет следующее.
    Высота = 1 + log10 (1000 / 10) = 3
    Число_индексных_стр = (10)3-1 + (10)2-1 + (10)1-1 = 111, что составляет число страниц, описанное в данном примере.
  10. Рассчитайте размер индекса (всего 8 192 байт на страницу):
    Index_Space_Used* = 8192 x *Num_Index_Pages

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

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

  1. Укажите количество столбцов переменой и фиксированной длины на конечном уровне и объем необходимого места для их хранения.
    ms190620.note(ru-ru,SQL.90).gifПримечание.
    SQL Server 2005 вводит возможность по расширению некластеризованного индекса включением неключевых столбцов в дополнение к ключевым столбцам индекса. Эти дополнительные столбцы сохраняются только на конечном уровне некластеризованного индекса. Дополнительные сведения см. в разделе Создание индексов с включенными столбцами.
    ms190620.note(ru-ru,SQL.90).gifПримечание.
    SQL Server 2005 вводит возможность совмещать столбцы varchar, nvarchar, varbinary или sql_variant, что приводит к превышению установленной ширины таблицы в 8 060 байт. Длина каждого из этих столбцов должна быть в пределах 8 000 байт для varchar, varbinary, или sql_variant столбца или 4 000 байт для nvarchar столбцов. Тем не менее их общая ширина в таблице может превышать предел в 8 060 байт. Это также применимо к конечным строкам некластеризованного индекса, которые включают столбцы. Дополнительные сведения см. в разделе Превышающие размер страницы данные строки, превышающие 8 КБ.
    Если некластеризованный индекс не имеет никаких включенных столбцов, используйте значения из шага 1, включая любые модификации, определенные в шаге 1.3:

Num_Leaf_Cols* = *Num_Key_Cols Объем_фикс_листьев* = *Объем_фикс_ключей Число_перем_столбцов_листьев* = *Число_перем_ключ_столбцов Макс_объем_перем_листьев* = *Макс_объем_перем_ключей Если некластеризованный индекс имеет включенные столбцы, добавьте соответствующие значения к значениям из шага 1, включая любые модификации из шага 1.3. Размер столбца зависит от типа данных и длины. Дополнительные сведения см. в разделе Типы данных (компонент Database Engine).
Num_Leaf_Cols = Num_Key_Cols + количество включенных столбцов
Объем_фикс_листьев = Объем_фикс_ключей + общий размер в байтах всех включенных столбцов постоянной длины
Число_перем_столбцов_листьев = Число_перем_ключ_столбцов + количество включенных столбцов переменной длины
Макс_объем_перем_листьев = Макс_объем_перем_ключей + максимальный размер в байтах включенных столбцов переменной длины
2. Учтите место для указателя на строку данных.
Если некластеризованный индекс является неуникальным, то дополнительное место для указателя на строку данных уже было рассмотрено в шаге 1.3 и никаких дополнительных модификаций не требуется. Переходите к следующему шагу.
Если некластеризованный индекс уникален, указатель на строку данных должен быть учтен во всех строках на конечном уровне.
Если некластеризованный индекс является индексом по куче, указателем на строку данных служит RID кучи (размером 8 байт).
Num_Leaf_Cols = Num_Leaf_Cols + 1
Число_перем_столбцов_листьев = Число_перем_столбцов_листьев + 1
Макс_объем_перем_листьев = Макс_объем_перем_листьев +8
Если некластеризованный индекс строится поверх кластеризованного, указателем строки данных является ключ кластеризации. Столбцы, которые должны быть объединены с ключом некластеризованного индекса — это те столбцы в ключе кластеризации, которые еще не присутствуют в наборе ключевых столбцов некластеризованного индекса.
Num_Leaf_Cols = Num_Leaf_Cols + количество ключевых столбцов кластеризации, не находящихся в наборе ключевых столбцов некластеризованного индекса (+ 1 если кластеризованный индекс не является уникальным)
Объем_фикс_листьев = Объем_фикс_листьев + число столбцов ключа кластеризации фиксированной длины, не вошедших в набор столбцов ключа некластеризованного индекса
Число_перем_столбцов_листьев = Число_перем_столбцов_листьев + число столбцов ключа кластеризации переменной длины, не вошедших в набор столбцов ключа некластеризованного индекса (+ 1, если кластеризованный индекс неуникален)
Макс_объем_перем_листьев = Макс_объем_перем_листьев + размер в байтах столбцов ключа кластеризации переменной длины, не вошедших в набор столбцов ключа некластеризованного индекса (+ 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 Эта формула исходит из предположения, что все столбцы переменной длины заполнены на 100%. Если предполагается, что для хранения столбца переменной длины будет использовано меньше места, то для более точного подсчета общего размера таблицы можно задать значение Макс_объем_перем_листьев как процент от максимально возможной длины.
Если в таблице нет столбцов переменной ширины, установите параметр Variable_Leaf_Size в 0.
5. Рассчитайте размер индексной строки:
Leaf_Row_Size = Fixed_Leaf_Size + Variable_Leaf_Size + Leaf_Null_Bitmap + 1 (дополнительный для заголовка строки индекса) + 6 (для указателя идентификатора страницы-потомка)
6. Вычислите количество индексных строк на страницу (8 096 свободных байт на страницу):
Leaf_Rows_Per_Page = 8096 / (Leaf_Row_Size + 2)
Так как индексная строка не может переходить с одной страницы на другую, общее количество строк на страницу необходимо округлить до ближайшего меньшего целого значения. Значение 2 в формуле соответствует записи строки в массиве слота.
7. Рассчитайте количество зарезервированных свободных строк страницы на основе указанного параметра коэффициента заполнения fill factor:
Free_Rows_Per_Page = 8096 x ((100 - Fill_Factor) / 100) / (Leaf_Row_Size + 2)
Коэффициент заполнения при вычислении должен быть целым значением, а не процентным соотношением. Так как строки не переходят с одной страницы на другую, общее количество строк на страницу необходимо округлить до ближайшего меньшего целого значения. При увеличении коэффициента заполнения на каждой странице будет сохранено больше данных и, соответственно, потребуется меньше страниц. Значение 2 в формуле соответствует записи строки в массиве слота.
8. Вычислите количество страниц, необходимое для хранения всех строк:
Num_Leaf_Pages = Num_Rows / (Leaf_Rows_Per_Page - Free_Rows_Per_Page)
Количество рассчитанных страниц должно быть округлено в большую сторону до ближайшего целого значения.
9. Вычислите размер индекса (всего 8 192 байт на страницу):
Leaf_Space_Used* = 8192 x *Num_Leaf_Pages

Шаг 3. Просуммируйте рассчитанные значения

Суммируем результаты двух предыдущих шагов.

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

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

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

См. также

Основные понятия

Правила проектирования кластеризованного индекса
Создание индексов (компонент Database Engine)
Правила проектирования некластеризованных индексов
Предполагаемый размер таблицы
Оценка размера кластеризованного индекса
Оценка размера кучи

Другие ресурсы

Оценка размера базы данных

Справка и поддержка

Получение помощи по SQL Server 2005