Option SORT_IN_TEMPDB pour les indexSORT_IN_TEMPDB Option For Indexes

S’APPLIQUE À : ouiSQL Server ouiAzure SQL Database nonAzure Synapse Analytics (SQL DW) nonParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Quand vous créez ou reconstruisez un index, vous pouvez, en affectant la valeur ON à l’option SORT_IN_TEMPDB, demander au Moteur de base de données SQL ServerSQL Server Database Engine d’utiliser la base de données tempdb pour stocker les résultats de tri intermédiaires qui sont utilisés pour générer l’index.When you create or rebuild an index, by setting the SORT_IN_TEMPDB option to ON you can direct the Moteur de base de données SQL ServerSQL Server Database Engine to use tempdb to store the intermediate sort results that are used to build the index. Bien que cette option augmente la quantité d’espace disque temporaire utilisé pour la création d’un index, elle peut réduire le temps nécessaire à la création ou à la reconstruction d’un index lorsque la base de données tempdb ne se trouve pas sur le même ensemble de disques que la base de données utilisateur.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. Pour plus d’informations sur tempdb, consultez Configurer l’option de configuration Création d’index en mémoire.For more information about tempdb, see Configure the index create memory Server Configuration Option.

Phases de la construction d'un indexPhases of Index Building

La construction d'un index par le Moteur de base de donnéesDatabase Engine comprend les phases suivantes :As the Moteur de base de donnéesDatabase Engine builds an index, it goes through the following phases:

  • Tout d'abord, le Moteur de base de donnéesDatabase Engine analyse les pages de données de la table de base pour récupérer les valeurs des clés et construire une ligne d'index de niveau feuille pour chaque ligne de données.The Moteur de base de donnéesDatabase Engine first scans the data pages of the base table to retrieve key values and builds an index leaf row for each data row. Lorsque les tampons de tri internes ont été remplis avec des entrées d'index de niveau feuille, les entrées sont triées et écrites sur le disque sous forme d'exécution triée intermédiaire.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. Le Moteur de base de donnéesDatabase Engine reprend ensuite l'analyse des pages de données jusqu'à ce que les tampons de tri soient de nouveau remplis.The Moteur de base de donnéesDatabase Engine then resumes the data page scan until the sort buffers are again filled. Ce modèle d'analyse de plusieurs pages de données, suivi d'un tri et d'une écriture d'exécution triée, se poursuit jusqu'à ce que toutes les lignes de la table de base de données aient été traitées.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.

    Dans un index cluster, les lignes du niveau feuille de l'index sont les lignes de données de la table, c'est pourquoi les tris intermédiaires contiennent toutes les lignes de données.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. Dans un index non-cluster, les lignes de niveau feuille peuvent contenir des colonnes non-clés, mais sont généralement plus petites que dans un index cluster.In a nonclustered index, the leaf rows may contain nonkey columns, but are generally smaller than a clustered index. Si les clés d'index sont longues, ou si l'index contient plusieurs colonnes non-clés, un tri non-cluster peut retourner beaucoup de lignes.If the index keys are large, or there are several nonkey columns included in the index, a nonclustered sort run can be large. Pour plus d’informations sur l’inclusion de colonnes non-clés, consultez Créer des index avec colonnes incluses.For more information about including nonkey columns, see Create Indexes with Included Columns.

  • Le Moteur de base de donnéesDatabase Engine fusionne les tris des lignes du niveau feuille de l'index en un flux unique trié.The Moteur de base de donnéesDatabase Engine merges the sorted runs of index leaf rows into a single, sorted stream. Le composant de fusion et tri du Moteur de base de donnéesDatabase Engine commence par la première page de chaque tri, recherche la clé la plus faible dans toutes les pages et passe cette ligne feuille au composant de création d'index.The sort merge component of the Moteur de base de donnéesDatabase 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. La clé la plus faible suivante est ensuite traitée, puis la suivante et ainsi de suite.The next lowest key is processed, and then the next, and so on. Lorsque la dernière ligne feuille de l'index est extraite d'une page d'exécution triée, le processus passe à la page suivante à partir de cette exécution triée.When the last leaf index row is extracted from a sort run page, the process shifts to the next page from that sort run. Lorsque toutes les pages d'une extension à exécution triée ont été traitées, l'extension est libérée.When all the pages in a sort run extent have been processed, the extent is freed. Lorsque chaque ligne feuille de l'index est passée au composant de création d'index, elle est placée dans une page feuille de l'index dans le tampon.As each leaf index row is passed to the index create component, it is included in a leaf index page in the buffer. Chaque page feuille est écrite au moment de son remplissage.Each leaf page is written as it is filled. Lors de l'écriture des pages de niveau feuille, le Moteur de base de donnéesDatabase Engine construit également les niveaux supérieurs de l'index.As leaf pages are written, the Moteur de base de donnéesDatabase Engine also builds the upper levels of the index. Chaque page d'index de niveau supérieur est écrite lorsqu'elle est remplie.Each upper level index page is written when it is filled.

option SORT_IN_TEMPDBSORT_IN_TEMPDB Option

