Ottimizzazione delle prestazioni con indice columnstore cluster ordinato

Si applica a: pool SQL dedicati Azure Synapse Analytics, SQL Server 2022 (16.x) e versioni successive

Quando gli utenti eseguono una query su una tabella columnstore nel pool SQL dedicato, l'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ò terminare più velocemente se il numero di segmenti da leggere e le dimensioni totali sono ridotte.

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 non ordinato (CCI) su di esso. I dati in ogni colonna vengono compressi in un segmento di rowgroup CCI separato. Sono presenti metadati nell'intervallo di valori di ogni segmento, pertanto i segmenti esterni ai limiti del predicato di query non vengono letti dal disco durante l'esecuzione di query. CCI offre il livello massimo di compressione dei dati e riduce le dimensioni dei segmenti per 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, i segmenti con intervalli di valori sovrapposti potrebbero verificarsi, causando la lettura di più segmenti dal disco e richiedere più tempo per terminare.

Indici columnstore cluster ordinati abilitando l'eliminazione efficiente del segmento, consentendo prestazioni molto più veloci ignorando grandi quantità di dati ordinati che non corrispondono al predicato di query. Quando si crea un CCI ordinato, il motore del pool SQL dedicato ordina i dati esistenti in memoria in base alle chiavi di ordine prima che il generatore di indici comprima tali dati in segmenti di indice. Con i dati ordinati, la sovrapposizione del segmento è ridotta consentendo alle query di avere un'eliminazione del segmento più efficiente e quindi prestazioni più veloci perché il numero di segmenti da leggere dal disco è più piccolo. Se tutti i dati possono essere ordinati in memoria contemporaneamente, è possibile evitare la sovrapposizione del segmento. A causa di tabelle di grandi dimensioni nei data warehouse, questo scenario non si verifica spesso.

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

SELECT o.name, pnp.index_id,
cls.row_count, pnp.data_compression_desc,
pnp.pdw_node_id, pnp.distribution_id, 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.pdw_nodes_partitions AS pnp
   JOIN sys.pdw_nodes_tables AS Ntables ON pnp.object_id = NTables.object_id AND pnp.pdw_node_id = NTables.pdw_node_id
   JOIN sys.pdw_table_mappings AS Tmap  ON NTables.name = TMap.physical_name AND substring(TMap.physical_name,40, 10) = pnp.distribution_id
   JOIN sys.objects AS o ON TMap.object_id = o.object_id
   JOIN sys.pdw_nodes_column_store_segments AS cls ON pnp.partition_id = cls.partition_id AND pnp.distribution_id  = cls.distribution_id
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>'  and TMap.physical_name  not like '%HdTable%'
ORDER BY o.name, pnp.distribution_id, cls.min_data_id;

Nota

In una tabella CCI ordinata i nuovi dati risultanti dallo stesso batch di operazioni di caricamento dati o DML vengono ordinate all'interno di tale batch, non esiste un ordinamento globale in tutti i dati della tabella. Gli utenti possono RICOMPILAre l'CCI ordinato per ordinare tutti i dati nella tabella. Nel pool SQL dedicato, l'indice columnstore REBUILD è un'operazione offline. Per una tabella partizionata, la ricompilazione viene eseguita una partizione alla volta. I dati nella partizione che viene ricompilata sono "offline" e non sono disponibili fino al completamento della ricompilazione della partizione.

Prestazioni delle query

Le prestazioni di una query ottenute da un CCI ordinato dipendono dai modelli di query, dalle dimensioni dei dati, dal modo in cui vengono ordinati i dati, dalla struttura fisica dei segmenti 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 CCI ordinata.

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

  1. Le query hanno predicati di uguaglianza, disuguaglianza o intervallo
  2. Le colonne predicate e le colonne CCI ordinate sono uguali.

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

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

Le prestazioni di query 1 e query 2 possono trarre vantaggio da CCI ordinati rispetto alle altre query, in quanto fanno riferimento a tutte le colonne CCI 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 di caricamento dei dati

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

