ALTER INDEX (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Modifie une table ou un index d’affichage (rowstore, columnstore ou XML) existant en désactivant, en régénérant ou en réorganisant l’index d’une part, ou en définissant les options portant sur l’index d’autre part.

Conventions de la syntaxe Transact-SQL

Syntaxe

-- Syntax for SQL Server and Azure SQL Database

ALTER INDEX { index_name | ALL } ON <object>
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> [ ,...n ] ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> [ ,...n ] ) ] ]
      }
    | DISABLE
    | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]
    | SET ( <set_index_option> [ ,...n ] )
    | RESUME [WITH (<resumable_index_option> [, ...n])]
    | PAUSE
    | ABORT
}
[ ; ]

<object> ::=
{
    { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
}

<rebuild_index_option> ::=
{
      PAD_INDEX = { ON | OFF }
    | FILLFACTOR = fillfactor
    | SORT_IN_TEMPDB = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | STATISTICS_INCREMENTAL = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [MINUTES]
    | ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]}

<single_partition_rebuild_index_option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [MINUTES]
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<reorganize_option> ::=
{
       LOB_COMPACTION = { ON | OFF }
    |  COMPRESS_ALL_ROW_GROUPS =  { ON | OFF}
}

<set_index_option> ::=
{
      ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | COMPRESSION_DELAY = { 0 | delay [Minutes] }
}

<resumable_index_option> ::=
 {
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION =<time> [MINUTES]
    | <low_priority_lock_wait>
 }

<low_priority_lock_wait> ::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse

ALTER INDEX { index_name | ALL }
    ON   [ schema_name. ] table_name
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> )] ]
      }
    | DISABLE
    | REORGANIZE [ PARTITION = partition_number ]
}
[;]

<rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
}

<single_partition_rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
}

Remarque

Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 (12.x) et versions antérieures, consultez la Documentation sur les versions antérieures.

Arguments

index_name

Nom de l’index. Les noms d’index doivent être uniques dans une table ou une vue, mais ils ne doivent pas nécessairement être uniques dans une base de données. Les noms d’index doivent se conformer aux règles régissant les identificateurs.

ALL

Indique tous les index associés à une table ou à une vue indépendamment du type d'index. L’ajout de l’option ALL provoque un échec de l’instruction si un ou plusieurs index se trouvent dans un groupe de fichiers hors connexion ou en lecture seule ou si l’opération spécifiée n’est pas autorisée sur un ou plusieurs types d’index. Le tableau suivant répertorie les types d'opérations ainsi que les types d'index non autorisés.

Utilisation du mot clé ALL avec cette opération	Entraîne un échec si la table possède des
REBUILD WITH ONLINE = ON	Index XML Index spatial Index columnstore : S’applique à : SQL Server (à compter de SQL Server 2012 (11.x)) et Azure SQL Database.
REBUILD PARTITION = partition_number	Index non partitionné, index XML, index spatial ou index désactivé
REORGANIZE	Index pour lesquels ALLOW_PAGE_LOCKS a la valeur OFF
REORGANIZE PARTITION = partition_number	Index non partitionné, index XML, index spatial ou index désactivé
IGNORE_DUP_KEY = ON	Index XML Index spatial Index columnstore : S’applique à : SQL Server (à compter de SQL Server 2012 (11.x)) et Azure SQL Database.
ONLINE = ON	Index XML Index spatial Index columnstore : S’applique à : SQL Server (à compter de SQL Server 2012 (11.x)) et Azure SQL Database.
RESUMABLE = ON	Les index pouvant être repris ne sont pas pris en charge avec le mot clé All. S’applique à : SQL Server (à compter de SQL Server 2017 (14.x)) et Azure SQL Database.

Avertissement

Pour plus d’informations sur les opérations d’index pouvant être effectuées en ligne, consultez Instructions pour les opérations d’index en ligne.

Si ALL est spécifié avec PARTITION = partition_number, tous les index doivent être alignés. Cela signifie qu’ils sont partitionnés selon des fonctions de partition équivalentes. L’utilisation de ALL avec PARTITION entraîne la reconstruction ou la réorganisation de toutes les partitions d’index qui ont le même partition_number. Pour plus d'informations sur les index partitionnés, consultez Partitioned Tables and Indexes.

database_name

Nom de la base de données.

schema_name

Nom du schéma auquel appartient la vue ou la table.

table_or_view_name

Nom de la table ou de la vue associée à l’index. Pour afficher un rapport des index relatifs à un objet, utilisez la vue de catalogue sys.indexes.

SQL Database prend en charge le format de nom en trois parties nom_bd.[nom_schéma].nom_table_ou_vue quand nom_bd est la base de données active ou la base de données tempdb et quand nom_table_ou_vue commence par #.

REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]

S’applique à : SQL Server (à compter de SQL Server 2012 (11.x)) et Azure SQL Database.

Index à reconstruire d'après les mêmes colonnes, le même type d'index, le même attribut assurant son unicité ainsi que le même ordre de tri. Cette clause équivaut à DBCC DBREINDEX. REBUILD permet de réactiver un index désactivé. La régénération d’un index cluster n’entraîne pas celle des index non cluster associés, sauf si le mot clé ALL est spécifié. Si les options d’index ne sont pas spécifiées, les valeurs d’index existantes stockées dans sys.indexes sont appliquées. Si une valeur d’option d’index n’est pas stockée dans sys.indexes, la valeur par défaut indiquée dans la définition de l’argument de l’option s’applique.

Si l’option ALL est indiquée et que la table sous-jacente correspond à un segment de mémoire, l’opération REBUILD n’a aucun effet sur la table. Tous les index non cluster associés à la table sont donc reconstruits.

L’opération REBUILD peut être consignée dans un journal au minimum si le mode de récupération de base de données est défini sur Utilisant les journaux de transactions ou sur Simple.

Notes

Si vous reconstruisez un index XML primaire, la table utilisateur sous-jacente devient indisponible pour toute la durée de l'opération d'index.

Pour les index columnstore, l’opération REBUILD :

• N’utilise pas l’ordre de tri.
• Acquiert un verrou exclusif sur la table ou la partition quand l’opération REBUILD se produit. Les données sont « hors connexion » et indisponibles pendant l’opération REBUILD, même si vous utilisez , l’isolation de capture instantanée de lecture validée (RCSI) ou l’isolation de capture instantanée (SI).
• Recompresse toutes les données dans le columnstore. Il existe deux copies de l’index columnstore pendant l’opération REBUILD. Quand l’opération REBUILD est terminée, SQL Server supprime l’index columnstore d’origine.

Pour plus d’informations, consultez Réorganiser et reconstruire des index.

PARTITION

Indique que seule une partition d'un index doit être reconstruite ou réorganisée. PARTITION ne peut pas être spécifié si index_name n’est pas un index partitionné.

PARTITION = ALL reconstruit toutes les partitions.

Avertissement

La création et la reconstruction des index non alignés sur une table contenant plus de 1 000 partitions sont possibles, mais ne sont pas prises en charge. Ces opérations peuvent entraîner une dégradation des performances ou une consommation de mémoire excessive. Microsoft vous recommande d’utiliser uniquement des index alignés lorsque le nombre de partitions est supérieur à 1 000.

partition_number

Numéro de partition d’un index partitionné à reconstruire ou à réorganiser. partition_number est une expression de constante qui peut référencer des variables. Cela inclut les fonctions ou variables de types définies par l’utilisateur et les fonctions définies par l’utilisateur, mais exclut l’instruction Transact-SQL. partition_number doit exister, sinon l’instruction échoue.

WITH (<single_partition_rebuild_index_option>)

SORT_IN_TEMPDB, MAXDOP, DATA_COMPRESSION et XML_COMPRESSION sont les options qui peuvent être spécifiées lorsque vous REBUILD une partition unique (PARTITION = partition_number). Les index XML ne peuvent pas être indiqués dans une opération REBUILD à partition unique.

DISABLE

Marque l'index comme désactivé et non disponible pour être utilisé par le Moteur de base de données. Tout index peut être désactivé. La définition d'un index désactivé est conservé dans le catalogue système sans y inclure de données sous-jacentes. Désactiver un index cluster permet d'éviter l'accès aux données de la table sous-jacente par les utilisateurs. Pour activer un index, utilisez ALTER INDEX REBUILD ou CREATE INDEX WITH DROP_EXISTING. Pour plus d’informations, consultez Désactiver les index et les contraintes et Activer les index et les contraintes.

Opération REORGANIZE sur un index rowstore

