Condividi tramite

Ottimizzazione delle prestazioni con indici columnstore ordinati

  • Articolo

Si applica a: SQL Server 2022 (16.x) Database SQL di AzureIstanza gestita di SQL di AzureDatabase SQL in Microsoft Fabric

Abilitando l'eliminazione efficiente dei segmenti, gli indici columnstore ordinati offrono prestazioni più veloci ignorando grandi quantità di dati ordinati che non corrispondono al predicato di query. Il caricamento dei dati in un indice columnstore ordinato e l'ordinamento tramite ricompilazione degli indici può richiedere più tempo rispetto a un indice non ordinato a causa dell'operazione di ordinamento dei dati, ma con le query sugli indici columnstore ordinati può essere eseguita più velocemente in un secondo momento.

Quando gli utenti eseguono query su una tabella columnstore, Optimizer controlla i valori minimi e massimi archiviati in ogni segmento. I segmenti esterni ai limiti del predicato di query non vengono letti dal disco alla memoria. Una query può essere completata più velocemente se il numero di segmenti da leggere e le relative dimensioni totali sono inferiori.

Per la disponibilità dell'indice columnstore ordinato, vedere Disponibilità dell'indice columnstore ordinato.

Per altre informazioni sulle funzionalità aggiunte di recente per gli indici columnstore, vedere Novità negli indici columnstore.

Indice columnstore ordinato e non ordinato

In un indice columnstore i dati in ogni colonna di ogni rowgroup vengono compressi in un segmento separato. Ogni segmento contiene metadati che descrivono i relativi valori minimi e massimi, pertanto i segmenti esterni ai limiti del predicato di query non vengono letti dal disco durante l'esecuzione della query.

Quando un indice columnstore non è ordinato, il generatore di indici non ordina i dati prima di comprimerli in segmenti. Ciò significa che possono verificarsi segmenti con intervalli di valori sovrapposti, causando la lettura di più segmenti dal disco e richiedere più tempo per il completamento.

Quando si crea un indice columnstore ordinato, il motore di database ordina i dati esistenti in base alle chiavi di ordine specificate prima che il generatore di indici le comprima in segmenti. Con i dati ordinati, la sovrapposizione dei segmenti viene ridotta o eliminata, consentendo alle query di ottenere un'eliminazione più efficiente dei segmenti e quindi prestazioni più veloci perché sono presenti meno segmenti da leggere dal disco.

A seconda della memoria disponibile, delle dimensioni dei dati, del grado di parallelismo, del tipo di indice (cluster e non cluster) e del tipo di compilazione dell'indice (offline e online), l'ordinamento per gli indici columnstore ordinati potrebbe essere pieno (nessuna sovrapposizione di segmenti) o parziale (alcune sovrapposizioni di segmenti). Ad esempio, l'ordinamento parziale si verifica quando la memoria disponibile non è sufficiente per un ordinamento completo. Le query che usano un indice columnstore ordinato spesso vengono eseguite più velocemente rispetto a un indice non ordinato anche se l'indice ordinato è stato compilato usando un ordinamento parziale.

L'ordinamento completo viene fornito per gli indici columnstore ordinati a cluster creati o ricompilati con le opzioni ONLINE = ON e MAXDOP = 1. In questo caso, l'ordinamento non è limitato dalla memoria disponibile perché usa il database tempdb per distribuire i dati che non rientrano nella memoria. Ciò può rallentare il processo di compilazione dell'indice a causa del tempdb I/O aggiuntivo. Tuttavia, con una ricompilazione dell'indice online, le query possono continuare a usare l'indice esistente durante la ricompilazione del nuovo indice ordinato.

È anche possibile specificare l'ordinamento completo per gli indici columnstore cluster ordinati e non cluster creati o ricompilati con opzioni di ONLINE = OFF e MAXDOP = 1 se la quantità di dati da ordinare è sufficientemente piccola da adattarsi completamente alla memoria disponibile.

In tutti gli altri casi, l'ordinamento negli indici columnstore ordinati è parziale.

Nota

Attualmente, gli indici columnstore ordinati possono essere creati o ricompilati online solo nel database SQL di Azure e nell'Istanza gestita di Azure SQL con la policy di aggiornamento Always-up-to-date update.

Per controllare gli intervalli di segmenti per una colonna e determinare se è presente una sovrapposizione di segmenti, usare la query seguente, sostituendo i segnaposto con i nomi di schema, tabella e colonna:

SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
       o.name AS table_name,
       cols.name AS column_name,
       pnp.index_id,
       cls.row_count,
       pnp.data_compression_desc,
       cls.segment_id,
       cls.column_id,
       cls.min_data_id,
       cls.max_data_id
FROM sys.partitions AS pnp
INNER JOIN sys.tables AS t
ON pnp.object_id = t.object_id
INNER JOIN sys.objects AS o
ON t.object_id = o.object_id
INNER JOIN sys.column_store_segments AS cls
ON pnp.partition_id = cls.partition_id
INNER JOIN sys.columns AS cols
ON o.object_id = cols.object_id
   AND
   cls.column_id = cols.column_id
WHERE OBJECT_SCHEMA_NAME(o.object_id) = '<Schema Name>'
      AND
      o.name = '<Table Name>'
      AND
      cols.name = '<Column Name>'
ORDER BY o.name, pnp.index_id, cls.min_data_id;

