Introduzione agli indici columnstore per l'analisi operativa in tempo reale
Si applica a: 
SQL Server database SQL di Azure Istanza gestita di SQL di Azure database SQL in Microsoft Fabric
SQL Server 2016 (13.x) introduce l'analisi operativa in tempo reale, cioè la possibilità di eseguire contemporaneamente analisi e carichi di lavoro OLTP nelle stesse tabelle di database. Oltre a eseguire analisi in tempo reale, è possibile eliminare la necessità di ETL e di un data warehouse.
Spiegazione dell'analisi operativa in tempo reale
In passato le aziende usavano sistemi separati per i carichi di lavoro operativi, ad esempio OLTP, e per quelli di analisi. Per questi sistemi, i processi di estrazione, trasformazione e caricamento (ETL) spostano regolarmente i dati dall'archivio operativo in un archivio di analisi. I dati di analisi sono vengono in genere archiviati in un data warehouse o data mart dedicato all'esecuzione di query di analisi. Anche se questa soluzione ha rappresentato lo standard, presentava tre problemi principali:
Complessità. L'implementazione di ETL può richiedere una notevole quantità di codifica, soprattutto per caricare solo le righe modificate. L'identificazione delle righe che sono state modificate può risultare difficile.
Costo. L'implementazione di ETL richiede il costo di acquisto di licenze software e hardware aggiuntive.
Latenza dei dati. L'implementazione di ETL aggiunge un ritardo per l'esecuzione delle analisi. Se, ad esempio, il processo ETL viene eseguito al termine di ogni giornata lavorativa, le query di analisi verranno eseguite sui dati che risalgono ad almeno un giorno prima. Per molte aziende questo ritardo è inaccettabile, perché le loro attività dipendono dall'analisi dei dati in tempo reale. Ad esempio, il rilevamento di frodi richiede l'analisi in tempo reale sui dati operativi.
L'analisi operativa in tempo reale offre una soluzione a questi problemi.
Non si verifica alcun ritardo durante l'esecuzione di analisi e carichi di lavoro OLTP nella stessa tabella sottostante. Per gli scenari in cui è possibile usare l'analisi in tempo reale, i costi e la complessità vengono notevolmente ridotti eliminando la necessità di ETL e di acquistare e gestire un data warehouse separato.
Nota
L'analisi operativa in tempo reale è destinata allo scenario di una singola origine dati, ad esempio un'applicazione ERP (Enterprise Resource Planning) in cui è possibile eseguire sia i carichi di lavoro operativi che i carichi di lavoro di analisi. Ciò non sostituisce la necessità di un data warehouse separato quando è necessario integrare dati da più origini prima di eseguire il carico di lavoro di analisi o quando sono necessarie prestazioni delle analisi estremamente elevate usando dati preaggregati, ad esempio cubi.
L'analisi in tempo reale usa un indice columnstore aggiornabile in una tabella rowstore. L'indice columnstore gestisce una copia dei dati, quindi i carichi di lavoro OLTP e di analisi vengono eseguiti su copie separate dei dati. In questo modo si riduce al minimo l'impatto sulle prestazioni causato dall'esecuzione contemporanea di entrambi i carichi di lavoro. SQL Server gestisce automaticamente le modifiche di indice in modo le modifiche OLTP siano sempre aggiornate per l'analisi. Con questa progettazione, è possibile e utile eseguire l'analisi in tempo reale su dati aggiornati. Ciò funziona sia per le tabelle basate su disco che per le tabelle ottimizzate per la memoria.
Esempio introduttivo
Per iniziare a usare l'analisi in tempo reale:
Nello schema operativo identificare le tabelle che contengono i dati necessari per l'analisi.
Per ogni tabella eliminare tutti gli indici ad albero B progettati principalmente per velocizzare l'analisi esistente sul carico di lavoro OLTP. Sostituirli con un indice columnstore singolo. Questo può migliorare le prestazioni complessive del carico di lavoro OLTP perché saranno presenti meno indici da gestire.
--This example creates a nonclustered columnstore index on an existing OLTP table. --Create the table CREATE TABLE t_account ( accountkey int PRIMARY KEY, accountdescription nvarchar (50), accounttype nvarchar(50), unitsold int ); --Create the columnstore index with a filtered condition CREATE NONCLUSTERED COLUMNSTORE INDEX account_NCCI ON t_account (accountkey, accountdescription, unitsold) ;L'indice columnstore in una tabella in memoria consente l'analisi operativa grazie all'integrazione di tecnologie OLTP in memoria e columnstore in memoria per fornire prestazioni elevate per carichi di lavoro OLTP e di analisi. L'indice columnstore in una tabella in memoria deve includere tutte le colonne.
-- This example creates a memory-optimized table with a columnstore index. CREATE TABLE t_account ( accountkey int NOT NULL PRIMARY KEY NONCLUSTERED, Accountdescription nvarchar (50), accounttype nvarchar(50), unitsold int, INDEX t_account_cci CLUSTERED COLUMNSTORE ) WITH (MEMORY_OPTIMIZED = ON ); GO
A questo punto si è pronti per eseguire l'analisi operativa in tempo reale senza apportare modifiche all'applicazione. Le query di analisi verranno eseguite sull'indice columnstore e le operazioni OLTP continueranno a essere eseguite su indici ad albero B OLTP. I carichi di lavoro OLTP continueranno a essere eseguiti, ma la gestione dell'indice columnstore determinerà un sovraccarico aggiuntivo. Vedere le ottimizzazioni delle prestazioni nella sezione successiva.
Nota
Nella documentazione viene usato in modo generico il termine albero B in riferimento agli indici. Negli indici rowstore, il motore di database implementa un albero B+. Ciò non si applica a indici columnstore o a indici in tabelle ottimizzate per la memoria. Per altre informazioni, vedere Architettura e guida per la progettazione degli indici di SQL Server e Azure SQL.
Post di blog
Leggere i post di blog seguenti per altre informazioni sull'analisi operativa in tempo reale. La lettura preliminare del blog potrebbe semplificare la comprensione delle sezioni relative ai suggerimenti sulle prestazioni.
Uso di un indice columnstore non cluster per l'analisi operativa in tempo reale
Un semplice esempio di uso di indice columnstore non cluster
Analisi operativa in tempo reale con tabelle ottimizzate per la memoria
Video
La serie video Data Exposed (Dati esposti) illustra in dettaglio alcune delle funzionalità e delle considerazioni.
- Parte 1: Come Azure SQL abilita l'analisi operativa in tempo reale (HTAP)
- Parte 2: Ottimizzare i database e le applicazioni esistenti con l'analisi operativa
- Parte 3: Come creare l'analisi operativa con Funzioni finestra.
Suggerimento per le prestazioni n. 1: usare indici filtrati per migliorare le prestazioni delle query
L'esecuzione dell'analisi operativa in tempo reale può compromettere le prestazioni del carico di lavoro OLTP. Tale impatto dovrebbe essere minimo. Esempio A illustra come usare gli indici filtrati per ridurre al minimo l'impatto dell'indice columnstore non cluster sul carico di lavoro transazionale, pur offrendo analisi in tempo reale.
Per ridurre al minimo l'overhead di gestione di un indice columnstore non cluster in un carico di lavoro operativo, è possibile usare una condizione filtrata per creare un indice columnstore non cluster solo per i dati meno attivi o a modifica lenta. Ad esempio, in un'applicazione di gestione degli ordini è possibile creare un indice columnstore non cluster negli ordini che sono già stati spediti. Dopo la spedizione, raramente l'ordine viene modificato e quindi i dati possono essere considerati meno attivi. Con l'indice filtrato i dati nell'indice columnstore non cluster richiedono meno aggiornamenti, riducendo in tal modo l'impatto sul carico di lavoro transazionale.
Le query di analisi accedono in modo trasparente sia ai dati meno attivi che ai dati attivi in base alle esigenze per fornire analisi in tempo reale. Se una parte importante del carico di lavoro operativo riguarda la gestione dei dati ad accesso frequente, le operazioni non richiederanno ulteriore manutenzione dell'indice columnstore. Una procedura consigliata prevede la creazione di un indice cluster rowstore per le colonne usate nella definizione dell'indice filtrato. SQL Server usa l'indice cluster per analizzare rapidamente le righe che non soddisfano la condizione filtrata. Senza questo indice cluster, sarà necessario eseguire una scansione completa della tabella rowstore per trovare le righe che possono esercitare un elevato impatto negativo sulle prestazioni di una query di analisi. In assenza di un indice cluster si potrebbe creare un indice ad albero B non cluster filtrato complementare per identificare le righe, ma questa scelta non è consigliabile perché l'accesso a un ampio intervallo di righe usando indici ad albero B non cluster comporta costi elevati.
Nota
Un indice columnstore non cluster filtrato è supportato solo nelle tabelle basate su disco. Non è supportato nelle tabelle ottimizzate per la memoria.
Esempio A: accesso a dati ad accesso frequente da un indice ad albero B e a dati ad accesso medio dall'indice columnstore
In questo esempio viene utilizzata una condizione filtrata (accountkey > 0) per stabilire quali righe saranno incluse nell'indice columnstore. L'obiettivo è di progettare la condizione filtrata e le query successive per accedere a dati attivi, caratterizzati da modifiche frequenti, dall'indice ad albero B e di accedere ai dati meno attivi, che sono più stabili, dall'indice columnstore.
Nota
Query Optimizer prenderà in considerazione, ma non sempre sceglierà, l'indice columnstore per il piano di query. Quando Query Optimizer sceglie l'indice columnstore filtrato, combina in modo trasparente le righe dall'indice columnstore nonché le righe che non soddisfano la condizione filtrata per consentire l'analisi in tempo reale. Si tratta di un indice diverso da un normale indice filtrato non cluster che può essere usato solo nelle query limitate alle righe presenti nell'indice.
--Use a filtered condition to separate hot data in a rowstore table
-- from "warm" data in a columnstore index.
-- create the table
CREATE TABLE orders (
AccountKey int not null,
Customername nvarchar (50),
OrderNumber bigint,
PurchasePrice decimal (9,2),
OrderStatus smallint not null,
OrderStatusDesc nvarchar (50));
-- OrderStatusDesc
-- 0 => 'Order Started'
-- 1 => 'Order Closed'
-- 2 => 'Order Paid'
-- 3 => 'Order Fulfillment Wait'
-- 4 => 'Order Shipped'
-- 5 => 'Order Received'
CREATE CLUSTERED INDEX orders_ci ON orders(OrderStatus);
--Create the columnstore index with a filtered condition
CREATE NONCLUSTERED COLUMNSTORE INDEX orders_ncci ON orders (accountkey, customername, purchaseprice, orderstatus)
where orderstatus = 5;
-- The following query returns the total purchase done by customers for items > $100 .00
-- This query will pick rows both from NCCI and from 'hot' rows that are not part of NCCI
SELECT top 5 customername, sum (PurchasePrice)
FROM orders
WHERE purchaseprice > 100.0
Group By customername;
La query di analisi verrà eseguita con il piano di query seguente. Come si può notare, è possibile accedere alle righe che non soddisfano la condizione filtrata solo usando l'indice ad albero B cluster.
Per altre informazioni, vedere Blog: Indice columnstore non cluster filtrato.
Suggerimento per le prestazioni n. 2: offload dell'analisi a una replica secondaria leggibile AlwaysOn
Anche se è possibile ridurre al minimo la manutenzione degli indici columnstore usando un indice columnstore filtrato, le query di analisi possono richiedere comunque risorse di calcolo elevate (CPU, I/O, memoria) che influiscono sulle prestazioni del carico di lavoro operativo. Per i carichi di lavoro maggiormente mission-critical, si consiglia di usare la configurazione AlwaysOn. In questa configurazione è possibile eliminare l'impatto dell'esecuzione dell'analisi eseguendone l'offload a una replica secondaria leggibile.
Suggerimento per le prestazioni n. 3: riduzione della frammentazione dell'indice, mantenendo i dati attivi nei rowgroup delta
Le tabelle con indice columnstore potrebbero essere frammentate in modo significativo( ovvero le righe eliminate) se il carico di lavoro aggiorna o elimina righe compresse. Un indice columnstore frammentato determina un utilizzo inefficiente della memoria o dell'archiviazione. Oltre all'uso inefficiente delle risorse, influisce negativamente sulle prestazioni delle query di analisi a causa dell'I/O aggiuntivo e della necessità di filtrare le righe eliminate dal set di risultati.
Le righe eliminate non vengono fisicamente rimosse fino a quando non si esegue la deframmentazione degli indici con il comando REORGANIZE o si ricompila l'indice columnstore nell'intera tabella o nelle partizioni interessate. Sia REORGANIZE che REBUILD sono operazioni di indice dispendiose in termini di risorse che diversamente potrebbero essere usate per il carico di lavoro. Inoltre, se le righe compresse troppo presto, potrebbe essere necessario ricomprimere più volte a causa di aggiornamenti che causano un sovraccarico di compressione sprecato.
È possibile ridurre al minimo la frammentazione dell'indice usando l'opzione COMPRESSION_DELAY.
-- Create a sample table
CREATE TABLE t_colstor (
accountkey int not null,
accountdescription nvarchar (50) not null,
accounttype nvarchar(50),
accountCodeAlternatekey int);
-- Creating nonclustered columnstore index with COMPRESSION_DELAY. The columnstore index will keep the rows in closed delta rowgroup for 100 minutes
-- after it has been marked closed
CREATE NONCLUSTERED COLUMNSTORE index t_colstor_cci on t_colstor (accountkey, accountdescription, accounttype)
WITH (DATA_COMPRESSION= COLUMNSTORE, COMPRESSION_DELAY = 100);
Per altre informazioni, vedere Blog: Ritardo della compressione.
Le procedure consigliate sono le seguenti:
Carico di lavoro costituito da inserimento/query: se il carico di lavoro è costituito principalmente dall'inserimento di dati e dall'esecuzione di query su tali dati, il valore COMPRESSION_DELAY predefinito di 0 è l'opzione consigliata. Le nuove righe inserite verranno compresse dopo l'inserimento di 1 milione di righe in un singolo rowgroup delta.
Alcuni esempi di tale carico di lavoro sono (a) tradizionali analisi del flusso di lavoro DW (b) quando è necessario analizzare il modello di selezione in un'applicazione Web.Carico di lavoro OLTP: se il carico di lavoro è pesante da DML, ovvero una combinazione intensa di aggiornamenti, eliminazione e inserimento, è possibile che venga visualizzata la frammentazione dell'indice columnstore esaminando la DMV
sys. dm_db_column_store_row_group_physical_stats. Se si nota che una percentuale > al 10% delle righe è contrassegnata come eliminata in rowgroup compressi di recente, è possibile usare l'opzione COMPRESSION_DELAY per aggiungere un ritardo quando le righe diventano idonee per la compressione. Se, ad esempio, il carico di lavoro appena inserito rimane attivo (ovvero viene aggiornato più volte) per 60 minuti, è consigliabile scegliere 60 come COMPRESSION_DELAY.
È probabile che nella maggior parte dei casi i clienti non debbano effettuare alcuna azione. Il valore predefinito dell'opzione COMPRESSION_DELAY dovrebbe funzionare per loro.
Per gli utenti esperti, è consigliabile eseguire la query seguente e raccogliere la percentuale di righe eliminate negli ultimi sette giorni.
SELECT row_group_id,cast(deleted_rows as float)/cast(total_rows as float)*100 as [% fragmented], created_time
FROM sys. dm_db_column_store_row_group_physical_stats
WHERE object_id = object_id('FactOnlineSales2')
AND state_desc='COMPRESSED'
AND deleted_rows>0
AND created_time > GETDATE() - 7
ORDER BY created_time DESC;
Se il numero di righe eliminate in rowgroup compressi > è pari al 20%, l'aumento dei rowgroup meno recenti con < variazione del 5% (definito rowgroup ad accesso sporadico) impostato COMPRESSION_DELAY = (youngest_rowgroup_created_time - current_time). Questo approccio funziona meglio con un carico di lavoro stabile e relativamente omogeneo.