Pour les index rowstore, REORGANIZE spécifie la réorganisation du niveau feuille d’index. L’opération REORGANIZE est :

• Toujours effectuée en ligne. En d’autres termes, les verrous de tables bloquants à long terme ne sont pas conservés, ce qui permet aux requêtes et aux mises à jour de la table sous-jacente de se poursuivre pendant la transaction ALTER INDEX REORGANIZE.
• Non autorisée sur un index désactivé.
• Non autorisée quand ALLOW_PAGE_LOCKS est OFF.
• Non restaurée quand elle est effectuée au sein d’une transaction qui, elle, est restaurée.

Notes

Quand ALTER INDEX REORGANIZE utilise des transactions explicites (par exemple, ALTER INDEX dans une instruction BEGIN TRAN ... COMMIT/ROLLBACK) au lieu du mode de transaction implicite par défaut, le comportement de verrouillage de REORGANIZE devient plus restrictif et peut entraîner un blocage. Pour plus d’informations sur les transactions implicites, consultez SET IMPLICIT_TRANSACTIONS (Transact-SQL).

Pour plus d’informations, consultez Réorganiser et reconstruire des index.

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )

S’applique aux index rowstore.

LOB_COMPACTION = ON

• Indique de compacter toutes les pages qui contiennent des données des types de données LOB (Large Object) suivants : image, text, ntext, varchar(max), nvarchar(max), varbinary(max) et xml. Le compactage de ces données peut réduire la taille des données sur le disque.
• Pour un index cluster, toutes les colonnes LOB qui sont contenues dans la table sont compactées.
• Pour un index non-cluster, toutes les colonnes LOB qui sont des colonnes non-clés (incluses) dans l’index sont compactées.
• REORGANIZE ALL effectue une opération LOB_COMPACTION sur tous les index. Pour chaque index, toutes les colonnes LOB de l’index cluster, la table sous-jacente ou les colonnes incluses dans un index non-cluster sont compactées.

LOB_COMPACTION = OFF

• Les pages contenant des données d’objet volumineux ne sont pas compactées.
• La valeur OFF n'a aucun effet sur un segment de mémoire.

Opération REORGANIZE sur un index columnstore

Pour les index columnstore, REORGANIZE compresse chaque rowgroup delta CLOSED dans le columnstore en tant que rowgroup compressé. L’opération REORGANIZE est toujours effectuée en ligne. En d’autres termes, les verrous de tables bloquants à long terme ne sont pas conservés, ce qui permet aux requêtes et aux mises à jour de la table sous-jacente de se poursuivre pendant la transaction ALTER INDEX REORGANIZE. Pour plus d’informations, consultez Réorganiser et reconstruire des index.

• REORGANIZE n’est pas nécessaire pour déplacer les rowgroups delta CLOSED dans les rowgroups compressés. Le moteur de tuple (TM) en arrière-plan se réveille régulièrement pour compresser les rowgroups delta CLOSED. Nous recommandons d’utiliser REORGANIZE lorsque le moteur de tuple est en retard. REORGANIZE peut compresser des rowgroups de manière plus radicale.
• Pour compresser tous les rowgroups OPEN et CLOSED, consultez l’option REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS) dans cette section.

Pour les index columnstore dans SQL Server (à compter de SQL Server 2016 (13.x)) et Azure SQL Database, REORGANIZE effectue les optimisations de défragmentation supplémentaires suivantes en ligne :

• Supprime physiquement les lignes d’un rowgroup quand au moins 10 % des lignes ont été supprimées de façon logique. Les octets supprimés sont récupérés sur le support physique. Par exemple, si un rowgroup compressé d’un million de lignes a 100 000 lignes supprimées, SQL Server efface les lignes supprimées et recompresse le rowgroup avec 900 000 lignes. Il économise du stockage en effaçant les lignes supprimées.

• Associe un ou plusieurs rowgroups compressés pour augmenter les lignes par rowgroup jusqu’à la valeur maximale de 1 048 576 lignes. Par exemple, si vous importez en bloc 5 lots de 102 400 lignes, vous obtenez 5 rowgroups compressés. Si vous exécutez REORGANIZE, ces rowgroups sont fusionnés dans 1 rowgroup compressé de 512 000 lignes. Cela suppose qu’il n’existe aucune limitation de mémoire ni de taille de dictionnaire.

• Pour les rowgroups dans lesquels au moins 10 % des lignes ont été supprimées de manière logique, SQL Server tente d’associer ce rowgroup à un ou plusieurs rowgroups. Par exemple, le rowgroup 1 est compressé avec 500 000 lignes et le rowgroup 21 est compressé avec un maximum de 1 048 576 lignes. Le rowgroup 21 a 60 % des lignes supprimées, ce qui laisse 409 830 lignes. SQL Server favorise la combinaison de ces deux rowgroups pour compresser un nouveau rowgroup qui contient 909 830 lignes.

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

S’applique aux index columnstore.

S’applique à : SQL Server (à compter de SQL Server 2016 (13.x)) et Azure SQL Database.

COMPRESS_ALL_ROW_GROUPS offre un moyen de forcer les rowgroups delta OPEN ou CLOSED dans le columnstore. Avec cette option, il n’est pas nécessaire de régénérer l’index columnstore pour vider les rowgroups delta. Ceci, combiné aux autres fonctionnalités de défragmentation par suppression et fusion, permet d’éviter de devoir reconstruire l’index dans la plupart des cas.

• ON force tous les rowgroups dans le columnstore, quels que soient la taille et l’état (CLOSE ou OPEN).
• OFF force tous les rowgroups CLOSED dans le columnstore.

Pour plus d’informations, consultez Réorganiser et reconstruire des index.

SET ( <set_index option> [ ,... n] )

Indique des options d'index sans pour autant reconstruire ou réorganiser l'index. SET ne peut pas être spécifié pour un index désactivé.

PAD_INDEX = { ON | OFF }

Spécifie le remplissage de l'index. La valeur par défaut est OFF.

ACTIVÉ

Le pourcentage d’espace libre indiqué par FILLFACTOR est appliqué aux pages du niveau intermédiaire de l’index. Si FILLFACTOR n’est pas spécifié alors que PAD_INDEX a la valeur ON, la valeur du facteur de remplissage stockée dans sys.indexes est utilisée.

OFF ou fillfactor n’est pas spécifié

Les pages de niveau intermédiaire sont remplies jusqu'à la presque totalité de la capacité. Cela laisse suffisamment d'espace libre pour au moins une ligne de la taille maximale que l'index peut occuper, d'après un ensemble de clés sur les pages intermédiaires.

Pour plus d’informations, consultez CREATE INDEX (Transact-SQL).

FILLFACTOR = fillfactor

Spécifie un pourcentage indiquant le taux de remplissage appliqué par le Moteur de base de données au niveau feuille de chaque page d'index lors de la création ou de la modification de l'index. La valeur de fillfactor doit être une valeur entière comprise entre 1 et 100. La valeur par défaut est 0. Les taux de remplissage 0 et 100 sont identiques en tous points.

Un paramètre FILLFACTOR explicite ne s’applique que lors de la première création ou reconstruction de l’index. Dans les pages, le Moteur de base de données ne conserve pas dynamiquement le pourcentage d’espace libre défini. Pour plus d’informations, consultez CREATE INDEX (Transact-SQL).

Pour afficher le paramètre du facteur de remplissage, utilisez fill_factor dans sys.indexes.

Important

La création ou la modification d’un index cluster avec une valeur de FILLFACTOR affecte la quantité de l’espace de stockage occupé par les données, car le Moteur de base de données redistribue les données quand il crée l’index en question.

SORT_IN_TEMPDB = { ON | OFF }

Indique si les résultats du tri doivent être stockés dans tempdb. La valeur par défaut est OFF, à l’exception d’Azure SQL Database Hyperscale. Pour toutes les opérations de nouvelle création d’index dans Hyperscale, SORT_IN_TEMPDB est toujours ON quelle que soit l’option spécifiée, sauf si la regénération de l’index pouvant être reprise est utilisée.

ACTIVÉ
Les résultats intermédiaires du tri utilisés pour créer l’index sont stockés dans tempdb. Si tempdb ne se trouve pas sur le même groupe de disques que la base de données utilisateur, le temps nécessaire à la création de l’index peut être plus court. Toutefois, une plus grande quantité d'espace disque est alors utilisée lors de la création de l'index.

OFF
Les résultats de tri intermédiaires sont stockés dans la même base de données que l'index.

Si aucune opération de tri n’est requise ou si le tri peut être effectué dans la mémoire, l’option SORT_IN_TEMPDB est ignorée.

