Instructions pour les opérations d'index en ligne
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 les types de données LOB (Large OBject) suivants : image, ntext et text.
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.
[!REMARQUE]
Les opérations d'index en ligne ne sont pas disponibles dans toutes les éditions de Microsoft SQL Server. Pour obtenir une liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Fonctionnalités prises en charge par les éditions de SQL Server 2012.
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 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é 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 cluster |
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. |
|
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 ces rares cas, le Moteur de base de données SQL Server 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. Toute autre opération d'indexation en ligne effectuée en même temps qu'une autre entraîne un échec de l'opération. 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 qu'il existe, dans la même transaction, 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
En général, l'espace disque nécessaire 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 nécessaire 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 2012, les instructions d'index peuvent, comme d'autres requêtes, utiliser davantage de processeurs pour réaliser les opérations d'analyse et de tri associées à l'instruction d'index. Vous pouvez utiliser l'option d'index MAXDOP pour contrôler le nombre de processeurs dédiés à 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 celles 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 Fonctionnalités prises en charge par les éditions de SQL Server 2012 (https://go.microsoft.com/fwlink/?linkid=232473).
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.
La reconstruction d'index en ligne peut accroître la fragmentation si elle est autorisée à s'exécuter avec les options MAX DOP > 1 et ALLOW_PAGE_LOCKS = OFF. Pour plus d'informations, consultez Procédure : reconstruction d'index en ligne - Possibilité de fragmentation accrue.
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.
Contenu associé
Fonctionnement des opérations d'index en ligne