Instructions pour les opérations d’index en ligne

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

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 non uniques peuvent être créés en ligne lorsque la table contient des types de données LOB, mais qu'aucune de ces colonnes n'est utilisée dans la définition de l'index en tant que colonne clé ou non-clé (incluse).
  • 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.
  • Les index peuvent être repris à partir de l’endroit où il s’est arrêté après une défaillance inattendue, un basculement de base de données ou une PAUSE commande. Consultez CREATE INDEX et ALTER INDEX.

Remarque

Les opérations d’index en ligne ne sont pas disponibles dans chaque édition de Microsoft SQL Server. Pour obtenir la liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Éditions et fonctionnalités prises en charge de SQL Server 2022.

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.

Opération en ligne sur l'index Index exclus Autres restrictions
ALTER INDEX REBUILD Index cluster désactivé ou vue indexée désactivée

Index XML

Index columnstore

Index de table temporaire locale
La spécification du mot clé ALL peut entraîner l’échec de l’opération lorsque la table contient un index exclu.

Des restrictions supplémentaires s'appliquent pour la reconstruction d'index désactivés. Pour plus d’informations, consultez Désactiver les index et contraintes.
CREATE INDEX Index XML

Index cluster unique de départ sur une vue

Index de table temporaire locale
CREATE INDEX WITH DROP_EXISTING Index cluster désactivé ou vue indexée désactivée

Index de table temporaire locale

Index XML
DROP INDEX Index désactivés.

Index XML

Index non cluster

Index de table temporaire locale
Plusieurs index ne peuvent pas être spécifiés dans une seule instruction.
ALTER TABLE ADD CONSTRAINT (PRIMARY KEY ou UNIQUE) Index de table temporaire locale

Index cluster
Une seule sous-clause est autorisée à la fois. Par exemple, vous ne pouvez pas ajouter et supprimer ou UNIQUE supprimer PRIMARY KEY des contraintes dans la même ALTER TABLE instruction.
ALTER TABLE DROP CONSTRAINT (PRIMARY KEY ou UNIQUE) Index cluster

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 d’option en ligne (ON ou OFF) spécifié lorsque vous créez ou supprimez un index cluster est appliqué à tous les index non cluster qui doivent être reconstruits. Par exemple, si l’index cluster est créé en ligne à l’aide CREATE INDEX WITH DROP_EXISTING, ONLINE=ONde , tous les index non cluster associés sont recréés en ligne également.

Lorsque vous créez ou régénérez un UNIQUE index en ligne, le générateur d’index et une transaction utilisateur simultanée peuvent essayer d’insérer la même clé, en violation de l’unicité. Si une ligne entrée par un utilisateur est insérée dans le nouvel index (cible) avant que la ligne d’origine de la table source soit déplacée vers le nouvel index, l’opération d’index en ligne é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, SQL Server Moteur de base de données sélectionne l’activité de l’utilisateur ou de l’application comme victime d’interblocage.

Vous pouvez effectuer des opérations DDL d’index en ligne simultanées sur la même table ou vue uniquement lorsque vous créez plusieurs index non cluster ou réorganisez des 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 d’index en ligne lors de la reconstruction d’un index existant en ligne 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 dans la même transaction, il existe des opérations de mise à jour avant cette opération en ligne. Pour contourner ce problème, placez l'opération en ligne en dehors de la transaction ou avant les éventuelles mises à jour dans la transaction.

Considérations relatives à l’espace disque

Les opérations d’index en ligne nécessitent davantage 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.
  • 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 Disk Space Requirements for Index DDL Operations.

Considérations relatives aux performances

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

Comme les structures source et cible sont conservées pendant l'opération en ligne sur l'index, l'utilisation des ressources pour les transactions d'insertion, de mise à jour et de suppression peuvent augmenter jusqu'à doubler. 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 optimal d’exécuter des opérations d’index hors connexion. Dans ce cas, l’accès utilisateur aux données est limité pendant l’opération, mais l’opération se termine plus rapidement et utilise moins de ressources.

Sur les ordinateurs multiprocesseurs exécutant SQL Server 2016 (13.x), les instructions d’index peuvent utiliser davantage de processeurs pour effectuer les opérations d’analyse et de tri associées à l’instruction d’index comme d’autres requêtes. Vous pouvez utiliser l’option MAXDOP d’index pour contrôler le nombre de processeurs dédiés à l’opération d’index en ligne. De cette façon, 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.

Étant donné qu’un verrou S-lock ou Sch-M est conservé dans la phase finale de l’opération d’index, veillez à exécuter une opération d’index en ligne à l’intérieur d’une transaction utilisateur explicite, telle que BEGIN TRANSACTION ... COMMIT le bloc. Cette action maintient le verrou jusqu'à la fin de la transaction, gênant ainsi l'accès concurrentiel des utilisateurs.