Pour plus d’informations, consultez Option SORT_IN_TEMPDB pour les index.

IGNORE_DUP_KEY = { ON | OFF }

Spécifie la réponse d'erreur lorsqu'une opération d'insertion essaie d'insérer des valeurs de clés en double dans un index unique. L’option IGNORE_DUP_KEY s’applique uniquement aux opérations d’insertion après la création ou la régénération de l’index. La valeur par défaut est OFF.

ACTIVÉ
Un message d'avertissement s'affichera lorsque des valeurs de clé en double sont insérées dans un index unique. Seules les lignes qui violent la contrainte d'unicité échouent.

OFF
Un message d'erreur s'affichera lorsque des valeurs de clé en double sont insérées dans un index unique. L'intégralité de l'opération INSERT sera restaurée.

IGNORE_DUP_KEY ne peut pas être défini sur ON pour les index créés sur une vue, les index non uniques, les index XML, les index spatiaux ni les index filtrés.

Pour voir IGNORE_DUP_KEY, utilisez sys.indexes.

Dans la syntaxe à compatibilité descendante, WITH IGNORE_DUP_KEY équivaut à WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | OFF}

Spécifie si les statistiques de distribution sont recalculées. La valeur par défaut est OFF.

ACTIVÉ
Les statistiques obsolètes ne sont pas recalculées automatiquement.

OFF
La mise à jour automatique des statistiques est activée.

Pour restaurer la mise à jour automatique des statistiques, affectez la valeur OFF à STATISTICS_NORECOMPUTE ou exécutez UPDATE STATISTICS sans la clause NORECOMPUTE.

Important

Si vous désactivez le recalcul automatique des statistiques de distribution, il se peut que cela empêche l’optimiseur de requête de choisir les plans d’exécution optimaux pour les requêtes impliquant la table.

STATISTICS_INCREMENTAL = { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2014 (12.x)) et Azure SQL Database.

Si la valeur ON est définie, les statistiques sont créées par partition. Si la valeur est OFF, l’arborescence des statistiques est supprimée et SQL Server recalcule les statistiques. La valeur par défaut est OFF.

Si les statistiques par partition ne sont pas prises en charge, l’option est ignorée et un avertissement est généré. Les statistiques incrémentielles ne sont pas prises en charge pour les types de statistique suivants :

• Statistiques créées à partir d’index qui n’ont pas d’alignement de partition avec la table de base
• statistiques créées sur les bases de données secondaires lisibles de groupe de disponibilité ;
• statistiques créées sur les bases de données en lecture seule ;
• statistiques créées sur les index filtrés ;
• statistiques créées sur les affichages ;
• statistiques créées sur les tables internes ;
• statistiques créées avec les index spatiaux ou les index XML.

ONLINE = { ON | OFF } <comme appliqué à rebuild_index_option>

Indique si les tables sous-jacentes et les index associés sont disponibles pour les requêtes et la modification de données pendant l'opération d'index. La valeur par défaut est OFF.

Pour un index XML ou un index spatial, seul ONLINE = OFF est pris en charge et si ONLINE est activé (ON), une erreur est générée.

Important

Les opérations d’index en ligne ne sont pas disponibles dans toutes les éditions 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.

ACTIVÉ
Les verrous de table à long terme ne sont pas maintenus pendant l’opération d’index. Lors de la principale phase de l'indexation, seul le verrou de partage intentionnel (IS, Intent Share) est maintenu sur la table source. Cela permet aux requêtes ou aux mises à jour effectuées dans la table et les index sous-jacents de continuer. Au début de l'opération, un verrou partagé (S, Shared) est très brièvement maintenu sur l'objet source. À la fin de l’opération, un verrou S est très brièvement maintenu sur la source si un index non-cluster est créé. Une modification du schéma (Sch-M) est acquise quand un index cluster est créé ou supprimé en ligne et quand un index cluster ou non-cluster est reconstruit. ONLINE ne peut pas prendre la valeur ON si un index est en cours de création sur une table locale temporaire.

OFF
Des verrous de table sont appliqués pendant l’opération d’indexation. Une opération d'index hors connexion qui crée, reconstruit ou supprime un index cluster, spatial ou XML, ou qui reconstruit ou supprime un index non cluster, acquiert un verrou Sch-M (Modification du schéma) sur la table. Cela empêche tous les utilisateurs d’accéder à la table sous-jacente pendant l’opération. Une opération d'indexation hors ligne qui crée un index non cluster acquiert un verrou partagé (S, Shared) sur la table. Cela empêche la mise à jour de la table sous-jacente, mais autorise les opérations de lecture, telles que des instructions SELECT.

Pour plus d'informations, consultez Perform Index Operations Online.

Les index, notamment les index portant sur des tables temporaires globales, ne peuvent pas être régénérés en ligne, à l’exception des index suivants :

• Index XML
• Index de table temporaire locale
• Index cluster unique de départ sur une vue
• Index columnstore
• Idex cluster si la table sous-jacente contient des types de données LOB (image, ntext, text) et des types de données spatiales
• Les colonnes varchar(max) et varbinary(max) ne peuvent pas faire partie d’un index. Dans SQL Server (à compter de SQL Server 2012 (11.x)) et dans Azure SQL Database, quand une table contient des colonnes varchar(max) ou varbinary(max) , un index cluster contenant d’autres colonnes peut être généré ou régénéré à l’aide de l’option ONLINE. Azure SQL Database n’autorise pas l’option ONLINE quand la table de base contient des colonnes varchar(max) ni varbinary(max)

Pour plus d’informations, consultez Fonctionnement des opérations d’index en ligne.

Les XEvents suivant sont liés à ALTER TABLE ... SWITCH PARTITION et la reconstruction de l’index en ligne.

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

L’événement XEvent progress_report_online_index_operation existant pour les opérations d’index en ligne inclut partition_number et partition_id.

RESUMABLE = { ON | OFF}

S’applique à : SQL Server (à compter de SQL Server 2017 (14.x)) et Azure SQL Database.

Spécifie si une opération d’index en ligne peut être reprise.

ACTIVÉ
L’opération d’index peut être reprise.

OFF
L’opération d’index ne peut pas être reprise.

MAX_DURATION = time [MINUTES] utilisé avec RESUMABLE = ON (nécessite ONLINE = ON)

S’applique à : SQL Server (à compter de SQL Server 2017 (14.x)) et Azure SQL Database.

Indique le temps (valeur entière spécifiée en minutes) pendant lequel une opération d’index en ligne pouvant être reprise est exécutée avant d’être mise en pause.

Important

Pour plus d’informations sur les opérations d’index pouvant être effectuées en ligne, consultez Instructions pour les opérations d’index en ligne.

Notes

Les régénérations d’index en ligne pouvant être reprise ne sont pas prises en charge sur les index columnstore.

ALLOW_ROW_LOCKS = { ON | OFF }

Indique si les verrous de ligne sont autorisés ou non. La valeur par défaut est ON.

ACTIVÉ
Les verrous de ligne sont autorisés lors de l'accès à l'index. Le Moteur de base de données détermine le moment où les verrous de ligne sont utilisés.

OFF
Les verrous de ligne ne sont pas utilisés.

ALLOW_PAGE_LOCKS = { ON | OFF }

Indique si les verrous de page sont autorisés. La valeur par défaut est ON.

ACTIVÉ
Les verrous de page sont autorisés lors de l'accès à l'index. Le Moteur de base de données détermine le moment où les verrous de page sont utilisés.

OFF
Les verrous de page ne sont pas utilisés.

Notes

Un index ne peut pas être réorganisé lorsque ALLOW_PAGE_LOCKS est désactivé (OFF).

OPTIMIZE_FOR_SEQUENTIAL_KEY = {ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)) et Azure SQL Database.

Spécifie s’il faut optimiser ou pas la contention d’insertion de la dernière page. La valeur par défaut est OFF. Pour plus d’informations, consultez Clés séquentielles.

MAXDOP = max_degree_of_parallelism

Remplace l’option de configuration max degree of parallelism pendant l’opération d’index. Pour plus d’informations, consultez Configurer l’option de configuration du serveur max degree of parallelism. Utilisez MAXDOP pour limiter le nombre de processeurs utilisés dans une exécution de plan parallèle. Le nombre maximal de processeurs est égal à 64.

Important

Bien que l’option MAXDOP soit prise en charge syntaxiquement pour tous les index XML, pour un index spatial ou un XML primaire, ALTER INDEX utilise seulement un processeur unique.

max_degree_of_parallelism peut avoir la valeur :