Ad esempio, l'output di questa query per un indice columnstore completamente ordinato potrebbe essere simile al seguente. Si noti che non vi è alcuna sovrapposizione nelle colonne min_data_id e max_data_id per segmenti diversi.

schema_name table_name column_name index_id row_count data_compression_desc segment_id column_id min_data_id max_data_id
----------- ---------- ----------- -------- --------- --------------------- ---------- --------- ----------- -----------
dbo         Table1     Column1     1        479779    COLUMNSTORE           0          1         -17         1469515
dbo         Table1     Column1     1        887658    COLUMNSTORE           1          1         1469516     2188146
dbo         Table1     Column1     1        930144    COLUMNSTORE           2          1         2188147     11072928

Nota

In un indice columnstore ordinato, i nuovi dati risultanti dallo stesso batch di operazioni di caricamento dati o DML vengono ordinati solo all'interno di tale batch. Non esiste un ordinamento globale che include i dati esistenti nella tabella.

Per ordinare i dati nell'indice dopo l'inserimento di nuovi dati o l'aggiornamento dei dati esistenti, ricompilare l'indice.

Per una ricompilazione offline di un indice columnstore partizionato, la ricompilazione viene eseguita una partizione alla volta. I dati nella partizione da ricompilare non sono disponibili fino al completamento della ricompilazione per tale partizione.

I dati rimangono disponibili durante una ricompilazione online. Per altre informazioni, vedere Eseguire operazioni sugli indici online.

Prestazioni delle query

Il miglioramento delle prestazioni da un indice columnstore ordinato dipende dai modelli di query, dalle dimensioni dei dati, dal livello di ordinamento dei dati, dalla struttura fisica dei segmenti e dalle risorse di calcolo disponibili per l'esecuzione delle query.

Le query con i modelli seguenti vengono in genere eseguite più velocemente con indici columnstore ordinati.

  • Le query con predicati di uguaglianza, disuguaglianza o intervallo.
  • Le query in cui le colonne del predicato e le colonne CCI ordinate sono le stesse.

In questo esempio la tabella T1 ha un indice columnstore cluster ordinato nella sequenza di Col_C, Col_Be Col_A.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON T1
ORDER (Col_C, Col_B, Col_A);

Le prestazioni delle query 1 e 2 possono trarre maggiore vantaggio dall'indice columnstore ordinato rispetto alle query 3 e 4, perché fanno riferimento a tutte le colonne ordinate.

-- query 1
SELECT *
FROM T1
WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- query 2
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- query 3
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_A = 'a';

-- query 4
SELECT *
FROM T1
WHERE Col_A = 'a' AND Col_C = 'c';

Prestazioni del caricamento dei dati

Le prestazioni del caricamento dei dati in una tabella con un indice columnstore ordinato sono simili a una tabella partizionata. Il caricamento dei dati può richiedere più tempo rispetto a un indice columnstore non ordinato a causa dell'operazione di ordinamento dei dati, ma le query possono essere eseguite più velocemente in seguito.

Ridurre la sovrapposizione dei segmenti

Il numero di segmenti sovrapposti dipende dalle dimensioni dei dati da ordinare, dalla memoria disponibile e dal grado massimo di parallelismo (MAXDOP) durante la compilazione dell'indice columnstore ordinato. Le strategie seguenti riducono la sovrapposizione dei segmenti, ma possono rendere più lungo il processo di compilazione dell'indice.

  • Se la creazione di indici online è disponibile, usare le opzioni sia ONLINE = ON che MAXDOP = 1 durante la creazione di un indice columnstore clustered ordinato. In questo modo viene creato un indice ordinato completamente.
  • Se la compilazione dell'indice online non è disponibile, usare l'opzione MAXDOP = 1.
  • Ordinamento preliminare dei dati in base alle chiavi di ordinamento prima del caricamento.

Quando MAXDOP è maggiore di 1, ogni thread usato per la compilazione dell'indice columnstore ordinato funziona su un subset di dati e lo ordina localmente. Non esiste un ordinamento globale tra i dati ordinati in base a thread diversi. L'uso di thread paralleli può ridurre il tempo necessario per creare l'indice, ma genera più segmenti sovrapposti rispetto a quando si usa un singolo thread. L'uso di un'operazione a thread singolo offre la massima qualità di compressione. È possibile specificare MAXDOP con il comando CREATE INDEX.

Esempi

Verificare la presenza di colonne ordinate e l'ordine degli ordinali

SELECT object_name(c.object_id) AS table_name,
       c.name AS column_name,
       i.column_store_order_ordinal
FROM sys.index_columns AS i
INNER JOIN sys.columns AS c
ON i.object_id = c.object_id
   AND
   c.column_id = i.column_id
WHERE column_store_order_ordinal <> 0;

Creare un indice columnstore ordinato

Indice columnstore ordinato con cluster

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2);

Indice columnstore ordinato non clusterizzato

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2);

Aggiungere o rimuovere colonne di ordine e ricompilare un indice columnstore ordinato esistente

Indice columnstore con ordinamento clusterizzato

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Indice columnstore non clusterizzato e ordinato

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Creare un indice columnstore cluster ordinato online con ordinamento completo in una tabella heap

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (ONLINE = ON, MAXDOP = 1);

Ricompilare un indice columnstore cluster ordinato online con ordinamento completo

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);

Contenuto correlato