線上執行索引作業Perform Index Operations Online

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

此主題描述如何使用 SQL Server 2019 (15.x)SQL Server 2019 (15.x)SQL Server Management StudioSQL Server Management Studio ,在 Transact-SQLTransact-SQL中線上建立、重建或卸除索引。This topic describes how to create, rebuild, or drop indexes online in SQL Server 2019 (15.x)SQL Server 2019 (15.x) by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 在這些索引作業期間,ONLINE 選項可讓並行使用者存取基礎資料表或叢集索引資料,以及任何關聯的非叢集索引。The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations. 例如,當某個使用者正在重建叢集索引時,此使用者和其他人可以繼續更新和查詢基礎資料。For example, while a clustered index is being rebuilt by one user, that user and others can continue to update and query the underlying data. 當您離線執行資料定義語言 (DDL) 作業 (例如建立或重建叢集索引) 時,這些作業會保有基礎資料和關聯索引的獨佔鎖定。When you perform data definition language (DDL) operations offline, such as building or rebuilding a clustered index; these operations hold exclusive locks on the underlying data and associated indexes. 這可避免在索引作業完成之前對基礎資料進行修改和查詢。This prevents modifications and queries to the underlying data until the index operation is complete.

注意

SQL ServerSQL Server 的所有版本都無法使用線上索引作業。Online index operations are not available in every SQL ServerSQL Server edition. 如需詳細資訊,請參閱 SQL Server 的版本及支援功能For more information, see Editions and supported features of SQL Server.

本主題內容In This Topic

開始之前Before You Begin

限制事項Limitations and Restrictions

  • 建議您針對全年無休的商務環境執行線上索引作業,在索引作業期間,這類環境的並行使用者活動需求相當重要。We recommend performing online index operations for business environments that operate 24 hours a day, seven days a week, in which the need for concurrent user activity during index operations is vital.

  • ONLINE 選項可用於下列 Transact-SQLTransact-SQL 陳述式。The ONLINE option is available in the following Transact-SQLTransact-SQL statements.

  • 如需更多有關線上建立、重建或卸除索引的限制,請參閱 線上索引作業的指導方針For more limitations and restrictions concerning creating, rebuilding, or dropping indexes online, see Guidelines for Online Index Operations.

SecuritySecurity

權限Permissions

需要資料表或檢視表的 ALTER 權限。Requires ALTER permission on the table or view.

使用 SQL Server Management StudioUsing SQL Server Management Studio

若要線上重建索引To rebuild an index online

  1. 在 [物件總管] 中,按一下加號展開包含您要線上重建索引之資料表的資料庫。In Object Explorer, click the plus sign to expand the database that contains the table on which you want to rebuild an index online.

  2. 展開 [資料表] 資料夾。Expand the Tables folder.

  3. 按一下加號展開要線上重建索引的資料表。Click the plus sign to expand the table on which you want to rebuild an index online.

  4. 展開 [索引] 資料夾。Expand the Indexes folder.

  5. 以滑鼠右鍵按一下要線上重建的索引,然後選取 [屬性] 。Right-click the index that you want to rebuild online and select Properties.

  6. [選取頁面] 底下,選取 [選項]Under Select a page, select Options.

  7. 選取 [允許線上 DML 處理] ,然後從清單中選取 [True]Select Allow online DML processing, and then select True from the list.

  8. 按一下 [確定] 。Click OK.

  9. 以滑鼠右鍵按一下要線上重建的索引,然後選取 [重建] 。Right-click the index that you want to rebuild online and select Rebuild.

  10. [重建索引] 對話方塊中,確認 [要重建的索引] 方格中有正確索引,然後按一下 [確定]In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to rebuild grid and click OK.

使用 Transact-SQLUsing Transact-SQL

若要線上建立、重建或卸除索引To create, rebuild, or drop an index online

下列範例會在 AdventureWorks 資料庫中重建現有線上索引。The following example rebuilds an existing online index in the AdventureWorks database.

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

下列範例會在線上刪除叢集索引,並利用 NewGroup 子句,將產生的資料表 (堆積) 移到 MOVE TO 檔案群組。The following example deletes a clustered index online and moves the resulting table (heap) to the filegroup NewGroup by using the MOVE TO clause. 它會查詢 sys.indexessys.tablessys.filegroups 目錄檢視來確認在移動之前和之後,索引和資料表在檔案群組中的位置。The sys.indexes, sys.tables, and sys.filegroups catalog views are queried to verify the index and table placement in the filegroups before and after the move.

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

如需詳細資訊,請參閱 ALTER INDEX (Transact-SQL)For more information, see ALTER INDEX (Transact-SQL).

後續步驟Next steps