Option SORT_IN_TEMPDB pour les index

Article
08/23/2023

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance

Lorsque vous créez ou régénérez un index, en définissant l’option SORT_IN_TEMPDB sur ON, vous pouvez diriger le moteur de base de données SQL Server pour utiliser tempdb pour stocker les résultats de tri intermédiaires utilisés pour générer l’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. Pour plus d’informations sur tempdb, consultez Configurer l’option de configuration Création d’index en mémoire.

Phases de la construction d'un index

À mesure que le moteur de base de données génère un index, il passe par les phases suivantes :

Le moteur de base de données analyse d’abord les pages de données de la table de base pour récupérer les valeurs clés et génère une ligne feuille d’index pour chaque ligne de données. 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. Le moteur de base de données reprend ensuite l’analyse de la page de données jusqu’à ce que les mémoires tampons de tri soient à nouveau remplies. 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.

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. 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. 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. Pour plus d’informations sur l’inclusion de colonnes non-clés, consultez Créer des index avec colonnes incluses.
Le moteur de base de données fusionne les exécutions triées des lignes feuilles d’index en un seul flux trié. Le composant de fusion de tri du moteur de base de données commence par la première page de chaque exécution de tri, recherche la clé la plus basse dans toutes les pages et transmet cette ligne feuille au composant de création d’index. La clé la plus faible suivante est ensuite traitée, puis la suivante et ainsi de suite. 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. Lorsque toutes les pages d'une extension à exécution triée ont été traitées, l'extension est libérée. 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. Chaque page feuille est écrite au moment de son remplissage. À mesure que les pages feuilles sont écrites, le moteur de base de données génère également les niveaux supérieurs de l’index. Chaque page d'index de niveau supérieur est écrite lorsqu'elle est remplie.

option SORT_IN_TEMPDB

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. 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. Les têtes se trouvent dans la zone de la page de données lors de l'analyse des pages de données. 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. Le mouvement de la tête de lecture-écriture est plus important dans la deuxième phase. À ce stade, le processus de tri alterne généralement les lectures de chaque zone d'exécution triée. Les exécutions triées et les nouvelles pages d'index sont construites dans le groupe de fichiers de destination. Cela signifie qu’en même temps, le moteur de base de données répartit les lectures entre les exécutions de tri, il doit passer régulièrement aux étendues d’index pour écrire de nouvelles pages d’index à mesure qu’elles sont remplies.

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. 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. 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.

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. 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. 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. Ceci peut rendre les emplacements des extensions d'index aléatoires jusqu'à un certain point. 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. 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. Cela augmente les chances de contiguïté des extensions d'index.

L'option SORT_IN_TEMPDB affecte uniquement l'instruction en cours. Aucune métadonnée n’enregistre le fait que l’index a ou n’a pas été trié dans tempdb. Par exemple, si vous créez un index non cluster à l’aide de l’option SORT_IN_TEMPDB et, ultérieurement, créez un index cluster sans spécifier l’option, le moteur de base de données n’utilise pas l’option lorsqu’il recrée l’index non cluster.

Note

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.

Espace disque nécessaire

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. 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).

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. 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. Pendant la deuxième phase, chaque extension comportant une exécution triée est libérée après avoir été traitée. 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. L’un des effets secondaires est que si la quantité d’espace libre est très proche de la taille de l’index final, le moteur de base de données réutilise généralement les étendues d’exécution de tri très rapidement après leur libération. 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. 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.

Lorsque vous créez un index non-cluster, vous devez respecter les règles suivantes en matière d'espace libre :

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. Les exécutions triées contiennent les lignes feuilles de l'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. La continuité des extensions d'index peut être améliorée lorsque l'espace libre est important.

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 :

Si l’option SORT_IN_TEMPDB est activée, il doit y avoir suffisamment d’espace libre dans tempdb pour stocker les tris. Ceux-ci comprennent les lignes de données de la table. Il doit y avoir suffisamment d'espace libre dans le groupe de fichiers de destination pour stocker la structure de l'index final. Ceci comprend les lignes de données de la table et l'arborescence binaire de l'index. 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.
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. Ceci comprend la structure de l'index. La continuité de la table et des extensions d'index peut être améliorée si l'espace libre disponible est plus important.

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 :

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. Ceci comprend l'index cluster qui contient les lignes de données de la 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. Ceci comprend les structures de tous les index. La continuité de la table et des extensions d'index peut être améliorée si l'espace libre disponible est plus important.

CREATE INDEX (Transact-SQL)

Réorganiser et reconstruire des index

ALTER INDEX (Transact-SQL)

Configurer l’option de configuration Création d’index en mémoire.

Espace disque nécessaire pour les opérations DDL sur les index