Indici columnstore - Data warehouseColumnstore indexes - data warehouse

QUESTO ARGOMENTO SI APPLICA A:sìSQL Server (a partire dalla versione 2016)noDatabase SQL di AzurenoAzure SQL Data WarehousenoParallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Gli indici columnstore, in combinazione con il partizionamento, sono essenziali per la creazione di un data warehouse SQL ServerSQL Server .Columnstore indexes, in conjunction with partitioning, are essential for building a SQL ServerSQL Server data warehouse.

NovitàWhat’s new

SQL Server 2016SQL Server 2016 offre prestazioni migliori degli indici columnstore grazie all'introduzione delle funzionalità seguenti: introduces these features for columnstore performance enhancements:

  • Supporto in AlwaysOn dell'esecuzione di query su un indice columnstore in una replica secondaria leggibile.Always On supports querying a columnstore index on a readable secondary replica.

  • Supporto in MARS (Multiple Active Result Sets) degli indici columnstore.Multiple Active Result Sets (MARS) supports columnstore indexes.

  • La nuova vista a gestione dinamica 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.A new dynamic management view sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) provides performance troubleshooting information at the row group level.

  • Possibilità di eseguire query a thread singolo in modalità batch su indici columnstore.Single-threaded queries on columnstore indexes can run in batch mode. In precedenza era possibile eseguire in modalità batch solo query a thread multipli.Previously, only multi-threaded queries could run in batch mode.

  • Possibilità di eseguire l'operatore SORT in modalità batch.The SORT operator runs in batch mode.

  • Possibilità di eseguire più operazioni DISTINCT in modalità batch.Multiple DISTINCT operation runs in batch mode.

  • Window Aggregates viene ora eseguito in modalità batch per il livello di compatibilità database 130Window Aggregates now runs in batch mode for database compatibility level 130

  • Distribuzione dell'aggregazione per un'elaborazione efficiente delle aggregazioni.Aggregate Pushdown for efficient processing of aggregates. Supportata in tutti i livelli di compatibilità del database.This is supported on all database compatibility levels.

  • Distribuzione dei predicati stringa per un'elaborazione efficiente dei predicati stringa.String predicate pushdown for efficient processing of string predicates. Supportata in tutti i livelli di compatibilità del database.This is supported on all database compatibility levels.

  • Isolamento dello snapshot per il livello di compatibilità del database 130Snapshot isolation for database compatibility level 130

Migliorare le prestazioni con una combinazione di indici non cluster e columnstoreImprove performance by combining nonclustered and columnstore indexes

A partire da SQL Server 2016SQL Server 2016è possibile definire indici non cluster in un indice columnstore cluster.Beginning with SQL Server 2016SQL Server 2016, you can define nonclustered indexes on a clustered columnstore index.

Esempio: migliorare l'efficienza del posizionamento all'interno delle tabelle con un indice non clusterExample: Improve efficiency of table seeks with a nonclustered index

Per migliorare l'efficienza del posizionamento all'interno delle tabelle in un data warehouse, è possibile creare un indice non cluster progettato per l'esecuzione di query le cui prestazioni vengono ottimizzate dal posizionamento all'interno delle tabelle.To improve efficiency of table seeks in a data warehouse, you can create a nonclustered index designed to run queries that perform best with table seeks. Le query che cercano valori corrispondenti o restituiscono un intervallo ridotto di valori garantiscono prestazioni migliori con un indice BTree che con un indice columnstore.For example, queries that look for matching values or return a small range of values will perform better against a btree index rather than a columnstore index. Tali query non richiedono l'analisi completa della tabella eseguita dall'indice columnstore e restituiscono il risultato corretto più velocemente eseguendo una ricerca binaria tramite un indice BTree.They don’t require a full table scan through the columnstore index and will return the correct result faster by doing a binary search through a btree index.

--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 cluster per imporre un vincolo di chiave primaria su un indice columnstore.Example: Use a nonclustered index to enforce a primary key constraint on a columnstore table

