Condividi tramite

Indici columnstore nei magazzini di dati

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di Azure SQLAnalytics 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:

  • Always On supporta 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.
  • Le query a thread singolo sugli indici columnstore possono essere eseguite in modalità batch. In precedenza, solo le query multi-thread potevano essere eseguite in modalità batch.
  • L'operatore SORT viene eseguito in modalità batch.
  • È possibile eseguire più operazioni DISTINCT in modalità batch.
  • Window Aggregates viene ora eseguito in modalità batch per il livello di compatibilità database 130 e i livelli superiori.
  • Distribuzione dell'aggregazione 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 con una combinazione di indici non cluster e columnstore

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

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. Queste query non richiedono l'esecuzione di una scansione completa della tabella tramite l'indice columnstore e restituiscono il risultato corretto più velocemente eseguendo una ricerca binaria tramite un indice B-tree.

--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  
);  
GO  
  
--Store the table as a columnstore.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;  
GO  
  
--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.

Per impostazione predefinita, una tabella columnstore non consente un vincolo di chiave primaria cluster. Ora è possibile utilizzare un indice non clusterizzato su una tabella columnstore per imporre un vincolo di chiave primaria. Una chiave primaria è equivalente a un vincolo UNIQUE su una colonna non NULL e SQL Server implementa un vincolo UNIQUE come indice non cluster. Combinando questi fatti, l'esempio seguente definisce un vincolo UNICO sulla colonna accountkey, che non può essere NULL. Il risultato è un indice non cluster che impone un vincolo di chiave primaria come vincolo UNIQUE su una colonna non NULL.

La tabella viene quindi convertita in un indice clustered columnstore. Durante la conversione, l'indice non clusterizzato si mantiene. Il risultato è un indice columnstore cluster con un indice non clusterizzato che impone un vincolo di chiave primaria. Poiché qualsiasi aggiornamento o inserimento nella tabella columnstore influisce anche sull'indice non-clustered, tutte le operazioni che violano il vincolo UNIQUE e il campo non-NULL causano il fallimento dell'intera operazione.

Il risultato è un indice columnstore con un indice non cluster che impone un vincolo di chiave primaria su entrambi gli indici.

--EXAMPLE: Enforce a primary key constraint on a columnstore table.   
  
--Create a rowstore table with a unique constraint.  
--The unique constraint is implemented as a nonclustered index.  
CREATE TABLE t_account (  
    AccountKey int NOT NULL,  
    AccountDescription nvarchar (50),  
    AccountType nvarchar(50),  
    UnitSold int,  
  
    CONSTRAINT uniq_account UNIQUE (AccountKey)  
);  
  
--Store the table as a columnstore.   
--The unique constraint is preserved as a nonclustered index on the columnstore table.  
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account  
  
--By using the previous two steps, every row in the table meets the UNIQUE constraint  
--on a non-NULL column.  
--This has the same end-result as having a primary key constraint  
--All updates and inserts must meet the unique constraint on the nonclustered index or they will fail.  
  
--If desired, add a foreign key constraint on AccountKey.  
  
ALTER TABLE [dbo].[t_account]  
WITH CHECK ADD FOREIGN KEY([AccountKey]) REFERENCES my_dimension(Accountkey);

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

Per integrare l'indice non cluster in un indice columnstore, SQL Server 2016 (13.x) offre la funzionalità di blocco granulare per le operazioni di selezione, aggiornamento ed eliminazione. È 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  
GO  
  
--Add a nonclustered index for use with this example  
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);  
GO  
  
--Look at locking with access through the nonclustered index  
SET TRANSACTION ISOLATION LEVEL repeatable read;  
GO  
  
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 altre informazioni, vedere Isolamento dello snapshot in SQL Server.

Contenuto correlato