Instructions pour les opérations d’index en ligne

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de données SQL dans Microsoft Fabric

Lorsque vous effectuez des opérations en ligne sur les index, les directives suivantes s'appliquent :

• Les index cluster doivent être créés, reconstruits ou supprimés hors connexion quand la table sous-jacente contient les types de données LOB (Large OBject) suivants : image, ntextet text.

• Les index non-cluster peuvent être créés en ligne lorsque la table comporte des colonnes utilisant les types de données LOB, mais aucune de ces colonnes n’est utilisée dans la définition d’index en tant que colonnes clés ou colonnes incluses.

• Des index de tables temporaires locales ne peuvent pas être créés, reconstruits ou supprimés en ligne. Cette restriction ne s'applique pas aux index des tables temporaires globales.

• Vous pouvez démarrer une opération d’index en ligne en tant qu’opération pouvant être reprise à l’aide de la RESUMABLE clause CREATE INDEX ou ALTER INDEX. Une opération d’index pouvant être reprise peut redémarrer après une défaillance inattendue, un basculement de base de données ou une ALTER INDEX PAUSE commande et continuer à partir de l’endroit où elle a été interrompue.

Le tableau suivant présente les opérations d’index réalisables en ligne, les index qui sont exclus de ces opérations en ligne et les restrictions d’index pouvant être reprises. Des restrictions supplémentaires sont également incluses.

La table sous-jacente ne peut pas être modifiée, tronquée ou supprimée pendant qu’une opération d’index en ligne est en cours.

Le paramètre de l’option online (ON ou OFF) spécifié lors de la création ou de la suppression d’un index cluster est appliqué à tous les index non cluster qui doivent être reconstruits. Par exemple, si l’index clusterisé est créé en ligne à l’aide de CREATE INDEX WITH DROP_EXISTING, ONLINE = ON, tous les index non clusterisés associés sont également recréés en ligne.

Lors de la création ou de la reconstruction d’un index UNIQUE en ligne, le générateur d’index et une transaction utilisateur simultanée peuvent tenter d’insérer la même clé, enfreignant ainsi la condition d’unicité. Si une ligne entrée par un utilisateur est insérée dans le nouvel index (cible) avant le déplacement de la ligne d’origine de la table source dans le nouvel index, l’opération en ligne sur l’index échoue.

Même si ce cas de figure est rare, l'opération en ligne sur l'index peut provoquer un blocage lorsqu'elle interagit avec les mises à jour de base de données en raison d'activités d'un utilisateur ou d'une application. Dans ces rares cas, l’activité de l’utilisateur ou de l’application est sélectionnée comme victime d’interblocage.

Vous pouvez exécuter sur les index des opérations DDL simultanées en ligne sur la même table ou sur la même vue uniquement lors de la création de plusieurs nouveaux index non cluster ou de la réorganisation d’index non cluster. Toutes les autres opérations en ligne sur les index exécutées en même temps échouent. Par exemple, vous ne pouvez pas créer un nouvel index en ligne tout en reconstruisant en ligne un index sur la même table.

Une opération en ligne ne peut pas être effectuée lorsqu’un index contient une colonne du type d’objet volumineux, et la même transaction apporte des modifications de données avant le démarrage de l’opération d’index en ligne. Pour contourner ce problème, déplacez l’opération d’index en ligne en dehors de la transaction ou déplacez-la avant toute modification des données dans la même transaction.

Considérations relatives à l’espace disque

Les opérations d’index en ligne nécessitent plus d’espace disque que les opérations d’index hors connexion.

• Lors des opérations de création et de reconstruction d’index, de l’espace additionnel est requis pour pouvoir créer (ou reconstruire) l’index. En règle générale, cet espace supplémentaire est identique à l’espace actuel occupé par l’index, mais il peut être supérieur ou plus petit en fonction de la compression utilisée dans l’index actuel ou reconstruit.

• De plus, de l’espace disque est requis pour l’index de mappage temporaire. Cet index temporaire est utilisé dans les opérations en ligne sur les index qui créent, reconstruisent ou suppriment un index cluster.

• La suppression d’un index cluster en ligne nécessite autant d’espace que la création (ou la reconstruction) d’un index cluster en ligne.

Pour plus d’informations, consultez Espace disque nécessaire pour les opérations DLL d’index.

Considérations relatives aux performances