Lorsque l'option SORT_IN_TEMPDB est désactivée, ce qui constitue l'option par défaut, les tris sont stockés dans le groupe de fichiers de destination.When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the destination filegroup. Pendant la première phase de création de l'index, les lectures des pages de la table de base et les écritures des exécutions triées déplacent les têtes de lecture-écriture du disque d'une zone du disque à l'autre.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. Les têtes se trouvent dans la zone de la page de données lors de l'analyse des pages de données.The heads are in the data page area as the data pages are scanned. Elles passent dans une zone d'espace libre lorsque les tampons de tri se remplissent et que le tri en cours doit être écrit sur le disque, puis reviennent sur la zone de la page de données lorsque l'analyse des pages de la table reprend.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. Le mouvement de la tête de lecture-écriture est plus important dans la deuxième phase.The read/write head movement is greater in the second phase. À ce stade, le processus de tri alterne généralement les lectures de chaque zone d'exécution triée.At that time the sort process is typically alternating reads from each sort run area. Les exécutions triées et les nouvelles pages d'index sont construites dans le groupe de fichiers de destination.Both the sort runs and the new index pages are built in the destination filegroup. Ainsi, lorsque le Moteur de base de donnéesDatabase Engine répartit les lectures entre les différents tris, il doit en même temps accéder périodiquement aux extensions d'index pour écrire les nouvelles pages d'index à mesure qu'elles sont remplies.This means that at the same time the Moteur de base de donnéesDatabase 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.

Si l’option SORT_IN_TEMPDB est activée et que tempdb ne se trouve pas dans le même ensemble de disques que le groupe de fichiers de destination, les lectures des pages de données pendant la première phase ne sont pas effectuées sur le même disque que les écritures dans la zone de travail de tri dans 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. Cela signifie que les lectures des clés de données se poursuivent davantage en série sur le disque, tout comme les écritures sur le disque tempdb et les écritures qui construisent l’index final.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. Même si d'autres utilisateurs utilisent la base de données et accèdent à des adresses de disque séparées, le modèle global de lecture et d'écriture est plus efficace lorsque SORT_IN_TEMPDB est spécifié que lorsqu'il ne l'est pas.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.

L'option SORT_IN_TEMPDB peut améliorer la contiguïté des extensions d'index, surtout si l'opération CREATE INDEX n'est pas traitée en parallèle.The SORT_IN_TEMPDB option may improve the contiguity of index extents, especially if the CREATE INDEX operation is not being processed in parallel. Les extensions de la zone de travail de tri sont libérées de façon quelque peu aléatoire par rapport à leur emplacement dans la base de données.The sort work area extents are freed on a somewhat random basis with regard to their location in the database. Si les zones de travail de tri sont contenues dans le groupe de fichiers de destination, les extensions qui se libèrent peuvent être acquises par les demandes d'extensions pour le stockage de la structure de l'index pendant sa construction.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. Ceci peut rendre les emplacements des extensions d'index aléatoires jusqu'à un certain point.This can randomize the locations of the index extents to a degree. Si les extensions de tri sont contenues séparément dans tempdb, la séquence selon laquelle elles sont libérées n’a aucune incidence sur l’emplacement des extensions d’index.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. En outre, lorsque les tris intermédiaires sont stockés dans tempdb et pas dans le groupe de fichiers de destination, l’espace disponible dans ce dernier est plus important.Also, when the intermediate sort runs are stored in tempdb instead of the destination filegroup, there is more space available in the destination filegroup. Cela augmente les chances de contiguïté des extensions d'index.This increases the chances that index extents will be contiguous.

L'option SORT_IN_TEMPDB affecte uniquement l'instruction en cours.The SORT_IN_TEMPDB option affects only the current statement. Aucune métadonnée n’enregistre le fait que l’index a ou n’a pas été trié dans tempdb.No metadata records that the index was or was not sorted in tempdb. Par exemple, si vous créez un index non-cluster avec l'option SORT_IN_TEMPDB et que, par la suite, vous créez un index cluster sans cette option, le Moteur de base de donnéesDatabase Engine n'utilise pas l'option lorsqu'il recrée l'index cluster.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 Moteur de base de donnéesDatabase Engine does not use the option when it re-creates the nonclustered index.

Notes

Si l'opération de tri n'est pas nécessaire ou peut avoir lieu dans la mémoire, l'option SORT_IN_TEMPDB est ignorée.If a sort operation is not required or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

Espace disque nécessaireDisk Space Requirements

Lorsque l’option SORT_IN_TEMPDB est activée, vous devez avoir suffisamment d’espace libre dans tempdb pour contenir les tris intermédiaires, et suffisamment d’espace libre dans le groupe de fichiers de destination pour contenir le nouvel index.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. L'instruction CREATE INDEX échoue s'il n'y a pas suffisamment d'espace libre et si, pour une raison quelconque, les bases de données ne peuvent pas croître automatiquement pour acquérir plus d'espace (notamment s'il n'y a pas d'espace sur le disque ou si la fonction de croissance automatique est désactivée).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.