1
Supprime la création de plans parallèles.

>1
Limite au nombre spécifié le nombre maximal de processeurs utilisés dans le traitement en parallèle des index.

0 (valeur par défaut)
Utilise le nombre réel de processeurs ou un nombre de processeurs inférieur en fonction de la charge de travail actuelle du système.

Pour plus d’informations, consultez Configurer des opérations d’index parallèles.

Notes

Les opérations d'index parallèles ne sont pas disponibles dans toutes les éditions de 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.

COMPRESSION_DELAY = { 0 | duration [Minutes] }

S’applique à : SQL Server (à compter de SQL Server 2016 (13.x))

Pour une table sur disque, le délai spécifie le nombre minimal de minutes pendant lesquelles un rowgroup delta à l’état CLOSED doit rester dans le rowgroup delta avant que SQL Server puisse le compresser dans le rowgroup compressé. Étant donné que les tables sur disque ne suivent pas les durées des opérations d’insertion et de mise à jour sur chaque ligne, SQL Server applique le délai aux rowgroups delta qui ont l’état CLOSED.

La valeur par défaut est 0 minute.

Pour obtenir des recommandations concernant l’utilisation de COMPRESSION_DELAY, consultez Prise en main de Columnstore pour l’analytique opérationnelle en temps réel.

DATA_COMPRESSION

Spécifie l'option de compression de données pour l'index, le numéro de partition ou la plage de partitions spécifiés. Les options disponibles sont les suivantes :

Aucune
L’index ou les partitions spécifiées ne sont pas compressés. Ne s’applique pas aux index columnstore.

ROW
L'index ou les partitions spécifiées sont compressés au moyen de la compression de ligne. Ne s’applique pas aux index columnstore.

PAGE
L'index ou les partitions spécifiées sont compressés au moyen de la compression de page. Ne s’applique pas aux index columnstore.

COLUMNSTORE

S’applique à : SQL Server (à compter de SQL Server 2014 (12.x)) et Azure SQL Database.

S'applique uniquement aux index columnstore, y compris aux index columnstore non cluster et cluster. COLUMNSTORE spécifie qu'il faut décompresser l'index ou les partitions spécifiées compressés à l'aide de l'option COLUMNSTORE_ARCHIVE. Lorsque les données sont restaurées, elles continuent à être compressées à l'aide de la compression columnstore utilisée pour tous les index columnstore.

COLUMNSTORE_ARCHIVE

S’applique à : SQL Server (à compter de SQL Server 2014 (12.x)) et Azure SQL Database.

S'applique uniquement aux index columnstore, y compris aux index columnstore non cluster et cluster. COLUMNSTORE_ARCHIVE compressera davantage la partition spécifiée en une plus petite taille. Peut être utilisé pour l'archivage, ou d'autres situations qui nécessitent moins de stockage et supportent plus de temps pour le stockage et la récupération.

Pour plus d’informations sur la compression, consultez Compression des données.

XML_COMPRESSION

S'applique à : SQL Server 2022 (16.x) et versions ultérieures, base de données Azure SQL, Azure SQL Managed Instance

Spécifie l’option de compression XML pour l’index spécifié qui contient une ou plusieurs colonnes de type de données XML. Les options disponibles sont les suivantes :

ACTIVÉ
L'index ou les partitions spécifiées sont compressés au moyen de la compression XML.

OFF
L'index ou les partitions spécifiées ne sont pas compressés.

ON PARTITIONS ( { <partition_number_expression> | <range> } [,...n] )

Spécifie les partitions auxquelles s’appliquent les paramètres DATA_COMPRESSION ou XML_COMPRESSION. Si l’index n’est pas partitionné, l’argument ON PARTITIONS génère une erreur. Si la clause ON PARTITIONS n’est pas fournie, l’option DATA_COMPRESSION ou XML_COMPRESSION s’applique à toutes les partitions d’un index partitionné.

<partition_number_expression> peut être spécifié des manières suivantes :

• Spécifie le numéro d’une partition, par exemple : ON PARTITIONS (2).
• Spécifie des numéros de partition pour plusieurs partitions individuelles séparées par des virgules, par exemple : ON PARTITIONS (1, 5).
• Spécifie à la fois des plages et des partitions individuelles : ON PARTITIONS (2, 4, 6 TO 8).

<range> peut être spécifié sous la forme de numéros de partitions séparés par le mot TO, par exemple : ON PARTITIONS (6 TO 8).

Pour définir des types différents de compression de données pour des partitions différentes, spécifiez plusieurs fois l'option DATA_COMPRESSION, par exemple :

REBUILD WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
);

Vous pouvez également spécifier l’option XML_COMPRESSION plusieurs fois, par exemple :

REBUILD WITH
(
  XML_COMPRESSION = OFF ON PARTITIONS (1),
  XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
  XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

ONLINE = { ON | OFF } <comme appliqué à single_partition_rebuild_index_option>

Spécifie si un index ou une partition d’index d'une table sous-jacente peut être reconstruit en ligne ou hors connexion. Si REBUILD ... ONLINE = ON est effectué, les données de cette table sont disponibles pour les requêtes et la modification de données pendant l’opération d’index. La valeur par défaut est OFF.

ACTIVÉ
Les verrous de table à long terme ne sont pas maintenus pendant l’opération d’index. Lors de la principale phase de l'indexation, seul le verrou de partage intentionnel (IS, Intent Share) est maintenu sur la table source. Un verrou Sch-S (stabilité du schéma) sur la table est nécessaire lors du démarrage de la régénération de l’index, et un verrou Sch-M (modification du schéma) est nécessaire sur la table à la fin de la régénération d’index en ligne. Bien que les deux verrous de métadonnées soient de courte durée, le verrou Sch-M en particulier doit attendre que toutes les transactions bloquantes soient terminées. Pendant le temps d’attente, le verrou Sch-M bloque toutes les autres transactions en attente derrière ce verrou en cas d’accès à la même table.

Notes

La reconstruction d’un index en ligne peut définir les options low_priority_lock_wait ; consultez, WAIT_AT_LOW_PRIORITY avec les opérations d’index en ligne.

OFF
Des verrous de table sont appliqués pendant l’opération d’indexation. Cela empêche tous les utilisateurs d’accéder à la table sous-jacente pendant l’opération.

RESUME

S’applique à : SQL Server (à compter de SQL Server 2017 (14.x)) et Azure SQL Database.

Reprenez une opération d’index mise en pause manuellement ou suite à une défaillance.

MAX_DURATION utilisé avec RESUMABLE = ON

Temps (valeur entière spécifiée en minutes) pendant lequel l’opération d’index en ligne pouvant être reprise est exécutée après avoir été reprise. Une fois que le délai expire, l’opération pouvant être reprise est suspendue si elle est toujours en cours d’exécution.

WAIT_AT_LOW_PRIORITY utilisé avec RESUMABLE = ON et ONLINE = ON.

La reprise d’une reconstruction d’index en ligne après une pause doit attendre les opérations de blocage sur cette table. WAIT_AT_LOW_PRIORITY indique que l’opération de reconstruction de l’index en ligne doit attendre les verrous de basse priorité, en permettant à d’autres opérations de continuer pendant que l’opération de construction de l’index en ligne est en train de patienter. Omettre l’option WAIT_AT_LOW_PRIORITY équivaut à WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). Pour plus d’informations, consultez WAIT_AT_LOW_PRIORITY.

PAUSE

S’applique à : SQL Server (à compter de SQL Server 2017 (14.x)) et Azure SQL Database.

Mettez en pause une opération de reconstruction d’index en ligne pouvant être reprise.

ABORT

S’applique à : SQL Server (à compter de SQL Server 2017 (14.x)) et Azure SQL Database.

Abandonnez une opération d’index en cours d’exécution ou en pause qui a été déclarée comme pouvant être reprise. Vous devez exécuter explicitement une commande ABORT pour terminer une opération de régénération d’index pouvant être reprise. L’échec ou l’interruption d’une opération d’index pouvant être reprise ne termine pas l’exécution. Elle reste dans un état de pause indéterminée.

Remarques

ALTER INDEX ne peut pas être utilisé pour repartitionner un index ni pour le déplacer vers un autre groupe de fichiers. Cette instruction ne peut pas être utilisée pour modifier la définition de l’index, comme l’ajout ou la suppression de colonnes ou la modification de l’ordre des colonnes. Utilisez CREATE INDEX avec la clause DROP_EXISTING pour effectuer ces opérations.