Bien que les opérations d’index en ligne autorisent l’activité de mise à jour utilisateur simultanée, les opérations d’index peuvent prendre plus de temps si l’activité de mise à jour est lourde. En général, les opérations d’index en ligne sont plus lentes que leurs équivalents hors connexion, quel que soit le niveau d’activité de mise à jour.

Étant donné que les structures source et cible sont conservées pendant l’opération d’index en ligne, l’utilisation des ressources pour les transactions d’insertion, de mise à jour et de suppression est augmentée, potentiellement doublée. Il pourrait s'ensuivre une dégradation des performances et une utilisation plus intense des ressources, en particulier du temps processeur, pendant l'opération d'index. Les opérations en ligne sur les index sont intégralement enregistrées dans le journal.

Même si les opérations en ligne sont préférables, vous devez évaluer votre environnement et les conditions spécifiques requises. Il peut être plus approprié d’exécuter hors connexion des opérations sur les index. Ce faisant, les utilisateurs disposent d’un accès restreint aux données pendant l’opération, mais l’opération est réalisée plus vite et consomme moins de ressources.

Sur les ordinateurs multiprocesseurs exécutant SQL Server 2016 (13.x) et versions ultérieures, les opérations d’index peuvent utiliser le parallélisme pour effectuer les opérations d’analyse et de tri associées à l’instruction d’index. Vous pouvez utiliser l’option MAXDOP d’index pour contrôler le degré de parallélisme de l’opération d’index en ligne. De cette manière, vous pouvez équilibrer les ressources utilisées par l’opération d’index avec les ressources des utilisateurs simultanés. Pour plus d’informations, consultez Configurer des opérations d’index parallèles. Pour plus d’informations sur les éditions de SQL Server qui prennent en charge les opérations d’index parallèles, consultez Éditions et fonctionnalités prises en charge de SQL Server 2022.

Faites attention lorsque vous exécutez une opération d’index en ligne à l’intérieur d’une transaction utilisateur explicite, comme un bloc S, étant donné qu’un verrou partagé (Sch-M) ou un verrou de modification de schéma (BEGIN TRANSACTION ... COMMIT) est conservé dans la phase finale de l’opération d’index. Cela entraîne la tenue des verrous jusqu’à la fin de la transaction, ce qui peut bloquer d’autres charges de travail.

Si les verrous de pages d’index sont désactivés à l’aide de ALLOW_PAGE_LOCKS = OFF, la reconstruction d’un index en ligne peut augmenter la fragmentation de l’index lors de son exécution avec MAXDOP supérieur à 1. Pour plus d’informations, consultez Fonctionnement : La reconstruction d’index en ligne peut entraîner une fragmentation accrue.

Considérations relatives au journal des transactions

Les opérations d’index à grande échelle effectuées hors connexion ou en ligne peuvent générer de grandes quantités de journal des transactions. Cela est dû au fait que les opérations de reconstruction d’index hors connexion et en ligne sont intégralement enregistrées dans le journal. Pour garantir que l’opération d’index peut être restaurée, le journal des transactions ne doit pas être tronqué tant que l’opération d’index n’est pas terminée. Toutefois, le journal peut être sauvegardé pendant l’opération d’index.

Par conséquent, le journal des transactions doit disposer d’un espace suffisant pour stocker les transactions des opérations d’index et les éventuelles transactions utilisateur simultanées pendant la durée de l’opération d’index. Pour plus d’informations, consultez Espace disque du journal des transactions pour les opérations d’index.

Les opérations d’index en ligne ne provoquent pas une forte croissance du journal des transactions si la récupération accélérée de base de données (ADR) est activée.

Considérations relatives au stockage de versions persistantes

Si ADR est activé, la création ou la reconstruction d’un index volumineux en ligne peut augmenter considérablement la taille du magasin de versions persistantes (PVS) pendant que l’opération d’index est en cours. Assurez-vous que la base de données dispose d’un espace libre suffisant pour que PVS augmente. Pour plus d’informations, consultez Surveiller et résoudre les problèmes de récupération de base de données accélérée.

Considérations relatives aux index pouvant être repris

