Indici columnstore - DeframmentazioneColumnstore indexes - defragmentation

QUESTO ARGOMENTO SI APPLICA A:sìSQL Server (a partire dalla versione 2012)sìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2012)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Attività per la deframmentazione degli indici columnstore.Tasks for defragmenting columnstore indexes.

Usare ALTER INDEX REORGANIZE per deframmentare un indice columnstore onlineUse ALTER INDEX REORGANIZE to defragment a columnstore index online

SI APPLICA A: SQL Server (a partire dalla versione 2016), database SQL di AzureAPPLIES TO: SQL Server (starting with 2016), Azure SQL Database

Dopo l'esecuzione di carichi di qualsiasi tipo, è possibile che rimangano più rowgroup piccoli nel deltastore.After performing loads of any type, you can have multiple small rowgroups in the deltastore. È possibile usare ALTER INDEX REORGANIZE per forzare l'inserimento di tutti i rowgroup in columnstore, per poi combinare i rowgroup in un numero inferiore di rowgroup con più righe.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. L'operazione di riorganizzazione rimuoverà anche le righe che sono state eliminate dal columnstore.The reorganize operation will also remove rows that have been deleted from the columnstore.

Per altre informazioni, vedere il post di blog di Sunil Agarwal nel blog del team del motore di database SQL.To learn more see Sunil Agarwal's blog posts on the SQL Database Engine Team Blog.

Suggerimenti per la riorganizzazioneRecommendations for reorganizing

Riorganizzare un indice columnstore dopo uno o più caricamenti di dati per migliorare rapidamente le prestazioni delle query.Reorganize a columnstore index after one or more data loads to achieve query performance benefits as quickly as possible. La riorganizzazione inizialmente richiede risorse di CPU aggiuntive per comprimere i dati, il che potrebbe globalmente ridurre le prestazioni del sistema.Reorganizing will initially require additional CPU resources to compress the data, which could slow overall system performance. Tuttavia, non appena i dati sono compressi, le prestazioni delle query possono migliorare.However, as soon as the data is compressed, query performance can improve.

Usare l'esempio in sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) per calcolare la frammentazione.Use the example in sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) to compute the fragmentation. Questo permette di determinare se vale la pena eseguire un'operazione REORGANIZE.This helps you to determine whether it is worthwhile to perform a REORGANIZE operation.

Esempio: funzionamento della riorganizzazioneExample: How reorganizing works

Questo esempio mostra come usare ALTER INDEX REORGANIZE per forzare l'inserimento di tutti i rowgroup di deltastore in columnstore, per poi combinare i rowgroup.This example shows how ALTER INDEX REORGANIZE can force all deltastore rowgroups into the columnstore and then combine the rowgroups.

  1. Eseguire questo codice Transact-SQL per creare una tabella di gestione temporanea contenente 300.000 righe.Run this Transact-SQL to create a staging table that contains 300,000 rows. Questa tabella verrà usata per il caricamento bulk di righe in un indice columnstore.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. Creare una tabella archiviata come indice columnstore.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. Inserimento bulk della righe della tabella di gestione temporanea nella tabella columnstore.Bulk insert the staging table rows into the columnstore table. INSERT INTO ... SELECT esegue un inserimento bulk.INSERT INTO ... SELECT performs a bulk insert. TABLOCK esegue l'inserimento in parallelo.The TABLOCK runs the insert in parallel.

    -- Insert rows in parallel  
    INSERT INTO cci_target WITH (TABLOCK)  
    SELECT TOP (300000) * FROM staging;  
    GO  
    
  4. Visualizzare i rowgroup tramite la DMV sys.dm_db_column_store_row_group_physical_stats.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 SQL server 2016.  
    
    SELECT *   
    FROM sys.dm_db_column_store_row_group_physical_stats   
    WHERE object_id  = object_id('cci_target')  
    ORDER BY row_group_id;  
    

    In questo esempio, i risultati mostrano 8 rowgroup OPEN ognuno con 37.500 righe.In this example, the results show 8 OPEN rowgroups that each have 37,500 rows. Il numero di rowgroup OPEN dipende dall'impostazione max_degree_of_parallelism.The number of OPEN rowgroups depends on the max_degree_of_parallelism setting.

    Rowgroup OPENOPEN rowgroups

  5. Usare ALTER INDEX REORGANIZE con l'opzione COMPRESS_ALL_ROW_GROUPS per forzare la compressione di tutti i rowgroup nel columnstore.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;  
    

    I risultati mostrano 8 rowgroup COMPRESSED e 8 rowgroup TOMBSTONE.The results show 8 COMPRESSED rowgroups and 8 TOMBSTONE rowgroups. Ogni rowgroup viene compresso nel columnstore indipendentemente dalle dimensioni.Each rowgroup got compressed into the columnstore regardless of its size. I rowgroup TOMBSTONE verranno rimossi dal sistema.The TOMBSTONE rowgroups will be removed by the system.

    Rowgroup TOMBSTONE e COMPRESSEDTOMBSTONE and COMPRESSED rowgroups

  6. Per le prestazioni delle query, è molto meglio combinare i rowgroup piccoli.For query performance, its much better to combine small rowgroups together. ALTER INDEX REORGANIZE combinerà i rowgroup COMPRESSED.ALTER INDEX REORGANIZE will combine COMPRESSED rowgroups together. Ora che i rowgroup delta sono compressi nel columnstore, eseguire di nuovo ALTER INDEX REORGANIZE per combinare i rowgroup COMPRESSED piccoli.Now that the delta rowgroups are compressed into the columnstore, run ALTER INDEX REORGANIZE again to combine the small COMPRESSED rowgroups. Questa volta non è necessaria l'opzione COMPRESS_ALL_ROW_GROUPS.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;  
    

    I risultati mostrano che gli 8 rowgroup COMPRESSED sono ora combinati in un solo rowgroup COMPRESSED.The results show the 8 COMPRESSED rowgroups are now combined into one COMPRESSED rowgroup.

    Rowgroup combinatiCombined rowgroups

