ALTER INDEX (Transact-SQL)

Article
05/23/2024

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics 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

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 1}
`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 1}
`ONLINE = ON`	Index XML Index spatial Index ^{Columnstore 1}
`RESUMABLE = ON` ²	Index pouvant être repris non pris en charge avec `ALL` le mot clé

¹ S’applique à SQL Server 2012 (11.x) et versions ultérieures et à Azure SQL Database.

² S’applique à SQL Server 2017 (14.x) et versions ultérieures, ainsi qu’à Azure SQL Database

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 qui peuvent être effectuées 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 un rapport des index relatifs à un objet, utilisez la vue de catalogue sys.indexes.

SQL Database prend en charge le format <database_name>.[schema_name].<table_or_view_name> de nom en trois parties lorsque l’database_name est la base de données actuelle ou que le database_name est tempdb et que le table_or_view_name commence par #.

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

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

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. Cette clause équivaut à DBCC DBREINDEX. 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’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 ALL elle est spécifiée et que la table sous-jacente est un tas, 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’utilisation, même lors de l’utilisationREBUILDNOLOCK, de l’isolation d’instantané validée (RCSI) ou de l’isolation d’instantané (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 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.

Avertissement

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 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 de réorganiser le niveau feuille d’index. L’opération REORGANIZE est la suivante :

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

Remarque

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.

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.
OFF n’a aucun effet sur un tas.

Opération REORGANIZE sur un index columnstore

Pour les index columnstore, REORGANIZE compresse chaque CLOSED rowgroup delta 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 CLOSED des rowgroups delta dans des rowgroups compressés. Le processus de tuple-mover (TM) d’arrière-plan se réveille régulièrement pour compresser CLOSED les rowgroups delta. 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 groupes de lignes et CLOSED les OPEN groupes de lignes, consultez l’option REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS) de 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 groupe de lignes compressé de 1 million de lignes a 100 000 lignes supprimées, SQL Server supprime les lignes supprimées et recompresse le groupe de lignes 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 en 1 rowgroup compressé de taille 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 10 % ou plus des lignes ont été supprimées logiquement, SQL Server 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. 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 fournit un moyen de forcer OPEN ou CLOSED de delta des rowgroups 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, quelle que soit la taille et l’état (CLOSED ou OPEN).
OFF force tous les CLOSED rowgroups 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 ] )

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

ACTIVÉ

Le pourcentage d’espace libre indiqué par FILLFACTOR est appliqué aux pages du 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 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.

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

Spécifie s’il faut stocker les résultats de tri dans tempdb. La valeur par défaut est à l’exception OFF 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 de tri intermédiaires utilisés pour générer l’index sont stockés dans tempdb. S’il tempdb se trouve sur un autre ensemble de disques que la base de données utilisateur, cela peut réduire le temps nécessaire pour créer 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 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. Par défaut, il s’agit de OFF.

ACTIVÉ

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é échouent.
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 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 }

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

ACTIVÉ

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_NORECOMPUTE OFFvaleur ou exécutez UPDATE STATISTICS sans la NORECOMPUTE clause.

Avertissement

Si vous désactivez la mise à jour automatique des statistiques, cela peut empêcher l’optimiseur de requête de choisir des plans d’exécution optimaux pour les requêtes qui impliquent la table. Vous devez utiliser cette option avec parcimonie et uniquement par un administrateur de base de données qualifié.

Ce paramètre n’empêche pas une mise à jour automatique avec l’analyse complète des statistiques liées à l’index, pendant l’opération de reconstruction.

STATISTICS_INCREMENTAL = { ON | OFF }

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

Quand ON, les statistiques créées sont par statistiques de partition. Quand OFF, l’arborescence des statistiques est supprimée et SQL Server 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 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. 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.

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) est brièvement conservé sur l’objet source. À la fin de l’opération, un verrou S est brièvement conservé sur la source 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.
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 les mises à jour de la table sous-jacente, mais autorise les opérations de lecture, telles que SELECT les instructions.

Pour plus d’informations, consultez Effectuer des 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 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 2017 (14.x) et versions ultérieures, 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 = heure [ MINUTES ] utilisée avec `RESUMABLE = ON` (nécessite `ONLINE = ON`)

S’applique à : SQL Server 2017 (14.x) et versions ultérieures, 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 qui peuvent être effectuées en ligne, consultez Instructions pour les opérations d’index en ligne.

Remarque

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

Remarque

Un index ne peut pas être réorganisé lorsqu’il ALLOW_PAGE_LOCKS est défini sur OFF.

OPTIMIZE_FOR_SEQUENTIAL_KEY = {ON | OFF }

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

Spécifie s’il faut optimiser ou pas la contention d’insertion de la 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 pendant l’opération d’index. Pour plus d’informations, consultez Configurer le degré maximal de parallélisme (option de configuration de serveur). Permet MAXDOP de 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 de manière syntactique pour tous les index XML, pour un index spatial ou un index XML principal, ALTER INDEX n’utilise actuellement qu’un seul processeur.

max_degree_of_parallelism peut avoir la valeur :

1: supprime la génération de plan parallèle.
>1: limite le nombre maximal de processeurs utilisés dans une opération d’index parallèle au nombre spécifié.
0 (par défaut) : utilise le nombre réel de processeurs ou moins en fonction de la charge de travail système actuelle.

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 dans 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 heures d’insertion et de mise à jour sur des lignes individuelles, SQL Server applique le délai aux rowgroups delta dans l’état CLOSED .

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 :

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

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

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

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, 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 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é 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. Par défaut, il s’agit de 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 2017 (14.x) et versions ultérieures, 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 d’index en ligne attend les verrous de faible priorité, ce qui permet à d’autres opérations de continuer pendant que l’opération de génération d’index en ligne attend. 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, et Azure SQL Database

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

ABORT

S’applique à : SQL Server 2017 (14.x) et versions ultérieures, 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 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. Quand vous exécutez ALTER INDEX REORGANIZE avec ou sans LOB_COMPACTION, la valeur de max degree of parallelism correspond à une opération monothread. 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 défini sur lecture seule. Si le mot clé ALL est spécifié et qu’un ou plusieurs index se trouvent dans un groupe de fichiers hors connexion ou en lecture seule, l’instruction échoue.

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 Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources.

Réorganiser les 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 Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources.

Important

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

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

Remarque

La documentation utilise le terme B-tree généralement en référence aux index. Dans les index rowstore, le Moteur de base de données implémente une arborescence B+. Cela ne s’applique pas aux index columnstore ou aux index sur les tables optimisé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 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 des options

Vous pouvez définir les options ALLOW_ROW_LOCKS, , OPTIMIZE_FOR_SEQUENTIAL_KEYALLOW_PAGE_LOCKS, 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	Informations
`ALLOW_ROW_LOCKS = ON or 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

