Exécuter des opérations d’index en ligne

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Cet article décrit comment créer, reconstruire ou supprimer (drop) des index en ligne dans SQL Server à 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 de langage de définition de données (DDL) 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 commandes de reconstruction d'index peuvent contenir des verrous exclusifs sur des index en cluster une fois qu'une colonne d'objet volumineuse est supprimée d'une table, même lorsqu'elle est effectuée en ligne.

Plateformes prises en charge

Les opérations d'index en ligne ne sont pas disponibles dans toutes les éditions de SQL Server. Pour plus d’informations, consultez Éditions et fonctionnalités prises en charge de SQL Server 2022.

Les opérations d’index en ligne sont disponibles dans Azure SQL Database et Azure SQL Managed Instance.

Limites

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.

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

Autorisations

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

Utiliser SQL Server Management Studio

  1. Dans l'Explorateur d'objets, sélectionnez 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. Sélectionnez 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 sélectionnez OK.

Utiliser Transact-SQL

L’exemple suivant reconstruit un index en ligne existant dans la base de données AdventureWorks.

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

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

-- 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 AdventureWorks2022
        ADD FILEGROUP NewGroup;
    ALTER DATABASE AdventureWorks2022
        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');

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

Contenu connexe