Si une option n’est pas spécifiée de façon explicite, le paramètre actuel s’applique. Par exemple, si un paramètre FILLFACTOR n’est pas spécifié dans la clause REBUILD, la valeur du facteur de remplissage stockée dans le catalogue système est utilisée pendant le processus de régénération. Pour voir les paramètres des options d’index actuels, utilisez sys.indexes.

Les valeurs de ONLINE, MAXDOP et SORT_IN_TEMPDB ne sont pas stockées dans le catalogue système. À moins qu'elle ne soit précisée dans l'instruction d'index, la valeur par défaut de l'option est alors utilisée.

Sur les ordinateurs multiprocesseurs, à la manière d’autres requêtes, ALTER INDEX REBUILD utilise automatiquement davantage de processeurs pour effectuer les opérations d’analyse et de tri associées à la modification de l’index. Lorsque vous exécutez ALTER INDEX REORGANIZE, avec ou sans LOB_COMPACTION, la valeur de degré maximal de parallélisme correspond à une opération de thread unique. Pour plus d’informations, consultez Configurer des opérations d’index parallèles.

Important

Un index ne peut pas être réorganisé ou reconstruit si le groupe de fichiers dans lequel il se trouve est hors connexion ou en lecture seule. Si le mot clé ALL est spécifié et que des index se trouvent dans un groupe de fichiers hors connexion ou en lecture seule, l'instruction échoue.

Regénération des index

La reconstruction d'un index entraîne sa suppression puis sa recréation. Ceci permet d'éviter toute fragmentation, de libérer de l'espace disque en compactant les pages d'après le paramètre du facteur de remplissage spécifié ou déjà existant et en retriant les lignes de l'index en pages contiguës. Quand ALL est précisé, tous les index sur la table sont supprimés puis reconstruits en une seule transaction. Il n’est pas nécessaire d’annuler les contraintes de clé étrangère à l’avance. Lorsque de la reconstruction d'index contenant au moins 128 étendues, le Moteur de base de données diffère les désallocations de pages ainsi que les verrous qui y sont associés jusqu'à ce que la transaction soit validée.

Pour plus d’informations, consultez Réorganiser et reconstruire des index.

Réorganisation des index

La réorganisation d'un index utilise des ressources système minimes. En effet, elle défragmente le niveau feuille des index cluster et non cluster sur les tables et les vues en retriant les pages de niveau feuille de façon physique afin de resuivre l'ordre logique, c'est-à-dire de gauche à droite, des nœuds. Cette opération compacte également les pages d'index. Le compactage s'appuie sur la valeur du facteur de remplissage existante.

Si ALL est spécifié, les index relationnels, aussi bien cluster que non cluster, et les index XML de la table sont réorganisés. Certaines restrictions s’appliquent quand ALL est spécifié ; consultez la définition de ALL dans la section Arguments de cet article.

Pour plus d’informations, consultez Réorganiser et reconstruire des index.

Important

Pour une table Azure Synapse Analytics avec un index Columnstore ordonné en cluster, ALTER INDEX REORGANIZE ne trie pas les données à nouveau. Pour trier à nouveau l’utilisation de données ALTER INDEX REBUILD.

Désactivation des index

Désactiver un index permet d'éviter l'accès à l'index, et dans le cas d'index cluster, aux données de la table sous-jacente par les utilisateurs. La définition de l'index est conservée dans le catalogue système. Désactiver un index, qu'il soit non cluster ou cluster, sur une vue supprime physiquement les données de l'index. Désactiver un index cluster permet d'éviter l'accès aux données mais celles-ci ne sont plus mises à jour dans l'arborescence binaire (appelé également arbre B) jusqu'à ce que l'index soit supprimé ou reconstruit. Pour afficher l’état d’un index, qu’il soit activé ou désactivé, lancez une requête sur la colonne is_disabled dans la vue de catalogue sys.indexes.

Notes

De manière générale, la documentation SQL Server utilise le terme B-tree en référence aux index. Dans les index rowstore, SQL Server implémente une structure B+. Cela ne s’applique pas aux index columnstore ou aux magasins de données en mémoire. Pour plus d’informations, consultez le Guide de conception et d’architecture d’index SQL Server et Azure SQL.

Si une table se trouve dans une publication de réplication transactionnelle, vous ne pouvez pas désactiver les index qui sont associés à des colonnes de clé primaire. Ces index sont requis par la réplication. Pour désactiver un index, vous devez d'abord supprimer la table de la réplication. Pour plus d’informations, consultez Publier des données et des objets de base de données.

Utilisez les instructions ALTER INDEX REBUILD ou CREATE INDEX WITH DROP_EXISTING pour activer l’index. La régénération d’un index cluster désactivé ne peut pas être effectuée si l’option ONLINE est activée (ON). Pour plus d’informations, consultez Désactiver les index et contraintes.

Options du paramètre

Vous pouvez définir les options ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, OPTIMIZE_FOR_SEQUENTIAL_KEY, IGNORE_DUP_KEY et STATISTICS_NORECOMPUTE pour un index spécifié sans régénérer ou réorganiser cet index. Les valeurs modifiées sont immédiatement appliquées à l'index. Pour afficher ces paramètres, utilisez sys.indexes. Pour plus d’informations, consultez Définir les options d’index.

Options de verrous de ligne et de page

Si ALLOW_ROW_LOCKS = ON et ALLOW_PAGE_LOCK = ON, les verrous au niveau de la ligne, de la page et de la table sont autorisés au moment où vous accédez à l’index. Le Moteur de base de données choisit le verrou approprié et peut promouvoir un verrou de ligne ou de page en verrou de table.

Si ALLOW_ROW_LOCKS = OFF et ALLOW_PAGE_LOCK = OFF, seul un verrou au niveau de la table est autorisé au moment où vous accédez à l’index.

Si ALL est indiqué lors de la définition des options de verrouillage de ligne ou de page, les paramètres s'appliquent à tous les index. Si la table sous-jacente correspond à un segment de mémoire, les paramètres s'appliquent des façons suivantes :

Option	Détails
ALLOW_ROW_LOCKS = ON ou OFF	S'applique au segment de mémoire et à tout index non cluster qui lui est associé.
ALLOW_PAGE_LOCKS = ON	S'applique au segment de mémoire et à tout index non cluster qui lui est associé.
ALLOW_PAGE_LOCKS = OFF	Verrou entier pour les index non cluster. En d’autres termes, tous les verrous de page ne sont pas autorisés sur les index non cluster. En ce qui concerne le segment de mémoire, seuls les verrous partagé (S), de mise à jour (U) et exclusifs (X) ne sont pas autorisés. Le Moteur de base de données peut toujours acquérir un verrou de page intentionnel (IS, IU ou IX) à des fins internes.

Opérations d’index en ligne

Si vous reconstruisez un index et que l'option ONLINE est activée (ON), les objets, les tables et les index associés sous-jacents sont disponibles pour permettre les requêtes et la modification de données. Vous pouvez également reconstruire en ligne une partie d'un index résidant sur une partition unique. Les verrous de tables exclusifs ne sont maintenus que pour une durée courte lors du processus de modification.

La réorganisation d'un index s'effectue toujours en ligne. Ce processus ne conserve pas les verrous à long terme. Par conséquent, il ne bloque pas les requêtes ni les mises à jour en cours.

Vous pouvez lancer des opérations d'index en ligne simultanées sur une même table ou partition de table, mais uniquement dans les cas suivants :

• Création de plusieurs index non cluster.
• réorganisation de différents index sur une même table ;
• réorganisation de différents index lors de la reconstruction d'index ne se chevauchant pas et portant sur une même table.

Toutes les autres opérations en ligne sur les index exécutées en même temps échouent. Vous ne pouvez pas par exemple régénérer simultanément plusieurs index sur une même table ni créer d’index lors de la régénération d’un index existant sur la même table.

Pour plus d'informations, consultez Perform Index Operations Online.

Opérations d’index pouvant être reprises

S’applique à : SQL Server (à compter de SQL Server 2017 (14.x)) et Azure SQL Database.

La reconstruction d’index en ligne est spécifiée comme pouvant être reprise avec l’option RESUMABLE = ON.

• L’option RESUMABLE n’est pas persistante dans les métadonnées d’un index donné. Elle s’applique uniquement à la durée d’une instruction DDL active. C’est pourquoi la clause RESUMABLE = ON doit être spécifiée explicitement pour permettre la reprise.