Usare ALTER INDEX REORGANIZE per deframmentare un indice columnstore offline Use ALTER INDEX REBUILD to defragment the columnstore index offline

Per SQL Server 2016 e versioni successive, la ricompilazione dell'indice columnstore in genere non è necessaria perché REORGANIZE esegue gli aspetti principali di una ricompilazione in background come operazione online.For SQL Server 2016 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.

La ricompilazione di un indice columnstore consente di rimuovere la frammentazione e spostare tutte le righe nel columnstore.Rebuilding a columnstore index removes fragmentation, and moves all rows into the columnstore. È possibile usare CREATE COLUMNSTORE INDEX (Transact-SQL) o ALTER INDEX (Transact-SQL) per eseguire la ricompilazione completa di un indice columnstore cluster esistente.Use CREATE COLUMNSTORE INDEX (Transact-SQL) or ALTER INDEX (Transact-SQL) to perform a full rebuild of an existing clustered columnstore index. Inoltre, è possibile usare ALTER INDEX...Additionally, you can use ALTER INDEX … REBUILD per ricompilare una partizione specifica.REBUILD to rebuild a specific partition.

Processo di ricompilazioneRebuild Process

Per ricompilare un indice columnstore, SQL ServerSQL Server:To rebuild a columnstore index, SQL ServerSQL Server:

  1. Acquisisce un blocco esclusivo sulla tabella o partizione durante la ricompilazione.Acquires an exclusive lock on the table or partition while the rebuild occurs. I dati sono "offline" e non disponibili durante la ricompilazione, anche quando si usa NOLOCK, RCSI o SI.The data is “offline” and unavailable during the rebuild, even when using NOLOCK, RCSI, or SI.

  2. Ricomprime tutti i dati nel columnstore.Re-compresses all data into the columnstore. Durante la ricompilazione esistono due copie dell'indice columnstore.Two copies of the columnstore index exist while the rebuild is taking place. Al termine della ricompilazione, SQL ServerSQL Server elimina l'indice columnstore originale.When the rebuild is finished, SQL ServerSQL Server deletes the original columnstore index.

Suggerimenti per la ricompilazione di un indice columnstoreRecommendations for Rebuilding a Columnstore Index

La ricompilazione di un indice columnstore è utile per rimuovere la frammentazione e spostare tutte le righe nel columnstore.Rebuilding a columnstore index is useful for removing fragmentation, and for moving all rows into the columnstore. Tenere presenti le seguenti indicazioni:We have the following recommendations:

  1. Ricompilare una partizione anziché l'intera tabella.Rebuild a partition instead of the entire table.

    • La ricompilazione di un'intera tabella richiede molto tempo se l'indice è esteso ed è necessario sufficiente spazio su disco per archiviare una copia aggiuntiva dell'indice durante la ricompilazione.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 genere è necessario solo ricompilare la partizione utilizzata più di recente.Usually it is only necessary to rebuild the most recently used partition.

    • Per le tabelle partizionate, non è necessario ricompilare l'intero indice columnstore perché la frammentazione probabilmente avviene solo nelle partizioni modificate di recente.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. Le tabelle dei fatti e le tabelle delle dimensioni grandi vengono in genere partizionate per eseguire operazioni di backup e di gestione dei blocchi della tabella.Fact tables and large dimension tables are usually partitioned in order to perform backup and management operations on chunks of the table.

  2. Ricompilare una partizione dopo onerose operazioni DMLRebuild a partition after heavy DML operations.

    • La ricompilazione di una partizione deframmenta la partizione e riduce lo spazio su disco.Rebuilding a partition will defragment the partition and reduce disk storage. La ricompilazione elimina dal columnstore tutte le righe contrassegnate per l'eliminazione e sposta tutti i rowgroup dal deltastore nel columnstore.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. Si noti che possono esistere più rowgroup nel deltastore e che ognuno include meno di un milione di righe.Note, there can be multiple rowgroups in the deltastore that each have less than one million rows.
  3. Ricompilare una partizione dopo il caricamento dei dati.Rebuild a partition after loading data.

    • In questo modo viene garantita l'archiviazione di tutti i dati nel columnstore.This ensures all data is stored in the columnstore. Quando i processi simultanei caricano ognuno meno di 100.000 righe nella stessa partizione contemporaneamente, si possono creare più deltastore nella partizione.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. La ricompilazione sposterà tutte le righe del deltastore nel columnstore.Rebuilding will move all deltastore rows into the columnstore.

Vedere ancheSee Also

Indici columnstore - NovitàColumnstore indexes - what's new

Indici columnstore - Prestazioni delle query Columnstore indexes - query performance
Introduzione a columnstore per l'analisi operativa in tempo reale Get started with Columnstore for real time operational analytics
Indici columnstore - Data warehouse Columnstore indexes - data warehouse