Columnstore-Index-DefragmentierungColumnstore indexes - defragmentation

GILT FÜR: jaSQL Server jaAzure SQL-DatenbankjaAzure SQL Data Warehouse jaParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Aufgaben für die Defragmentierung von Columnstore-Indizes.Tasks for defragmenting columnstore indexes.

Verwenden von ALTER INDEX REORGANIZE, um einen Columnstore-Index online zu defragmentierenUse ALTER INDEX REORGANIZE to defragment a columnstore index online

Gilt für: SQL ServerSQL Server (ab SQL Server 2016 (13.x)SQL Server 2016 (13.x)), SQL-DatenbankSQL DatabaseApplies to: SQL ServerSQL Server (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x)), SQL-DatenbankSQL Database

Nach dem Ausführen verschiedener Auslastungstypen weist Ihr Deltastore möglicherweise mehrere kleine Zeilengruppen auf.After performing loads of any type, you can have multiple small rowgroups in the deltastore. Sie können ALTER INDEX REORGANIZE verwenden, um alle Zeilengruppen in den Columnstore zu zwingen und anschließend zu kombinieren, sodass weniger Zeilengruppen mit mehr Zeilen entstehen.You can use ALTER INDEX REORGANIZE to force all of the rowgroups into the columnstore, and then to combine the rowgroups into fewer rowgroups with more rows. Der Neuorganisationsvorgang entfernt auch Zeilen, die aus dem Columnstore gelöscht wurden.The reorganize operation will also remove rows that have been deleted from the columnstore.

Weitere Informationen finden Sie in folgenden Blogbeiträgen des Teamblogs für SQL Server-Datenbank-Engine.For more information, refer to the following blog posts on the SQL Database Engine Team Blog.

Empfehlungen zum NeuorganisierenRecommendations for reorganizing

Organisieren Sie einen Columnstore-Index nach einem oder mehreren Datenladevorgängen neu, um so schnell wie möglich die bessere Abfrageleistung nutzbar zu machen.Reorganize a columnstore index after one or more data loads to achieve query performance benefits as quickly as possible. Beim Neuorganisieren sind zunächst zusätzliche CPU-Ressourcen zum Komprimieren der Daten erforderlich. Dies kann die Gesamtleistung des Systems beeinträchtigen.Reorganizing will initially require additional CPU resources to compress the data, which could slow overall system performance. Sobald die Daten jedoch komprimiert sind, kann sich die Abfrageleistung verbessern.However, as soon as the data is compressed, query performance can improve.

Verwenden Sie das Beispiel in sys.dm_db_column_store_row_group_physical_stats (Transact-SQL), um die Fragmentierung zu berechnen.Use the example in sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) to compute the fragmentation. Dadurch können Sie bestimmen, ob ein REORGANIZE-Vorgang sinnvoll ist.This helps you to determine whether it is worthwhile to perform a REORGANIZE operation.

Beispiel: So funktioniert eine NeuorganisierungExample: How reorganizing works

