Ottimizzazione delle prestazioni con indici columnstore cluster ordinati

Si applica a: SQL Server 2022 (16.x) Database Azure SQL Istanza gestita di SQL di Azure

Abilitando l'eliminazione efficiente dei segmenti, gli indici columnstore cluster ordinati offrono prestazioni molto più veloci ignorando grandi quantità di dati ordinati che non corrispondono al predicato di query. Il caricamento dei dati in una tabella con CCI ordinato può richiedere più tempo rispetto a una tabella con CCI non ordinato a causa dell'operazione di ordinamento dei dati, tuttavia le query possono essere eseguite più velocemente in seguito con un CCI ordinato.

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 dimensioni totali sono ridotte.

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

Indice columnstore cluster ordinato e non ordinato

Per impostazione predefinita, per ogni tabella creata senza un'opzione di indice, un componente interno (generatore di indici) crea un indice columnstore cluster (CCI) non ordinato su di esso. I dati in ogni colonna vengono compressi in un segmento rowgroup CCI separato. Sono presenti metadati nell'intervallo di valori di ogni segmento, quindi i segmenti esterni ai limiti del predicato di query non vengono letti dal disco durante l'esecuzione della query. Il CCI offre il massimo livello di compressione dei dati e riduce le dimensioni dei segmenti da leggere in modo che le query possano essere eseguite più velocemente. Tuttavia, poiché il generatore di indici non ordina i dati prima di comprimerli in segmenti, 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 CCI ordinato, il motore di database SQL ordina i dati esistenti in memoria in base alle chiavi dell'ordine prima che il generatore di indici li comprima in segmenti di indice. Con i dati ordinati, la sovrapposizione dei segmenti viene ridotta consentendo alle query di ottenere un'eliminazione dei segmenti più efficiente e quindi prestazioni più veloci perché il numero di segmenti da leggere dal disco è inferiore. Se tutti i dati possono essere ordinati in memoria contemporaneamente, è possibile evitare la sovrapposizione dei segmenti. A causa di tabelle di grandi dimensioni nei data warehouse, questo scenario non si verifica spesso.

Per controllare gli intervalli di segmenti per una colonna, eseguire il comando seguente con il nome della tabella e della colonna:

SELECT
o.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,
cls.max_data_id-cls.min_data_id as difference 

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 o.name = '<Table Name>' and cols.name = '<Column Name>'  
ORDER BY o.name, pnp.index_id, cls.min_data_id;

Prestazioni delle query

Il miglioramento delle prestazioni di una query da un CCI ordinato dipende dai modelli di query, dalle dimensioni dei dati, dal livello di ordinamento dei dati, dalla struttura fisica dei segmenti e dalla DWU e dalla classe di risorse scelta per l'esecuzione della query. Gli utenti devono esaminare tutti questi fattori prima di scegliere le colonne di ordinamento durante la progettazione di una tabella con CCI ordinato.

Le query con tutti questi modelli vengono in genere eseguite più velocemente con un CCI ordinato.

• Le query hanno predicati di uguaglianza, disuguaglianza o intervallo
• Le colonne del predicato e le colonne con CCI ordinato 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 della query 1 e della query 2 possono trarre vantaggio da CCI ordinati rispetto alle altre query, perché fanno riferimento a tutte le colonne con CCI ordinato.

-- 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 di caricamento dei dati

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

Ridurre la sovrapposizione dei segmenti

Il numero di segmenti sovrapposti dipende dalle dimensioni dei dati da ordinare, dalla memoria disponibile e dall'impostazione del massimo grado di parallelismo (MAXDOP) durante la creazione di CCI ordinati. Le strategie seguenti riducono la sovrapposizione dei segmenti durante la creazione di CCI ordinati.

