Exécuter des opérations en ligne sur les index

Cette rubrique explique comment créer, reconstruire ou supprimer des index en ligne dans SQL Server 2012 à l'aide de SQL Server Management Studio ou de Transact-SQL. L'option ONLINE permet l'accès simultané des utilisateurs aux tables sous-jacentes ou aux données des index cluster et aux index non-cluster associés pendant ces opérations sur les index. Par exemple, pendant qu'un index cluster est reconstruit par un utilisateur, cet utilisateur et d'autres peuvent continuer de mettre à jour et d'interroger les données sous-jacentes. Lorsque vous effectuez des opérations DDL (Data Definition Language) en mode hors connexion, comme la construction ou la reconstruction d'un index cluster, ces opérations posent des verrous exclusifs sur les données sous-jacentes et les index associés. Ces verrous empêchent toute modification et toute interrogation des données sous-jacentes jusqu'à la fin de l'opération effectuée sur l'index.

[!REMARQUE]

Les opérations d'index en ligne ne sont pas disponibles dans toutes les édition de SQL Server. Pour plus d'informations, consultez Fonctionnalités prises en charge par les éditions de SQL Server 2012.

Dans cette rubrique

• Avant de commencer :

  Limitations et restrictions

  Sécurité

• Pour reconstruire un index en ligne à l'aide de :

  SQL Server Management Studio

  Transact-SQL

Avant de commencer

Limitations et restrictions

• Il est recommandé d'effectuer les opérations sur les index en ligne dans les environnements qui sont opérationnels 24 heures sur 24 et 7 jours sur 7, dans lesquels il est vital de maintenir l'accès des utilisateurs.

• L'option ONLINE peut être utilisée dans les instructions Transact-SQL suivantes.

  • CREATE INDEX

  • ALTER INDEX

  • DROP INDEX

  • ALTER TABLE (Pour ajouter ou supprimer des contraintes UNIQUE ou PRIMARY KEY avec l'option d'index CLUSTERED)

• Pour plus d'informations sur les limitations et les restrictions de création, reconstruction ou suppression d'index en ligne, consultez Instructions pour les opérations d'index en ligne.

Sécurité

Autorisations

Nécessite une autorisation ALTER sur la table ou la vue.

[Top]

Utilisation de SQL Server Management Studio

Pour reconstruire un index en ligne

1. Dans l'Explorateur d'objets, cliquez sur le signe plus (+) pour développer la base de données qui contient la table sur laquelle vous souhaitez reconstruire un index en ligne.

2. Développez le dossier Tables.

3. Cliquez sur le signe plus (+) pour développer la table sur laquelle vous souhaitez reconstruire un index en ligne.

4. Développez le dossier Index.

5. Cliquez avec le bouton droit sur l'index à reconstruire en ligne, puis sélectionnez Propriétés.

6. Sous Sélectionner une page, sélectionnez Options.

7. Sélectionnez Autoriser le traitement des instructions DML en ligne, puis sélectionnez True dans la liste.

8. Cliquez sur OK.

9. Cliquez avec le bouton droit sur l'index à reconstruire en ligne, puis sélectionnez Reconstruire.

10. Dans la boîte de dialogue Reconstruire les index, vérifiez que l'index correct figure dans la grille Index à reconstruire, puis cliquez sur OK.

[Top]

Utilisation de Transact-SQL

Pour créer, reconstruire ou supprimer un index en ligne

1. Dans l'Explorateur d'objets, connectez-vous à une instance du Moteur de base de données.

2. Dans la barre d'outils standard, cliquez sur Nouvelle requête.

3. Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter. L'exemple reconstruit un index en ligne existant.

   USE AdventureWorks2012;
   GO
   ALTER INDEX AK_Employee_NationalIDNumber ON HumanResources.Employee
   REBUILD WITH (ONLINE = ON);
   GO
   

   L'exemple suivant supprime un index cluster en ligne et déplace la table résultante (segment de mémoire) vers le groupe de fichiers NewGroup en utilisant la clause MOVE TO. Les affichages catalogue sys.indexes, sys.tables et sys.filegroups sont interrogés pour vérifier le placement de l'index et de la table dans les groupes de fichiers avant et après l'opération de déplacement.

   USE AdventureWorks2012;
   GO
   --Create a clustered index on the PRIMARY filegroup if the index does not exist.
   IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 
               N'AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate')
       CREATE UNIQUE CLUSTERED INDEX
           AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate 
       ON Production.BillOfMaterials (ProductAssemblyID, ComponentID, 
           StartDate)
       ON 'PRIMARY';
   GO
   -- Verify filegroup location of the clustered index.
   SELECT t.name AS [Table Name], i.name AS [Index Name], i.type_desc,
       i.data_space_id, f.name AS [Filegroup Name]
   FROM sys.indexes AS i
       JOIN sys.filegroups AS f ON i.data_space_id = f.data_space_id
       JOIN sys.tables as t ON i.object_id = t.object_id
           AND i.object_id = OBJECT_ID(N'Production.BillOfMaterials','U')
   GO
   --Create filegroup NewGroup if it does not exist.
   IF NOT EXISTS (SELECT name FROM sys.filegroups
                   WHERE name = N'NewGroup')
       BEGIN
       ALTER DATABASE AdventureWorks2012
           ADD FILEGROUP NewGroup;
       ALTER DATABASE AdventureWorks2012
           ADD FILE (NAME = File1,
               FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\File1.ndf')
           TO FILEGROUP NewGroup;
       END
   GO
   --Verify new filegroup
   SELECT * from sys.filegroups;
   GO
   -- Drop the clustered index and move the BillOfMaterials table to
   -- the Newgroup filegroup.
   -- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.
   DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate 
       ON Production.BillOfMaterials 
       WITH (ONLINE = ON, MOVE TO NewGroup);
   GO
   -- Verify filegroup location of the moved table.
   SELECT t.name AS [Table Name], i.name AS [Index Name], i.type_desc,
       i.data_space_id, f.name AS [Filegroup Name]
   FROM sys.indexes AS i
       JOIN sys.filegroups AS f ON i.data_space_id = f.data_space_id
       JOIN sys.tables as t ON i.object_id = t.object_id
           AND i.object_id = OBJECT_ID(N'Production.BillOfMaterials','U');
   GO
   

Pour plus d'informations, consultez ALTER INDEX (Transact-SQL).

[Top]