In diesem Beispiel wird zeigt, wie ALTER INDEX REORGANIZE alle Deltastore-Zeilengruppen in den Columnstore zwingen und sie kombinieren kann.This example shows how ALTER INDEX REORGANIZE can force all deltastore rowgroups into the columnstore and then combine the rowgroups.

  1. Führen Sie diese Transact-SQL-Anweisung aus, um eine Stagingtabelle mit 300.000 Zeilen zu erstellen.Run this Transact-SQL to create a staging table that contains 300,000 rows. Wir verwenden dies, um Zeilen massenhaft in einen Columnstore-Index zu laden.We will use this to bulk load rows into a columnstore index.

    USE master;  
    GO  
    
    IF EXISTS (SELECT name FROM sys.databases  
        WHERE name = N'[columnstore]')  
        DROP DATABASE [columnstore];  
    GO  
    
    CREATE DATABASE [columnstore];  
    GO  
    
    IF EXISTS (SELECT name FROM sys.tables  
        WHERE name = N'staging'  
        AND object_id = OBJECT_ID (N'staging'))  
    DROP TABLE dbo.staging;  
    GO  
    
    CREATE TABLE [staging] (  
         AccountKey int NOT NULL,  
         AccountDescription nvarchar (50),  
         AccountType nvarchar(50),  
         AccountCodeAlternateKey int  
    );  
    GO  
    
    -- Load data  
    DECLARE @loop int;  
    DECLARE @AccountDescription varchar(50);  
    DECLARE @AccountKey int;  
    DECLARE @AccountType varchar(50);  
    DECLARE @AccountCode int;  
    
    SELECT @loop = 0;  
    BEGIN TRAN  
        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  
    
  2. Erstellen Sie eine als Columnstore-Index gespeicherte Tabelle.Create a table stored as a columnstore index.

    IF EXISTS (SELECT name FROM sys.tables  
        WHERE name = N'cci_target'  
        AND object_id = OBJECT_ID (N'cci_target'))  
    DROP TABLE dbo.cci_target;  
    GO  
    
    -- Create a table with a clustered columnstore index  
    -- and the same columns as the rowstore staging table.  
    CREATE TABLE cci_target (  
         AccountKey int NOT NULL,  
         AccountDescription nvarchar (50),  
         AccountType nvarchar(50),  
         AccountCodeAlternateKey int,  
         INDEX idx_cci_target CLUSTERED COLUMNSTORE  
    )  
    GO  
    
  3. Fügen Sie die Zeilen der Stagingtabelle massenhaft in die Columnstore-Tabelle ein.Bulk insert the staging table rows into the columnstore table. INSERT INTO ... SELECT führt eine Masseneinfügung aus.INSERT INTO ... SELECT performs a bulk insert. TABLOCK ermöglicht, dass INSERT mit Parallelität ausgeführt wird.The TABLOCK allows the INSERT to execute with parallelism.

    -- Insert rows in parallel  
    INSERT INTO cci_target WITH (TABLOCK)  
    SELECT TOP (300000) * FROM staging;  
    GO  
    
  4. Zeigen Sie die Zeilengruppen mithilfe der dynamischen Verwaltungssicht (Dynamic Management View; DMV) sys.dm_db_column_store_row_group_physical_stats an.View the rowgroups by using the sys.dm_db_column_store_row_group_physical_stats dynamic management view (DMV).

    -- Run this dynamic management view (DMV) to see the OPEN rowgroups.   
    -- The number of rowgroups depends on the degree of parallelism.   
    -- You will see multiple OPEN rowgroups depending on the degree of parallelism.   
    -- This is because insert operation can run in parallel in [!INCLUDE[ssSQL15](../../includes/sssql15-md.md)].  
    
    SELECT *   
    FROM sys.dm_db_column_store_row_group_physical_stats   
    WHERE object_id  = object_id('cci_target')  
    ORDER BY row_group_id;  
    

    In diesem Beispiel weisen die Ergebnisse acht OPEN-Zeilengruppen auf, von denen jede 37.500 Zeilen besitzt.In this example, the results show 8 OPEN rowgroups that each have 37,500 rows. Die Anzahl der OPEN-Zeilengruppen hängt von der Einstellung max_degree_of_parallelism ab.The number of OPEN rowgroups depends on the max_degree_of_parallelism setting.

    OPEN ZeilengruppenOPEN rowgroups

  5. Verwenden Sie ALTER INDEX REORGANIZE mit der COMPRESS_ALL_ROW_GROUPS-Option, um eine Komprimierung aller Zeilengruppen in den Columnstore zu erzwingen.Use ALTER INDEX REORGANIZE with the COMPRESS_ALL_ROW_GROUPS option to force all rowgroups to be compressed into the columnstore.

    -- This command will force all CLOSED and OPEN rowgroups into the columnstore.  
    ALTER INDEX idx_cci_target ON cci_target   
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);  
    
    SELECT *   
    FROM sys.dm_db_column_store_row_group_physical_stats   
    WHERE object_id  = object_id('cci_target')  
    ORDER BY row_group_id;  
    

    Die Ergebnisse weisen acht COMPRESSED-Zeilengruppen und acht TOMBSTONE-Zeilengruppen auf.The results show 8 COMPRESSED rowgroups and 8 TOMBSTONE rowgroups. Jede Zeilengruppe wurde unabhängig von ihrer Größe in den Columnstore komprimiert.Each rowgroup got compressed into the columnstore regardless of its size. Die TOMBSTONE-Zeilengruppen werden vom System entfernt.The TOMBSTONE rowgroups will be removed by the system.

    TOMBSTONE- und COMPRESSED- ZeilengruppenTOMBSTONE and COMPRESSED rowgroups

  6. Die Abfrageleistung verbessert sich maßgeblich, wenn Sie kleine Zeilengruppen kombinieren.For query performance, its much better to combine small rowgroups together. ALTER INDEX REORGANIZE fasst COMPRESSED-Zeilengruppen zusammen.ALTER INDEX REORGANIZE will combine COMPRESSED rowgroups together. Da die Delta-Zeilengruppen nun in den Columnstore komprimiert wurden, führen Sie ALTER INDEX REORGANIZE erneut aus, um die kleinen COMPRESSED-Zeilengruppen zu kombinieren.Now that the delta rowgroups are compressed into the columnstore, run ALTER INDEX REORGANIZE again to combine the small COMPRESSED rowgroups. In diesem Fall benötigen Sie die COMPRESS_ALL_ROW_GROUPS-Option nicht.This time you don't need the COMPRESS_ALL_ROW_GROUPS option.

    -- Run this again and you will see that smaller rowgroups   
    -- combined into one compressed rowgroup with 300,000 rows  
    ALTER INDEX idx_cci_target ON cci_target REORGANIZE;  
    
    SELECT *   
    FROM sys.dm_db_column_store_row_group_physical_stats   
    WHERE object_id  = object_id('cci_target')  
    ORDER BY row_group_id;  
    

    Die Ergebnisse weisen die acht COMPRESSED-Zeilengruppen auf, die nun in einer COMPRESSED-Zeilengruppe kombiniert sind.The results show the 8 COMPRESSED rowgroups are now combined into one COMPRESSED rowgroup.

    Kombinierte ZeilengruppenCombined rowgroups

