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

Применимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL DatabaseПрименимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database

При создании или перестроении индекса можно установить параметр SORT_IN_TEMPDB в значение ON, чтобы компонент Компонент SQL Server Database EngineSQL Server Database Engine использовал базу данных tempdb для хранения промежуточных результатов сортировки, которые применяются для построения индекса.When you create or rebuild an index, by setting the SORT_IN_TEMPDB option to ON you can direct the Компонент SQL Server Database EngineSQL Server Database Engine to use tempdb to store the intermediate sort results that are used to build the index. Данный параметр увеличивает место на диске, временно занимаемое при построении индекса, но с его помощью можно сократить время, необходимое для создания или перестроения индекса, когда tempdb находится в наборе дисков, отличном от набора, используемого для размещения пользовательской базы данных.Although this option increases the amount of temporary disk space that is used to create an index, the option could reduce the time that is required to create or rebuild an index when tempdb is on a set of disks different from that of the user database. Дополнительные сведения о параметре tempdb см. в разделе Настройка параметра конфигурации сервера index create memory.For more information about tempdb, see Configure the index create memory Server Configuration Option.

Фазы построения индексаPhases of Index Building

Процесс построения индекса компонентой Компонент Database EngineDatabase Engine состоит из следующих фаз.As the Компонент Database EngineDatabase Engine builds an index, it goes through the following phases:

  • Сначала компонент Компонент Database EngineDatabase Engine просматривает страницы данных базовой таблицы, чтобы получить значение ключа, и строит конечную строку индекса для каждой строки данных.The Компонент Database EngineDatabase Engine first scans the data pages of the base table to retrieve key values and builds an index leaf row for each data row. Когда внутренние буферы сортировки заполняются конечными элементами индекса, эти элементы сортируются и записываются на диск как промежуточный проход сортировки.When the internal sort buffers have been filled with leaf index entries, the entries are sorted and written to disk as an intermediate sort run. Затем компонент Компонент Database EngineDatabase Engine возобновляет просмотр страниц данных, пока буфер сортировки не заполнится вновь.The Компонент Database EngineDatabase Engine then resumes the data page scan until the sort buffers are again filled. Процедура просмотра нескольких страниц данных с последующей сортировкой и записью результатов сортировки продолжается до тех пор, пока не будут обработаны все строки базовой таблицы.This pattern of scanning multiple data pages followed by sorting and writing a sort run continues until all the rows of the base table have been processed.

    В кластеризованном индексе конечные строки являются строками данных таблицы, поэтому промежуточная сортировка содержит все строки.In a clustered index, the leaf rows of the index are the data rows of the table; therefore, the intermediate sort runs contain all the data rows. В некластеризованном индексе конечные строки могут содержать неключевые столбцы, но в целом они меньше, чем кластеризованный индекс.In a nonclustered index, the leaf rows may contain nonkey columns, but are generally smaller than a clustered index. Если ключи индекса большие или в индекс входят несколько неключевых столбцов, то операция некластеризованной сортировки может быть большей.If the index keys are large, or there are several nonkey columns included in the index, a nonclustered sort run can be large. Дополнительные сведения о неключевых столбцах см. в разделе Create Indexes with Included Columns.For more information about including nonkey columns, see Create Indexes with Included Columns.

  • Компонент Компонент Database EngineDatabase Engine объединяет отсортированные потоки конечных строк индекса в единый отсортированный поток.The Компонент Database EngineDatabase Engine merges the sorted runs of index leaf rows into a single, sorted stream. Компонент объединенной сортировки компонента Компонент Database EngineDatabase Engine начинает с первой страницы каждой операции сортировки, отыскивает самый нижний ключ во всех страницах и передает эту конечную строку компоненту создания индекса.The sort merge component of the Компонент Database EngineDatabase Engine starts with the first page of each sort run, finds the lowest key in all the pages, and passes that leaf row to the index create component. Затем обрабатывается следующий индекс снизу, потом следующий и т. д.The next lowest key is processed, and then the next, and so on. Когда последняя конечная строка индекса извлекается из страницы операции сортировки, процесс переходит к следующей странице этой операции сортировки.When the last leaf index row is extracted from a sort run page, the process shifts to the next page from that sort run. Когда все страницы в операции сортировки обработаны, кластер страниц освобождается.When all the pages in a sort run extent have been processed, the extent is freed. Когда каждая конечная строка индекса пересылается в компонент создания индекса, она включается в конечную страницу индекса в буфере.As each leaf index row is passed to the index create component, it is included in a leaf index page in the buffer. Каждая конечная страница записывается по мере заполнения.Each leaf page is written as it is filled. По мере записи конечных страниц компонента Компонент Database EngineDatabase Engine также строит верхние уровни индекса.As leaf pages are written, the Компонент Database EngineDatabase Engine also builds the upper levels of the index. Каждая страница индекса верхнего уровня записывается по мере заполнения.Each upper level index page is written when it is filled.