• L’option MAX_DURATION est prise en charge pour l’option RESUMABLE = ON et l’option low_priority_lock_wait.

  • MAX_DURATION pour l’option RESUMABLE spécifie l’intervalle de temps pour un index en cours de reconstruction. Une fois que cette durée est utilisée, la reconstruction d’index est mise en pause ou termine son exécution. L’utilisateur décide quand une reconstruction d’un index en pause peut être reprise. La durée (en minutes) de MAX_DURATION doit être supérieure à 0 minute et inférieur ou égale à une semaine (7 * 24 * 60 = 10 080 minutes). Si la mise en pause d’une opération d’index est longue, les performances DML d’une table spécifique et la capacité de disque de la base de données risquent d’être impactées parce que les deux index, l’original et celui nouvellement créé, ont besoin d’espace disque et doivent être mis à jour au cours des opérations DML. Si l’option MAX_DURATION est omise, l’opération d’index se poursuit jusqu’à ce qu’elle se termine ou qu’une défaillance se produise.
  • L’option d’argument low_priority_lock_wait vous permet de décider comment l’opération d’index peut continuer quand elle est bloquée sur le verrou Sch-M.

• La réexécution de l’instruction ALTER INDEX REBUILD d’origine avec les mêmes paramètres reprend une opération de reconstruction d’index mise en pause. Vous pouvez également reprendre une opération de reconstruction d’index en pause en exécutant l’instruction ALTER INDEX RESUME.

• L’option SORT_IN_TEMPDB = ON n’est pas prise en charge pour l’index pouvant être repris

• La commande DDL avec RESUMABLE = ON ne peut pas être exécutée dans une transaction explicite (ne peut pas faire partie du bloc BEGIN TRAN ... COMMIT).

• Seules les opérations d’index qui sont mises en pause peuvent être reprises.

• Quand vous reprenez une opération d’index qui est en pause, vous pouvez remplacer la valeur MAXDOP par une nouvelle valeur. Si MAXDOP n’est pas spécifié lors de la reprise d’une opération d’index qui est en pause, la dernière valeur MAXDOP est prise. Si l’option MAXDOP n’est pas du tout spécifiée pour l’opération de régénération d’index, la valeur par défaut est prise.

• Pour mettre tout de suite en pause l’opération d’index, vous pouvez arrêter la commande en cours (Ctrl-C) ou vous pouvez exécuter la commande ALTER INDEX PAUSE ou la commande KILL <session_id>. Une fois la commande en pause, elle peut être reprise avec l’option RESUME.

• La commande ABORT met fin à la session qui a hébergé la reconstruction d’index d’origine et abandonne l’opération d’index

• Aucune autre ressource n’est nécessaire pour la reconstruction d’index pouvant être reprise sauf pour

  • De l’espace supplémentaire nécessaire pour conserver l’index en cours de construction, y compris le temps lorsque l’index est en pause
  • Un état DDL empêchant toute modification DDL

• Le nettoyage des éléments fantômes s’exécutera pendant la phase de pause de l’index, mais sera mis en pause pendant l’exécution de l’index. Les fonctionnalités suivantes sont désactivées pour les opérations de reconstruction d’index pouvant être reprises

  • La régénération d’un index désactivé n’est pas prise en charge avec RESUMABLE = ON
  • Commande ALTER INDEX REBUILD ALL
  • ALTER TABLE avec une reconstruction d’index
  • La commande DDL avec RESUMABLE = ON ne peut pas être exécutée dans une transaction explicite (ne peut pas faire partie du bloc BEGIN TRAN ... COMMIT)
  • La reconstruction d’un index qui a calculé ou horodaté (TIMESTAMP) la ou les colonnes en tant que colonnes clés.

• Si la table de base contient une ou plusieurs colonnes LOB, la reconstruction d’index cluster pouvant être reprise nécessite un verrou Sch-M au début de cette opération

Notes

La commande DDL s’exécute tant qu’elle n’a pas fini, n’est pas mise en pause ou n’a pas échoué. Si la commande est en pause, une erreur s’affiche indiquant que l’opération a été mise en pause et que la création d’index n’a pas été effectuée. Vous trouverez plus d’informations sur l’état d’index actuel dans sys.index_resumable_operations. Comme avant, en cas de défaillance, une erreur s’affiche également.

WAIT_AT_LOW_PRIORITY avec les opérations d’index en ligne

S’applique à : SQL Server (à compter de SQL Server 2014 (12.x)) et Azure SQL Database.

La syntaxe low_priority_lock_wait permet de spécifier le comportement de WAIT_AT_LOW_PRIORITY. WAIT_AT_LOW_PRIORITY peut être utilisé uniquement avec ONLINE = ON.

Pour exécuter l'instruction DDL pour une reconstruction d'index en ligne, toutes les transactions bloquantes actives qui s'exécutent sur une table particulière doivent être terminées. Lorsque la reconstruction d'index en ligne s'exécute, elle bloque toutes les nouvelles transactions qui sont prêtes à s'exécuter sur cette table. Bien que la durée du verrou pour la reconstruction de l'index en ligne soit très courte, le fait d'attendre que toutes les transactions ouvertes sur une table spécifique soient exécutées, et le fait de bloquer les nouvelles transactions qui doivent démarrer, peuvent avoir un impact important sur le débit et provoquer un ralentissement ou un délai d'attente des charges de travail, limitant considérablement l'accès à la table sous-jacente.

L’option WAIT_AT_LOW_PRIORITY permet aux administrateurs de base de données de gérer les verrous Sch-S (stabilité du schéma) et Sch-M (modification du schéma) nécessaires pour les régénérations d’index en ligne, et permet de sélectionner une des trois options. Dans tous les cas, si aucune activité n’est bloquante pendant le temps d’attente MAX_DURATION = n [minutes], la régénération d’index en ligne est exécutée immédiatement, sans attendre et l’instruction DDL est effectuée.

WAIT_AT_LOW_PRIORITY indique que l’opération de reconstruction de l’index en ligne doit attendre les verrous de basse priorité, en permettant à d’autres opérations de continuer pendant que l’opération de construction de l’index en ligne est en train de patienter. Omettre l’option WAIT AT LOW PRIORITY équivaut à WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = time [MINUTES]

Temps d'attente (valeur entière spécifiée en minutes) pendant lequel les verrous de reconstruction d'index en ligne devront attendre avec une faible priorité lors de l'exécution de la commande DDL. Si l’opération est bloquée pendant la durée MAX_DURATION, l’action ABORT_AFTER_WAIT spécifiée est exécutée. La durée MAX_DURATION est toujours spécifiée en minutes, et le mot MINUTES peut être omis.

ABORT_AFTER_WAIT = [ NONE | SELF | BLOCKERS ]

Aucune
Continuez à attendre le verrou avec la priorité normale.

SELF
Quittez l'opération DDL de reconstruction de l'index en ligne actuellement exécutée sans effectuer aucune action. L’option SELF ne peut pas être utilisée avec une durée MAX_DURATION égale à 0.

BLOCKERS
Annulez toutes les transactions utilisateur qui bloquent l'opération DDL de reconstruction de l'index en ligne afin que l'opération puisse continuer. L’option BLOCKERS nécessite que la connexion ait une autorisation ALTER ANY CONNECTION.

Restrictions des index spatiaux

Quand vous reconstruisez un index spatial, la table utilisateur sous-jacente est indisponible pendant l’opération d’index, car l’index spatial détient un verrou de schéma.

La contrainte PRIMARY KEY dans la table utilisateur ne peut pas être modifiée quand un index spatial est défini sur une colonne de cette table. Pour modifier la contrainte PRIMARY KEY, commencez par supprimer chaque index spatial de la table. Après avoir modifié la contrainte PRIMARY KEY, vous pouvez recréer chaque index spatial.

Dans une opération individuelle de régénération de partition, vous ne pouvez pas spécifier d’index spatial. Toutefois, vous pouvez spécifier des index spatiaux dans une reconstruction de partition complète.

Pour changer des options spécifiques à un index spatial, telles que BOUNDING_BOX ou GRID, vous pouvez utiliser une instruction CREATE SPATIAL INDEX qui spécifie DROP_EXISTING = ON ou supprimer l’index spatial et en créer un. Pour obtenir un exemple, consultez CREATE SPATIAL INDEX (Transact-SQL).

Compression des données

Pour plus d’informations sur la compression de données, consultez Compression des données.

Pour évaluer dans quelle mesure le changement de compression PAGE et ROW affecte une table, un index ou une partition, utilisez la procédure stockée sp_estimate_data_compression_savings.

Les restrictions suivantes s'appliquent aux index partitionnés :

