Condividi tramite

Novità degli indici columnstore

  • Articolo

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure database SQL di Azure Synapse Analytics Platform System (PDW) in Microsoft Fabric

Informazioni sulle funzionalità columnstore disponibili per ogni versione di SQL Server e sulle versioni più recenti di database SQL, Azure Synapse Analytics e Piattaforma di strumenti analitici (PDW).

Riepilogo delle funzionalità per le versioni dei prodotti

Questa tabella riepiloga le funzionalità principali per gli indici columnstore e i prodotti in cui sono disponibili.

Funzionalità indice columnstore SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x)1 SQL Server 2017 (14.x) SQL Server 2019 (15.x) SQL Server 2022 (16.x) Database SQL1 Pool SQL dedicato di Azure Synapse Analytics
Esecuzione in modalità batch per le query multithreading2 yes sì sì sì sì sì sì yes
Esecuzione in modalità batch per le query a thread singolo yes sì sì sì sì yes
Opzione di compressione dell'archivio yes sì sì sì sì sì yes
Isolamento dello snapshot e dello snapshot Read Committed yes sì sì sì sì yes
Specificare l'indice columnstore durante la creazione di una tabella yes sì sì sì sì yes
Always On supporta gli indici columnstore yes sì sì sì sì sì sì yes
Le repliche secondarie leggibili Always On supportano l'indice columnstore non cluster di sola lettura yes sì sì sì sì sì sì yes
Le repliche secondarie leggibili Always On supportano gli indici columnstore aggiornabili yes sì sì yes
Indice columnstore non cluster di sola lettura su heap o albero B yes yes sì 3 sì 3 sì 3 sì 3 sì 3 sì 3
Indice columnstore non cluster aggiornabile su heap o albero B yes sì sì sì sì yes
Indici albero B aggiuntivi consentiti su un heap o albero B che dispone di un indice columnstore non cluster yes sì sì sì sì sì sì yes
Indice columnstore cluster aggiornabile yes sì sì sì sì sì yes
Indice albero B su un indice columnstore cluster yes sì sì sì sì yes
Indice columnstore su una tabella ottimizzata per la memoria yes sì sì sì sì yes
La definizione degli indici columnstore non cluster supporta l'uso di una condizione filtrata yes sì sì sì sì yes
Opzione relativa al ritardo di compressione per gli indici columnstore in CREATE TABLE e ALTER TABLE yes sì sì sì sì yes
Supporto per il tipo nvarchar(max) yes sì sì yes no 4
L'indice columnstore può avere una colonna calcolata non persistente yes sì yes
Supporto dell'unione in background del motore di tuple yes sì sì yes
Indici columnstore cluster ordinati yes sì yes
Indici columnstore non cluster ordinati yes

1 Per il database SQL, gli indici columnstore sono disponibili nei livelli Premium DTU del database SQL di Azure, nei livelli Standard - S3 DTU e versioni successive e in tutti i livelli vCore. Per SQL Server 2016 (13.x) SP1 e versioni successive), gli indici columnstore sono disponibili in tutte le edizioni. Per SQL Server 2016 (13.x) (prima di SP1) e versioni precedenti, gli indici columnstore sono disponibili solo nell'edizione Enterprise.

2 Il grado di parallelismo per le operazioni in modalità batch è limitato a 2 per SQL Server Standard Edition e 1 per SQL Server Web Edition ed Express Edition. Questa limitazione si riferisce agli indici columnstore creati tramite le tabelle basate su disco e le tabelle ottimizzate per la memoria.

3 Per creare un indice columnstore non cluster di sola lettura, archiviare l'indice in un filegroup di sola lettura.

4 Non supportato nei pool SQL dedicati, ma supportato nel pool SQL serverless.

SQL Server 2022 (16.x)

