ALTER INDEX (Transact-SQL)

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)Base de données SQL dans Microsoft Fabric

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

Syntax

Syntaxe pour SQL Server, Azure SQL Database et Azure SQL Managed Instance.

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 } )
}

Syntaxe pour Azure Synapse Analytics and Analytics Platform System (PDW).

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 }
}

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. La spécification entraîne l’échec de ALL 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 dans SQL Server 2017 (14.x) et versions antérieures uniquement. Les versions ultérieures prennent en charge la reconstruction en ligne des index columnstore.
`REBUILD PARTITION = <partition_number>`	Index non partitionné, index XML, index spatial ou index désactivé
`REORGANIZE`	Index avec `ALLOW_PAGE_LOCKS` la valeur définie sur `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
`ONLINE = ON`	Index XML Index spatial Index columnstore
`RESUMABLE = ON`	Index pouvant être repris non pris en charge avec le `ALL` mot clé

Si ALL elle est spécifiée 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 ALL avec des causes de PARTITION reconstruction ou de réorganisation de toutes les partitions d’index ayant la même <partition_number> valeur. Pour plus d’informations sur les index partitionnés, consultez tables et index partitionnés.

Pour plus d’informations sur les opérations d’index en ligne, consultez Instructions pour les opérations d’index en ligne.

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 les détails d’index d’une table ou d’une vue, utilisez la vue catalogue sys.indexes .

Azure SQL Database prend en charge le format <database_name>.<schema_name>.<object_name> de nom en trois parties quand <database_name> il s’agit du nom de la base de données actuelle, ou <database_name> est tempdb et <object_name> commence par # ou ##. Si le nom du schéma est dbo, <schema_name> peut être omis.

RECONSTRUIRE [ AVEC ( <rebuild_index_option> [ ,... n ] ) ]

S’applique à : SQL Server 2012 (11.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Spécifie que l’index est reconstruit à l’aide des mêmes colonnes, du type d’index, de l’attribut d’unicité et de l’ordre de tri. REBUILD permet de réactiver un index désactivé. La reconstruction d’un index cluster ne régénère pas les 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’option d’index existantes dans sys.indexes sont appliquées . Pour toute option d’index dont la valeur n’apparaît pas dans sys.indexes, la valeur par défaut indiquée dans la définition d’argument de l’option s’applique.

Si ALL elle est spécifiée et que la table sous-jacente est un tas, l’opération de reconstruction n’a aucun effet sur le tas. Tous les index non cluster associés à la table sont donc reconstruits.

L’opération REBUILD peut être enregistrée minimalement si le modèle de récupération de base de données est enregistré en bloc ou simple.

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 de reconstruction :

Recompresse toutes les données dans le columnstore. Deux copies de l’index columnstore existent pendant que l’opération de reconstruction est en cours. Une fois la reconstruction terminée, le moteur de base de données supprime l’index columnstore d’origine.
Ne conserve pas l’ordre de tri, le cas échéant. Pour reconstruire un index columnstore et conserver ou introduire un ordre de tri, utilisez l’instruction CREATE [CLUSTERED] COLUMNSTORE INDEX ... ORDER (...) ... WITH (DROP_EXISTING = ON) .

Pour plus d’informations, consultez Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources.

PARTITION

Spécifie qu’une seule partition d’un index est 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.

Warning

La création et la reconstruction d’index non alignés sur une table avec plus de 1 000 partitions sont possibles, mais elles ne sont pas prises en charge. Cela peut entraîner une dégradation des performances ou une consommation excessive de mémoire pendant ces opérations. Microsoft recommande d’utiliser uniquement des index alignés lorsque le nombre de partitions dépasse 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.
AVEC ( <single_partition_rebuild_index_option> )

SORT_IN_TEMPDB, , MAXDOPDATA_COMPRESSIONet XML_COMPRESSION sont les options qui peuvent être spécifiées lorsque vous régénérez une partition unique à l’aide de la (PARTITION = partition_number) syntaxe. Les index XML ne peuvent pas être spécifiés dans une seule opération de reconstruction de partition.

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 contraintes.

Opération REORGANIZE sur un index rowstore

Pour les index rowstore, REORGANIZE spécifie de réorganiser le niveau feuille d’index. L’opération REORGANIZE est la suivante :

Toujours effectuée en ligne. Cela signifie que les verrous de table bloquants à long terme ne sont pas conservés et que les requêtes ou mises à jour des données de la table sous-jacente peuvent continuer pendant la ALTER INDEX REORGANIZE transaction.
Non autorisée sur un index désactivé.
Non autorisé lorsqu’il ALLOW_PAGE_LOCKS est défini sur OFF.
Non restauré lors de l’exécution d’une transaction et la transaction est restaurée.

Note

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.

Pour plus d’informations, consultez Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources.

RÉORGANISER WITH ( LOB_COMPACTION = { ON | OFF } )

S’applique aux index rowstore.

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 un compactage LOB 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.
OFF
- Les pages contenant des données d’objet volumineux ne sont pas compactées.
- OFF n’a aucun effet sur un tas.

Opération REORGANIZE sur un index columnstore

Pour les index columnstore, REORGANIZE compresse chaque rowgroup delta fermé 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 Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources.

REORGANIZE n’est pas nécessaire pour déplacer les rowgroups delta fermés dans des rowgroups compressés. Le processus tm (tuple-mover) d’arrière-plan se réveille régulièrement pour compresser les rowgroups delta fermés. Nous vous recommandons d’utiliser REORGANIZE quand tuple-mover tombe derrière. REORGANIZE peut compresser les rowgroups de manière plus agressive.
Pour compresser tous les rowgroups ouverts et fermés, consultez reORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS).

Pour les index columnstore dans SQL Server 2016 (13.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance, REORGANIZE effectuent les optimisations de défragmentation supplémentaires suivantes en ligne :

Supprime physiquement les lignes supprimées d’un rowgroup lorsque 10% ou plus des lignes ont été supprimées logiquement. Les octets supprimés sont récupérés sur le support physique. Par exemple, si un groupe de lignes compressé de 1 million de lignes a 100 000 lignes supprimées, le moteur de base de données supprime les lignes supprimées et recompresse le rowgroup avec 900 000 lignes.
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 en 1 rowgroup compressé avec 512 000 lignes. Cela suppose qu’il n’existe aucune limite de taille de dictionnaire ou de mémoire.
Pour les rowgroups dans lesquels 10% ou plusieurs lignes ont été supprimées logiquement, le moteur de base de données tente de combiner ce rowgroup avec 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. Le moteur de base de données favorise la combinaison de ces deux rowgroups pour compresser un nouveau rowgroup qui a 909 830 lignes.

RÉORGANISER WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

S’applique aux index columnstore.

S’applique à : SQL Server 2016 (13.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

COMPRESS_ALL_ROW_GROUPS permet de forcer les rowgroups delta ouverts ou fermés 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. Combiné avec les autres fonctionnalités de suppression et de défragmentation de fusion, il n’est plus nécessaire de reconstruire un index columnstore dans la plupart des situations.

ON

Force tous les rowgroups dans le columnstore, quelle que soit la taille et l’état (fermés ou ouverts).
OFF

Force tous les rowgroups fermés dans le columnstore.

Pour plus d’informations, consultez Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources.

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

Modifie les options d’index sans regénérer 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. Par défaut, il s’agit de OFF.

ON

Le pourcentage d’espace libre spécifié par le facteur de remplissage est appliqué aux pages de niveau intermédiaire de l’index. Si FILLFACTOR aucune valeur n’est spécifiée en même temps PAD_INDEX , ONla valeur du facteur de remplissage dans sys.indexes est utilisée.
OFF

Les pages de niveau intermédiaire sont presque entièrement remplies, ce qui laisse suffisamment d'espace libre pour au moins une ligne de la taille maximale permise par l'index, en prenant en compte l'ensemble de clés sur les pages intermédiaires. Cela se produit également si PAD_INDEX la valeur est définie ON , mais que le facteur de remplissage n’est pas spécifié.

Pour plus d’informations, consultez CREATE INDEX.

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.

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

Important

La création d’un index avec moins FILLFACTOR de 100 augmente la quantité d’espace de stockage occupée par le moteur de base de données, car le moteur de base de données redistribue les données en fonction du facteur de remplissage lors de la création ou de la reconstruction d’un index.

SORT_IN_TEMPDB = { ON | OFF }

Spécifie s’il faut stocker les résultats de tri temporaire dans tempdb. La valeur par défaut est à l’exception OFF d’Azure SQL Database Hyperscale. Pour toutes les opérations de génération d’index dans Hyperscale, SORT_IN_TEMPDB elle est toujours sauf ON si une build d’index pouvant être reprise est utilisée. Pour les builds d’index pouvant être reprise, SORT_IN_TEMPDB est toujours OFF.

ON

Les résultats de tri intermédiaires utilisés pour générer l’index sont stockés dans tempdb. Cela peut réduire le temps nécessaire à la création d’un index. 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 SORT_IN_TEMPDB option 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. Par défaut, il s’agit de OFF.

ON

Un message d’avertissement s’affiche quand des valeurs de clé en double sont insérées dans un index unique. Seules les lignes qui ne respectent pas la contrainte d’unicité ne sont pas insérées.
OFF

Une erreur s’affiche quand des valeurs de clé en double sont insérées dans un index unique. INSERT Toute l’opération est restaurée.

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

Pour afficher le IGNORE_DUP_KEY paramètre d’un index, utilisez la ignore_dup_key colonne dans la vue catalogue sys.indexes .

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

STATISTICS_NORECOMPUTE = { ON | OFF }

Désactivez ou activez l’option de mise à jour automatique des statistiques, AUTO_STATISTICS_UPDATEpour les statistiques sur l’index. Par défaut, il s’agit de OFF.

ON

Les mises à jour automatiques des statistiques sont désactivées après la reconstruction de l’index.
OFF

Les mises à jour automatiques des statistiques sont activées après la reconstruction de l’index.

Pour restaurer la mise à jour automatique des statistiques, définissez la STATISTICS_NORECOMPUTEOFFvaleur ou exécutez UPDATE STATISTICS sans la NORECOMPUTE clause.

Warning

Si vous désactivez la recomputation automatique des statistiques en définissant STATISTICS_NORECOMPUTE = ON, vous pouvez empêcher l’optimiseur de requête de choisir des plans d’exécution optimaux pour les requêtes impliquant la table.

Le paramètre STATISTICS_NORECOMPUTE pour ON n’empêche pas la mise à jour des statistiques d’index qui se produisent pendant l’opération de reconstruction d’index.

STATISTICS_INCREMENTAL = { ON | OFF }

S’applique à : SQL Server 2014 (12.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Quand ON, les statistiques créées sur l’index sont par statistiques de partition. Quand OFF, les statistiques existantes sont supprimées et le moteur de base de données recompute les statistiques. Par défaut, il s’agit de 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 dans les cas 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.

EN LIGNE = { ON | OFF }

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. Par défaut, il s’agit de OFF.

Pour un index XML ou un index spatial, seul ONLINE = OFF est pris en charge, et s’il ONLINE est défini sur ON une erreur est déclenché.

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.

ON

Les verrous de table à long terme ne sont pas maintenus pendant la durée de l'opération d'index. Pendant la phase principale de l’opération d’index, seul un verrou partagé d’intention (IS) est conservé sur la table source. Ceci permet d'exécuter les requêtes ou les mises à jour dans la table sous-jacente et ses index. Au début de l’opération, un verrou partagé (S) est conservé sur l’objet source pendant une courte période. À la fin de l’opération, pendant une courte période, un verrou partagé (S) est acquis sur l’objet si un index non cluster est créé. Un verrou de modification de schéma (Sch-M) est acquis lorsqu’un index cluster est créé ou supprimé en ligne et lorsqu’un index cluster ou non cluster est reconstruit. ONLINE ne peut pas être défini ON sur le moment où un index est créé sur une table temporaire locale.

Note

Vous pouvez utiliser l’option WAIT_AT_LOW_PRIORITY pour réduire ou éviter le blocage pendant les opérations d’index en ligne. Pour plus d’informations, 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. Une opération d’index hors connexion qui crée, reconstruit ou supprime un index cluster, spatial ou XML, ou reconstruit ou supprime un index non cluster, acquiert un verrou de modification de schéma (Sch-M) sur la table. Cela empêche tous les utilisateurs d'accéder à la table sous-jacente pendant la durée de l'opération. Une opération d’index hors connexion qui crée un index non cluster acquiert initialement un verrou partagé (S) sur la table. Cela empêche les modifications de la définition de table sous-jacente, mais permet de lire et de modifier les données de la table pendant que la build d’index est en cours.

Pour plus d’informations, consultez Effectuer des opérations d’index en ligne et des instructions pour les opérations d’index en ligne.

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 cluster désactivés
Index columnstore en cluster dans SQL Server 2017 (14.x)) et versions antérieures
Index columnstore non cluster dans SQL Server 2016 (13.x)) et versions antérieures
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 clé d’index. Dans SQL Server (à partir de SQL Server 2012 (11.x)), dans Azure SQL Database et dans Azure SQL Managed Instance, lorsqu’une table contient des colonnes varchar(max) ou varbinary(max), un index cluster contenant d’autres colonnes peut être généré ou reconstruit à l’aide de l’option ONLINE .

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

RESUMIBLE = { ON | OFF}

S’applique à : SQL Server 2017 (14.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

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

ON

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

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

MAX_DURATION = heure [ MINUTES ] utilisée avec `RESUMABLE = ON` (nécessite `ONLINE = ON`)

S’applique à : SQL Server 2017 (14.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Spécifie la durée pendant laquelle, en minutes entières, une opération d’index pouvant être reprise est exécutée avant d’être suspendue.

ALLOW_ROW_LOCKS = { ON | OFF }

Indique si les verrous de ligne sont autorisés ou non. Par défaut, il s’agit de ON.

ON

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. Par défaut, il s’agit de ON.

ON

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.

OPTIMIZE_FOR_SEQUENTIAL_KEY = {ON | OFF }

s’applique à: SQL Server 2019 (15.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Spécifie s’il faut ou non optimiser pour éviter la contention d’insertion de dernière page. Par défaut, il s’agit de OFF. Pour plus d’informations, consultez Clés séquentielles.

MAXDOP = max_degree_of_parallelism

Remplace l’option de configuration max degree of parallelism pour l’opération d’index. Pour plus d’informations, consultez Configurer l’option de configuration du serveur max degree of parallelism. Permet MAXDOP de limiter le degré de parallélisme et la consommation de ressources résultante pour une opération de génération d’index.

Bien que l’option MAXDOP soit prise en charge de manière syntactique pour tous les index XML et les index spatiaux, ALTER INDEX n’utilise actuellement qu’un seul processeur.

max_degree_of_parallelism peut avoir la valeur :

1

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

Limite le degré maximal de parallélisme utilisé dans une opération d’index parallèle au nombre spécifié ou inférieur en fonction de la charge de travail système actuelle.
0 (valeur par défaut)

Utilise le degré de parallélisme spécifié au niveau du serveur, de la base de données ou du groupe de charge de travail, sauf s’il est réduit en fonction de la charge de travail système actuelle.

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

Note

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 | Durée [ minutes ] }

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

Pour une table sur disque avec un index columnstore, spécifie le nombre minimal de minutes pendant lesquelles un rowgroup delta dans l’état fermé doit rester dans le magasin delta avant que le moteur de base de données puisse le compresser dans un rowgroup compressé. Étant donné que les tables sur disque ne suivent pas les heures d’insertion et de mise à jour sur des lignes individuelles, le moteur de base de données applique ce délai uniquement aux rowgroups du magasin delta dans l’état fermé.

La valeur par défaut est 0 minute.

Pour obtenir des recommandations sur le moment d’utilisation 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 :

NONE

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 2014 (12.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

S'applique uniquement aux index columnstore, y compris aux index columnstore non cluster et cluster. La spécification supprime toutes les autres compressions de COLUMNSTORE données, notamment COLUMNSTORE_ARCHIVE.
COLUMNSTORE_ARCHIVE

S’applique à : SQL Server 2014 (12.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

S'applique uniquement aux index columnstore, y compris aux index columnstore non cluster et cluster. COLUMNSTORE_ARCHIVE compresse davantage la partition spécifiée en une taille plus petite. 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, Azure SQL Database et 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 :

ON

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 les paramètres DATA_COMPRESSION ou XML_COMPRESSION s’appliquent. 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é en tant que nombres de partition 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)
);

RESUME

S’applique à : SQL Server 2017 (14.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Reprend une opération d’index suspendue manuellement, car la durée maximale est atteinte ou en raison d’un échec.

MAX_DURATION

Spécifie la durée pendant laquelle, en minutes entières, une opération d’index pouvant être reprise est exécutée après avoir repris avant qu’elle ne soit suspendue.
WAIT_AT_LOW_PRIORITY

Reprise d’une opération de génération d’index après une pause doit acquérir les verrous nécessaires. WAIT_AT_LOW_PRIORITY indique que l’opération de génération d’index acquiert des verrous de faible priorité, ce qui permet à d’autres opérations de continuer pendant l’attente de l’opération de génération d’index. 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 2017 (14.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Suspend une opération de génération d’index pouvant être reprise.

ABORT

S’applique à : SQL Server 2017 (14.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Abandonne une opération de génération d’index en cours d’exécution ou suspendue qui a été démarrée comme pouvant être reprise. Vous devez exécuter explicitement une ABORT commande pour mettre fin à une opération de génération d’index pouvant être reprise. Une défaillance ou une pause dans une opération d’index pouvant être reprise ne met pas fin à son exécution ; il laisse plutôt l’opération dans un état de pause indéfini.

Remarks

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 FILLFACTOR paramètre n’est pas spécifié dans la REBUILD clause, la valeur du facteur de remplissage stockée dans le catalogue système est utilisée pendant le processus de reconstruction. 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. À l’inverse, ALTER INDEX REORGANIZE il s’agit d’une seule opération threaded. Pour plus d’informations, consultez Configurer des opérations d’index parallèles.

Dans une base de données SQL dans Microsoft Fabric, ALTER INDEX ALL ce n’est pas pris en charge, mais ALTER INDEX <index name> l’est.

Reconstruire 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. Si 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 La désallocation différée.

Pour plus d’informations, consultez Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources.

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 lors de la spécification ALL.

Pour plus d’informations, consultez Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources.

Note

Pour une table avec un index columnstore ordonné, ALTER INDEX REORGANIZE ne trie pas les données. Pour trier à nouveau l’utilisation de données CREATE [CLUSTERED] COLUMNSTORE INDEX ... ORDER (...) ... WITH (DROP_EXISTING = ON).

Désactiver les 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 voir si un index est désactivé, utilisez la is_disabled colonne dans l’affichage sys.indexes catalogue.

Note

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, le moteur de base de données implémente une structure B+. Cela ne s’applique pas aux index columnstore ou aux index sur les tables à mémoire optimisée. 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 un index associé à une contrainte de clé primaire. Ces index sont requis par la réplication. Pour désactiver un tel index, vous devez d’abord supprimer la table de la composition. 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 reconstruction d’un index cluster désactivé ne peut pas être effectuée avec l’option ONLINE définie sur ON. Pour plus d’informations, consultez Désactiver les index et les contraintes.

Définir les options

Vous pouvez définir les options ALLOW_ROW_LOCKS, , ALLOW_PAGE_LOCKSOPTIMIZE_FOR_SEQUENTIAL_KEY, IGNORE_DUP_KEYet STATISTICS_NORECOMPUTE pour un index spécifié sans regé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 spécifié quand les options de verrou de ligne ou de page sont définies, 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	S’applique à
`ALLOW_ROW_LOCKS = ON` ou `OFF`	Segment de mémoire et tous les index non cluster associés.
`ALLOW_PAGE_LOCKS = ON`	Segment de mémoire et tous les index non cluster associés.
`ALLOW_PAGE_LOCKS = OFF`	Index non cluster, où tous les verrous de page ne sont pas autorisés. Pour le tas, seuls les verrous de page partagés (), de mise à jour (`SU`) et exclusifs (`X`) ne sont pas autorisés. Le moteur de base de données peut toujours acquérir des verrous de page d’intention (`IS`ou `IUIX`) à des fins internes.

Warning

Il n’est pas recommandé de désactiver les verrous de ligne ou de page sur un index. Des problèmes liés à l’accès concurrentiel peuvent se produire et certaines fonctionnalités peuvent être indisponibles. Par exemple, un index ne peut pas être réorganisé lorsqu’il ALLOW_PAGE_LOCKS est défini OFFsur .

Opérations d’index en ligne

Lors de la reconstruction d’un index et que l’option ONLINE est définie ONsur , les données de l’index, sa table associée et d’autres index sur la même table sont disponibles pour les requêtes et la modification. Vous pouvez également reconstruire en ligne une partie d'un index résidant sur une partition unique. Les verrous de table exclusifs ne sont conservés que pendant une courte durée à la fin de la reconstruction de l’index.

La réorganisation d'un index s'effectue toujours en ligne. Le processus contient des verrous uniquement pendant de courtes périodes et est peu susceptible de bloquer les requêtes ou les mises à jour.

Vous pouvez effectuer des opérations d’index en ligne simultanées sur la même table ou la même partition de table uniquement lorsque vous effectuez les opérations suivantes :

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 Effectuer des opérations d’index en ligne.

Opérations d’index pouvant être reprises

S’applique à : SQL Server 2017 (14.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Vous pouvez rendre un index en ligne régénéré. Cela signifie que la reconstruction d’index peut être arrêtée et redémarrée ultérieurement à partir du point où elle s’est arrêtée. Pour exécuter une reconstruction d’index comme pouvant être reprise, spécifiez l’option RESUMABLE = ON .

Les instructions suivantes s’appliquent aux opérations d’index pouvant être reprise :

Pour utiliser l’option RESUMABLE , vous devez également utiliser l’option ONLINE .
L’option RESUMABLE n’est pas conservée dans les métadonnées d’un index donné et s’applique uniquement à la durée de l’instruction DDL actuelle. C’est pourquoi la clause RESUMABLE = ON doit être spécifiée explicitement pour permettre la reprise.
L’option MAX_DURATION peut être spécifiée dans deux contextes :
- MAX_DURATION pour l’option RESUMABLE spécifie l’intervalle de temps pour un index généré. Une fois ce temps écoulé, et si la build d’index est toujours en cours d’exécution, elle est suspendue. Vous décidez quand la build d’un index suspendu peut être reprise. La durée en minutes MAX_DURATION doit être supérieure à 0 minutes et inférieure ou égale à une semaine (7 * 24 * 60 = 10080 minutes). Une longue pause dans une opération d’index peut avoir un impact notable sur les performances DML sur une table spécifique ainsi que sur la capacité du disque de base de données, car l’index d’origine et l’index nouvellement créé nécessitent un espace disque et doivent être mis à jour par les opérations DML. Si MAX_DURATION l’option est omise, l’opération d’index se poursuit jusqu’à l’achèvement ou jusqu’à ce qu’un échec se produise.
- MAX_DURATION pour l’option WAIT_AT_LOW_PRIORITY spécifie le temps d’attente à l’aide de verrous de faible priorité si l’opération d’index est bloquée, avant d’agir. Pour plus d’informations, consultez WAIT_AT_LOW_PRIORITY avec les opérations d’index en ligne.
Pour suspendre immédiatement l’opération d’index, vous pouvez exécuter la ALTER INDEX PAUSE commande ou exécuter la KILL <session_id> commande.
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.
La ABORT commande tue la session qui exécute une build d’index et annule l’opération d’index. Vous ne pouvez pas reprendre une opération d’index qui a été abandonnée.
Lors de la reprise d’une opération de reconstruction d’index suspendue, vous pouvez remplacer la MAXDOP valeur par une nouvelle valeur. Si MAXDOP elle n’est pas spécifiée lors de la reprise d’une opération d’index suspendue, la MAXDOP valeur utilisée pour la dernière reprise est utilisée. Si l’option MAXDOP n’est pas spécifiée du tout pour une opération de reconstruction d’index, la valeur par défaut est utilisée.

Une opération d’index pouvant être reprise s’exécute jusqu’à ce qu’elle se termine, suspend ou échoue. Si l’opération s’interrompt, une erreur est émise indiquant que l’opération a été suspendue et que la reconstruction de l’index n’a pas terminé. En cas d’échec de l’opération, une erreur est également émise.

Pour voir si une opération d’index est exécutée en tant qu’opération pouvant être reprise et pour vérifier son état d’exécution actuel, utilisez l’affichage catalogue sys.index_resumable_operations .

Resources

Les ressources suivantes sont requises pour les opérations d’index pouvant être reprise :

Espace supplémentaire nécessaire pour maintenir la génération de l’index, y compris l’heure à laquelle la génération est suspendue.
Du débit de journal supplémentaire pendant la phase de tri. L’utilisation globale de l’espace journal pour l’index pouvant être repris est inférieure à la reconstruction régulière de l’index en ligne et permet la troncation des journaux pendant cette opération.
Les instructions DDL qui tentent de modifier un index en cours de reconstruction ou sa table associée pendant que l’opération d’index est suspendue ne sont pas autorisées.
Le nettoyage des éléments fantômes est bloqué sur l’index en génération pendant toute la durée de l’opération, qu’elle soit en pause ou en cours d’exécution.
Si la table contient des colonnes métier, une build d’index cluster pouvant être reprise nécessite un verrou de modification de schéma (Sch-M) au début de l’opération.

Limitations fonctionnelles actuelles

Les opérations de reconstruction d’index pouvant être reprise ont les limitations suivantes :

L’option SORT_IN_TEMPDB = ON n’est pas prise en charge pour les opérations d’index pouvant être reprise.
Impossible d’exécuter la commande RESUMABLE = ON DDL à l’intérieur d’une transaction explicite.
Vous ne pouvez pas créer un index pouvant être repris qui contient :
- Colonnes derowversion calculées ou / en tant que colonnes clés.
- Colonne métier en tant que colonne incluse.
Les opérations d’index pouvant être reprise ne sont pas prises en charge pour :
- La commande ALTER INDEX REBUILD ALL
- La commande ALTER TABLE REBUILD
- Index columnstore
- Index filtrés
- Index désactivés

WAIT_AT_LOW_PRIORITY avec les opérations d’index en ligne

S’applique à : SQL Server 2014 (12.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Lorsque vous n’utilisez pas l’option WAIT_AT_LOW_PRIORITY , toutes les transactions bloquantes actives contenant des verrous sur la table ou l’index doivent être terminées pour que l’opération de reconstruction d’index démarre et se termine. Lorsque l’opération d’index en ligne démarre et avant qu’elle ne se termine, elle doit acquérir un verrou partagé (S) ou une modification de schéma (Sch-M) sur la table et la conserver pendant une courte période. Même si le verrou est conservé pendant une courte durée uniquement, il peut affecter considérablement le débit de la charge de travail, augmenter la latence des requêtes ou provoquer des délais d’exécution.

Pour éviter ces problèmes, l’option WAIT_AT_LOW_PRIORITY vous permet de gérer le comportement ou SSch-M les verrous requis pour qu’une opération d’index en ligne démarre et se termine, en sélectionnant parmi trois options. Dans tous les cas, si pendant le temps d’attente spécifié par MAX_DURATION = n [minutes] aucun blocage n’implique l’opération d’index, l’opération d’index se poursuit immédiatement.

WAIT_AT_LOW_PRIORITY fait attendre l’opération d’index en ligne à l’aide de verrous de faible priorité, ce qui permet à d’autres opérations utilisant des verrous de priorité normaux de continuer en attendant. Omettre l’option WAIT_AT_LOW_PRIORITY équivaut à WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = Temps [MINUTES]

Temps d’attente (valeur entière spécifiée en minutes) que l’opération d’index en ligne attend à l’aide de verrous de faible priorité. Si l’opération est bloquée pendant le MAX_DURATION temps, l’action spécifiée ABORT_AFTER_WAIT est exécutée. MAX_DURATION l’heure est toujours en minutes, et le mot MINUTES peut être omis.

ABORT_AFTER_WAIT= [NONE | SELF | BLOCKERS ]

NONE: Continuez à attendre le verrou avec une priorité normale.
SELF: quittez l’opération d’index en ligne en cours d’exécution, sans effectuer d’action. L’option SELF ne peut pas être utilisée lorsque MAX_DURATION la valeur est 0.
BLOCKERS: supprimez toutes les transactions utilisateur qui bloquent l’opération d’index en ligne afin que l’opération puisse continuer. L’option BLOCKERS nécessite que le principal exécutant l’instruction CREATE INDEX ou ALTER INDEX l’instruction dispose de l’autorisation ALTER ANY CONNECTION .

Vous pouvez utiliser les événements étendus suivants pour surveiller les opérations d’index qui attendent des verrous à faible priorité :

lock_request_priority_state
process_killed_by_abort_blockers
ddl_with_wait_at_low_priority

Restrictions des index spatiaux

Lorsque vous régénérez un index spatial, la table utilisateur sous-jacente n’est pas disponible pendant l’opération d’index.

La PRIMARY KEY contrainte dans la table utilisateur ne peut pas être modifiée alors qu’un index spatial est défini sur une colonne de cette table. Pour modifier la PRIMARY KEY contrainte, supprimez tout d’abord chaque index spatial de la table. Après avoir modifié la PRIMARY KEY contrainte, vous pouvez recréer chacun des index spatiaux.

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 table.

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.

Compression des données

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

Voici les points clés à prendre en compte dans le contexte des opérations de génération d’index lorsque la compression des données est utilisée :

La compression permet de stocker un plus grand nombre de lignes dans une page, mais ne modifie pas la taille maximale des lignes.
Les pages non-feuille d'un index ne sont pas compressées par le biais de la compression de page, mais par le biais de la compression de ligne.
Chaque index non cluster dispose d'un paramètre de compression individuel et n'hérite pas du paramètre de compression de la table sous-jacente.
Lorsqu'un index cluster est créé sur un segment de mémoire, l'index cluster hérite de l'état de compression du segment, à moins qu'un autre état de compression soit spécifié.

Les considérations suivantes appliquent la reconstruction d’index partitionnés :

Vous ne pouvez pas modifier le paramètre de compression d'une partition unique si la table possède des index non alignés.
La ALTER INDEX <index> ... REBUILD PARTITION ... WITH DATA_COMPRESSION = ... syntaxe reconstruit la partition spécifiée de l’index avec l’option de compression spécifiée. Si la WITH DATA_COMPRESSION clause est omise, l’option de compression existante est utilisée.
La ALTER INDEX <index> ... REBUILD PARTITION = ALL syntaxe reconstruit toutes les partitions de l’index à l’aide des options de compression existantes.
La syntaxe ALTER INDEX <index> ... REBUILD PARTITION = ALL (WITH ...) reconstruit toutes les partitions de l’index. Vous pouvez choisir une compression différente pour différentes partitions à l’aide de la DATA_COMPRESSION = ... ON PARTITIONS ( ...) clause.

Pour évaluer la façon dont la modification PAGE et ROW la compression affectent une table, un index ou une partition, utilisez la procédure stockée sp_estimate_data_compression_savings .

Statistics

Lorsque vous régénérez un index, les statistiques sur l’index sont mises à jour avec l’analyse complète des index non partitionnés et le ratio d’échantillonnage par défaut pour les index partitionnés. Aucune autre statistique de la table n’est mise à jour dans le cadre de la reconstruction d’index.

Permissions

L’autorisation ALTER sur la table ou la vue est requise.

Notes de version

Azure SQL Database ne prend pas en charge les groupes de fichiers autres que PRIMARY.
Azure SQL Database et Azure SQL Managed Instance ne prennent pas en charge FILESTREAM les options.
Les index Columnstore ne sont pas disponibles avant SQL Server 2012 (11.x).
Les opérations d’index pouvant être reprise sont disponibles dans SQL Server 2017 (14.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance.

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.

A. Démo 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 TRANSACTION

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. À compter de SQL Server 2016 (13.x), l’opération INSERT INTO peut s’exécuter en parallèle quand elle TABLOCK est utilisée.

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

Exécutez cette commande pour afficher les OPEN rowgroups delta. 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 CLOSED groupes de lignes dans OPEN le columnstore.

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

Réexécutez cette commande et vous voyez que des rowgroups plus petits 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 permettant de compresser chaque CLOSED rowgroup delta dans le columnstore en tant que rowgroup compressé. Cela n’est pas nécessaire, mais est utile lorsque le tuple-mover ne compresse CLOSED pas suffisamment rapidement les rowgroups.

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

Cet exemple s’exécute REORGANIZE sur toutes les partitions.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

Cet exemple s’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 2016 (13.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

La commande REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON) compresse chaque OPEN rowgroup CLOSED delta 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 des rowgroups pour remplir des rowgroups jusqu’à un nombre maximal de lignes <= 1 024 576. Par conséquent, lorsque vous compressez tous OPEN et CLOSED rowgroups, vous ne vous retrouvez pas avec un grand nombre de rowgroups compressés qui n’ont que 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 AdventureWorksDW2025.

Cet exemple montre comment déplacer tous les OPENCLOSED rowgroups delta 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 OPENCLOSED rowgroups delta 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).

À compter de SQL Server 2016 (13.x), REORGANIZE effectue plus que de 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.

Note

À compter de SQL Server 2016 (13.x), la reconstruction d’un index columnstore n’est plus nécessaire dans la plupart des situations, car REORGANIZE il supprime physiquement les lignes supprimées et fusionne les rowgroups. L’option COMPRESS_ALL_ROW_GROUPS force tous les OPEN rowgroups CLOSED delta dans le columnstore, ce qui n’a pu être effectué qu’avec une reconstruction. REORGANIZE est en ligne et se produit en arrière-plan afin que les requêtes puissent continuer à mesure que l’opération se produit.

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

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

E. Reconstruction d’un index cluster columnstore hors connexion

S’applique à : SQL Server, Azure SQL Database et Azure SQL Managed Instance

Tip

À 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.

Note

Dans SQL Server 2012 (11.x) et SQL Server 2014 (12.x), REORGANIZE est utilisé uniquement pour compresser CLOSED des rowgroups 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 AdventureWorksDW2025 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;

Les résultats montrent un OPEN rowgroup, ce qui signifie que SQL Server attend que d’autres lignes soient ajoutées avant de fermer le rowgroup et déplace 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;

Les résultats de l’instruction montrent que le SELECT rowgroup est COMPRESSED, ce qui signifie que les segments de colonne du rowgroup sont désormais compressés et stockés dans le columnstore.

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

S’applique à : SQL Server 2012 (11.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

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 la taille d’un index columnstore cluster encore plus en utilisant l’option COLUMNSTORE_ARCHIVE de compression des données. C’est pratique pour les données plus anciennes que vous souhaitez conserver sur un stockage plus économique. Nous vous recommandons d’utiliser uniquement cette option sur les données qui ne sont pas souvent accessibles, car la décompression est plus lente que la compression normale COLUMNSTORE .

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

A. Regénérer un index

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

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 AdventureWorks2025. 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 2014 (12.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

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 AdventureWorks2025. 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 AdventureWorks2025.

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 AdventureWorks2025.

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F. Désactiver les contraintes

L’exemple suivant désactive une PRIMARY KEY contrainte en désactivant l’index PRIMARY KEY dans la AdventureWorks2025 base de données. La FOREIGN KEY contrainte sur la table sous-jacente est automatiquement désactivée et le message d’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 PRIMARY KEY contraintes qui FOREIGN KEY ont été désactivées dans l’exemple F.

La PRIMARY KEY contrainte est activée en regénérer l’index PRIMARY KEY .

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD;

La FOREIGN KEY contrainte 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 AdventureWorks2025. 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 2014 (12.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

-- 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 plus d’exemples de compression de données, consultez Compression des données.

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

S’applique à : SQL Server 2017 (14.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

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

Exécutez une reconstruction d’index en ligne en tant qu’opération pouvant être reprise avec MAXDOP = 1. L’exécution de la même commande une fois qu’une opération d’index a été suspendue, reprend automatiquement l’opération de reconstruction d’index.

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

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

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

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;

Reprendre une reconstruction d’index en ligne pour une reconstruction d’index exécutée en tant qu’opération pouvant être reprise spécifiant une nouvelle valeur définie sur MAXDOP 4.

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

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éfini MAXDOP sur 2, définissez l’heure d’exécution de l’index en cours d’exécution comme pouvant être reprise à 240 minutes et, si un index est bloqué sur le verrou, attendez 10 minutes et après cela, 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));

Annuler l’opération de reconstruction d’index pouvant être reprise qui est en cours d’exécution ou suspendue.

ALTER INDEX test_idx on test_table ABORT;

Rétroaction

Cette page vous a-t-elle été utile ?

Last updated on 2025-11-19

Partager via

ALTER INDEX (Transact-SQL)

Syntax

Arguments

index_name

ALL

database_name

schema_name

table_or_view_name

RECONSTRUIRE [ AVEC ( <rebuild_index_option> [ ,... n ] ) ]

PARTITION

DISABLE

Opération REORGANIZE sur un index rowstore

RÉORGANISER WITH ( LOB_COMPACTION = { ON | OFF } )

Opération REORGANIZE sur un index columnstore

RÉORGANISER WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

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

PAD_INDEX = { ON | OFF }

FILLFACTOR = fillfactor

SORT_IN_TEMPDB = { ON | OFF }

IGNORE_DUP_KEY = { ON | OFF }

STATISTICS_NORECOMPUTE = { ON | OFF }

STATISTICS_INCREMENTAL = { ON | OFF }

EN LIGNE = { ON | OFF }

RESUMIBLE = { ON | OFF}

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

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

OPTIMIZE_FOR_SEQUENTIAL_KEY = {ON | OFF }

MAXDOP = max_degree_of_parallelism

COMPRESSION_DELAY = { 0 | Durée [ minutes ] }

DATA_COMPRESSION

XML_COMPRESSION

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

RESUME

PAUSE

ABORT

Remarks

Reconstruire des index

Réorganisation des index

Désactiver les index

Définir les options

Options de verrous de ligne et de page

Opérations d’index en ligne

Opérations d’index pouvant être reprises

Resources

Limitations fonctionnelles actuelles

WAIT_AT_LOW_PRIORITY avec les opérations d’index en ligne

Restrictions des index spatiaux

Compression des données

Statistics

Permissions

Notes de version

Exemple de syntaxe de base

Exemples : index Columnstore

A. Démo REORGANIZE

B. Compression des rowgroups delta CLOSED dans le columnstore

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

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

E. Reconstruction d’un index cluster columnstore hors connexion

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

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

Exemples : index Rowstore

A. Regénérer un index

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

C. Réorganiser un index avec compactage LOB

D. Définir des options pour un index

E. Désactiver un index

F. Désactiver les contraintes

G. Activer les contraintes

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

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

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

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

Contenu connexe

Rétroaction

Ressources additionnelles

MAX_DURATION = heure [ MINUTES ] utilisée avec `RESUMABLE = ON` (nécessite `ONLINE = ON`)