Directives pour effectuer des opérations en ligne sur les index

  • Article

Mis à jour : 14 avril 2006

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 lorsque la table sous-jacente contient des types de données LOB (Large OBject) : image, ntext, text, varchar(max), nvarchar(max), varbinary(max) et xml.
  • 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). Les index non-cluster définis avec des colonnes de type de données LOB doivent être créés et reconstruits hors connexion.
  • Les 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.
ms190981.note(fr-fr,SQL.90).gifRemarque :
Les opérations en ligne sur les index sont disponibles uniquement dans Microsoft SQL Server 2005 Enterprise Edition.

Le tableau suivant présente les opérations d'index réalisables en ligne et les index qui sont exclus de ces opérations en ligne. 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 de table temporaire locale

Si le mot clé ALL est spécifié, l'opération peut échouer 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 Recommandations pour la désactivation des index.

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

Il n'est pas possible de spécifier plusieurs index dans une même instruction.

ALTER TABLE ADD CONSTRAINT (PRIMARY KEY ou UNIQUE)

Index de table temporaire locale

Index ordonné en clusters

Une seule sous-clause est autorisée à la fois. Par exemple, vous ne pouvez ni ajouter ni supprimer les contraintes PRIMARY KEY ou UNIQUE dans la même instruction ALTER TABLE.

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 tant qu'une opération en ligne sur l'index 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 cluster est construit en ligne à l'aide de CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, tous les index non-cluster 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 cette situation, le moteur de base de données SQL Server 2005 sélectionne l'activité de l'utilisateur ou de l'application comme victime du blocage.

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.

Considérations relatives à l'espace disque

En général, l'espace disque requis est identique pour les opérations en ligne et hors connexion sur les index. Une exception concerne l'espace disque supplémentaire nécessaire à 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 d'un index cluster en ligne. Pour plus d'informations, consultez Espace disque requis pour les opérations DDL d'index.

Considérations relatives aux performances

Même si les opérations en ligne sur les index autorisent une activité de mise à jour utilisateur simultanée, elles durent plus longtemps si l'activité de mise à jour est très lourde. En général, les opérations en ligne sur les index sont plus lentes que leurs équivalents hors connexion, quel que soit le niveau d'activité de mise à jour.

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 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 qui exécutent SQL Server 2005 Enterprise Edition, les instructions d'index peuvent utiliser davantage de processeurs pour réaliser les opérations d'analyse et de tri associées, au même titre que d'autres requêtes. Vous pouvez utiliser l'option d'index MAXDOP pour contrôler le nombre de processeurs dédiés à l'opération en ligne sur l'index. De cette manière, vous pouvez équilibrer les ressources utilisées par l'opération d'index avec celles des utilisateurs simultanés. Pour plus d'informations, consultez Configuration d'opérations d'index parallèles.

Du fait qu'un verrou S ou Sch-M lock est conservé dans la phase finale de l'opération sur un index, soyez prudent lorsque vous exécutez une opération en ligne sur un index dans une transaction utilisateur explicite, telle qu'un blocage BEGIN TRANSACTION...COMMIT. Cette action maintient le verrou jusqu'à la fin de la transaction, gênant ainsi l'accès concurrentiel des utilisateurs.

Considérations relatives aux journaux de transactions

Les opérations d'index à grande échelle, réalisées hors connexion ou en ligne, peuvent générer de fortes charges de données susceptibles de remplir rapidement le journal de transactions. 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.

Voir aussi

Concepts

Fonctionnement des opérations d'index en ligne
Exécution d'opérations en ligne sur les index

Autres ressources

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

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version Historique

14 avril 2006
Contenu mis à jour :
  • Suppression de l'index non-cluster unique de la liste des index exclus. Ce changement s'applique à SQL Server 2005 Service Pack 1 ou supérieur.

5 décembre 2005
Nouveau contenu :
  • Ajout de l'index non-cluster unique à la liste des index exclus pour ces instructions : CREATE INDEX, CREATE INDEX WITH DROP_EXISTING et ALTER TABLE ADD CONSTRAINT (PRIMARY KEY ou UNIQUE).