Verwenden von ALTER INDEX REBUILD, um den Columnstore-Index offline zu defragmentierenUse ALTER INDEX REBUILD to defragment the columnstore index offline

Für SQL Server 2016 (13.x)SQL Server 2016 (13.x) oder höher ist die Neuerstellung des Columnstore-Indexes in der Regel nicht notwendig, da REORGANIZE die Grundlagen der Neuerstellung im Hintergrund als Onlinevorgang ausführt.For SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later, rebuilding the columnstore index is usually not needed since REORGANIZE performs the essentials of a rebuild in the background as an online operation.

Das Neuerstellen eines Columnstore-Indexes entfernt die Fragmentierung und verschiebt alle Zeilen in den Columnstore.Rebuilding a columnstore index removes fragmentation, and moves all rows into the columnstore. Verwenden Sie CREATE COLUMNSTORE INDEX (Transact-SQL) oder ALTER INDEX (Transact-SQL), um einen vorhandenen gruppierten Columnstore-Index komplett neu zu erstellen.Use CREATE COLUMNSTORE INDEX (Transact-SQL) or ALTER INDEX (Transact-SQL) to perform a full rebuild of an existing clustered columnstore index. Darüber hinaus können Sie ALTER INDEX... REBUILD zum Neu erstellen einer bestimmten Partition.Additionally, you can use ALTER INDEX ... REBUILD to rebuild a specific partition.

NeuerstellungsprozessRebuild Process

SQL ServerSQL Serverführt Folgendes durch, um einen Columnstore-Index neu zu erstellen:To rebuild a columnstore index, SQL ServerSQL Server:

  1. Abrufen einer exklusiven Sperre für die Tabelle oder Partition, während die Neuerstellung ausgeführt wird.Acquires an exclusive lock on the table or partition while the rebuild occurs. Die Daten sind während der Neuerstellung offline und nicht verfügbar, auch wenn Sie NOLOCK, RCSI oder SI verwenden.The data is "offline" and unavailable during the rebuild, even when using NOLOCK, RCSI, or SI.

  2. Neukomprimierung aller Daten im Columnstore.Re-compresses all data into the columnstore. Während die Neuerstellung ausgeführt wird, gibt es zwei Kopien des columnstore-Indexes.Two copies of the columnstore index exist while the rebuild is taking place. Nach Abschluss der Neuerstellung wird der ursprüngliche columnstore-Index in SQL ServerSQL Server gelöscht.When the rebuild is finished, SQL ServerSQL Server deletes the original columnstore index.

Empfehlungen zum Neuerstellen eines Columnstore-IndexesRecommendations for Rebuilding a Columnstore Index