• Quand vous utilisez ALTER INDEX ALL ..., vous ne pouvez pas modifier le paramètre de compression d’une partition unique si la table contient des index non alignés.
• La syntaxe ALTER INDEX <index> ... REBUILD PARTITION ... reconstruit la partition spécifiée de l’index.
• La syntaxe ALTER INDEX <index> ... REBUILD WITH ... reconstruit toutes les partitions de l’index.

Statistiques

Quand vous exécutez ALTER INDEX ALL ... sur une table, seules les statistiques associées aux index sont mises à jour. Les statistiques automatiques ou manuelles créées sur la table (au lieu d’un index) ne sont pas mises à jour.

Autorisations

L’exécution de ALTER INDEX nécessite au moins une autorisation ALTER sur la table ou la vue.

Notes de version

• SQL Database n’utilise pas les options filegroup ni filestream.
• Les index columnstore ne sont pas disponibles avant SQL Server 2012 (11.x).
• Les opérations d’index pouvant être reprises sont disponibles depuis SQL Server 2017 (14.x) et Azure SQL Database.

Exemple de syntaxe de base

ALTER INDEX index1 ON table1 REBUILD;

ALTER INDEX ALL ON table1 REBUILD;

ALTER INDEX ALL ON dbo.table1 REBUILD;

Exemples : Index Columnstore

Ces exemples s’appliquent aux index columnstore.

R. Démonstration REORGANIZE

Cet exemple illustre le fonctionnement de la commande ALTER INDEX REORGANIZE. Il crée une table qui contient plusieurs rowgroups et montre comment REORGANIZE fusionne les rowgroups.

-- Create a database
CREATE DATABASE [ columnstore ];
GO

-- Create a rowstore staging table
CREATE TABLE [ staging ] (
     AccountKey              int NOT NULL,
     AccountDescription      nvarchar (50),
     AccountType             nvarchar(50),
     AccountCodeAlternateKey     int
     )

-- Insert 10 million rows into the staging table.
DECLARE @loop int
DECLARE @AccountDescription varchar(50)
DECLARE @AccountKey int
DECLARE @AccountType varchar(50)
DECLARE @AccountCode int

SELECT @loop = 0
BEGIN TRAN
    WHILE (@loop < 300000)
      BEGIN
        SELECT @AccountKey = CAST (RAND()*10000000 as int);
        SELECT @AccountDescription = 'accountdesc ' + CONVERT(varchar(20), @AccountKey);
        SELECT @AccountType = 'AccountType ' + CONVERT(varchar(20), @AccountKey);
        SELECT @AccountCode =  CAST (RAND()*10000000 as int);

        INSERT INTO  staging VALUES (@AccountKey, @AccountDescription, @AccountType, @AccountCode);

        SELECT @loop = @loop + 1;
    END
COMMIT

-- Create a table for the clustered columnstore index

CREATE TABLE cci_target (
     AccountKey              int NOT NULL,
     AccountDescription      nvarchar (50),
     AccountType             nvarchar(50),
     AccountCodeAlternateKey int
     )

-- Convert the table to a clustered columnstore index named inxcci_cci_target;
CREATE CLUSTERED COLUMNSTORE INDEX idxcci_cci_target ON cci_target;

Utilisez l’option TABLOCK pour insérer des lignes en parallèle. Depuis SQL Server 2016 (13.x), l’opération INSERT INTO peut s’exécuter en parallèle quand TABLOCK est utilisé.

INSERT INTO cci_target WITH (TABLOCK)
SELECT TOP 300000 * FROM staging;

Exécutez cette commande pour voir les rowgroups delta OPEN. Le nombre de rowgroups dépend du degré de parallélisme.

SELECT *
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id  = object_id('cci_target');

Exécutez cette commande pour forcer tous les rowgroups CLOSED et OPEN dans le columnstore.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Réexécutez cette commande et vous verrez que les plus petits rowgroups sont fusionnés dans un rowgroup compressé.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

B. Compression des rowgroups delta CLOSED dans le columnstore

Cet exemple utilise l’option REORGANIZE pour compresser chaque rowgroup delta CLOSED dans le columnstore en tant que rowgroup compressé. Cette opération n’est pas nécessaire, mais elle est utile quand le moteur de tuple ne compresse pas les rowgroups CLOSED assez rapidement.

Vous pouvez exécuter les deux exemples dans l’exemple de base de données AdventureWorksDW2022.

Cet exemple exécute REORGANIZE sur toutes les partitions.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

Cet exemple exécute REORGANIZE sur une partition spécifique.

-- REORGANIZE a specific partition
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0;

C. Compression de tous les rowgroups delta OPEN et CLOSED dans le columnstore

S’applique à : SQL Server (à compter de SQL Server 2016 (13.x)) et Azure SQL Database.

La commande REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = ON ) compresse chaque rowgroup delta OPEN et CLOSED dans le columnstore en tant que rowgroup compressé. Cette opération vide le deltastore et force la compression de toutes les lignes dans le columnstore. Elle est utile en particulier après l’exécution de nombreuses opérations d’insertion, dans la mesure où ces opérations stockent les lignes dans un ou plusieurs rowgroups delta.

REORGANIZE combine les rowgroups pour remplir les rowgroups avec un nombre maximal de lignes de <= 1 024 576. Par conséquent, quand vous compressez tous les rowgroups OPEN et CLOSED, vous ne vous retrouvez pas avec un grand nombre de rowgroups compressés qui contiennent uniquement quelques lignes. Vous souhaitez que les rowgroups soient aussi remplis que possible pour réduire la taille compressée et améliorer les performances des requêtes.

Les exemples suivants utilisent la base de données AdventureWorksDW2022.

Cet exemple déplace tous les rowgroups delta OPEN et CLOSED dans l’index columnstore.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Cet exemple montre comment déplacer tous les rowgroups delta OPEN AND CLOSED dans l’index columnstore d’une partition spécifique.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0 WITH (COMPRESS_ALL_ROW_GROUPS = ON);

D. Défragmentation d’un index columnstore en ligne

Ne s’applique pas à : SQL Server 2012 (11.x) et SQL Server 2014 (12.x).

Depuis SQL Server 2016 (13.x), REORGANIZE ne fait pas que compresser les rowgroups delta dans le columnstore. Il procède aussi à la défragmentation en ligne. Tout d’abord, il réduit la taille du columnstore en effaçant physiquement les lignes supprimées quand au moins 10 % des lignes d’un rowgroup ont été supprimées. Ensuite, il combine les rowgroups ensemble pour former des rowgroups plus grands qui peuvent contenir jusqu’à 1 024 576 lignes par rowgroup. Tous les rowgroups qui sont modifiés sont de nouveau compressés.

Notes

Depuis SQL Server 2016 (13.x), la reconstruction d’un index columnstore n’est plus nécessaire dans la plupart des cas, car REORGANIZE efface physiquement les lignes supprimées et fusionne les rowgroups. L’option COMPRESS_ALL_ROW_GROUPS force tous les rowgroups delta OPEN ou CLOSED dans le columnstore, ce qui ne pouvait être fait qu’avec une reconstruction. REORGANIZE est en ligne et se produit en arrière-plan afin que les requêtes puissent continuer quand l’opération a lieu.

L’exemple suivant effectue une opération REORGANIZE pour défragmenter l’index en supprimant physiquement les lignes qui ont été supprimées logiquement de la table et en fusionnant des groupes de lignes.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

E. Reconstruction d’un index cluster columnstore hors connexion

S’applique à : SQL Server (à compter de SQL Server 2012 (11.x))

Conseil

À compter de SQL Server 2016 (13.x) et dans Azure SQL Database, nous vous recommandons d’utiliser ALTER INDEX REORGANIZE au lieu de ALTER INDEX REBUILD pour les index columnstore.

Notes

Dans SQL Server 2012 (11.x) et SQL Server 2014 (12.x), REORGANIZE est uniquement utilisé pour compresser les rowgroups CLOSED dans le columnstore. La seule façon d’effectuer des opérations de défragmentation et de forcer tous les rowgroups delta dans le columnstore est de reconstruire l’index.

Cet exemple montre comment reconstruire un index cluster columnstore et forcer tous les rowgroups delta dans le columnstore. Cette première étape prépare une table FactInternetSales2 dans la base de données AdventureWorksDW2022 avec un index columnstore cluster et insère les données des quatre premières colonnes.

CREATE TABLE dbo.FactInternetSales2 (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL);

CREATE CLUSTERED COLUMNSTORE INDEX cci_FactInternetSales2
ON dbo.FactInternetSales2;

INSERT INTO dbo.FactInternetSales2
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
FROM dbo.FactInternetSales;