• Creare un CCI ordinato con OPTION (MAXDOP = 1). Ogni thread usato per la creazione di un CCI 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 un CCI ordinato, ma genererà più segmenti sovrapposti rispetto all'uso di un singolo thread. L'uso di un'operazione a thread singolo offre la massima qualità di compressione. È possibile specificare MAXDOP con i CREATE INDEX comandi o CREATE TABLE . Ad esempio:

CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);

• Pre-ordinare i dati in base alle chiavi di ordinamento prima di caricarli nelle tabelle.

Di seguito è riportato un esempio di distribuzione di una tabella con CCI ordinato senza sovrapposizione dei segmenti seguendo le indicazioni precedenti. L'CCI ordinato viene ordinato in una colonna bigint senza duplicati.

Creare CCI ordinati in tabelle di grandi dimensioni

La creazione di un CCI ordinato è un'operazione offline. Per le tabelle senza partizioni, i dati non saranno accessibili agli utenti fino al completamento del processo di creazione del CCI ordinato. Per le tabelle partizionate, poiché il motore crea la partizione del CCI ordinato in base alla partizione, gli utenti possono comunque accedere ai dati nelle partizioni in cui la creazione del CCI ordinato non è in corso. È possibile usare questa opzione per ridurre al minimo i tempi di inattività durante la creazione di CCI ordinati in tabelle di grandi dimensioni:

1. Creare partizioni nella tabella di grandi dimensioni di destinazione (denominata Table_A).
2. Creare una tabella con CCI ordinato vuota (denominata Table_B) con lo stesso schema di tabella e partizione di Table_A.
3. Commutare una partizione da Table_A a Table_B.
4. Eseguire ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> per ricompilare la partizione commutata in Table_B.
5. Ripetere i passaggi 3 e 4 per ogni partizione in Table_A.
6. Dopo che tutte le partizioni sono passate da Table_A a Table_B e sono state ricompilate, eliminare Table_A e rinominare Table_B in Table_A.

Funzionalità di SQL Server 2022

SQL Server 2022 (16.x) ha introdotto indici columnstore cluster ordinati simili alla funzionalità nei pool SQL dedicati di Azure Synapse.

• SQL Server 2022 (16.x) e versioni successive e altre piattaforme SQL supportano funzionalità avanzate di eliminazione dei segmenti cluster per i tipi di dati string, binary e GUID e il tipo di dati datetimeoffset per la scala maggiore di due. In precedenza, questa eliminazione di segmento si applica ai tipi di dati numerici, di data e ora e al tipo di dati datetimeoffset con scala minore o uguale a due.
• Attualmente, solo SQL Server 2022 (16.x) e versioni successive e altre piattaforme SQL supportano l'eliminazione di rowgroup columnstore cluster per il prefisso dei LIKE predicati, ad esempio column LIKE 'string%'. L'eliminazione dei segmenti non è supportata per l'uso senza prefisso di LIKE, ad esempio column LIKE '%string'.

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

Per altre informazioni, vedere Novità degli indici columnstore.

Per informazioni sugli indici columnstore ordinati nei pool SQL dedicati in Azure Synapse Analytics, vedere Ottimizzazione delle prestazioni con indici columnstore cluster ordinati.

Esempi

R. Per verificare la presenza di colonne ordinate e di ordinali:

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

B. Per modificare l'ordinale di colonna, aggiungere o rimuovere colonne dall'elenco degli ordini o per passare dal CCI al CCI ordinato:

CREATE CLUSTERED COLUMNSTORE INDEX InternetSales ON dbo.InternetSales
ORDER (ProductKey, SalesAmount)
WITH (DROP_EXISTING = ON);

Contenuto correlato

• Linee guida per la progettazione di indici columnstore
• Indici columnstore - Linee guida per il caricamento di dati
• Introduzione a columnstore per l'analisi operativa in tempo reale
• Indici columnstore nel data warehousing
• Ottimizzare la manutenzione dell'indice per migliorare le prestazioni delle query e ridurre il consumo di risorse
• Architettura degli indici columnstore
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)