L’option RESUMABLE d’index pour CREATE INDEX et ALTER INDEX s’applique à SQL Server (ALTER INDEX à partir de SQL Server 2017 (14.x) et CREATE INDEX à partir de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance. Pour plus d’informations, consultez CREATE INDEX et ALTER INDEX.

Pour utiliser l’option RESUMABLE , vous devez également utiliser l’option ONLINE . Lorsque vous effectuez une création ou une reconstruction d’index pouvant être reprise, les instructions suivantes s’appliquent :

• Vous avez un meilleur contrôle sur la gestion, la planification et l’extension des fenêtres de maintenance des index. Vous pouvez suspendre et redémarrer une opération de création ou de reconstruction d’index à plusieurs reprises en fonction de vos fenêtres de maintenance.

• Vous pouvez récupérer des échecs de création ou de reconstruction d’index (par exemple, basculements de base de données ou manque d’espace disque) sans avoir à redémarrer l’opération d’index à partir du début.

• Quand une opération d’index est en pause, l’index d’origine et celui qui vient d’être créé nécessitent de l’espace disque et doivent être mis à jour durant les opérations DML.

• L’option SORT_IN_TEMPDB = ON n’est pas prise en charge.

• Les index désactivés ne sont pas pris en charge.

En règle générale, il n’existe aucune différence de performances entre les opérations d’index en ligne pouvant être reprise et non résumables. Pour les opérations reprenables CREATE INDEX, il existe une surcharge constante pouvant rendre les opérations sensiblement plus lentes pour de petites tables.

Lorsqu’une opération d’index pouvant être reprise est suspendue :

• Pour la plupart des charges de travail de lecture, la dégradation des performances est insignifiante.
• Pour les charges de travail volumineuses de mise à jour, vous pouvez rencontrer une dégradation du débit en fonction des spécificités de la charge de travail.

En règle générale, il n’existe aucune différence de qualité de défragmentation entre la création ou la reconstruction d’index en ligne avec et sans reprise.

Pendant qu’une opération d’index en ligne est suspendue, toute transaction nécessitant un verrou exclusif au niveau de la table (X) qui contient l’index suspendu échoue. Par exemple, cela peut se produire avec INSERT ... WITH (TABLOCK) des opérations. Dans ce cas, vous obtenez l’erreur 10637 :

Cannot perform this operation on '<object name>' with ID (<object ID>) as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

Pour résoudre l’erreur 10637, supprimez l’indicateur TABLOCK de votre transaction ou annulez l’interruption de l’opération d’index et attendez qu’elle se termine avant de tenter à nouveau votre transaction.

Options par défaut d’exécution en ligne

Vous pouvez définir des opérations d’index en ligne et pouvant être reprises comme options par défaut au niveau de la base de données en définissant les configurations délimitées à la base de données avec ELEVATE_ONLINE ou ELEVATE_RESUMABLE. Avec ces options par défaut, vous pouvez éviter de démarrer accidentellement une opération d’index hors connexion qui rend une table ou un index inaccessible pendant son exécution. Les deux options entraînent le fait que le moteur de base de données élève automatiquement certaines opérations d’index à l’exécution en ligne ou pouvant être reprise.

Vous pouvez définir l’une ou l’autre option comme FAIL_UNSUPPORTED, WHEN_SUPPORTEDou OFF. Vous pouvez définir des valeurs différentes pour ELEVATE_ONLINE et ELEVATE_RESUMABLE. Pour plus d’informations, consultez ALTER DATABASE SCOPED CONFIGURATION.

ELEVATE_ONLINE et ELEVATE_RESUMABLE s’appliquent uniquement aux instructions DDL qui prennent en charge la syntaxe online et resumable, respectivement. Par exemple, si vous tentez de créer un index XML avec ELEVATE_ONLINE = FAIL_UNSUPPORTED, l’opération s’exécute hors connexion, car les index XML ne prennent pas en charge l’option ONLINE . Les options n'affectent que les instructions DDL qui sont soumises sans spécifier une option ONLINE ou RESUMABLE. Par exemple, en soumettant une instruction avec l’option ONLINE = OFF oy RESUMABLE = OFF, l’utilisateur peut remplacer un paramètre FAIL_UNSUPPORTED et exécuter une instruction en mode hors connexion et/ou sans qu’elle puisse être reprise.

Contenu connexe

• Fonctionnement des opérations d’index en ligne
• Exécuter des opérations d’index en ligne
• ALTER INDEX (Transact-SQL)
• CRÉER UN INDEX (Transact-SQL)