Das Neuerstellen eines Columnstore-Indexes ist sinnvoll, um Fragmentierungen zu beseitigen, und um alle Zeilen in den Columnstore zu verschieben.Rebuilding a columnstore index is useful for removing fragmentation, and for moving all rows into the columnstore. Berücksichtigen Sie die folgenden Empfehlungen:We have the following recommendations:

  1. Erstellen Sie nur eine Partition neu und nicht die gesamte Tabelle.Rebuild a partition instead of the entire table.

    • Wenn der Index groß ist, nimmt das Neuerstellen der gesamten Tabelle viel Zeit in Anspruch, und es muss ausreichend Speicherplatz verfügbar sein, um während der Neuerstellung eine zusätzliche Kopie des Indexes speichern zu können.Rebuilding the entire table takes a long time if the index is large, and it requires enough disk space to store an additional copy of the index during the rebuild. In der Regel muss nur die zuletzt verwendete Partition neu erstellt werden.Usually it is only necessary to rebuild the most recently used partition.
    • Bei partitionierten Tabellen müssen Sie nicht den gesamten Columnstore-Index neu erstellen, da die Fragmentierung wahrscheinlich nur in den Partitionen vorliegt, die kürzlich geändert wurden.For partitioned tables, you do not need to rebuild the entire columnstore index because fragmentation is likely to occur in only the partitions that have been modified recently. Faktentabellen und große Dimensionstabellen werden i. d. R. partitioniert, um Sicherungs- und Verwaltungsvorgänge für Segmente der Tabelle auszuführen.Fact tables and large dimension tables are usually partitioned in order to perform backup and management operations on chunks of the table.
  2. Erstellen Sie eine Partition nach intensiven DML-Vorgängen neu.Rebuild a partition after heavy DML operations.

    • Durch das Neuerstellen einer Partition wird die Partition defragmentiert und der benötigte Festplattenspeicherplatz reduziert.Rebuilding a partition will defragment the partition and reduce disk storage. Beim Neuerstellen werden alle zum Löschen markierten Zeilen aus dem Columnstore gelöscht und alle Zeilengruppen aus dem Deltastore in den Columnstore verschoben.Rebuilding will delete all rows from the columnstore that are marked for deletion, and it will move all rowgroups from the deltastore into the columnstore. Beachten Sie, dass mehrere Zeilengruppen im Deltastore vorhanden sein können, von denen jede weniger als eine Million Zeilen enthält.Note, there can be multiple rowgroups in the deltastore that each have less than one million rows.
  3. Erstellen Sie eine Partition neu, nachdem Sie Daten geladen haben.Rebuild a partition after loading data.

    • Dadurch wird sichergestellt, dass alle Daten im Columnstore gespeichert sind.This ensures all data is stored in the columnstore. Wenn gleichzeitige Prozesse zur gleichen Zeit jeweils weniger als 100.000 Zeilen in dieselbe Partition laden, kann die Partition im Endeffekt mehrere Deltastores aufweisen.When concurrent processes each load less than 100K rows into the same partition at the same time, the partition can end up with multiple deltastores. Durch das Neuerstellen werden alle Deltastore-Zeilen in den Columnstore verschoben.Rebuilding will move all deltastore rows into the columnstore.

Automatische Verwaltung von Index und StatistikenAutomatic index and statistics management

Nutzen Sie Lösungen wie Adaptive Index Defrag, um die Indexdefragmentierung und das Aktualisieren der Statistiken für eine oder mehrere Datenbanken automatisch zu verwalten.Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. Dieser Vorgang entscheidet unter anderem anhand des Fragmentierungsgrads automatisch, ob ein Index neu organisiert oder neu erstellt wird und aktualisiert Statistiken mit einem linearen Schwellenwert.This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics with a linear threshold.

Weitere Informationen finden Sie unterSee Also

Columnstore indexes - what's new (Columnstore-Indizes: Neuigkeiten) Columnstore indexes - what's new
Abfrageleistung für Columnstore-Indizes Columnstore Indexes Query Performance
Erste Schritte mit Columnstore für operative Echtzeitanalyse Get started with Columnstore for real-time operational analytics
Columnstore-Indizes für Data WarehousingColumnstore Indexes for Data Warehousing
Columnstore Index Architecture (Columnstore-Indizes: Architektur) Columnstore Index Architecture
Adaptive IndexdefragmentierungAdaptive Index Defrag