Lors de la reconstruction d’un index et que l’option ONLINE est définie ONsur , les objets sous-jacents, les tables et les index associés, sont disponibles pour les requêtes et la modification des données. 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 période pendant le processus d’altération.

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 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, 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 RESUMABLE l’option spécifie l’intervalle de temps d’une reconstruction d’un index. Une fois cette fois utilisée, la reconstruction de l’index est suspendue 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). L’exécution d’une longue pause pour une opération d’index peut affecter les performances DML sur une table spécifique, ainsi que la capacité du disque de base de données, car les deux index (l’index d’origine et celui nouvellement créé) nécessitent un espace disque et doivent être mis à jour pendant les opérations DML. Si MAX_DURATION l’option est omise, l’opération d’index continue jusqu’à son achèvement ou jusqu’à ce 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.
Lors de la reprise d’une opération 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 dernière MAXDOP valeur est prise. SI l’option MAXDOP n’est pas spécifiée du tout pour l’opération de reconstruction 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 suspendue, elle peut être reprise à l’aide de RESUME l’option.
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
- Espace supplémentaire nécessaire pour maintenir la génération de l’index, y compris l’heure à laquelle l’index est suspendu
- Un état DDL empêchant toute modification DDL
Le nettoyage fantôme s’exécute pendant la phase de pause d’index, mais est suspendu 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)
- Regénérer un index qui a calculé ou TIMESTAMP des colonnes en tant que colonnes clés.
Si la table de base contient des colonnes métier pouvant être régénérées d’index cluster, une reconstruction d’index cluster nécessite un verrou Sch-M dans le démarrage de cette opération

Remarque

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 s’interrompt, une erreur est émise indiquant que l’opération a été suspendue et que la création de l’index n’a pas terminé. 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 2014 (12.x) et versions ultérieures, 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 d’index en ligne soit courte, en attendant que toutes les transactions ouvertes d’une table donnée se terminent et bloquent les nouvelles transactions à démarrer, peuvent affecter considérablement le débit, ce qui entraîne un ralentissement ou un délai d’expiration de la charge de travail, et limite 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 d’index en ligne attend les verrous de faible priorité, ce qui permet à d’autres opérations de continuer pendant que l’opération de génération d’index en ligne attend. Omettre l’option WAIT AT LOW PRIORITY équivaut à WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = heure [ MINUTES ]

Temps d’attente (valeur entière spécifiée en minutes) que l’index en ligne régénère attend avec une priorité faible lors de l’exécution de la commande DDL. 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 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 MAX_DURATION valeur de 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 requiert 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 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 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.

Compression des données

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

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 .

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

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 (à compter de SQL Server 2016 (13.x)) et Azure SQL Database.

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

Cet exemple montre comment déplacer tous les OPEN CLOSED 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 OPEN CLOSED 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.

Remarque

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

Remarque

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

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 ultérieur

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

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 2014 (12.x) et versions ultérieures, 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 PRIMARY KEY contrainte en désactivant l’index PRIMARY KEY dans la AdventureWorks2022 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 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 2014 (12.x) et versions ultérieures, 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 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, et Azure SQL Database

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;

Partage via

ALTER INDEX (Transact-SQL)

Syntaxe

Arguments

index_name

ALL

database_name

schema_name

table_or_view_name

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

PARTITION

DISABLE

Opération REORGANIZE sur un index rowstore

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )

Opération REORGANIZE sur un index columnstore

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

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

RESUMABLE = { 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 | duration [ minutes ] }

DATA_COMPRESSION

XML_COMPRESSION

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

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

RESUME

PAUSE

ABORT

Remarques

Reconstruire des index

Réorganiser les index

Désactiver les index

Définir des options

Options de verrous de ligne et de page

Opérations d’index en ligne

Opérations d’index pouvant être reprises

WAIT_AT_LOW_PRIORITY avec les opérations d’index en ligne

MAX_DURATION = heure [ MINUTES ]

ABORT_AFTER_WAIT = [ NONE | SELF | BLOCKERS ]

Restrictions des index spatiaux

Compression des données

Statistiques

Autorisations

Notes de version

Exemple de syntaxe de base

Exemples : index Columnstore

R. Démonstration 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

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

Commentaires

Ressources supplémentaires

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