Leggere in inglese

Condividi tramite

Novità degli indici columnstore

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)Database SQL 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à dell'indice a colonne 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 SQL di Azure2 e Istanza gestita di SQL di Azure AUTD Pool SQL dedicato di Azure Synapse Analytics
Esecuzione in modalità batch per query multithreaded3 sì sì sì sì sì sì sì sì
Esecuzione in modalità batch per le query a thread singolo sì sì sì sì sì sì
Opzione di compressione dell'archivio sì sì sì sì sì sì sì
Isolamento dello snapshot e isolamento dello snapshot a livello di lettura confermata sì sì sì sì sì sì
Specificare l'indice columnstore durante la creazione di una tabella sì sì sì sì sì sì
Always On supporta gli indici columnstore sì sì sì sì sì sì sì sì
Le repliche secondarie leggibili di Always On supportano un indice columnstore non raggruppato in modalità di sola lettura. sì sì sì sì sì sì sì sì
Le repliche secondarie leggibili di Always On supportano indici columnstore aggiornabili. sì sì sì sì
Indice columnstore non clusterizzato di sola lettura su heap o B-Tree sì sì Sì 4 Sì 4 Sì 4 Sì 4 Sì 4 Sì 4
Indice archivio di colonne non clusterizzato aggiornabile su heap o B-tree sì sì sì sì sì sì
Indici B-Tree aggiuntivi consentiti su un heap o B-Tree che dispone di un indice archivio di colonne non cluster. sì sì sì sì sì sì sì sì
Indice columnstore raggruppato aggiornabile sì sì sì sì sì sì sì
Indice ad albero B su un indice columnstore raggruppato sì sì sì sì sì sì
Indice columnstore su una tabella ottimizzata per la memoria sì sì sì sì sì sì
La definizione degli indici columnstore non-clustered consente l'uso di una condizione filtrata sì sì sì sì sì sì
Opzione relativa al ritardo di compressione per gli indici columnstore in CREATE TABLE e ALTER TABLE sì sì sì sì sì sì
Supporto per il tipo nvarchar(max) sì sì sì sì nessun 5
L'indice di tipo columnstore può avere una colonna calcolata non persistente sì sì sì
Supporto dell'unione in background di trasferimento di tuple sì sì sì sì
Indici ordinati e clusterizzati di tipo columnstore sì sì sì
Indici columnstore ordinati non raggruppati sì
Creazione e ricompilazione dell'indice columnstore online sì sì
Creazione e ricompilazione online dell'indice columnstore ordinato sì

1 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 in Enterprise Edition.
2 Per il database SQL di Azure, gli indici columnstore sono disponibili nei livelli DTU Premium, livelli DTU Standard - S3 e versioni successive e tutti i livelli vCore. 3 Il grado di parallelismo (DOP) per modalità batch operazioni è limitato a 2 per SQL Server Standard Edition e 1 per le edizioni Web ed Express di SQL Server. Questa limitazione si riferisce agli indici columnstore creati tramite le tabelle basate su disco e le tabelle ottimizzate per la memoria.
4 Per creare un indice columnstore non clusterizzato di sola lettura, archiviare su un filegroup di sola lettura.
5 Non supportato nei pool SQL dedicati, ma è supportato nel pool SQL serverless.
AUTD si applica all'istanza gestita di SQL di Azure configurata con i criteri di aggiornamento sempre aggiornati.

SQL Server 2022 (16.x)

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

  • Gli indici columnstore clustered ordinati migliorano le prestazioni delle query basate su 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. Per altre informazioni, vedere CREATE COLUMNSTORE INDEX e Ottimizzazione delle prestazioni con indici columnstore ordinati.
  • L'ottimizzazione delle ricerche di stringhe avviene tramite il pushdown del predicato e l'eliminazione di gruppi di righe nei columnstore raggruppati, utilizzando i valori di confine. 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), queste funzionalità di eliminazione dei segmenti si estendono ai tipi di dati string, binary e GUID e al datetimeoffset tipo di dati per la scalabilità maggiore di due. In precedenza, l'eliminazione del segmento columnstore si applicava solo ai tipi di dati numerici, di data e ora e al datetimeoffset tipo di dati con scala minore o uguale a due. Dopo l'aggiornamento a una versione di SQL Server che supporta l'eliminazione di segmenti min/max stringa (SQL Server 2022 (16.x) e versioni successive, l'indice columnstore non trae vantaggio da questa funzionalità finché non viene ricompilato usando ALTER INDEX REBUILD o CREATE INDEX WITH (DROP_EXISTING = ON).
  • Esclusione del raggruppamento di righe columnstore per i prefissi dei predicati LIKE, ad esempio column LIKE 'string%'. L'eliminazione dei segmenti non è supportata per l'uso di LIKE in modi che non siano prefissi, come nel caso di column LIKE '%string'.
  • 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 le colonne calcolate non persistenti negli indici columnstore clusterizzati. Le colonne calcolate persistenti non sono supportate negli indici columnstore clusterizzati. Non è possibile creare un indice columnstore non clusterizzato 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 di tipo 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 clusterizzati 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 clusterizzato solo sui dati freddi del carico di lavoro operativo.

  • Una tabella in memoria può avere un solo indice a colonne. È 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 clusterizzato può avere uno o più indici rowstore non clusterizzati. In precedenza, l'indice columnstore non supportava gli indici non cluster. SQL Server gestisce automaticamente gli indici non cluster per le operazioni DML.

  • Supporto per chiavi primarie e chiavi esterne tramite un indice B-tree per imporre questi vincoli su un indice columnstore clusterizzato.

  • 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 con conferma lettura (RCSI) e l'isolamento snapshot (SI). 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 possono essere 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.

  • Aggregazione Pushdown determina 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 di dato stringa. Il pushdown delle aggregazioni è supportato con o senza clausola GROUP BY sia per gli indici columnstore clusterizzati sia per quelli non clusterizzati. In SQL Server questa funzionalità avanzata è riservata per l'edizione Enterprise.

  • Lo spostamento del predicato di stringa accelera 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 collazioni 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 sia quando accedono ai dati in un archivio a righe (rowstore) che in un indice a colonne (columnstore).

Supportabilità

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 l'archiviazione a colonne:

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à di interoperabilità e non in modalità di compilazione nativa. È supportata l'esecuzione parallela.

Problemi noti

Si applica a: SQL Server e Istanza gestita di SQL di Azure

  • Attualmente, le colonne LOB (varbinary(max), varchar(max) e nvarchar(max)) nei segmenti compressi columnstore non sono influenzate da DBCC SHRINKDATABASE 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 clusterizzato come principale sistema di archiviazione della tabella. 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 mantiene la stessa funzionalità come in SQL Server 2012 (11.x), eccetto per gli 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 clusterizzato è supportato solo nelle tabelle su disco e non in quelle in memoria.
  • L'indice columnstore clusterizzato e non clusterizzato ha un'opzione di compressione per archiviazione 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 a colonne clusterizzato e quello non clusterizzato 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 è tra i tipi di indice primario e secondario, e l'indice columnstore non clusterizzato è 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 clusterizzato come un'ulteriore tipologia di indice per le tabelle rowstore e l'elaborazione massiva per le query sui dati columnstore.

  • Una tabella rowstore può avere un solo indice columnstore non clusterizzato.
  • 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