Параметр SORT_IN_TEMPDB для индексов

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

При создании или перестроении индекса, установив для параметра SORT_IN_TEMPDB значение ON, можно указать ядру СУБД SQL Server использовать tempdb для хранения промежуточных результатов сортировки, применяемых при построении индекса. Данный параметр увеличивает место на диске, временно занимаемое при построении индекса, но с его помощью можно сократить время, необходимое для создания или перестроения индекса, когда tempdb находится в наборе дисков, отличном от набора, используемого для размещения пользовательской базы данных. Дополнительные сведения о параметре tempdbсм. в разделе Настройка параметра конфигурации сервера index create memory.

Фазы построения индекса

Как ядро СУБД создает индекс, он проходит через следующие этапы:

• Ядро СУБД сначала сканирует страницы данных базовой таблицы, чтобы получить ключевые значения и создать конечную строку индекса для каждой строки данных. Когда внутренние буферы сортировки заполнены элементами листового индекса, эти элементы сортируются и записываются на диск как промежуточная серия сортировки. Затем ядро СУБД возобновляет сканирование страницы данных, пока буферы сортировки не будут заполнены. Процедура просмотра нескольких страниц данных с последующей сортировкой и записью результатов сортировки продолжается до тех пор, пока не будут обработаны все строки базовой таблицы.

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

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

Параметр SORT_IN_TEMPDB

Если параметр SORT_IN_TEMPDB имеет значение OFF, устанавливаемое по умолчанию, то операции сортировки хранятся в целевой файловой группе. Во время первой фазы создания индекса чередующиеся операции чтения страниц базовой таблицы и записи операций сортировки перемещают головки чтения и записи диска из одной области диска в другую. Головки находятся в области страниц данных во время сканирования страниц данных. Они перемещаются в область свободного пространства, когда буферы сортировки заполняются и текущая операция сортировки записывается на диск, а затем перемещаются назад в область страницы данных при возобновлении просмотра табличной страницы. Перемещение читающей/записывающей головки больше во второй фазе. На этом этапе процесса сортировки, как правило, чередуются операции чтения из каждой области сортировки. Как операции сортировки, так и новые страницы индекса строятся в целевой файловой группе. Это означает, что в то время как компонент ядро СУБД распределяет операции чтения по сериям сортировки, ему также приходится периодически обращаться к экстентам индекса, чтобы записывать новые страницы индекса по мере их заполнения.

Если параметр SORT_IN_TEMPDB имеет значение ON и tempdb находится на отдельном наборе дисков по отношению к целевой файловой группе, то на первом этапе чтение страниц данных выполняется с одного диска, а запись в рабочую область сортировки в tempdb — на другой. Это значит, что считывание ключей данных с диска проводится в более последовательной манере, и операции записи на диск tempdb также в основном последовательные, как и операции записи при построении окончательного индекса. Даже если другие пользователи используют базу данных и обращаются по раздельным дисковым адресам, общая последовательность операций чтения и записи будет более эффективной, если задан параметр SORT_IN_TEMPDB.

Параметр SORT_IN_TEMPDB может улучшить смежность экстентов индекса, особенно если операция CREATE INDEX не выполняется параллельно. Экстенты рабочей области сортировки освобождаются в достаточно случайном порядке, независимо от их расположения в базе данных. Если рабочие области сортировки содержатся в целевой файловой группе, то по мере освобождения экстентов сортировки они могут быть задействованы по запросу для хранения структуры индекса в процессе ее построения. Это может привести к некоторому разупорядочению местонахождений экстентов индексов. Если экстенты сортировки хранятся в tempdbраздельно, то последовательность их освобождения не влияет на расположение экстентов индекса. Кроме того, если промежуточные операции сортировки хранятся в tempdb вместо целевой файловой группы, то в целевой файловой группе имеется больше доступного пространства. Это увеличивает вероятность того, что экстенты индекса будут смежными.

Параметр SORT_IN_TEMPDB влияет только на текущую инструкцию. В метаданных не отмечается, был ли индекс сортирован в tempdb. Например, если создать некластеризованный индекс с помощью параметра SORT_IN_TEMPDB, а затем создать кластеризованный индекс без указания параметра, ядро СУБД не использует этот параметр при повторном создании некластеризованного индекса.