Ecco un confronto delle prestazioni di esempio per il caricamento dei dati in tabelle con schemi diversi.

Grafico a barre che mostra il confronto delle prestazioni del caricamento dei dati in tabelle con schemi diversi.

Ecco un confronto delle prestazioni di query di esempio tra CCI e CCI ordinato.

Grafico a barre che confronta le prestazioni durante data_loading. Un indice columnstore cluster ordinato ha una durata inferiore.

Ridurre la sovrapposizione del segmento

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

  • Usare xlargerc la classe di risorse in una DWU superiore per consentire una maggiore memoria per l'ordinamento dei dati prima che il generatore di indici comprima i dati nei segmenti. Una volta in un segmento di indice, non è possibile modificare la posizione fisica dei dati. Non esiste alcun ordinamento dei dati all'interno di un segmento o tra segmenti.

  • Creare l'CCI ordinato con OPTION (MAXDOP = 1). Ogni thread usato per la creazione di CCI ordinata funziona in un subset di dati e lo ordina in locale. Non esiste un ordinamento globale tra i dati ordinati da thread diversi. L'uso di thread paralleli può ridurre il tempo per creare un CCI ordinato, ma genererà segmenti più sovrapposti rispetto all'uso di un singolo thread. L'uso di un'operazione a thread singolo offre la massima qualità di compressione. Ad esempio:

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

Nota

Attualmente, nei pool SQL dedicati in Azure Synapse Analytics, l'opzione MAXDOP è supportata solo nella creazione di una tabella CCI ordinata usando CREATE TABLE AS SELECT il comando . La creazione di un CCI ordinato tramite CREATE INDEX o CREATE TABLE comandi non supporta l'opzione MAXDOP. Questa limitazione non si applica a SQL Server 2022 e versioni successive, in cui è possibile specificare MAXDOP con i CREATE INDEX comandi oCREATE TABLE.

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

Di seguito è riportato un esempio di distribuzione ordinata della tabella CCI con un segmento zero sovrapposto seguendo le raccomandazioni riportate sopra. La tabella CCI ordinata viene creata in un database DWU1000c tramite CTAS da una tabella heap da 20 GB usando MAXDOP 1 e xlargerc. L'CCI viene ordinato in una colonna BIGINT senza duplicati.

Screenshot dei dati di testo che non mostrano sovrapposizioni di segmenti.

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 dell'interfaccia di rete ordinata. Per le tabelle partizionate, poiché il motore crea la PARTIZIONE CCI ordinata in base alla partizione, gli utenti possono comunque accedere ai dati nelle partizioni in cui la creazione dell'CCI ordinata non è in processo. È 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 CCI ordinata vuota (denominata Table_B) con lo stesso schema di tabella e partizione di Table_A.
  3. Passare a 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 il passaggio 3 e 4 per ogni partizione in Table_A.
  6. Dopo che tutte le partizioni vengono spostate da Table_A a Table_B e sono state ricompilate, eliminare Table_Ae rinominare Table_B in Table_A.

Suggerimento

Per una tabella di pool SQL dedicata con un CCI ordinato, ALTER INDEX REBUILD riordinamentorà i dati usando tempdb. Monitorare tempdb durante le operazioni di ricompilazione. Se è necessario più tempdb spazio, aumentare il pool. Tornare alle dimensioni precedenti al termine della ricompilazione dell'indice.

Per una tabella di pool SQL dedicata con un CCI ordinato, ALTER INDEX REORGANIZE non esegue nuovamente l'ordinamento dei dati. Per ricorrere ai dati, usare ALTER INDEX REBUILD.

Per altre informazioni sulla manutenzione ordinata dell'CCI, vedere Ottimizzazione degli indici columnstore cluster.

Differenze di funzionalità nelle funzionalità SQL Server 2022

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

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

Per altre informazioni, vedere Novità negli indici Columnstore.

Esempi

R. Per verificare la presenza di colonne ordinate e 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 passare da CCI a CCI ordinato:

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

Passaggi successivi