SORT_IN_TEMPDB, параметрSORT_IN_TEMPDB Option

Если параметр SORT_IN_TEMPDB имеет значение OFF, устанавливаемое по умолчанию, то операции сортировки хранятся в целевой файловой группе.When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the destination filegroup. Во время первой фазы создания индекса чередующиеся операции чтения страниц базовой таблицы и записи операций сортировки перемещают головки чтения и записи диска из одной области диска в другую.During the first phase of creating the index, the alternating reads of the base table pages and writes of the sort runs move the disk read/write heads from one area of the disk to another. Головки находятся в области страницы данных, когда просматриваются страницы данных.The heads are in the data page area as the data pages are scanned. Они перемещаются в область свободного пространства, когда буферы сортировки заполняются и текущая операция сортировки записывается на диск, а затем перемещаются назад в область страницы данных при возобновлении просмотра табличной страницы.They move to an area of free space when the sort buffers fill and the current sort run has to be written to disk, and then move back to the data page area as the table page scan is resumed. Скорость головки на чтение-запись больше во второй фазе.The read/write head movement is greater in the second phase. На этом этапе процесса сортировки, как правило, чередуются операции чтения из каждой области сортировки.At that time the sort process is typically alternating reads from each sort run area. Как операции сортировки, так и новые страницы индекса строятся в целевой файловой группе.Both the sort runs and the new index pages are built in the destination filegroup. Это значит, что компонент Компонент Database EngineDatabase Engine одновременно распределяет операции чтения по операциям сортировки, при этом приходится периодически переходить к экстентам индекса для записи новых страниц индекса по мере их заполнения.This means that at the same time the Компонент Database EngineDatabase Engine is spreading reads across the sort runs, it has to periodically jump to the index extents to write new index pages as they are filled.

Если параметр SORT_IN_TEMPDB имеет значение ON и база данных tempdb размещена на ином наборе дисков, нежели целевая файловая группа, то чтение страниц данных в первой фазе выполняется не с того диска, на который производится запись в рабочую область сортировки tempdb.If the SORT_IN_TEMPDB option is set to ON and tempdb is on a separate set of disks from the destination filegroup, during the first phase, the reads of the data pages occur on a different disk from the writes to the sort work area in tempdb. Это значит, что считывание ключей данных с диска проводится в более последовательной манере, и операции записи на диск tempdb также в основном последовательные, как и операции записи при построении окончательного индекса.This means the disk reads of the data keys generally continue more serially across the disk, and the writes to the tempdb disk also are generally serial, as do the writes to build the final index. Даже если другие пользователи используют базу данных и обращаются по раздельным дисковым адресам, общая последовательность операций чтения и записи будет более эффективной, если задан параметр SORT_IN_TEMPDB.Even if other users are using the database and accessing separate disk addresses, the overall pattern of reads and writes are more efficient when SORT_IN_TEMPDB is specified than when it is not.