Per motivi di progettazione, una tabella columnstore non consente vincoli di chiave primaria.By design, a columnstore table does not allow a primary key constraint. È ora possibile imporre un vincolo di chiave primaria su una tabella columnstore tramite un indice non cluster.Now you can use a nonclustered index on a columnstore table to enforce a primary key constraint. Una chiave primaria è equivalente a un vincolo UNIQUE su una colonna non NULL e SQL Server implementa un vincolo UNIQUE come indice non cluster.A primary key is equivalent to a UNIQUE constraint on a non-NULL column, and SQL Server implements a UNIQUE constraint as a nonclustered index. Combinando questi fatti, l'esempio seguente definisce un vincolo UNIQUE sulla colonna non NULL accountkey.Combining these facts, the following example defines a UNIQUE constraint on the non-NULL column accountkey. Il risultato è un indice non cluster che impone un vincolo di chiave primaria come vincolo UNIQUE su una colonna non NULL.The result is a nonclustered index that enforces a primary key constraint as a UNIQUE constraint on a non-NULL column.

La tabella viene quindi convertita in un indice columnstore cluster.Next, the table is converted to a clustered columnstore index. Durante la conversione l'indice non cluster diventa permanente.During the conversion, the nonclustered index persists. Il risultato è un indice columnstore cluster con un indice non cluster che impone un vincolo di chiave primaria.The result is a clustered columnstore index with a nonclustered index that enforces a primary key constraint. Poiché qualsiasi aggiornamento o inserimento nella tabella columnstore influirà anche sull'indice non cluster, tutte le operazioni che violano il vincolo UNIQUE e la caratteristica non NULL causeranno l'esito negativo dell'intera operazione.Since any update or insert on the columnstore table will also affect the nonclustered index, all operations that violate the unique constraint and the non-NULL will cause the entire operation to fail.

Il risultato è un indice columnstore con un indice non cluster che impone un vincolo di chiave primaria su entrambi gli indici.The result is a columnstore index with a nonclustered index that enforces a primary key constraint on both indexes.

--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 righeImprove performance by enabling row-level and row-group-level locking

Per integrare l'indice non cluster in un indice columnstore, SQL Server 2016SQL Server 2016 offre la funzionalità di blocco granulare per le operazioni di selezione, aggiornamento ed eliminazione.To complement the nonclustered index on a columnstore index feature, SQL Server 2016SQL Server 2016 offers granular locking capability for select, update, and delete operations. È 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.Queries can run with row-level locking on index seeks against a nonclustered index and rowgroup-level locking on full table scans against the columnstore index. Usare questa funzionalità per ottenere una concorrenza più elevata in lettura e scrittura con l'uso appropriato del blocco a livello di riga e a livello di gruppo di righe.Use this to achieve higher read/write concurrency by using row-level and rowgroup-level locking appropriately.

--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;  
END TRAN  

Isolamento dello snapshot e isolamento dello snapshot Read CommittedSnapshot isolation and read-committed snapshot isolations

Usare l'isolamento dello snapshot per garantire la coerenza transazionale e l'isolamento dello snapshot Read Committed per garantire la coerenza a livello di istruzione per le query su indici columnstore.Use snapshot isolation (SI) to guarantee transactional consistency, and read-committed snapshot isolations (RCSI) to guarantee statement level consistency for queries on columnstore indexes. Questo consente di eseguire le query senza bloccare le scritture di dati.This allows the queries to run without blocking data writers. Questo comportamento, oltre a non causare blocchi, riduce anche in modo significativo la probabilità di deadlock per le transazioni complesse.This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions. Per altre informazioni, vedere Isolamento dello Snapshot in SQL Server in MSDN.For more information, see Snapshot Isolation in SQL Server on MSDN.

Vedere ancheSee Also

Guida agli indici columnstoreColumnstore Indexes Guide
Caricamento dati di indici columnstoreColumnstore Indexes Data Loading
Riepilogo delle funzionalità con versione degli indici columnstoreColumnstore Indexes Versioned Feature Summary
Prestazioni delle query per gli indici columnstore Columnstore Indexes Query Performance
Introduzione a columnstore per l'analisi operativa in tempo reale Get started with Columnstore for real time operational analytics
Deframmentazione degli indici columnstore Columnstore Indexes Defragmentation