La reconstruction d’index en ligne peut augmenter la fragmentation lorsqu’elle est exécutée avec MAXDOP une valeur supérieure 1à , et ALLOW_PAGE_LOCKS=OFF. 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 des charges de données volumineuses, ce qui entraîne le remplissage rapide du journal des transactions. Cela est dû au fait que les opérations de reconstruction d’index hors connexion et en ligne sont entièrement journalisées. Pour vous assurer que l’opération d’index peut être restaurée, le journal des transactions ne peut 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 avoir suffisamment d’espace pour stocker les transactions d’opération d’index et toutes les transactions utilisateur simultanées pendant l’opération d’index. Pour plus d’informations, consultez Espace disque du journal des transactions pour les opérations d’index.

Considérations relatives aux index pouvant être repris

L’option d’index pouvant être reprise pour créer un index et une reconstruction d’index s’applique à SQL Server (reconstruction d’index à partir de SQL Server 2017 (14.x) et créez un index pris en charge dans SQL Server 2019 (15.x)) et Azure SQL Database. Pour plus d’informations, consultez Créer un index et modifier l’index.

Lors de la création ou de la reconstruction d’un index en ligne pouvant être repris, les recommandations suivantes s’appliquent :

  • Gestion, planification et extension des fenêtres de maintenance d’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.
  • Récupération après des échecs de création ou de reconstruction d’index (par exemple, un basculement de la base de données ou un manque d’espace disque).
  • 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.
  • Activation de la troncation des journaux des transactions au cours d’une opération de création ou de reconstruction d’index.
  • L’option SORT_IN_TEMPDB=ON n’est pas prise en charge.
  • Les index désactivés ne sont pas pris en charge.

Important

La création ou la reconstruction d’index pouvant être reprise ne vous oblige pas à conserver une transaction de longue durée, ce qui permet la troncation du journal pendant cette opération et une meilleure gestion de l’espace journal. Avec la nouvelle conception, nous avons réussi à conserver les données nécessaires dans une base de données, ainsi que toutes les références indispensables au redémarrage de l’opération pouvant être reprise.

En règle générale, il n’existe aucune différence de performances entre la reconstruction d’index en ligne pouvant être reprise et non régénérée. Pour créer un index pouvant être repris, une surcharge constante entraîne une petite différence de performances entre la création d’index pouvant être reprise et non résumable. D’une manière générale, cette différence n’est sensible que pour les petites tables.

Lors de la mise à jour d’un index pouvant être repris alors qu’une opération d’index est en pause :

  • Pour les charges de travail en lecture principalement, l’effet de performances est négligeable.
  • Pour les charges de travail lourdes de mise à jour, vous pouvez rencontrer une dégradation du débit (nos tests montrent moins de 10 % de dégradation).

En règle générale, il n’existe aucune différence dans la qualité de défragmentation entre la création ou la reconstruction d’index en ligne pouvant être reprise et non régénérée.

Remarque

Pendant qu’une opération d’index en ligne est suspendue, toute opération nécessitant un verrou exclusif au niveau de la table qui contient l’index suspendu échoue. Ceci est le plus souvent rencontré avec INSERT ... WITH (TABLOCK) les opérations. L’erreur suivante peut s’afficher :

Msg 10637, Level 16, State 1, Line 32: Cannot perform this operation on 'object' with ID (objectid) 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 supprimez l’opération d’index et attendez qu’elle se termine avant de réessayer de tenter votre transaction.

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

Vous pouvez définir des options par défaut pour la mise en ligne ou la reprise au niveau de la base de données en définissant les options de configuration délimitées à la base de données ou ELEVATE_RESUMABLE en définissant les options de configuration délimitées à la ELEVATE_ONLINE base de données. Avec ces options par défaut, vous pouvez éviter l’exécution accidentelle d’une opération qui met votre table de base de données en mode hors connexion. Les deux options entraînent l’élévation automatique de certaines opérations à l’exécution en ligne ou pouvant être reprise.
Vous pouvez définir l’une ou l’autre option en tant que , ou à l’aide de la commande ALTER DATABASE SCOPED CONFIGURATION.OFFWHEN_SUPPORTEDFAIL_UNSUPPORTED Vous pouvez attribuer différentes valeurs aux options d’exécution en ligne (« online ») et d’exécution pouvant être reprise (« resumable »).

Les deux ELEVATE_ONLINE et ELEVATE_RESUMABLE s’appliquent uniquement aux instructions DDL qui prennent en charge la syntaxe en ligne et pouvant être reprise respectivement. Par exemple, si vous tentez de créer un index XML avec ELEVATE_ONLINE=FAIL_UNSUPORTED, l’opération s’exécute hors connexion, car les index XML ne prennent pas en charge la ONLINE= syntaxe. Les options affectent uniquement les instructions DDL soumises sans spécifier d’option ONLINE ou RESUMABLE. Par exemple, en envoyant une instruction avec ONLINE=OFF ou RESUMABLE=OFF, l’utilisateur peut remplacer un FAIL_UNSUPPORTED paramètre et exécuter une instruction hors connexion et/ou non.

Remarque

ELEVATE_ONLINE et ELEVATE_RESUMABLE ne s’appliquent pas aux opérations d’index XML.