Si l'option SORT_IN_TEMPDB n'est pas activée, l'espace libre disponible dans le groupe de fichiers de destination doit correspondre environ à la taille de l'index final.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. Pendant la première phase, les exécutions triées sont construites et nécessitent environ la même quantité d'espace que l'index final.During the first phase, the sort runs are built and require about the same amount of space as the final index. Pendant la deuxième phase, chaque extension comportant une exécution triée est libérée après avoir été traitée.During the second phase, each sort run extent is freed after it has been processed. Cela signifie que les extensions de tri sont libérées à peu près à la même vitesse que des extensions sont acquises pour le stockage des pages de l'index final et, de ce fait, l'espace nécessaire global ne dépasse pas beaucoup la taille de l'index final.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. Ceci présente toutefois l'inconvénient suivant : si la quantité d'espace libre est très proche de la taille de l'index final, le Moteur de base de donnéesDatabase Engine tend à réutiliser les extensions de tri très rapidement après leur libération.One side effect of this is that if the amount of free space is very close to the size of the final index, the Moteur de base de donnéesDatabase Engine will generally reuse the sort run extents very quickly after they are freed. Puisque ces dernières sont libérées d'une façon quelque peu aléatoire, la continuité des extensions d'index dans ce scénario est réduite.Because the sort run extents are freed in a somewhat random manner, this reduces the continuity of the index extents in this scenario. Lorsque l'option SORT_IN_TEMPDB est désactivée, la continuité des extensions d'index est améliorée s'il y a, dans le groupe de fichiers de destination, suffisamment d'espace libre pour permettre l'allocation des extensions d'index à partir d'un pool contigu et non à partir des extensions de tri qui viennent d'être libérées.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.

Lorsque vous créez un index non-cluster, vous devez respecter les règles suivantes en matière d'espace libre :When you create a nonclustered index, you must have available as free space:

  • Si l’option SORT_IN_TEMPDB est activée, il doit y avoir suffisamment d’espace libre dans tempdb pour stocker les tris, et suffisamment d’espace dans le groupe de fichiers de destination pour stocker la structure finale de l’index.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. Les exécutions triées contiennent les lignes feuilles de l'index.The sort runs contain the leaf rows of the index.

  • Si l'option SORT_IN_TEMPDB n'est pas activée, l'espace libre dans le groupe de fichiers de destination doit être suffisamment grand pour stocker la structure finale de l'index.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. La continuité des extensions d'index peut être améliorée lorsque l'espace libre est important.The continuity of the index extends may be improved if more free space is available.

Lorsque vous créez un index cluster sur une table qui n'a pas d'index non-cluster, vous devez respecter les règles suivantes en matière d'espace libre :When you create a clustered index on a table that does not have nonclustered indexes, you must have available as free space:

  • Si l’option SORT_IN_TEMPDB est activée, il doit y avoir suffisamment d’espace libre dans tempdb pour stocker les tris.If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs. Ceux-ci comprennent les lignes de données de la table.These include the data rows of the table. Il doit y avoir suffisamment d'espace libre dans le groupe de fichiers de destination pour stocker la structure de l'index final.There must be sufficient free space in the destination filegroup to store the final index structure. Ceci comprend les lignes de données de la table et l'arborescence binaire de l'index.This includes the data rows of the table and the index B-tree. Il se peut que vous deviez ajuster l'estimation en fonction de facteurs tels que le fait d'avoir une taille de clé élevée ou un faible taux de remplissage.You may have to adjust the estimate for factors such as having a large key size or a fill factor with a low value.

  • Si l'option SORT_IN_TEMPDB n'est pas activée, l'espace libre dans le groupe de fichiers de destination doit être suffisamment grand pour stocker la table finale.If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table. Ceci comprend la structure de l'index.This includes the index structure. La continuité de la table et des extensions d'index peut être améliorée si l'espace libre disponible est plus important.The continuity of the table and index extents may be improved if more free space is available.

Lorsque vous créez un index cluster sur une table qui a des index non-cluster, vous devez respecter les règles suivantes en matière d'espace libre :When you create a clustered index on a table that has nonclustered indexes, you must have available as free space:

  • Si l’option SORT_IN_TEMPDB est activée, il doit y avoir suffisamment d’espace libre dans tempdb pour stocker la collection de tris pour le plus grand index, généralement l’index cluster, et suffisamment d’espace dans le groupe de fichiers de destination pour stocker les structures finales de tous les index.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. Ceci comprend l'index cluster qui contient les lignes de données de la table.This includes the clustered index that contains the data rows of the table.

  • Si l'option SORT_IN_TEMPDB n'est pas activée, l'espace libre dans le groupe de fichiers de destination doit être suffisamment grand pour stocker la table finale.If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table. Ceci comprend les structures de tous les index.This includes the structures of all the indexes. La continuité de la table et des extensions d'index peut être améliorée si l'espace libre disponible est plus important.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)

Réorganiser et reconstruire des indexReorganize and Rebuild Indexes

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

Configurer l’option de configuration Création d’index en mémoire.Configure the index create memory Server Configuration Option

Espace disque nécessaire pour les opérations DDL d'indexDisk Space Requirements for Index DDL Operations