Utilizzo di indici columnstore cluster
Attività per l'uso di indici columnstore cluster in SQL Server.
Per una panoramica di indici columnstore, vedere Columnstore Indexes Described.
Per informazioni sugli indici columnstore cluster, vedere Using Clustered Columnstore Indexes.
Contenuto
Creare un indice columnstore cluster
Per creare un indice columnstore cluster, creare prima una tabella rowstore come indice heap o cluster e quindi usare l'istruzione CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL) per convertire la tabella in un indice columnstore cluster. Se si desidera che l'indice columnstore cluster abbia lo stesso nome dell'indice cluster, utilizzare l'opzione DROP_EXISTING.
In questo esempio viene creata una tabella come heap, che viene poi convertita in un indice columnstore cluster denominato cci_Simple. In questo modo viene modificata l'archiviazione dell'intera tabella da rowstore a columnstore.
CREATE TABLE T1(
ProductKey [int] NOT NULL,
OrderDateKey [int] NOT NULL,
DueDateKey [int] NOT NULL,
ShipDateKey [int] NOT NULL);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_T1 ON T1;
GO
Per altri esempi, vedere la sezione Esempi in CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL).
Eliminare un indice columnstore cluster
Usare l'istruzione DROP INDEX (Transact-SQL) per eliminare un indice columnstore cluster. Questa operazione consente di eliminare l'indice e convertire la tabella columnstore nell'heap di un rowstore.
Caricare dati in un indice columnstore cluster
È possibile aggiungere dati a un indice columnstore cluster esistente utilizzando uno dei metodi di caricamento standard. Ad esempio, lo strumento di caricamento bulk bcp, Integration Services e INSERT ... SELECT può caricare tutti i dati in un indice columnstore cluster.
Gli indici columnstore cluster utilizzano il deltastore per impedire la frammentazione dei segmenti di colonna nel columnstore.
Caricamento in una tabella partizionata
Per i dati partizionati, SQL Server assegna prima ogni riga a una partizione e quindi esegue operazioni columnstore sui dati all'interno della partizione. Ogni partizione ha i propri rowgroup e almeno un deltastore.
Scenari di caricamento deltastore
Le righe vengono accumulate nel deltastore finché non viene raggiunto il numero di righe massimo consentito per un rowgroup. Quando l'archivio delta contiene il numero massimo di righe per rowgroup, SQL Server contrassegna il rowgroup come "CLOSED". Un processo in background, denominato "tuple-mover", trova il rowgroup CLOSED e si sposta nel columnstore, dove il rowgroup viene compresso in segmenti di colonna e i segmenti di colonna vengono archiviati nel columnstore.
Per ogni indice columnstore cluster possono essere presenti più deltastore.
Se un archivio delta è bloccato, SQL Server tenterà di ottenere un blocco in un archivio delta diverso. Se non sono disponibili archivi delta, SQL Server creerà un nuovo archivio delta.
Per una tabella partizionata, possono essere presenti uno o più deltastore per ogni partizione.
Solo per gli indici columnstore cluster, gli scenari seguenti indicano quando le righe caricate vengono direttamente indirizzate al columnstore o passano per il deltastore.
Nell'esempio, ogni rowgroup può avere 102.400-1.048.576 righe per rowgroup.
| Righe per il caricamento bulk | Righe aggiunte al columnstore | Righe aggiunte al deltastore |
|---|---|---|
| 102.000 | 0 | 102.000 |
| 145.000 | 145.000 Dimensioni rowgroup: 145.000 |
0 |
| 1\.048.577 | 1,048,576 Dimensioni rowgroup: 1.048.576. |
1 |
| 2\.252.152 | 2\.252.152 Dimensioni rowgroup: 1.048.576, 1.048.576, 155.000. |
0 |
Nell'esempio seguente vengono illustrati i risultati del caricamento di 1.048.577 righe in una partizione. I risultati mostrano che esiste un rowgroup COMPRESSED nel columnstore (come segmenti di colonna compressi) e 1 riga nel deltastore.
SELECT * FROM sys.column_store_row_groups;
di
Modificare i dati in un indice columnstore cluster
Gli indici columnstore cluster supportano le operazione DML di inserimento, aggiornamento ed eliminazione.
Usare INSERT (Transact-SQL) per inserire una riga. La riga viene aggiunta al deltastore.
Usare DELETE (Transact-SQL) per eliminare una riga.
Se la riga si trova nel columnstore, SQL Server contrassegna la riga come eliminata logicamente, ma non recupera l'archiviazione fisica per la riga finché non viene ricompilato l'indice.
Se la riga si trova nell'archivio delta, SQL Server logicamente ed elimina fisicamente la riga.
Usare UPDATE (Transact-SQL) per aggiornare una riga.
Se la riga si trova nel columnstore, SQL Server contrassegna la riga come eliminata logicamente e quindi inserisce la riga aggiornata nell'archivio delta.
Se la riga si trova nell'archivio delta, SQL Server aggiorna la riga nell'archivio delta.
Ricompilare un indice columnstore cluster
Usare CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL) o ALTER INDEX (Transact-SQL) per eseguire una ricompilazione completa di un indice columnstore cluster esistente. Inoltre, è possibile usare ALTER INDEX ... REBUILD per ricompilare una partizione specifica.
Processo di ricompilazione
Per ricompilare un indice columnstore cluster, SQL Server:
Acquisisce un blocco esclusivo sulla tabella o partizione durante la ricompilazione. I dati sono "offline" e non disponibili durante la ricompilazione.
Deframmenta il columnstore eliminando fisicamente le righe eliminate in modo logico dalla tabella; i byte eliminati vengono recuperati sui supporti fisici.
Esegue l'unione dei dati del rowstore nel deltastore con i dati del columnstore prima che venga ricompilato l'indice. Al termine della ricompilazione, tutti i dati vengono archiviati nel formato columnstore e il deltastore è vuoto.
Ricomprime tutti i dati nel columnstore. Durante la ricompilazione esistono due copie dell'indice columnstore. Al termine della ricompilazione, SQL Server elimina l'indice columnstore originale.
Suggerimenti per la ricompilazione di un indice columnstore cluster
La ricompilazione di un indice columnstore cluster è utile per rimuovere la frammentazione e spostare tutte le righe nel columnstore. Tenere presenti le seguenti indicazioni:
Ricompilare una partizione anziché l'intera tabella.
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. In genere è necessario solo ricompilare la partizione utilizzata più di recente.
Per le tabelle partizionate, non è necessario ricompilare l'intero indice columnstore perché la frammentazione probabilmente avviene solo nelle partizioni modificate di recente. 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.
Ricompilare una partizione dopo onerose operazioni DML
La ricompilazione di una partizione deframmenta la partizione e riduce lo spazio su disco. La ricompilazione elimina dal columnstore tutte le righe contrassegnate per l'eliminazione e sposta tutte le righe dal deltastore nel columnstore.
Ricompilare una partizione dopo il caricamento dei dati.
In questo modo viene garantita l'archiviazione di tutti i dati nel columnstore. Se si verificano più caricamenti contemporaneamente, ogni partizione potrebbe avere più deltastore. La ricompilazione sposterà tutte le righe del deltastore nel columnstore.
Riorganizzare un indice columnstore cluster
La riorganizzazione di un indice columnstore cluster sposta tutti i rowgroup CLOSED nel columnstore. Per eseguire una riorganizzazione, usare ALTER INDEX (Transact-SQL)con l'opzione REORGANIZE.
La riorganizzazione non è necessaria per spostare i rowgroup CLOSED nel columnstore. Tramite il processo tuple-mover verranno infine trovati e spostati tutti i rowgroup CLOSED. Tuple-mover è un processo a thread singolo e potrebbe non consentire uno spostamento sufficientemente rapido dei rowgroup per il carico di lavoro.
Suggerimenti per la riorganizzazione
Quando riorganizzare un indice columnstore cluster:
- Riorganizzare un indice columnstore cluster dopo uno o più caricamenti di dati per migliorare rapidamente le prestazioni delle query. La riorganizzazione inizialmente richiede risorse di CPU aggiuntive per comprimere i dati, il che potrebbe globalmente ridurre le prestazioni del sistema. Tuttavia, non appena i dati sono compressi, le prestazioni delle query possono migliorare.