Indici columnstore nei magazzini di dati

Articolo
2025-04-09

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

Gli indici columnstore, in combinazione con il partizionamento, sono essenziali per la creazione di un data warehouse SQL Server. Questo articolo è incentrato sui casi d'uso principali ed esempi per le progettazioni di data warehousing con il motore di database SQL.

Funzionalità principali per l'archiviazione dati

SQL Server 2016 (13.x) ha introdotto queste funzionalità per i miglioramenti delle prestazioni columnstore:

I gruppi di disponibilità AlwaysOn supportano l'esecuzione di query su un indice columnstore in una replica secondaria leggibile.
MARS (Multiple Active Result Sets) supporta gli indici columnstore.
La nuova vista a gestione dinamica (DMV) sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) offre informazioni sulla risoluzione dei problemi di prestazioni a livello di gruppo di righe.
Tutte le query sugli indici columnstore possono essere eseguite in modalità batch. In precedenza, solo le query parallele potevano essere eseguite in modalità batch.
Gli operatori Sort, Distinct Sort e Distinct vengono eseguiti in modalità batch.
Le aggregazioni delle finestre vengono ora eseguite in modalità batch per il livello di compatibilità del database 130 e versioni successive.
Ottimizzazione degli aggregati per un'elaborazione efficiente delle aggregazioni. Supportata in tutti i livelli di compatibilità del database.
Distribuzione dei predicati stringa per un'elaborazione efficiente dei predicati stringa. Supportata in tutti i livelli di compatibilità del database.
Isolamento dello snapshot per il livello di compatibilità del database 130 e i livelli superiori.
Gli indici columnstore cluster ordinati sono stati introdotti con SQL Server 2022 (16.x). Per altre informazioni, vedere CREATE COLUMNSTORE INDEX e Ottimizzazione delle prestazioni con indici columnstore ordinati. Per la disponibilità degli indici columnstore ordinati, vedere Disponibilità indici columnstore ordinati.

Per altre informazioni sulle nuove funzionalità nelle versioni e nelle piattaforme di SQL Server e Azure SQL, vedere Novità negli indici columnstore.

Migliorare le prestazioni unendo indici non clusterizzati e columnstore.

A partire da SQL Server 2016 (13.x), è possibile creare indici non cluster rowstore in un indice columnstore raggruppato.

Esempio: migliorare l'efficienza delle ricerche nella tabella con un indice non clusterizzato

Per migliorare l'efficienza della ricerca nelle tabelle in un data warehouse, è possibile creare un indice non cluster progettato per ottimizzare le query che danno il meglio con la ricerca nelle tabelle. Le query che cercano valori corrispondenti o restituiscono un intervallo di valori ridotto garantiscono prestazioni migliori con un indice albero B che con un indice columnstore. Non richiedono un'analisi completa dell'indice columnstore e restituiscono più velocemente il risultato corretto eseguendo una ricerca binaria tramite un indice albero B.

--BASIC EXAMPLE: Create a nonclustered index on a columnstore table.

--Create the table
CREATE TABLE t_account (
    AccountKey int NOT NULL,
    AccountDescription nvarchar (50),
    AccountType nvarchar(50),
    UnitSold int
);

--Store the table as a columnstore.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;

--Add a nonclustered index.
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

Esempio: usare un indice non clusterizzato per imporre un vincolo di chiave primaria su una tabella columnstore.

Poiché una tabella può avere al massimo un indice clusterizzato, una tabella con un indice columnstore clusterizzato non può avere un vincolo di chiave primaria clusterizzata. Per creare un vincolo di chiave primaria in una tabella columnstore, è necessario dichiararlo come non clusterizzato.

Nell'esempio seguente viene creata una tabella con un vincolo di chiave primaria non clusterizzato e quindi viene creato un indice columnstore clusterizzato sulla tabella. Poiché qualsiasi inserimento o aggiornamento nella tabella columnstore modifica anche l'indice non cluster, tutte le operazioni che violano il vincolo di chiave primaria causano l'esito negativo dell'intera operazione.

--Create a primary key constraint on a columnstore table.

--Create a rowstore table with a nonclustered primary key constraint.
CREATE TABLE t_account (
    AccountKey int NOT NULL,
    AccountDescription nvarchar (50),
    AccountType nvarchar(50),
    UnitSold int,
    CONSTRAINT pk_account PRIMARY KEY NONCLUSTERED (AccountKey)
);

--Convert the table to columnstore.
--The primary key constraint is preserved as a nonclustered index on the columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account;

Migliorare le prestazioni abilitando il blocco a livello di riga e di gruppo di righe.

Per integrare l'indice non clusterizzato in una funzionalità di indice columnstore, SQL Server 2016 (13.x) offre la capacità di blocco granulare per le operazioni SELECT, UPDATE e DELETE. È possibile eseguire query con blocco a livello di riga per operazioni di index seek su un indice non cluster e con blocco a livello di gruppo di righe per operazioni di analisi completa di tabelle sull'indice columnstore. Usa questa funzionalità per ottenere un maggiore parallelismo di lettura e scrittura utilizzando in modo appropriato il blocco a livello di riga e il blocco a livello di gruppo di righe.

--Granular locking example
--Store table t_account as a columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account

--Add a nonclustered index for use with this example
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

--Look at locking with access through the nonclustered index
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRAN
    -- The query plan chooses a seek operation on the nonclustered index
    -- and takes the row lock
    SELECT * 
    FROM t_account 
    WHERE AccountKey = 100;
COMMIT TRAN;

Isolamento dello snapshot e isolamento dello snapshot read-committed

Usare l'isolamento dello snapshot (SI) per garantire la coerenza transazionale e l'isolamento dello snapshot read-committed (RCSI) per garantire la coerenza a livello di dichiarazione per le query sugli indici di colonna. Questo consente di eseguire le query senza bloccare le scritture di dati. Questo comportamento, oltre a non causare blocchi, riduce anche in modo significativo la probabilità di deadlock per le transazioni complesse. Per ulteriori informazioni, consultare Livelli di isolamento basati sul controllo delle versioni delle righe nel motore di database.

Condividi tramite