SQL Server 2022 (16.x) ha aggiunto queste funzionalità.

  • Gli indici columnstore cluster ordinati migliorano le prestazioni per le query in base ai predicati di colonna ordinati. Gli indici columnstore ordinati possono migliorare le prestazioni ignorando del tutto i segmenti di dati. Ciò può ridurre drasticamente le operazioni di I/O necessarie per completare le query sui dati columnstore. Per altre informazioni, consultare Eliminazione dei segmenti. Gli indici columnstore cluster ordinati sono disponibili in SQL Server 2022 (16.x). Per altre informazioni, vedere CREATE COLUMNSTORE INDEX and Performance tuning with ordered clustered columnstore indexes .For more information, see CREATE COLUMNSTORE INDEX and Performance tuning with ordered clustered columnstore indexes.

  • Il pushdown del predicato con l'eliminazione di rowgroup columnstore cluster di stringhe usa valori limite per ottimizzare le ricerche di stringhe. Tutti gli indici columnstore traggono vantaggio da una migliore eliminazione dei segmenti in base al tipo di dati. A partire da SQL Server 2022 (16.x), le funzionalità di eliminazione dei segmenti si estendono ai tipi di dati stringa, binario, guid e al tipo di dati datetimeoffset con scala superiore a due. In passato, l'eliminazione dei segmenti columnstore si applicava solo ai tipi di dati numerici, di data e ora e al tipo di dati datetimeoffset con scala inferiore o uguale a due. Dopo l'aggiornamento a una versione di SQL Server che supporta l'eliminazione di segmenti min/max di tipo stringa (SQL Server 2022 (16.x) e versioni successive, l'indice columnstore non trarrà vantaggio da questa funzionalità fino a quando non viene ricompilata usando una funzione REBUILD o DROP/CREATE.

  • Per altre informazioni sulle funzionalità aggiunte, vedere Novità di SQL Server 2022.

SQL Server 2019 (15.x)

SQL Server 2019 (15.x) aggiunge queste nuove funzionalità:

Funzionale

A partire da SQL Server 2019 (15.x), il motore di tuple viene aiutato da un'attività di unione in background, che comprime automaticamente i rowgroup delta aperti più piccoli che sono esistiti per un dato periodo di tempo (come determinato da una soglia interna) oppure unisce i rowgroup compressi da cui è stato eliminato un numero elevato di righe. In precedenza era necessaria un'operazione di riorganizzazione dell'indice per unire i rowgroup con dati eliminati parzialmente. In questo modo viene migliorata la qualità dell'indice columnstore nel tempo.

SQL Server 2017 (14.x)

SQL Server 2017 (14.x) aggiunge queste nuove funzionalità.

Funzionale

  • SQL Server 2017 (14.x) supporta colonne calcolate non persistenti negli indici columnstore cluster. Le colonne calcolate persistenti non sono supportate in indici columnstore cluster. Non è possibile creare un indice columnstore non cluster su una colonna calcolata.

SQL Server 2016 (13.x)

SQL Server 2016 (13.x) aggiunge miglioramenti importanti per aumentare le prestazioni e la flessibilità degli indici columnstore. In questo modo è possibile migliorare gli scenari di data warehouse e abilitare l'analisi operativa in tempo reale.

Funzionale

  • Una tabella rowstore può avere un solo indice columnstore non cluster aggiornabile. In precedenza, l'indice columnstore non cluster era di sola lettura.

  • La definizione degli indici columnstore non cluster supporta l'uso di una condizione filtrata. Per ridurre al minimo l'impatto sulle prestazioni conseguente all'aggiunta di un indice columnstore in una tabella OLTP, usare una condizione filtrata per creare un indice columnstore non cluster solo sui dati usati meno di frequente del carico di lavoro operativo.

  • Una tabella in memoria può avere un solo indice columnstore. È possibile crearlo durante la creazione della tabella o aggiungerlo in un secondo momento con ALTER TABLE (Transact-SQL). In precedenza, solo una tabella basata su disco poteva avere un indice columnstore.

  • Un indice columnstore cluster può avere uno o più indici rowstore non cluster. In precedenza, l'indice columnstore non supportava gli indici non cluster. SQL Server gestisce automaticamente gli indici non cluster per le operazioni DML.

  • Supporto di chiavi primarie e chiavi esterne usando un indice albero B per imporre questi vincoli su un indice columnstore cluster.

  • Gli indici columnstore hanno un'opzione relativa al ritardo di compressione che riduce al minimo l'impatto che il carico di lavoro transazionale ha sull'analisi operativa in tempo reale. Questa opzione consente di modificare frequentemente le righe per stabilizzarle prima di comprimerle nel columnstore. Per informazioni dettagliate, vedere CREATE COLUMNSTORE INDEX (Transact-SQL); e Introduzione a columnstore per l'analisi operativa in tempo reale.

Prestazioni per il livello di compatibilità del database 120 o 130

  • Gli indici columnstore supportano il livello di isolamento dello snapshot Read Committed e l'isolamento dello snapshot. Questo consente le query di analisi coerente transazionale senza alcun blocco.

  • Columnstore supporta la deframmentazione degli indici rimuovendo le righe eliminate senza necessità di ricompilare l'indice in modo esplicito. L'istruzione ALTER INDEX ... REORGANIZE rimuove dal columnstore le righe eliminate in base a un criterio definito internamente, con un'operazione online

  • Gli indici columnstore sono accessibili su una replica secondaria leggibile Always On. È possibile migliorare le prestazioni per l'analisi operativa ripartendo le query di analisi su una replica secondaria Always On.

  • La distribuzione dell'aggregazione calcola le funzioni di aggregazione MIN, MAX, SUM, COUNT e AVG durante le scansioni di tabella quando il tipo di dati usa non più di 8 byte e non è di tipo stringa. La distribuzione dell'aggregazione è supportata con o senza clausola GROUP BY sia per gli indici columnstore cluster sia per quelli non cluster. In SQL Server questa funzionalità avanzata è riservata per l'edizione Enterprise.

  • La distribuzione del predicato stringa consente di velocizzare le query che confrontano stringhe di tipo VARCHAR/CHAR o NVARCHAR/NCHAR. Questo si applica ai comuni operatori di confronto e include operatori come LIKE che usano i filtri bitmap. Funziona con tutte le regole di confronto supportate. In SQL Server questa funzionalità avanzata è riservata per l'edizione Enterprise.

  • Miglioramenti per le operazioni in modalità batch sfruttando le funzionalità hardware basate su vettori. Il motore di database rileva il livello di supporto CPU per le estensioni hardware AVX 2 (Advanced Vector Extensions) e SSE 4 (Streaming SIMD Extensions 4) e le usa se supportate. In SQL Server questa funzionalità avanzata è riservata per l'edizione Enterprise.

Prestazioni per il livello di compatibilità del database 130

  • Nuovo supporto dell'esecuzione in modalità batch per le query che usano uno di questi operatori:

    • SORT
    • Funzioni di aggregazione con più funzioni distinte. Alcuni esempi: COUNT/COUNT, AVG/SUM, CHECKSUM_AGG, STDEV/STDEVP
    • Funzioni di aggregazione della finestra: COUNT, COUNT_BIG, SUM, AVG, MIN, MAX e CLR
    • Funzioni di aggregazione della finestra definite dall'utente: CHECKSUM_AGG, STDEV, STDEVP, VAR, VARP e GROUPING
    • Funzioni analitiche di aggregazione della finestra: LAG, LEAD, FIRST_VALUE, LAST_VALUE, PERCENTILE_CONT, PERCENTILE_DISC, CUME_DIST e PERCENT_RANK

  • Le query a thread singolo in esecuzione in MAXDOP 1 o con un piano di query seriale vengono eseguite in modalità batch. In passato, le query multithreading venivano eseguite in modalità batch.

  • Le query delle tabelle ottimizzate per la memoria possono avere piani paralleli in modalità SQL InterOp durante l'accesso ai dati nell'indice rowstore o columnstore.

Supporto

Queste viste di sistema sono una novità per columnstore:

sys.column_store_row_groups (Transact-SQL)

sys.dm_column_store_object_pool (Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)

sys.dm_db_index_operational_stats (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

sys.internal_partitions (Transact-SQL)

Queste DMV basate su OLTP in memoria contengono aggiornamenti per columnstore:

sys.dm_db_xtp_hash_index_stats (Transact-SQL)

sys.dm_db_xtp_index_stats (Transact-SQL)

sys.dm_db_xtp_memory_consumers (Transact-SQL)

sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)

sys.dm_db_xtp_object_stats (Transact-SQL)

sys.dm_db_xtp_table_memory_stats (Transact-SQL)

Limiti

  • Per le tabelle in memoria, un indice columnstore deve includere tutte le colonne; l'indice columnstore non può avere una condizione di filtrata.
  • Per le tabelle in memoria, le query sugli indici columnstore vengono eseguite solo in modalità InterOP e non in modalità nativa in memoria. È supportata l'esecuzione parallela.

Problemi noti

Si applica a: SQL Server, Database SQL di Azure, Istanza gestita di SQL di Azure e pool SQL dedicati di Azure Synapse Analytics.

  • Attualmente, le colonne LOB (varbinary(max), varchar(max) e nvarchar(max)) nei segmenti columnstore compressi non sono interessate da DBCC SHRINKDATABA e DBCC SHRINKFILE.

SQL Server 2014 (12.x)

SQL Server 2014 (12.x) ha introdotto l'indice columnstore cluster come formato di archiviazione primario. Questo ha consentito caricamenti regolari, nonché operazioni di aggiornamento, eliminazione e inserimento.

  • La tabella può usare un indice columnstore cluster come archiviazione tabella primaria. Nella tabella non è consentito nessun altro indice, ma l'indice columnstore cluster è aggiornabile, pertanto è possibile eseguire caricamenti regolari e apportare modifiche alle singole righe.
  • L'indice columnstore non cluster continua ad avere la stessa funzionalità in SQL Server 2012 (11.x), ad eccezione degli operatori aggiuntivi che ora possono essere eseguiti in modalità batch. Al momento è aggiornabile solo tramite ricompilazione e usando un cambio di partizione. L'indice columnstore non cluster è supportato solo nelle tabelle basate su disco e non in quelle in memoria.
  • L'indice columnstore cluster e non cluster ha un'opzione relativa alla compressione dell'archivio che comprime ulteriormente i dati. L'opzione di archiviazione è utile per ridurre le dimensioni dei dati in memoria e su disco, ma comporta un rallentamento delle prestazioni delle query. Funziona anche per i dati a cui si accede raramente.
  • L'indice columnstore cluster e quello non cluster funzionano in modo molto simile: usano lo stesso formato di archiviazione a colonne, lo stesso motore di elaborazione delle query e lo stesso insieme di viste di gestione dinamica. La differenza risiede nei tipi di indice primario e secondario e nel fatto che l'indice columnstore non cluster è di sola lettura.
  • Questi operatori vengono eseguiti in modalità batch per le query multithreading: SCAN, FILTER, PROJECT, JOIN, GROUP BY e UNION ALL.

SQL Server 2012 (11.x)

SQL Server 2012 (11.x) ha introdotto l'indice columnstore non cluster come un altro tipo di indice nelle tabelle rowstore e l'elaborazione batch per le query sui dati columnstore.

  • Una tabella rowstore può avere un solo indice columnstore non cluster.
  • L'indice columnstore è di sola lettura. Dopo aver creato l'indice columnstore non è possibile aggiornare la tabella tramite operazioni INSERT, DELETE e UPDATE: per eseguire queste operazioni è necessario eliminare l'indice, aggiornare la tabella e ricompilare l'indice columnstore. È possibile caricare dati aggiuntivi nella tabella usando un cambio di partizione. Il vantaggio del cambio di partizione è che consente di caricare dati senza eliminare e ricompilare l'indice columnstore.
  • L'indice columnstore richiede sempre memoria aggiuntiva, in genere un ulteriore 10% per rowstore, poiché archivia una copia dei dati.
  • L'elaborazione batch consente di raddoppiare o migliorare le prestazioni delle query, ma è disponibile solo per l'esecuzione di query parallele.

Contenuto correlato