Благодаря параметру SORT_IN_TEMPDB может быть повышена непрерывность экстентов индекса, особенно если параллельно не обрабатывается операция CREATE INDEX.The SORT_IN_TEMPDB option may improve the contiguity of index extents, especially if the CREATE INDEX operation is not being processed in parallel. Экстенты рабочей области сортировки освобождаются довольно беспорядочно относительно их местоположения в базе данных.The sort work area extents are freed on a somewhat random basis with regard to their location in the database. Если рабочие области сортировки содержатся в целевой файловой группе, то по мере освобождения экстентов сортировки они могут быть задействованы по запросу для хранения структуры индекса в процессе ее построения.If the sort work areas are contained in the destination filegroup, as the sort work extents are freed, they can be acquired by the requests for extents to hold the index structure as it is built. Это может привести к некоторому разупорядочению местонахождений экстентов индексов.This can randomize the locations of the index extents to a degree. Если экстенты сортировки хранятся в tempdb раздельно, то последовательность их освобождения не влияет на расположение экстентов индекса.If the sort extents are held separately in tempdb, the sequence in which they are freed has no effect on the location of the index extents. Кроме того, если промежуточные операции сортировки хранятся в tempdb вместо целевой файловой группы, то в целевой файловой группе имеется больше доступного пространства.Also, when the intermediate sort runs are stored in tempdb instead of the destination filegroup, there is more space available in the destination filegroup. В результате увеличивается вероятность того, что экстенты индекса будут последовательными.This increases the chances that index extents will be contiguous.

Параметр SORT_IN_TEMPDB влияет только на текущую инструкцию.The SORT_IN_TEMPDB option affects only the current statement. В метаданных не отмечается, был ли индекс сортирован в tempdb.No metadata records that the index was or was not sorted in tempdb. Например, если создается некластеризованный индекс с использованием параметра SORT_IN_TEMPDB, а позднее создается кластеризованный индекс без указания этого параметра, то компонент Компонент Database EngineDatabase Engine не использует параметр при повторном создании некластеризованного индекса.For example, if you create a nonclustered index using the SORT_IN_TEMPDB option, and at a later time create a clustered index without specifying the option, the Компонент Database EngineDatabase Engine does not use the option when it re-creates the nonclustered index.

Примечание

Если выполнение сортировки не требуется или если сортировка может быть выполнена в памяти, параметр SORT_IN_TEMPDB пропускается.If a sort operation is not required or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

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

При установке параметра SORT_IN_TEMPDB в значение ON требуется достаточно свободного места на диске в базе данных tempdb , чтобы хранить запуски промежуточной сортировки, и достаточно места на диске в целевой файловой группе, чтобы хранить новый индекс.When you set the SORT_IN_TEMPDB option to ON, you must have sufficient free disk space available in tempdb to hold the intermediate sort runs, and enough free disk space in the destination filegroup to hold the new index. Выполнить инструкцию CREATE INDEX не удается, если свободное пространство недостаточно и по какой-то причине база данных не может автоматически задействовать дополнительное пространство, например нет места на диске или режим автоматического расширения отключен.The CREATE INDEX statement fails if there is insufficient free space and there is some reason the databases cannot autogrow to acquire more space, such as no space on the disk or autogrow is set to off.

Если параметр SORT_IN_TEMPDB имеет значение OFF, то размер свободного места на диске для целевой файловой группы должен быть примерно равным размеру окончательного индекса.If SORT_IN_TEMPDB is set to OFF, the available free disk space in the destination filegroup must be roughly the size of the final index. Во время первой фазы строятся операции сортировки, которым требуется примерно такое же пространство, как и окончательному индексу.During the first phase, the sort runs are built and require about the same amount of space as the final index. Во время второй фазы каждый экстент сортировки освобождается после обработки.During the second phase, each sort run extent is freed after it has been processed. Это значит, что экстенты сортировки освобождаются примерно с такой же частотой, с которой экстенты задействуются для хранения страниц окончательного индекса; поэтому общие требования к пространству незначительно превышают размера окончательного индекса.This means that sort run extents are freed at about the same rate at which extents are acquired to hold the final index pages; therefore, the overall space requirements do not greatly exceed the size of the final index. Побочным эффектом того, что размер свободного пространства очень близок к размеру окончательного индекса, является то, что компонент Компонент Database EngineDatabase Engine , как правило, очень быстро повторно использует освобождаемые экстенты сортировки.One side effect of this is that if the amount of free space is very close to the size of the final index, the Компонент Database EngineDatabase Engine will generally reuse the sort run extents very quickly after they are freed. Поскольку кластеры страниц сортировки освобождаются в произвольной манере, в результате увеличивается прерывистость экстентов индекса.Because the sort run extents are freed in a somewhat random manner, this reduces the continuity of the index extents in this scenario. Если значение параметра SORT_IN_TEMPDB равно OFF, то непрерывность экстентов индекса повышается при наличии свободного пространства для целевой файловой группы, которое можно выделить для экстентов индекса из непрерывного пула, а не из вновь освобожденных экстентов сортировки.If SORT_IN_TEMPDB is set to OFF, the continuity of the index extents is improved if there is sufficient free space available in the destination filegroup that the index extents can be allocated from a contiguous pool instead of from the freshly deallocated sort run extents.