SELECT * FROM sys.column_store_row_groups;

Le résultat montre un rowgroup OPEN, ce qui signifie que SQL Server attend que d’autres lignes soient ajoutées avant de fermer le rowgroup et de déplacer les données vers le columnstore. L’instruction suivante reconstruit l’index cluster columnstore, ce qui force toutes les lignes dans le columnstore.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REBUILD;
SELECT * FROM sys.column_store_row_groups;

Le résultat de l’instruction SELECT indique que le rowgroup est COMPRESSED, ce qui signifie que les segments de colonne du rowgroup sont compressés et stockés dans l’index columnstore.

F. Reconstruction d’une partition d’un index cluster columnstore hors connexion

S’applique à : SQL Server (à compter de SQL Server 2012 (11.x))

Pour reconstruire une partition d’un grand index cluster columnstore, utilisez ALTER INDEX REBUILD avec l’option de partition. Cet exemple reconstruit la partition 12. À compter de SQL Server 2016 (13.x), nous vous recommandons de remplacer REBUILD par REORGANIZE.

ALTER INDEX cci_fact3
ON fact3
REBUILD PARTITION = 12;

G. Changement d’un index cluster columnstore pour utiliser la compression d’archivage

Ne s’applique pas à : SQL Server 2012 (11.x)

Vous pouvez choisir de réduire encore plus la taille d’un index cluster columnstore en utilisant l’option de compression de données COLUMNSTORE_ARCHIVE. C’est pratique pour les données plus anciennes que vous souhaitez conserver sur un stockage plus économique. Nous recommandons d’utiliser cette opération uniquement sur les données auxquelles vous n’accédez pas souvent, car la décompression est plus lente qu’avec la compression COLUMNSTORE normale.

L'exemple suivant reconstruit un index columnstore cluster pour utiliser la compression d'archivage, puis montre comment supprimer la compression d'archivage. Le résultat final utilise uniquement la compression columnstore.

Tout d’abord, préparez l’exemple en créant une table avec un index columnstore cluster. Compressez ensuite davantage la table à l’aide de la compression d’archivage.

--Prepare the example by creating a table with a clustered columnstore index.
CREATE TABLE SimpleTable (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL
);

CREATE CLUSTERED INDEX cci_SimpleTable ON SimpleTable (ProductKey);

CREATE CLUSTERED COLUMNSTORE INDEX cci_SimpleTable
ON SimpleTable
WITH (DROP_EXISTING = ON);

--Compress the table further by using archival compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO

Cet exemple supprime la compression d’archive et utilise uniquement la compression columnstore.

ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE);
GO

Exemples : index Rowstore

R. Regénérer un index

L'exemple suivant reconstruit un seul index portant sur la table Employee de la base de données AdventureWorks2022.

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

B. Régénérer tous les index sur une table et spécifier des options

L’exemple suivant spécifie le mot clé ALL. Cette opération permet de régénérer tous les index associés à la table Production.Product dans la base de données AdventureWorks2022. Trois options sont spécifiées.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

L'exemple suivant ajoute l'option ONLINE incluant l'option de verrou de faible priorité, et ajoute l'option de compression de ligne.

S’applique à : SQL Server (à compter de SQL Server 2014 (12.x)) et Azure SQL Database.

ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),
    DATA_COMPRESSION = ROW
);

C. Réorganiser un index avec compactage LOB

L'exemple suivant réorganise un seul index cluster dans la base de données AdventureWorks2022. L'index contenant un type de données LOB au niveau de la feuille, l'instruction compacte par la même occasion toutes les pages contenant les données LOB. Il n’est pas nécessaire de spécifier l’option WITH (LOB_COMPACTION = ON), car la valeur par défaut est ON (activé).

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE WITH (LOB_COMPACTION = ON);

D. Définir des options pour un index

L'exemple suivant définit plusieurs options sur l'index AK_SalesOrderHeader_SalesOrderNumber dans la base de données AdventureWorks2022.

ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

E. Désactiver un index

L'exemple suivant désactive un index non cluster sur la table Employee dans la base de données AdventureWorks2022.

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F. Désactiver les contraintes

L’exemple suivant désactive une contrainte PRIMARY KEY en désactivant l’index PRIMARY KEY dans la base de données AdventureWorks2022. La contrainte FOREIGN KEY portant sur la table sous-jacente est automatiquement désactivée et un avertissement s'affiche.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department DISABLE;

Le jeu de résultats retourne l'avertissement suivant.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'
on table 'EmployeeDepartmentHistory' referencing table 'Department'
was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G. Activer les contraintes

L'exemple suivant active les contraintes PRIMARY KEY et FOREIGN KEY désactivées dans l'exemple F.

La contrainte PRIMARY KEY est activée lors de la reconstruction de l'index PRIMARY KEY.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD;

La contrainte FOREIGN KEY est ensuite activée.

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. Régénérer un index partitionné

L'exemple suivant reconstruit une seule partition, celle portant le numéro de partition 5, de l'index partitionné IX_TransactionHistory_TransactionDate dans la base de données AdventureWorks2022. La partition 5 est régénérée avec ONLINE=ON et le délai d’attente de 10 minutes pour le verrou de basse priorité s’applique séparément à chaque verrou acquis par l’opération de régénération d’index. Si pendant ce temps, le verrou ne peut pas être obtenu pour terminer la régénération de l’index complet, l’instruction de l’opération de régénération elle-même est abandonnée en raison de ABORT_AFTER_WAIT = SELF.

S’applique à : SQL Server (à compter de SQL Server 2014 (12.x)) et Azure SQL Database.

-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5
   WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF)));
GO

I. Modifier le paramètre de compression d’un index

L'exemple suivant reconstruit un index sur une table rowstore non partitionnée.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO

J. Modifier le paramètre d’un index avec la compression XML

S'applique à : SQL Server 2022 (16.x) et versions ultérieures, base de données Azure SQL, Azure SQL Managed Instance

L'exemple suivant reconstruit un index sur une table rowstore non partitionnée.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (XML_COMPRESSION = ON);
GO

Pour obtenir davantage d’exemples de compression de données, consultez Compression de données.

K. Reconstruction d’index pouvant être reprise en ligne

S’applique à : SQL Server (à compter de SQL Server 2017 (14.x)) et Azure SQL Database.

Les exemples suivants montrent comment utiliser une reconstruction d’index pouvant être reprise en ligne.

1. Exécutez une régénération d’index en ligne en tant qu’opération pouvant être reprise avec MAXDOP = 1.

   ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON) ;
   

2. La réexécution de la même commande (voir ci-dessus) après qu’une opération d’index a été mise en pause reprend automatiquement l’opération de reconstruction d’index.

3. Exécutez une reconstruction d’index en ligne en tant qu’opération pouvant être reprise avec MAX_DURATION défini sur 240 minutes.

   ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240) ;
   

4. Mettez en pause une reconstruction d’index en ligne pouvant être reprise en cours d’exécution.

   ALTER INDEX test_idx on test_table PAUSE ;
   

5. Reprenez une reconstruction d’index en ligne pour une reconstruction d’index qui a été exécutée en tant qu’opération pouvant être reprise en spécifiant une nouvelle valeur pour MAXDOP de 4.

   ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP = 4) ;
   

6. Reprenez une opération de reconstruction d’index en ligne pour une reconstruction d’index en ligne qui a été exécutée comme pouvant être reprise. Définissez MAXDOP avec la valeur 2 et la durée d’exécution de l’index en cours d’exécution pouvant être repris avec la valeur 240 minutes. Si un index est bloqué sur le verrou, patientez 10 minutes, puis tuez tous les bloqueurs.

      ALTER INDEX test_idx on test_table
         RESUME WITH (MAXDOP = 2, MAX_DURATION = 240 MINUTES,
         WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10, ABORT_AFTER_WAIT = BLOCKERS)) ;
   

7. Abandonnez l’opération de reconstruction d’index pouvant être reprise qui est en cours d’exécution ou mise en pause.

   ALTER INDEX test_idx on test_table ABORT ;
   

Voir aussi

• Guide de conception et d’architecture d’index SQL Server
• Exécuter des opérations en ligne sur les index
• CREATE INDEX (Transact-SQL)
• CREATE SPATIAL INDEX (Transact-SQL)
• CREATE XML INDEX (Transact-SQL)
• DROP INDEX (Transact-SQL)
• Désactiver les index et les contraintes
• Index XML (SQL Server)
• Réorganiser et reconstruire des index
• sys.dm_db_index_physical_stats (Transact-SQL)
• EVENTDATA (Transact-SQL)