Требования к месту на диске

При установке параметра SORT_IN_TEMPDB в значение ON требуется достаточно свободного места на диске в базе данных tempdb , чтобы хранить запуски промежуточной сортировки, и достаточно места на диске в целевой файловой группе, чтобы хранить новый индекс. Инструкция CREATE INDEX завершается ошибкой, если недостаточно свободного места и есть причина, по которой базы данных не могут автоматически увеличиться, чтобы получить больше места, например если на диске нет места или автоматическое увеличение отключено.

Если параметр SORT_IN_TEMPDB имеет значение OFF, то размер свободного места на диске для целевой файловой группы должен быть примерно равным размеру окончательного индекса. Во время первой фазы строятся операции сортировки, которым требуется примерно такое же пространство, как и окончательному индексу. Во время второй фазы каждый экстент сортировки освобождается после обработки. Это значит, что экстенты сортировки освобождаются примерно с такой же частотой, с которой экстенты задействуются для хранения страниц окончательного индекса; поэтому общие требования к пространству незначительно превышают размера окончательного индекса. Одним из побочных эффектов этого является то, что если объем свободного пространства очень близок к размеру окончательного индекса, ядро СУБД обычно повторно будет использовать экстенты выполнения сортировки очень быстро после их освобождения. Поскольку экстенты проходов сортировки освобождаются в довольно случайном порядке, это снижает непрерывность экстентов индекса в данном сценарии. Если для параметра SORT_IN_TEMPDB задано значение OFF, непрерывность экстентов индекса улучшается, если в целевой файловой группе имеется достаточно свободного места, чтобы экстенты индекса могли быть выделены из непрерывной области, а не из только что освобождённых экстентов прогона сортировки.

При создании некластеризованного индекса необходимо иметь доступное свободное пространство:

• Если параметр SORT_IN_TEMPDB имеет значение ON, то в tempdb необходимо иметь свободное пространство, достаточное для хранения результатов операций сортировки, и свободное пространство в целевой файловой группе, достаточное для хранения структуры окончательного индекса. Сегменты сортировки содержат листовые строки индекса.

• если параметр SORT_IN_TEMPDB имеет значение OFF, то свободное пространство целевой файловой группы должно быть достаточным для сохранения структуры окончательного индекса. Если доступно больше свободного пространства, непрерывность экстентов индекса может быть улучшена.

При создании кластеризованного индекса таблицы, которая не имеет некластеризованных индексов, необходимо иметь свободное пространство:

• Если параметр SORT_IN_TEMPDB имеет значение ON, то в tempdb должно быть достаточно свободного пространства для хранения результатов операций сортировки. К ним относятся строки данных таблицы. Необходимо свободное место на диске в целевой файловой группе, для хранения структуры окончательного индекса. Сюда входят строки данных таблицы и B-дерево индекса. Иногда приходится корректировать оценку с учетом таких факторов, как большой размер ключа или коэффициент заполнения с низким значением;

• Если параметр SORT_IN_TEMPDB имеет значение OFF, в целевой файловой группе должно быть достаточно свободного места для хранения итоговой таблицы. Это включает структуру индекса. При наличии большего объема свободного места можно повысить непрерывность экстентов таблицы и индекса.

При создании кластеризованного индекса таблицы, которая имеет некластеризованные индексы, необходимо иметь свободное пространство:

• Если параметр SORT_IN_TEMPDB имеет значение ON, то свободное пространство tempdb должно быть достаточным для хранения коллекции операций сортировки для самого большого, обычно кластеризованного, индекса, а свободное пространство в целевой файловой группе должно быть достаточным для хранения окончательных структур всех индексов. Это включает кластеризованный индекс, который содержит строки данных таблицы;

• Если для параметра SORT_IN_TEMPDB задано значение OFF, в целевой файловой группе должно быть достаточно свободного места для хранения конечной таблицы. Это включает структуры всех индексов. При наличии большего объема свободного пространства можно повысить непрерывность экстентов таблицы и индекса.

Связанные задачи

CREATE INDEX (Transact-SQL)

Реорганизация и перестроение индексов

Связанные материалы

ALTER INDEX (Transact-SQL)

Настройка параметра конфигурации сервера index create memory

Требования к свободному месту на диске для операций DDL над индексами