При создании некластеризованного индекса необходимо иметь доступное свободное пространство:When you create a nonclustered index, you must have available as free space:

  • Если параметр SORT_IN_TEMPDB имеет значение ON, то в tempdb необходимо иметь свободное пространство, достаточное для хранения результатов операций сортировки, и свободное пространство в целевой файловой группе, достаточное для хранения структуры окончательного индекса.If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs, and sufficient free space in the destination filegroup to store the final index structure. Результаты операций сортировки содержат конечные строки индекса;The sort runs contain the leaf rows of the index.

  • если параметр SORT_IN_TEMPDB имеет значение OFF, то свободное пространство целевой файловой группы должно быть достаточным для сохранения структуры окончательного индекса.If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final index structure. При наличии большего объема свободного пространства может быть повышена степень непрерывности кластера страниц индекса.The continuity of the index extends may be improved if more free space is available.

При создании кластеризованного индекса таблицы, которая не имеет некластеризованных индексов, необходимо иметь свободное пространство:When you create a clustered index on a table that does not have nonclustered indexes, you must have available as free space:

  • Если параметр SORT_IN_TEMPDB имеет значение ON, то в tempdb должно быть достаточно свободного пространства для хранения результатов операций сортировки.If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs. К ним относятся строки данных таблицы.These include the data rows of the table. Необходимо свободное место на диске в целевой файловой группе, для хранения структуры окончательного индекса.There must be sufficient free space in the destination filegroup to store the final index structure. К ней относятся строки данных таблицы и сбалансированное дерево индекса.This includes the data rows of the table and the index B-tree. Иногда приходится корректировать оценку с учетом таких факторов, как большой размер ключа или коэффициент заполнения с низким значением;You may have to adjust the estimate for factors such as having a large key size or a fill factor with a low value.

  • если параметр SORT_IN_TEMPDB имеет значение OFF, с целью сохранения окончательной таблицы должно быть свободное пространство для целевой файловой группы.If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table. Это включает структуру индекса.This includes the index structure. При наличии большего объема свободного пространства может быть повышена степень непрерывности экстента таблицы и индекса.The continuity of the table and index extents may be improved if more free space is available.

При создании кластеризованного индекса таблицы, которая имеет некластеризованные индексы, необходимо иметь свободное пространство:When you create a clustered index on a table that has nonclustered indexes, you must have available as free space:

  • Если параметр SORT_IN_TEMPDB имеет значение ON, то свободное пространство tempdb должно быть достаточным для хранения коллекции операций сортировки для самого большого, обычно кластеризованного, индекса, а свободное пространство в целевой файловой группе должно быть достаточным для хранения окончательных структур всех индексов.If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the collection of sort runs for the largest index, typically the clustered index, and sufficient free space in the destination filegroup to store the final structures of all the indexes. Это включает кластеризованный индекс, который содержит строки данных таблицы;This includes the clustered index that contains the data rows of the table.

  • если параметр SORT_IN_TEMPDB имеет значение OFF, с целью сохранения окончательной таблицы должно быть свободное пространство для целевой файловой группы.If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table. Это включает структуры всех индексов.This includes the structures of all the indexes. При наличии большего объема свободного пространства может быть повышена степень непрерывности экстента таблицы и индекса.The continuity of the table and index extents may be improved if more free space is available.

CREATE INDEX (Transact-SQL)CREATE INDEX (Transact-SQL)

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

ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL)

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

Требования к месту на диске для DDL-операций индексаDisk Space Requirements for Index DDL Operations