Effectuer des opérations d’index en ligne

Cette rubrique explique comment créer, reconstruire ou supprimer des index en ligne dans SQL Server 2014 à l’aide de SQL Server Management Studio ou de Transact-SQL. L’option ONLINE permet à l’utilisateur simultané d’accéder à la table sous-jacente ou aux données d’index en cluster et à tous les index non cluster associés pendant ces opérations d’index. Par exemple, lorsqu’un index cluster est reconstruit par un utilisateur, cet utilisateur et d’autres utilisateurs peuvent continuer à mettre à jour et à interroger les données sous-jacentes. Lorsque vous effectuez des opérations de langage de définition de données (DDL) hors connexion, telles que la génération ou la reconstruction d’un index cluster ; ces opérations contiennent des verrous exclusifs sur les données sous-jacentes et les index associés. Cela empêche les modifications et les requêtes apportées aux données sous-jacentes jusqu’à ce que l’opération d’index soit terminée.

Dans cette rubrique

• Avant de commencer :

  Limitations et restrictions

  Sécurité

• Pour reconstruire un index en ligne, utilisez :

  SQL Server Management Studio

  Transact-SQL

Avant de commencer

Limitations et restrictions

• Nous vous recommandons d’effectuer des opérations d’index en ligne pour les environnements métier qui opèrent 24 heures par jour, sept jours par semaine, dans lesquels la nécessité d’une activité utilisateur simultanée pendant les opérations d’index est essentielle.

• L’option ONLINE est disponible dans les instructions Transact-SQL suivantes.

  • CREATE INDEX

  • ALTER INDEX

  • DROP INDEX

  • ALTER TABLE (Pour ajouter ou supprimer des contraintes UNIQUE ou CLÉ PRIMAIRE avec l’option d’index CLUSTERED)

• Pour plus de limitations et de restrictions concernant la création, la reconstruction ou la suppression d’index en ligne, consultez Recommandations pour les opérations d’index en ligne.

Sécurité

Autorisations

Nécessite l’autorisation ALTER sur la table ou la vue.

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 contenant 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 que vous souhaitez reconstruire en ligne et sélectionnez Propriétés.

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

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

8. Cliquez sur OK.

9. Cliquez avec le bouton droit sur l’index que vous souhaitez reconstruire en ligne, puis sélectionnez Reconstruire.

10. Dans la boîte de dialogue Reconstruire les index , vérifiez que l’index correct se trouve dans les index pour reconstruire la grille, puis cliquez sur OK.

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 site 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 obtenue (tas) vers le groupe NewGroup de fichiers à l’aide de la MOVE TO clause. Les vues de catalogue sys.indexes, sys.tables et sys.filegroups sont interrogées pour vérifier l'index et le placement des tables dans les groupes de fichiers avant et après le 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).