Guida alla progettazione e all'architettura dell'indice SQL di SQL Server e di Azure

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di sistemi analitici (PDW)

Gli indici progettati in modo non corretto e la mancanza di indici costituiscono le cause principali dei colli di bottiglia delle applicazioni di database. La progettazione di indici efficienti è fondamentale per ottenere buone prestazioni del database e dell'applicazione. Questa guida per la progettazione degli indici di contiene informazioni sull'architettura degli indici e le procedure consigliate che consentono di progettare indici validi per soddisfare le esigenze dell'applicazione.

In questa guida si presuppone che il lettore abbia una conoscenza generale dei tipi di indice disponibili. Per una descrizione generale dei tipi di indice, vedere Tipi di indice.

In questa guida vengono illustrati i tipi di indici seguenti:

Formato di archiviazione primario	Tipo di indice
Rowstore basato su disco
	Cluster
	Non cluster
	Unica
	Filtrato
Columnstore
	Columnstore cluster
	Columnstore non cluster
Ottimizzate per la memoria
	Hash
	Non cluster con ottimizzazione per la memoria

Per informazioni sugli indici XML, vedere Cenni preliminari sugli indici XML e indici XML selettivi (SXI).

Per informazioni sugli indici spaziali, vedere Panoramica degli indici spaziali.

Per informazioni sugli indici full-text, vedere Popolamento di indici full-text.

Nozioni di base sulla progettazione degli indici

Pensa a un normale libro: alla fine del libro c'è un indice che aiuta a individuare rapidamente le informazioni all'interno del libro. L'indice è un elenco ordinato di parole chiave e a ogni parola chiave segue un gruppo di numeri di pagina, che riporta alle pagine in cui è possibile trovare ciascuna parola chiave.

Un indice rowstore non è diverso: è un elenco ordinato di valori e per ogni valore sono presenti puntatori alle pagine di dati in cui si trovano questi valori. L'indice stesso viene archiviato in pagine denominate pagine di indice. In un libro, se l'indice si estende su più pagine e ad esempio è necessario trovare puntatori a tutte le pagine che contengono la parola "SQL", sarà necessario sfogliare il libro fino a quando non si trova la pagina di indice che contiene la parola chiave "SQL". Da qui segui i puntatori a tutte le pagine del libro. Questa operazione può essere ulteriormente ottimizzata se all'inizio dell'indice si crea una singola pagina contenente un elenco alfabetico indicante dove è possibile trovare ogni lettera. Ad esempio: "da A a D - pagina 121", "da E a G - pagina 122" e così via. Questa pagina aggiuntiva eliminerà il passaggio di consultazione dell'intero indice per trovare il punto di partenza. Tale pagina non esiste nei normali libri, ma esiste in un indice rowstore. Questa pagina singola viene definita pagina radice dell'indice. La pagina radice è la pagina iniziale della struttura ad albero utilizzata da un indice. Seguendo l'analogia dell'albero, le pagine finali che contengono puntatori ai dati effettivi vengono definite "pagine foglia" dell'albero.

Un indice è una struttura su disco o memoria associata a una tabella o a una vista che consente di recuperare in modo rapido le righe della tabella o della vista. Un indice rowstore contiene chiavi create da una o più colonne nella tabella o nella vista. Per gli indici rowstore, queste chiavi vengono archiviate in una struttura ad albero (albero B+) che consente al motore di database di trovare la riga o le righe associate ai valori chiave in modo rapido ed efficiente.

Un indice rowstore archivia i dati organizzati logicamente come tabella con righe e colonne e archiviati fisicamente in un formato di dati row-wise denominato rowstore¹ o archiviato in un formato di dati a livello di colonna denominato columnstore.

La selezione degli indici adatti a un database e al relativo carico di lavoro è un'operazione complessa che comporta la ricerca di un equilibrio tra velocità delle query e costi di aggiornamento. Gli indici rowstore basati su disco, ovvero indici con poche colonne nella chiave di indice, richiedono meno spazio su disco e spese di manutenzione inferiori. Gli indici estesi, d'altra parte, coprono più query. Potrebbe essere necessario sperimentare diverse soluzioni prima di trovare l'indice più efficiente. È possibile aggiungere, modificare ed eliminare indici senza modificare lo schema del database o la struttura dell'applicazione. Pertanto, non si dovrebbe esitare a sperimentare con indici diversi.

Query Optimizer nella motore di database sceglie in modo affidabile l'indice più efficace nella maggior parte dei casi. La strategia di progettazione complessiva degli indici deve fornire vari indici per Query Optimizer per scegliere tra e considerarlo attendibile per prendere la decisione corretta. In questo modo, è possibile ridurre i tempi di analisi e garantire buone prestazioni in numerose situazioni. Per visualizzare gli indici usati da Query Optimizer per una query specifica, in SQL Server Management Studio scegliere Includi piano di esecuzione effettivo dal menu Query.

L'utilizzo di indici non consente necessariamente di ottenere prestazioni ottimali e prestazioni ottimali non sempre sono da mettere in relazione all'utilizzo di indici. Se l'utilizzo di un indice garantisse sempre le prestazioni migliori, il processo di Query Optimizer risulterebbe semplice. In realtà, una scelta non corretta di un indice può portare a prestazioni per niente ottimali. Di conseguenza, l'attività di Query Optimizer consiste nel selezionare un indice o una combinazione di indici, solo quando migliora le prestazioni e per evitare il recupero indicizzato quando ostacola le prestazioni.

¹ Il formato rowstore è il metodo di archiviazione tradizionale per i dati relazionali di tabella. Rowstore fa riferimento alla tabella in cui il formato di archiviazione dei dati sottostante è un heap, un albero B+ (indice cluster) o una tabella ottimizzata per la memoria. Rowstore basato su disco esclude le tabelle ottimizzate per la memoria.

Attività di progettazione dell'indice

Le attività seguenti costituiscono la strategia consigliata per la progettazione degli indici:

1. Comprendere le caratteristiche del database.

   • Ad esempio, si tratta di un database OLTP (Online Transaction Processing) con frequenti modifiche ai dati che devono sostenere una velocità effettiva elevata? Le tabelle e gli indici ottimizzati per la memoria sono particolarmente appropriati per questo scenario, fornendo una progettazione senza latch. Per altre informazioni, vedere Indici per tabelle ottimizzate per la memoria, oppure Indici non cluster per le linee guida di progettazione di tabelle ottimizzate per la memoria e Indice Hash per le linee guida di progettazione di tabelle ottimizzate per la memoria presenti in questa guida.
   • Oppure è un esempio di un database OLAP (Decision Support System) o di data warehousing (OLAP) che deve elaborare rapidamente set di dati di grandi dimensioni? Gli indici columnstore sono particolarmente appropriati per i set di dati tipici di data warehousing. Gli indici columnstore possono trasformare l'ambiente di data warehousing per gli utenti consentendo prestazioni più veloci per le query di data warehousing comuni quali quelle di filtro, aggregazione, raggruppamento e join a stella. Per altre informazioni, vedere Indici columnstore - Panoramica e Linee guida per la progettazione di indici columnstore presenti in questa guida.

2. Comprendere le caratteristiche delle query utilizzate più di frequente. Ad esempio, sapendo che una query usata di frequente unisce due o più tabelle consente di determinare il tipo migliore di indici da usare.

3. Comprendere le caratteristiche delle colonne utilizzate nelle query. Ad esempio, un indice è ideale per le colonne con un tipo di dati Integer e sono anche colonne univoche o non Null. Per le colonne con subset di dati ben definiti, è possibile usare un indice filtrato in SQL Server 2008 (10.0.x) e versioni successive. Per altre informazioni, vedere Linee guida per la progettazione di indici filtrati in questa guida.

4. Determinare quali opzioni dell'indice potrebbero migliorare le prestazioni in fase di creazione o manutenzione dell'indice. Per la creazione, ad esempio, di un indice cluster in una tabella esistente di grandi dimensioni può essere utile usare l'opzione ONLINE. Tale opzione consente l'esecuzione di attività simultanee sui dati sottostanti durante la creazione o la ricompilazione dell'indice. Per altre informazioni vedere Impostare le opzioni di indice.

5. Determinare il percorso di archiviazione ottimale per l'indice.

   Un indice non cluster può essere archiviato nello stesso filegroup della tabella sottostante oppure in un filegroup diverso. Il percorso di archiviazione degli indici può consentire di migliorare le prestazioni di esecuzione delle query grazie a un aumento delle prestazioni di I/O su disco. L'archiviazione, ad esempio, di un indice non cluster in un filegroup in un disco diverso rispetto al filegroup della tabella può consentire di migliorare le prestazioni in quanto è possibile leggere più dischi contemporaneamente. In alternativa, per gli indici cluster e non cluster è possibile utilizzare uno schema di partizione in più filegroup. Quando si considera il partizionamento, determinare se è necessario che l'indice sia allineato, ovvero partizionato nello stesso modo della tabella, o partizionato in modo indipendente. Per altre informazioni, vedere la sezione relativa al posizionamento degli indici su filegroup o schemi di partizioni di questo articolo.

6. Quando si identificano gli indici mancanti con dmv (Dynamic Management Views), ad esempio sys.dm_db_missing_index_details e sys.dm_db_missing_index_columns, è possibile offrire variazioni simili di indici nella stessa tabella e colonna. Esaminare gli indici esistenti nella tabella insieme ai suggerimenti per l'indice mancanti per impedire la creazione di indici duplicati. Per altre informazioni, vedere Ottimizzare gli indici non cluster con suggerimenti per l'indice mancanti.

Linee guida generali per la progettazione degli indici

Gli amministratori esperti di database possono progettare un buon set di indici, ma questa attività è complessa, dispendiosa in termini di tempo e soggetta a errori anche per database e carichi di lavoro moderatamente complessi. Conoscere le caratteristiche del database, delle query e delle colonne di dati può aiutare a progettare indici ottimali.

Considerazioni sul database

Quando si progetta un indice è consigliabile attenersi alle linee guida seguenti sui database:

• Un numero elevato di indici in una tabella ha ripercussioni sulle prestazioni delle istruzioni INSERT, UPDATE, DELETE, e MERGE perché, quando vengono modificati i dati nella tabella, tutti gli indici devono essere modificati di conseguenza. Ad esempio, se una colonna viene usata in molti indici e si esegue un'istruzione UPDATE tramite cui i dati della colonna vengono modificati, ogni indice contenente la colonna in questione deve essere aggiornato, nonché la colonna nella tabella di base sottostante (heap o indice cluster).

  • Evitare di creare un numero eccessivo di indici in tabelle che vengono aggiornate spesso e mantenere indici di piccole dimensioni, vale a dire con il minor numero possibile di colonne.

  • Utilizzare molti indici per migliorare le prestazioni delle query nelle tabelle che vengono aggiornate raramente ma che contengono grandi volumi di dati. Un numero elevato di indici può aiutare le prestazioni delle query che non modificano i dati, ad esempio le istruzioni edizione Standard LECT, perché Query Optimizer include più indici tra cui scegliere per determinare il metodo di accesso più veloce.

• L'indicizzazione di tabelle di piccole dimensioni potrebbe non essere ottimale perché può richiedere più tempo a Query Optimizer di attraversare l'indice cercando i dati anziché eseguire un'analisi di tabella di base. Pertanto, può accadere che gli indici delle tabelle di piccole dimensioni non vengano mai utilizzati, ma devono comunque essere gestiti in caso di modifica dei dati della tabella.

• Gli indici nelle viste possono garantire miglioramenti significativi delle prestazioni quando la vista contiene aggregazioni, join di tabella o una combinazione di aggregazioni e join. Non è necessario fare riferimento in modo esplicito alla vista nella query per l'uso di Query Optimizer.

• I database nelle repliche primarie in database SQL di Azure generano automaticamente raccomandazioni sulle prestazioni di Database Advisor per gli indici. Facoltativamente , è possibile abilitare l'ottimizzazione automatica degli indici.

• Query Store consente di identificare le query con prestazioni non ottimali e fornisce una cronologia dei piani di esecuzione delle query selezionati dall'utilità di ottimizzazione.

Considerazioni sulle query

Quando si progetta un indice è consigliabile attenersi alle linee guida seguenti sulle query:

• Creare indici non cluster nelle colonne che vengono utilizzate spesso in predicati e condizioni di join nelle query. Queste sono le colonne SARGable¹. Tuttavia, è consigliabile non aggiungere colonne non necessarie. L'aggiunta di un numero eccessivo di colonne di indice può avere effetti negativi sullo spazio su disco e sulle prestazioni per la gestione degli indici.

• Gli indici di copertura possono migliorare le prestazioni delle query, perché tutti i dati necessari per soddisfare i requisiti della query esistono all'interno dell'indice stesso. In altre parole, per recuperare i dati richiesti sono necessarie solo le pagine di indice, e non le pagine di dati della tabella o dell'indice cluster. Viene dunque ridotto l'I/O complessivo del disco. Ad esempio, una query di colonne A e B su una tabella con un indice composito creato su colonne A, Be C può recuperare i dati specificati dall'indice da solo.

  Importante

  Il termine indice di copertura indica un indice non cluster che risolve uno o più risultati di query simili direttamente, senza accedere alla tabella di base e senza incorrere in ricerche.

  Tali indici hanno tutte le colonne non SARGable necessarie al livello foglia. Questo significa che le colonne restituite dalla clausola SELECT e tutti gli argomenti WHERE e JOIN sono coperti dall'indice.

  Il carico di I/O richiesto per eseguire la query è potenzialmente molto minore, se l'indice è sufficientemente ridotto rispetto alle righe e alle colonne nella tabella, ovvero rappresenta realmente un subset del totale delle colonne.

  Prendere in considerazione gli indici di copertura quando si seleziona una piccola parte di una tabella di grandi dimensioni e tale piccola parte è definita da un predicato fisso, ad esempio le colonne di tipo sparse che contengono solo pochi valori non NULL.

• Scrivere query che inseriscono o modificano il numero più alto possibile di righe con una sola istruzione, anziché utilizzare più query per aggiornare le stesse righe. L'utilizzo di una sola istruzione consente di avvalersi della gestione ottimizzata degli indici.

• Valutare il tipo di query e la modalità di utilizzo delle colonne nella query. Una colonna utilizzata in un tipo di query di corrispondenze esatte, ad esempio, potrebbe essere valida per un indice non cluster o cluster.

¹ Il termine SARGable nei database relazionali si riferisce a un predicato in grado di usare un indice per velocizzare l'esecuzione della query.

Considerazioni sulle colonne

Quando si progetta un indice è consigliabile attenersi alle linee guidata seguenti:

• Mantenere corta la chiave dell'indice negli indici cluster. Inoltre, gli indici cluster traggono vantaggio dalla creazione in colonne univoche o non Null.

• Le colonne dei tipi di dati ntext, text, image, varchar(max), nvarchar(max)e varbinary(max) non possono essere specificate come colonne chiave dell'indice. Tuttavia, i tipi di dati varchar(max), nvarchar(max), varbinary(max)e xml possono essere usati in un indice non cluster come colonne di indice non chiave. Per altre informazioni, vedere la sezione " Indice con colonne incluse" in questa guida.

• Un tipo di dati xml può essere solo una colonna chiave esclusivamente in un indice XML. Per altre informazioni, vedere Indici XML. SQL Server 2012 SP1 ha introdotto un nuovo tipo di indice XML noto come indice XML selettivo. Questo nuovo indice può migliorare le prestazioni di query sui dati archiviati come XML, consentire un'indicizzazione più rapida di carichi di lavoro di dati XML di grandi dimensioni e migliorare la scalabilità riducendo i costi di archiviazione dell'indice stesso. Per altre informazioni vedere Indici XML selettivi (SXI).

• Esaminare l'univocità delle colonne. Un indice univoco al posto di un indice non univoco nella stessa combinazione di colonne fornisce informazioni aggiuntive per Query Optimizer, che rendono l'indice più utile. Per altre informazioni, vedere Linee guida per la progettazione di indici univoci in questa guida.

• Esaminare la distribuzione dei dati nelle colonne indicizzate. Spesso l'esecuzione prolungata di una query deriva dall'indicizzazione di una colonna con pochi valori univoci o dall'esecuzione di un join su tale colonna. Si tratta di un problema fondamentale con i dati e le query e in genere non può essere risolto senza identificare questa situazione. Ad esempio, una directory telefonica fisica ordinata alfabeticamente sul cognome non accelera l'individuazione di una persona se tutte le persone della città sono denominate Smith o Jones. Per altre informazioni sulla distribuzione dei dati, vedere Statistiche.

• È consigliabile utilizzare indici filtrati su colonne dispongono di subset ben definiti, ad esempio colonne di tipo sparse, colonne con la maggior parte di valori Null, colonne con categorie di valori e colonne con intervalli di valori distinti. Un indice filtrato progettato correttamente consente di migliorare le prestazioni di esecuzione delle query e di ridurre i costi di archiviazione e di manutenzione dell'indice.

• Prendere in considerazione l'ordine delle colonne se l'indice contiene più colonne. La colonna utilizzata nella clausola WHERE in un oggetto uguale a (=), maggiore di (), minore di (><) o BETW edizione Enterprise N, o partecipa a un join, deve essere inserita per prima. Le altre colonne devono essere ordinate in base alla presenza di valori distinct, vale a dire da quella con più valori distinct a quella con meno valori distinct.

  Se, ad esempio, l'indice è definito come LastName, FirstName l'indice risulterà utile se il criterio di ricerca è WHERE LastName = 'Smith' o WHERE LastName = Smith AND FirstName LIKE 'J%'. Tuttavia, Query Optimizer non userà l'indice per una query in cui è stata eseguita la ricerca solo in FirstName (WHERE FirstName = 'Jane').

• Valutare l'opportunità di indicizzare colonne calcolate. Per altre informazioni, vedere Indici per le colonne calcolate.

Caratteristiche dell'indice

Dopo aver determinato che un indice è appropriato per una query, è possibile selezionare il tipo di indice più adatto alla situazione. Le caratteristiche degli indici sono:

• Cluster e non cluster
• Univoci e non univoci
• A colonna singola e a più colonne
• In ordine crescente o decrescente per le colonne dell'indice
• Di tabella completa o filtrato per gli indici non cluster
• Columnstore e rowstore
• Hash e non cluster per le tabelle ottimizzate per la memoria

È inoltre possibile personalizzare le caratteristiche iniziali dell'archiviazione dell'indice per ottimizzarne le prestazioni o la gestione impostando un'opzione quale FILLFACTOR. È inoltre possibile determinare la posizione di archiviazione dell'indice utilizzando filegroup o schemi di partizione per ottimizzare le prestazioni.

Posizionamento dell'indice in filegroup o schemi di partizioni

Durante lo sviluppo della strategia di progettazione degli indici, è opportuno considerare la posizione degli indici nei filegroup associati al database. Un'attenta selezione dello schema di filegroup o di partizione può contribuire a migliorare le prestazioni delle query.

Per impostazione predefinita, gli indici vengono archiviati nello stesso filegroup della tabella di base in cui viene creato l'indice. Un indice cluster non partizionato e la tabella di base sono sempre inclusi nello stesso filegroup. È tuttavia possibile eseguire una delle operazioni seguenti:

• Creare indici non cluster in un filegroup diverso dal filegroup della tabella di base o un indice cluster.
• Partizionare indici cluster e non cluster tra più filegroup.
• Spostare una tabella da un filegroup a un altro eliminando l'indice cluster e specificando un nuovo schema di filegroup o di partizione nella clausola MOVE TO dell'istruzione DROP INDEX oppure utilizzando l'istruzione CREATE INDEX con la clausola DROP_EXISTING.

La creazione dell'indice non cluster in un altro filegroup consente di migliorare le prestazioni se i filegroup utilizzano unità fisiche diverse con controller distinti. In tal caso, i dati e le informazioni degli indici possono essere letti in parallelo contemporaneamente da più testine. Ad esempio, se Table_A nel filegroup f1 e Index_A nel filegroup f2 vengono entrambi utilizzati dalla stessa query, è possibile che si riscontrino miglioramenti delle prestazioni, in quanto i filegroup vengono utilizzati integralmente senza contese. Tuttavia, se Table_A la query viene analizzata ma Index_A non viene fatto riferimento, viene usato solo il filegroup f1 . e non si otterrà un miglioramento delle prestazioni.

Poiché non è possibile stimare il tipo di accesso che si verificherà e quando si verifica, potrebbe essere una decisione migliore distribuire le tabelle e gli indici in tutti i filegroup. Poiché tutti i dati e gli indici sono suddivisi equamente in tutti i dischi, l'accesso riguarderà tutti i dischi, indipendentemente dalla modalità di accesso. Questo approccio è inoltre più semplice per gli amministratori del sistema.

Partizioni tra più filegroup

È anche possibile prendere in considerazione il partizionamento di indici cluster e non cluster basati su disco tra più filegroup. Gli indici vengono partizionati in orizzontale, ovvero per riga, in base a una funzione di partizione. Tale funzione definisce la modalità di mapping di ciascuna riga a un set di partizioni sulla base dei valori di colonne specifiche, dette colonne di partizionamento. Uno schema di partizione consente di specificare il mapping delle partizioni a un set di filegroup.

Il partizionamento di un indice può offrire i vantaggi seguenti:

• Fornire sistemi scalabili per una maggior gestibilità degli indici di grandi dimensioni. I sistemi OLTP, ad esempio, possono implementare applicazioni che riconoscono le partizioni e gestiscono correttamente gli indici di grandi dimensioni.

• Rendere più rapida ed efficace l'esecuzione delle query. Quando le query accedono a più partizioni di un indice, Query Optimizer può elaborare contemporaneamente singole partizioni ed escludere partizioni non interessate dalla query.

Per ulteriori informazioni, vedere Partitioned Tables and Indexes.

Linee guida per la progettazione degli ordini di ordinamento degli indici

Quando si definiscono gli indici, valutare se i dati per la colonna chiave di indice devono essere archiviati in ordine crescente o decrescente. Crescente è l'impostazione predefinita e mantiene la compatibilità con le versioni precedenti del motore di database. La sintassi delle istruzioni CREATE INDEX, CREATE TABLE e ALTER TABLE supporta le parole chiave ASC (ascending, crescente) e DESC (descending, decrescente) per singole colonne di indici e vincoli.

È utile specificare l'ordine di archiviazione dei valori chiave in un indice nel caso in cui le query che fanno riferimento alla tabella includono clausole ORDER BY che specificano direzioni diverse per la colonna o le colonne chiave nell'indice. In questi casi, l'indice non richiede un operatore SORT nel piano della query e pertanto la query risulta più efficiente. Ad esempio, gli acquirenti del reparto di acquisto Adventure Works Cycles devono valutare la qualità dei prodotti acquistati dai fornitori. Gli acquirenti sono più interessati a trovare i prodotti inviati dai fornitori con una percentuale di resi maggiore.

Come illustrato nella query seguente sul database di esempio AdventureWorks, il recupero dei dati per soddisfare questi criteri richiede l'ordinamento RejectedQty della colonna nella Purchasing.PurchaseOrderDetail tabella in ordine decrescente (da grande a piccolo) e la ProductID colonna da ordinare in ordine crescente (da piccolo a grande).

SELECT RejectedQty, ((RejectedQty/OrderQty)*100) AS RejectionRate,
    ProductID, DueDate
FROM Purchasing.PurchaseOrderDetail
ORDER BY RejectedQty DESC, ProductID ASC;
GO

Il piano di esecuzione seguente per questa query mostra che Query Optimizer ha usato un operatore SORT per restituire il set di risultati nell'ordine specificato dalla clausola ORDER BY.

Se viene creato un indice rowstore basato su disco con colonne chiave corrispondenti a quelle nella clausola ORDER BY nella query, l'operatore SORT può essere eliminato nel piano di query e il piano di query è più efficiente.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);
GO

Dopo che la query è stata eseguita di nuovo, il piano di esecuzione seguente mostra che l'operatore SORT è stato eliminato ed è stato utilizzato il nuovo indice non cluster creato.

Il motore di database può spostarsi in modo altrettanto efficiente in entrambe le direzioni. Un indice definito come (RejectedQty DESC, ProductID ASC) può comunque essere usato per una query in cui la direzione di ordinamento delle colonne nella clausola ORDER BY viene invertita. L'indice può ad esempio essere utilizzato da una query con la clausola ORDER BY ORDER BY RejectedQty ASC, ProductID DESC .

È possibile specificare l'ordinamento solo per le colonne chiave nell'indice. La vista del catalogo sys.index_columns e la funzione INDEXKEY_PROPERTY indicano se una colonna di un indice è archiviata in ordine crescente o decrescente.

Se si seguono gli esempi di codice nel database di esempio AdventureWorks, è possibile eliminare IX_PurchaseOrderDetail_RejectedQty con il codice Transact-SQL seguente:

DROP INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail;
GO

Metadati UFX

Usare queste viste dei metadati per visualizzare gli attributi degli indici. In alcune di queste viste sono incorporate altre informazioni sull'architettura.

Nota

Tutte le colonne di un indice columnstore vengono archiviate nei metadati come colonne incluse. L'indice columnstore non contiene colonne chiave.

sys.indexes (Transact-SQL)

sys.index_columns (Transact-SQL)

sys.partitions (Transact-SQL)

sys.internal_partitions (Transact-SQL)

sys.dm_db_index_operational_stats (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

sys.column_store_segments (Transact-SQL)

sys.column_store_dictionaries (Transact-SQL)

sys.column_store_row_groups (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_column_store_object_pool (Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

sys.dm_db_xtp_hash_index_stats (Transact-SQL)

sys.dm_db_xtp_index_stats (Transact-SQL)

sys.dm_db_xtp_object_stats (Transact-SQL)

sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)

sys.dm_db_xtp_table_memory_stats (Transact-SQL)

sys.hash_indexes (Transact-SQL)

sys.memory_optimized_tables_internal_attributes (Transact-SQL)

linee guida per la progettazione di indici cluster

Gli indici cluster ordinano e archiviano le righe di dati della tabella in base ai valori di chiave. Per ogni tabella è disponibile un solo indice cluster poiché le righe di dati possono essere ordinate con un solo tipo di ordinamento. Con poche eccezioni, ogni tabella deve avere un indice cluster definito nella colonna o nelle colonne, che offre quanto segue:

• Possono essere utilizzate per query frequenti.

• Fornisce un elevato grado di univocità.

  Nota

  Quando si crea un vincolo PRIMARY KEY, viene automaticamente creato un indice univoco sulla colonna o sulle colonne. Per impostazione predefinita, tale indice è cluster. È tuttavia possibile specificare un indice non cluster durante la creazione del vincolo.

• Possono essere utilizzate in query di intervallo.

Se l'indice cluster non viene creato con la UNIQUE proprietà , il motore di database aggiunge automaticamente una colonna uniqueifier a 4 byte alla tabella. Quando è necessario, il motore di database aggiunge automaticamente un valore univoco a una riga per rendere univoca ogni chiave. Questa colonna e i relativi valori vengono usati internamente e non possono essere visualizzati o accessibili dagli utenti.

Architettura dell'indice cluster

Gli indici rowstore sono organizzati come alberi B+. Ogni pagina in un albero di indice B+ è denominata nodo di indice. Il nodo superiore dell'albero B+ è denominato nodo radice. I nodi inferiori dell'indice vengono definiti nodi foglia. I livelli dell'indice compresi tra il nodo radice e i nodi foglia sono noti come livelli intermedi. In un indice cluster il livello foglia include le pagine di dati della tabella sottostante. I nodi di livello radice e intermedio contengono pagine di indice che includono le righe dell'indice. Ogni riga di indice contiene un valore chiave e un puntatore a una pagina di livello intermedio nell'albero B+ o una riga di dati nel livello foglia dell'indice. Le pagine di ogni livello dell'indice sono collegate in un elenco collegato doubly.

Gli indici cluster hanno una riga in sys.partitions, con index_id = 1 per ogni partizione usata dall'indice. Per impostazione predefinita, un indice cluster include una singola partizione. Quando un indice cluster ha più partizioni, ogni partizione ha una struttura ad albero B+ che contiene i dati per tale partizione specifica. Ad esempio, se un indice cluster ha quattro partizioni, sono presenti quattro strutture ad albero B+; uno in ogni partizione.

A seconda dei tipi di dati nell'indice cluster, ogni struttura di indice cluster ha una o più unità di allocazione in cui archiviare e gestire i dati per una partizione specifica. Come minimo, ogni indice cluster ha un'unità di allocazione IN_ROW_DATA per partizione. L'indice cluster include anche un'unità di allocazione LOB_DATA per partizione se contiene colonne LOB (Large Object). Ha anche un'unità di allocazione ROW_OVERFLOW_DATA per partizione se contiene colonne a lunghezza variabile che superano il limite di dimensioni di riga di 8.060 byte.

Le pagine nella catena di dati e le righe incluse nelle pagine vengono ordinate in base al valore della chiave dell'indice cluster. Tutti gli inserimenti vengono eseguiti in corrispondenza del punto in cui il valore di chiave della riga inserita rientra nella sequenza di ordinamento tra righe esistenti.

Nella figura seguente viene illustrata la struttura di un indice cluster in una singola partizione.

Considerazioni sulle query

Prima di creare indici cluster, comprendere come si accede ai dati. Utilizzare ad esempio un indice cluster per query che eseguono le operazioni seguenti:

• Restituisce un intervallo di valori usando operatori come BETWEEN, >>=<, , e .<=

  Quando la riga con il primo valore viene trovata utilizzando l'indice cluster, le righe con i valori indicizzati successivi sono sempre fisicamente adiacenti. Se, ad esempio, tramite una query vengono recuperati i record compresi tra un intervallo di numeri di ordini vendita, la presenza di un indice cluster nella colonna SalesOrderNumber consente di individuare rapidamente la riga contenente il numero iniziale dell'ordine di vendita e quindi di recuperare tutte le righe successive nella tabella fino al raggiungimento dell'ultimo numero dell'ordine vendita.

• Restituiscono set di risultati di grandi dimensioni.

• Usano clausole JOIN, come nel caso delle colonne chiave esterne.

• Usano clausole ORDER BY o GROUP BY.

  Un indice per le colonne specificate nella clausola ORDER BY o GROUP BY può rimuovere la necessità dell'motore di database di ordinare i dati, perché le righe sono già ordinate. e pertanto di ottimizzare le prestazioni delle query.

Considerazioni sulle colonne

È in genere opportuno definire la chiave di indice cluster con il minor numero di colonne possibile. Utilizzare colonne che presentano o più degli attributi seguenti:

• Sono univoche o contengono molti valori distinti.

  Gli ID dipendente consentono ad esempio di identificare in modo univoco i dipendenti. Un indice cluster o un vincolo PRIMARY KEY nella colonna EmployeeID contribuisce a migliorare le prestazioni di query tramite cui viene eseguita la ricerca di informazioni sui dipendenti in base al numero ID del dipendente. In alternativa, è possibile creare un indice cluster in LastName, FirstName, MiddleName perché i record relativi ai dipendenti sono in genere raggruppati e sottoposti a query in questo modo. Inoltre, la combinazione di queste colonne garantisce un elevato livello di differenziazione.

  Suggerimento

  Se non specificato in modo diverso, quando si crea un vincolo PRIMARY KEY, il motore di database crea un indice cluster per supportare tale vincolo. Sebbene un uniqueidentifier possa essere usato per applicare una PRIMARY KEY di univocità, non si tratta di una chiave di clustering efficiente. Se si usa un valore uniqueidentifier come PRIMARY KEY, è consigliabile crearlo come un indice non cluster e usare un'altra colonna, ad esempio IDENTITY, per creare l'indice cluster.

• Sono caratterizzate dall'accesso in modalità sequenziale.

  Tramite un ID prodotto, ad esempio, vengono identificati in modo univoco i prodotti inclusi nella tabella Production.Product del database AdventureWorks2022 . La definizione di un indice cluster in WHERE ProductID BETWEEN 980 and 999produce effetti positivi sulle query in cui è stata specificata una ricerca sequenziale, come nel caso di ProductID, in quanto le righe vengono archiviate in base all'ordinamento definito per tale colonna chiave.

• Definito come IDENTITY.

• Vengono utilizzate di frequente per ordinare i dati recuperati da una tabella.

  Può essere consigliabile raggruppare (fisicamente) la tabella in tale colonna, per risparmiare il costo di un'operazione di ordinamento ogni volta che viene eseguita una query sulla colonna.

Gli indici cluster non sono una scelta ottimale per gli attributi seguenti:

• Colonne che vengono modificate di frequente

  Ciò determina lo spostamento dell'intera riga, perché il motore di database deve mantenere i valori di dati di una riga in ordine fisico. Si tratta di una considerazione importante nel caso di sistemi che elaborano volumi elevati di transazioni in cui i dati sono in genere volatili.

• Chiavi estese

  Le chiavi estese sono costituite da diverse colonne normali o di grandi dimensioni. I valori di chiave dell'indice cluster vengono utilizzati come chiavi di ricerca da tutti gli indici non cluster. Gli indici non cluster definiti nella stessa tabella saranno significativamente più grandi perché le voci di indice non cluster includono la chiave di clustering, nonché le colonne chiave definite per l'indice non cluster.

Linee guida per la progettazione di indici non cluster

Un indice rowstore non cluster basato su disco contiene i valori della chiave di indice e i localizzatori di righe che puntano alla posizione di archiviazione dei dati della tabella. In una vista tabella o indicizzata è possibile creare più indici non cluster. In genere, gli indici non cluster devono essere progettati per migliorare le prestazioni delle query usate di frequente che non sono coperte dall'indice cluster.

Analogamente a quando si utilizza l'indice di un libro, Query Optimizer cerca un valore di dati eseguendo una ricerca nell'indice non cluster per trovare la posizione del valore di dati nella tabella e quindi recupera i dati direttamente da quella posizione. Per questo motivo, gli indici non cluster sono la scelta ottimale per le query di corrispondenza esatta, in quanto l'indice contiene le voci che descrivono la posizione esatta nella tabella dei valori di dati cercati dalle query. Per eseguire, ad esempio, una query sulla tabella HumanResources.Employee per cercare tutti i dipendenti che fanno riferimento a un responsabile specifico, tramite Query Optimizer si potrebbe utilizzare l'indice non cluster IX_Employee_ManagerID, la cui colonna chiave è ManagerID . Query Optimizer consente di trovare in modo rapido tutte le voci di indice che corrispondono all'oggetto ManagerIDspecificato. Ogni voce di indice punta alla pagina e alla riga esatte nella tabella o all'indice cluster in cui è possibile trovare i dati corrispondenti. Dopo avere trovato tutte le voci nell'indice, Query Optimizer può passare direttamente alla pagina e alla riga esatte per recuperare i dati.

Architettura dell'indice non cluster

Gli indici rowstore non cluster basati su disco hanno la stessa struttura ad albero B+ degli indici cluster, ad eccezione delle differenze significative seguenti:

• Le righe di dati della tabella sottostante non vengono ordinate e archiviate in ordine in base alle chiavi non cluster.

• Il livello foglia di un indice non cluster è composto da pagine di indice invece che da pagine di dati. Le pagine di indice a livello foglia di un indice non cluster contengono colonne chiave e colonne incluse.

Gli indicatori di posizione delle righe nelle righe di indice non cluster sono rappresentati da un puntatore a una riga o da una chiave di indice cluster per una riga, come illustrato di seguito:

• Se la tabella è un heap, il che significa che non ha un indice cluster, il localizzatore di righe è un puntatore alla riga. e compilato in base all'ID del file, al numero della pagina e al numero della riga nella pagina. L'intero puntatore è noto come ID di riga.

• Se una tabella include un indice cluster oppure l'indice è riferito a una vista indicizzata, l'indicatore di posizione delle righe corrisponde alla chiave di indice cluster per la riga.

I localizzatori di righe garantiscono anche l'univocità per le righe di indice non cluster. Nella tabella seguente viene descritto come l'motore di database aggiunge localizzatori di righe a indici non cluster:

Tipo di tabella	Tipo di indice non cluster	Localizzatore di righe
Heap
	Non univoco	RID aggiunto alle colonne chiave
	Unica	RID aggiunto alle colonne incluse
Indice cluster univoco
	Non univoco	Chiavi di indice cluster aggiunte alle colonne chiave
	Unica	Chiavi di indice cluster aggiunte alle colonne incluse
Indice cluster non univoco
	Non univoco	Chiavi di indice cluster e uniqueifier (se presenti) aggiunte alle colonne chiave
	Unica	Chiavi di indice cluster e univoco (se presenti) aggiunte alle colonne incluse

Il motore di database non archivierà mai una determinata colonna due volte in un indice non cluster. L'ordine della chiave di indice specificato dall'utente quando crea un indice non cluster viene sempre rispettato: tutte le colonne del localizzatore di righe che devono essere aggiunte alla chiave di un indice non cluster verranno aggiunte alla fine della chiave, seguendo le colonne specificate nella definizione dell'indice. Le colonne del localizzatore di righe basate su chiave dell'indice cluster in un indice non cluster possono essere usate da Query Optimizer, indipendentemente dal fatto che siano state specificate in modo esplicito nella definizione dell'indice.

Gli esempi seguenti illustrano come i localizzatori di righe vengono implementati in indici non cluster:

Indice cluster	Definizione di indice non cluster	Definizione di indice non cluster con localizzatori di righe	Spiegazione
Indice cluster univoco con colonne chiave (A, B, C)	Indice non cluster non univoco con colonne chiave (B, A) e colonne incluse (E,G)	Colonne chiave (B, A, C) e colonne incluse (E, G)	L'indice non cluster non è univoco, pertanto il localizzatore di righe deve essere presente nelle chiavi di indice. Le colonne B e A dal localizzatore di righe sono già presenti, quindi viene aggiunta solo la colonna c . La colonna c viene aggiunta alla fine dell'elenco di colonne chiave.
Indice cluster univoco con colonna chiave (A)	Indice non cluster non univoco con colonne chiave (B, C) e colonna inclusa (A)	Colonne chiave (B, C, A)	L'indice non cluster non è univoco, quindi il localizzatore di righe viene aggiunto alla chiave. La colonna A non è già specificata come colonna chiave, quindi viene aggiunta alla fine dell'elenco di colonne chiave. La colonna A è ora nella chiave, quindi non è necessario archiviarla come colonna inclusa.
Indice cluster univoco con colonna chiave (A,B)	Indice non cluster univoco con colonna chiave (C)	Colonna chiave (C) e colonne incluse (A,B)	L'indice non cluster è univoco, quindi il localizzatore di righe viene aggiunto alle colonne incluse.

Gli indici non cluster hanno una riga in sys.partitions con index_id> 1 per ogni partizione usata dall'indice. Per impostazione predefinita, un indice non cluster include una singola partizione. Quando un indice non cluster ha più partizioni, ogni partizione ha una struttura ad albero B+ che contiene le righe di indice per tale partizione specifica. Ad esempio, se un indice non cluster ha quattro partizioni, sono presenti quattro strutture ad albero B+, con una in ogni partizione.

A seconda dei tipi di dati nell'indice non cluster, ogni struttura di indice non cluster ha una o più unità di allocazione in cui archiviare e gestire i dati per una partizione specifica. Come minimo, ogni indice non cluster ha un'unità di allocazione IN_ROW_DATA per partizione che archivia le pagine dell'albero B+ dell'indice. L'indice non cluster include anche un'unità di allocazione LOB_DATA per partizione se contiene colonne LOB (Large Object). Inoltre, dispone di un'unità di allocazione ROW_OVERFLOW_DATA per partizione se contiene colonne a lunghezza variabile che superano il limite di dimensioni di riga di 8.060 byte.

Nella figura seguente viene illustrata la struttura di un indice non cluster in una singola partizione.

Considerazioni sul database

Quando si progettano indici non cluster, considerare le caratteristiche del database.

• In caso di database o tabelle che richiedono pochi aggiornamenti ma contengono grandi volumi di dati, l'utilizzo di molti indici non cluster può consentire di migliorare le prestazioni di esecuzione delle query. È consigliabile creare indici filtrati per subset ben definiti di dati per ottimizzare le prestazioni relative alle query e ridurre i costi di archiviazione e di manutenzione dell'indice rispetto agli indici non cluster di tabella completa.

  In caso di applicazioni DSS (Decision Support System) e database che contengono principalmente dati di sola lettura è consigliabile l'utilizzo di molti indici non cluster. Query Optimizer include più indici tra cui scegliere per determinare il metodo di accesso più rapido e le caratteristiche di aggiornamento ridotte della manutenzione dell'indice medio del database non impediranno le prestazioni.

• In caso di applicazioni di elaborazione delle transazioni online (OLTP) e database contenenti tabelle aggiornate di frequente, è consigliabile evitare di usare un numero eccessivo di indici. È inoltre necessario che gli indici siano limitati, ovvero con il minor numero possibile di colonne.

  Un numero elevato di indici in una tabella ha ripercussioni sulle prestazioni delle istruzioni INSERT, UPDATE, DELETE e MERGE perché, quando vengono modificati i dati nella tabella, tutti gli indici devono essere modificati di conseguenza.

Considerazioni sulle query

Prima di creare indici non cluster, è necessario comprendere come si accede ai dati. Utilizzare un indice non cluster per le query con gli attributi seguenti:

• Usano clausole JOIN o GROUP BY.

  Creare più indici non cluster in colonne interessate da operazioni di join e raggruppamento e un indice cluster in ogni colonna chiave esterna.

• Query che non restituiscono set di risultati di grandi dimensioni.

  Creare indici filtrati per coprire query che restituiscono un subset ben definito di righe da una tabella di elevate dimensioni.

  Suggerimento

  In genere la clausola WHERE dell'istruzione CREATE INDEX corrisponde alla clausola WHERE di una query con copertura.

• Contengono colonne spesso coinvolte nelle condizioni di ricerca di una query, ad esempio una clausola WHERE, che restituiscono corrispondenze esatte.

  Suggerimento

  Quando si aggiungono nuovi indici, valutare il rapporto tra costi e benefici. Può essere preferibile consolidare le esigenze di query aggiuntive in un indice esistente. Ad esempio, è consigliabile aggiungere uno o due colonne a livello foglia a un indice esistente, se ciò consente la copertura di diverse query critiche, invece di avere un indice di copertura per ogni query critica.

Considerazioni sulle colonne

Considerare le colonne con uno o più degli attributi seguenti:

• Colonne che coprono la query.

  È possibile ottenere un miglioramento delle prestazioni quando l'indice contiene tutte le colonne nella query. Query Optimizer può individuare tutti i valori di colonna all'interno dell'indice; non è possibile accedere ai dati dell'indice cluster o tabella, con un minor numero di operazioni di I/O su disco. Usare un indice con colonne incluse per aggiungere colonne di copertura invece di creare una chiave di indice esteso.

  Se la tabella ha un indice cluster, la colonna o le colonne definite nell'indice cluster vengono aggiunte automaticamente a ogni indice non cluster nella tabella. In questo modo, è possibile produrre una query coperta senza specificare le colonne dell'indice cluster nella definizione dell'indice non cluster. Ad esempio, se una tabella ha un indice cluster nella colonna C, un indice non cluster non univoco nelle colonne B e A ha come valori chiave le colonne B, Ae C. Per altre informazioni, vedere Architettura dell'indice non cluster.

• Colonne che includono un numero elevato di valori distinct, ad esempio una combinazione di cognome e nome, se per le altre colonne viene utilizzato un indice cluster.

  Se sono presenti pochissimi valori distinti, ad esempio solo 1 e 0, la maggior parte delle query non userà l'indice perché un'analisi di tabella è in genere più efficiente. Per questo tipo di dati, è consigliabile creare un indice filtrato su un valore distinto che si verifica solo in poche righe. Se la maggior parte dei valori è impostata su 0, Query Optimizer potrebbe utilizzare un indice filtrato per le righe di dati che contengono il valore 1.

Usare le colonne incluse per estendere gli indici non cluster

È possibile estendere le funzionalità degli indici non cluster aggiungendo colonne non chiave a livello foglia dell'indice non cluster. Con l'inclusione di colonne non chiave è possibile creare indici non cluster in grado di coprire più query. Ciò è possibile perché le colonne non chiave presentano i vantaggi seguenti:

• Possono essere tipi di dati che non sono consentiti come colonne chiave indice.

• Non vengono considerati dal motore di database quando si calcola il numero di colonne chiave di indice o le dimensioni della chiave di indice.

Un indice con colonne non chiave incluse può aumentare significativamente le prestazioni delle query quando tutte le colonne della query sono incluse nell'indice come colonne chiave o non chiave. Si ottengono miglioramenti delle prestazioni perché Query Optimizer può individuare tutti i valori di colonna all'interno dell'indice; non è possibile accedere ai dati dell'indice cluster o tabella, con un minor numero di operazioni di I/O su disco.

Nota

Quando un indice contiene tutte le colonne a cui fa riferimento la query, viene generalmente indicato come indice di copertura.

Mentre le colonne chiave sono archiviate a tutti i livelli dell'indice, le colonne non chiave sono archiviate solo al livello foglia.

Usare le colonne incluse per evitare limiti di dimensioni

È possibile includere colonne non chiave in un indice non cluster per evitare il superamento delle limitazioni di dimensione correnti degli indici (numero massimo di colonne chiave pari a 16 e dimensione massima delle chiavi di indice pari a 900 byte). Il motore di database non considera le colonne non chiave durante il calcolo del numero di colonne chiave di indice o delle dimensioni della chiave di indice.

Si supponga, ad esempio, che si desideri indicizzare le colonne seguenti nella tabella Document :

Title NVARCHAR(50)
Revision NCHAR(5)
FileName NVARCHAR(400)

Poiché i tipi di dati nchar e nvarchar richiedono 2 byte per ogni carattere, un indice che contiene queste tre colonne supererebbe la limitazione delle dimensioni di 900 byte di 10 byte (455 * 2). Utilizzando la clausola INCLUDE dell'istruzione CREATE INDEX , è possibile definire la chiave dell'indice come (Title, Revision) e FileName come colonna non chiave. In questo modo, le dimensioni della chiave di indice sarebbero di 110 byte (55 * 2) e l'indice conterrà comunque tutte le colonne necessarie. Nell'istruzione seguente viene creato un simile indice.

CREATE INDEX IX_Document_Title
ON Production.Document (Title, Revision)
INCLUDE (FileName);
GO

Se si seguono gli esempi di codice, è possibile eliminare questo indice usando questa istruzione Transact-SQL:

DROP INDEX IX_Document_Title
ON Production.Document;
GO

Indice con linee guida per le colonne incluse

Quando si progettano indici non cluster con colonne incluse è opportuno considerare le indicazioni generali seguenti:

• Le colonne non chiave vengono definite nella clausola INCLUDE dell'istruzione CREATE INDEX.

• Le colonne non chiave possono essere definite solo su indici non cluster su tabelle o viste indicizzate.

• È possibile usare qualsiasi tipo di dati, ad eccezione di text, ntexte image.

• Come colonne incluse è possibile utilizzare colonne calcolate che sono deterministiche, sia precise che imprecise. Per altre informazioni, vedere Indici per le colonne calcolate.

• In maniera simile alle colonne chiave, le colonne calcolate derivate dai tipi di dati image, ntexte text possono essere colonne non chiave (incluse) purché il tipo di dati della colonna calcolata sia consentito come colonna non chiave dell'indice.

• I nomi delle colonne non possono essere specificati sia nell'elenco INCLUDE che nell'elenco delle colonne chiave.

• I nomi delle colonne non possono essere ripetuti nell'elenco INCLUDE.

Linee guida per le dimensioni delle colonne

• È necessario definire almeno una colonna chiave. Il numero massimo di colonne non chiave è 1023. Questo limite è rappresentato dal numero massimo di colonne nelle tabelle meno 1.

• Le colonne chiave non indice, escluse le colonne non chiave dell'indice, devono soddisfare le limitazioni di dimensione correnti degli indici (numero massimo di colonne chiave pari a 16 e dimensione totale delle chiavi di indice pari a 900 byte).

• La dimensione totale delle colonne non chiave è limitata solo dalle dimensioni delle colonne specificate nella clausola INCLUDE; ad esempio, le colonne varchar(max) sono limitate a 2 GB.

Linee guida per la modifica delle colonne

Quando si modifica una colonna di tabella che è stata definita come colonna inclusa, vengono applicate le limitazioni seguenti:

• Le colonne non chiave non possono essere eliminate dalla tabella a meno che l'indice non venga eliminato per primo.

• Non è possibile modificare le colonne non chiave, ad eccezione di quanto segue:

  • Modifica del supporto di valori NULL della colonna da NOT NULL a NULL.

  • Aumento della lunghezza di colonne varchar, nvarcharo varbinary .

    Nota

    Tali restrizioni sulla modifica delle colonne sono valide anche per le colonne chiave indice.

Suggerimenti per la progettazione

Progettare nuovamente gli indici non cluster con chiavi di indice dalle dimensioni elevate in modo da utilizzare come colonne chiave solo le colonne utilizzate per le ricerche. Modificare in colonne non chiave incluse tutte le altre colonne che coprono la query. In questo modo, sono disponibili tutte le colonne necessarie per coprire la query, ma la chiave di indice stessa è piccola ed efficiente.

Si supponga, ad esempio, che si desideri progettare un indice per coprire la query seguente.

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' AND N'99999';
GO

Per coprire la query è necessario definire tutte le colonne nell'indice. Sebbene sia possibile definire tutte le colonne come colonne chiave, la dimensione delle chiavi sarebbe di 334 byte. Dal momento che la sola colonna effettivamente utilizzata come criterio di ricerca è la colonna PostalCode , la quale ha una lunghezza pari a 30 byte, è possibile migliorare la progettazione degli indici definendo PostalCode come colonna chiave e includendo tutte le altre colonne come colonne non chiave.

Nell'istruzione seguente viene creato un indice con colonne incluse per coprire la query.

CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

Per verificare che l'indice copre la query, creare l'indice e quindi visualizzare il piano di esecuzione stimato.

Se il piano di esecuzione mostra solo un operatore edizione Standard LECT e un operatore Index Seek per l'indiceIX_Address_PostalCode, la query è stata "coperta" dall'indice.

È possibile eliminare l'indice con l'istruzione seguente:

DROP INDEX IX_Address_PostalCode
ON Person.Address;
GO

Considerazioni sulle prestazioni

Evitare di aggiungere colonne non necessarie. L'aggiunta di troppe colonne indice, chiave o non chiave, può avere le implicazioni sulle prestazioni seguenti:

• Meno righe di indice si adattano a una pagina. Ciò potrebbe causare più operazioni di I/O e ridurre l'efficienza della cache.

• Per archiviare l'indice è necessario più spazio su disco. In particolare, l'aggiunta di tipi di dati varchar(max), nvarchar(max), varbinary(max)o xml come colonne non chiave dell'indice potrebbe aumentare significativamente l'utilizzo di spazio su disco. Ciò accade perché i valori delle colonne vengono copiati nel livello foglia dell'indice, risiedendo in tal modo sia nell'indice che nella tabella di base.

• La manutenzione degli indici può aumentare il tempo necessario per eseguire modifiche, inserimenti, aggiornamenti o eliminazioni nella tabella sottostante o nella vista indicizzata.

È necessario determinare se i miglioramenti delle prestazioni delle query superano l'effetto sulle prestazioni durante la modifica dei dati e in requisiti di spazio su disco aggiuntivi.

Linee guida per la progettazione di indici univoci

Un indice univoco consente di garantire che nella chiave dell'indice non siano contenuti valori duplicati e che pertanto ogni riga della tabella sia univoca. È consigliabile specificare un indice univoco solo se l'unicità è una caratteristica dei dati stessi. Per verificare, ad esempio, che i valori della colonna NationalIDNumber nella tabella HumanResources.Employee siano univoci quando la chiave primaria è EmployeeID, creare un vincolo UNIQUE nella colonna NationalIDNumber . Se l'utente tenta di immettere lo stesso valore in tale colonna per più dipendenti, viene visualizzato un messaggio di errore e il valore duplicato non viene immesso.

Per gli indici univoci a più colonne, l'indice garantisce che ogni combinazione di valori nella chiave dell'indice sia univoca. Ad esempio, se si crea un indice univoco basato su una combinazione delle colonne LastName, FirstNamee MiddleName , non è possibile che nella tabella siano incluse due righe in cui è presente la stessa combinazione di valori per queste colonne.

Sia gli indici cluster che non cluster possono essere univoci. Se i dati nella colonna sono univoci, è possibile creare sia un indice cluster univoco che più indici non cluster univoci nella stessa tabella.

Gli indici univoci offrono i vantaggi seguenti:

• Garantiscono l'integrità dei dati delle colonne definite.

• Forniscono informazioni aggiuntive utili per Query Optimizer.

Se si crea un vincolo PRIMARY KEY o UNIQUE, viene creato automaticamente un indice univoco basato sulle colonne specificate. Non esistono differenze significative tra la creazione determinata da un vincolo UNIQUE e la creazione di un indice univoco indipendente da un vincolo. La convalida dei dati viene eseguita nello stesso modo e Query Optimizer non distingue tra un indice univoco creato da un vincolo o creato manualmente. Se l'obiettivo è l'integrità dei dati, è tuttavia consigliabile creare un vincolo UNIQUE o PRIMARY KEY sulla colonna, In questo modo, l'obiettivo dell'indice sarà chiaro.

Considerazioni

• Non è possibile creare un indice univoco, un vincolo UNIQUE o PRIMARY KEY se nei dati esistono valori di chiave duplicati.

• Se i dati sono univoci e si desidera imporre l'unicità, la creazione di un indice univoco anziché di un indice non univoco per la stessa combinazione di colonne fornirà a Query Optimizer una maggiore quantità di informazioni che consentiranno di creare piani di esecuzione più efficienti. In questo caso è consigliabile creare un indice univoco, preferibilmente tramite un vincolo UNIQUE.

• In un indice non cluster univoco possono essere contenute colonne non chiave. Per altre informazioni, vedere Indice con colonne incluse.

Linee guida per la progettazione di indici filtrati

Un indice filtrato è un indice non cluster ottimizzato, particolarmente indicato per coprire query che selezionano dati da un subset ben definito. Un indice di questo tipo utilizza un predicato del filtro per indicizzare una parte di righe nella tabella. Se confrontato con indici di tabella completa, un indice filtrato progettato correttamente consente di migliorare le prestazioni di esecuzione delle query e di ridurre i costi di manutenzione e di archiviazione dell'indice stesso.

Rispetto agli indici di tabella completa, gli indici filtrati consentono di ottenere i vantaggi seguenti:

• Prestazioni di esecuzione delle query e qualità del piano migliorate

  Un indice filtrato progettato correttamente migliora le prestazioni di esecuzione delle query e la qualità del piano di esecuzione poiché è caratterizzato da dimensioni minori rispetto a un indice non cluster di tabella completa e dispone di statistiche filtrate. Queste ultime sono più accurate delle statistiche di tabella completa poiché coprono solo le righe nell'indice filtrato.

• Costi di manutenzione dell'indice ridotti

  La manutenzione di un indice viene eseguita solo quando le istruzioni DML (Data Manipulation Language) influiscono sui dati relativi all'indice. Un indice filtrato consente di ridurre i costi di manutenzione rispetto a un indice non cluster di tabella completa poiché è caratterizzato da dimensioni minori e viene gestito solo se i dati relativi sono interessati. È possibile disporre di un numero elevato di indici filtrati, soprattutto quando questi ultimi contengono dati interessati raramente. In modo analogo, se un indice filtrato contiene dati interessati di frequente, la dimensione minore dell'indice consente di ridurre il costo di aggiornamento delle statistiche.

• Costi di archiviazione dell'indice ridotti

  La creazione di un indice filtrato può ridurre l'archiviazione su disco per gli indici non cluster quando non è necessario un indice di tabella completa. È possibile sostituire un indice non cluster di tabella completa con più indici filtrati senza aumentare in modo significativo i requisiti di archiviazione.

Gli indici filtrati risultano particolarmente utili quando le colonne contengono subset ben definiti di dati cui le query fanno riferimento nelle istruzioni SELECT. Alcuni esempi:

• Colonne di tipo sparse che contengono solo un numero limitato di valori non NULL.

• Colonne eterogenee che contengono categorie di dati.

• Colonne che contengono intervalli di valori diversi, ad esempio quantità di denaro, ore e date.

• Partizioni di tabelle definite da logica di confronto semplice per i valori di colonna.

I costi di manutenzione ridotti ottenuti dall'utilizzo di indici filtrati sono più apprezzabili quando il numero di righe dell'indice non è elevato rispetto a quello di un indice di tabella completa. Se l'indice filtrato include la maggior parte delle righe della tabella, è possibile che i costi di manutenzione siano maggiori rispetto a quelli relativi a un indice di tabella completa. In questo caso è opportuno utilizzare un indice di tabella completa anziché un indice filtrato.

Gli indici filtrati sono definiti in una tabella e supportano solo operatori di confronto semplici. Se è necessaria un'espressione di filtro in cui viene fatto riferimento a più tabelle o in cui è presente della logica complessa, è necessario creare una vista.

Considerazioni relative alla progettazione

Per progettare indici filtrati efficaci, è importante comprendere quali sono le query utilizzate dall'applicazione e il modo in cui sono correlate ai subset dei dati. Alcuni esempi di dati che dispongono di subset ben definiti sono costituiti da colonne con la maggior parte di valori NULL, colonne con categorie eterogenee di valori e colonne con intervalli di valori distinti. Nelle considerazioni sulla progettazione seguenti viene indicata una varietà di scenari in cui un indice filtrato può fornire vantaggi rispetto agli indici di tabella completa.

Suggerimento

La definizione degli indici columnstore non cluster 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 cluster solo sui dati usati meno di frequente del carico di lavoro operativo.

Indici filtrati per subset di dati

Quando una colonna contiene solo alcuni valori rilevanti per le query, è possibile creare un indice filtrato nel subset di valori. Ad esempio, quando la maggior parte dei valori di una colonna è costituita da valori NULL e la query esegue la selezione solo dai valori non NULL, è possibile creare un indice filtrato per le righe di dati non NULL. L'indice risultante è minore e i costi sono inferiori a quelli di un indice non cluster di tabella completo definito nelle stesse colonne chiave.

Ad esempio, il database di esempio AdventureWorks ha una Production.BillOfMaterials tabella con 2679 righe. La colonna EndDate dispone solo di 199 righe che contengono un valore non NULL, mentre le altre 2480 righe contengono valori NULL. L'indice filtrato seguente coprirà query che restituiscono le colonne definite nell'indice e che selezionano solo righe con un valore non NULL per EndDate.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;
GO

L'indice filtrato FIBillOfMaterialsWithEndDate è valido per la query seguente. Visualizzare il piano di esecuzione stimato per determinare se Query Optimizer ha usato l'indice filtrato.

SELECT ProductAssemblyID, ComponentID, StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
    AND ComponentID = 5
    AND StartDate > '20080101';
GO

Per altre informazioni sulla creazione di indici filtrati e sulla definizione dell'espressione del predicato dell'indice filtrato, vedere Creare indici filtrati.

Indici filtrati per dati eterogenei

Se in una tabella sono presenti righe di dati eterogenei, è possibile creare un indice filtrato per una o più categorie di dati.

Ogni prodotto elencato nella tabella Production.Product , ad esempio, è assegnato a un ProductSubcategoryID, associato a sua volta alle categorie di prodotti Bikes, Components, Clothing o Accessories. Queste categorie sono eterogenee perché i relativi valori di colonna nella Production.Product tabella non sono strettamente correlati. Ad esempio, le colonne Color, ReorderPoint, ListPrice, Weight, Classe Style dispongono di caratteristiche univoche per ogni categoria di prodotti. Si supponga che siano presenti frequenti query per gli accessori, con sottocategorie comprese tra 27 e 36 inclusi. è possibile migliorarne le prestazioni creando un indice filtrato nella sottocategoria degli accessori come mostrato nell'esempio riportato di seguito.

CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (ProductSubcategoryID, ListPrice)
        Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

L'indice FIProductAccessories filtrato copre la query seguente perché i risultati della query sono contenuti nell'indice e il piano di query non include una ricerca di tabella di base. Ad esempio, l'espressione del predicato di query ProductSubcategoryID = 33 è un subset del predicato dell'indice filtrato ProductSubcategoryID >= 27 e ProductSubcategoryID <= 36, le colonne ProductSubcategoryID e ListPrice nel predicato di query sono entrambe colonne chiave nell'indice e il nome è archiviato al livello foglia dell'indice come colonna inclusa.

SELECT Name, ProductSubcategoryID, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00;
GO

Colonne chiave

È consigliabile inserire un numero ridotto di colonne chiave o incluse in una definizione di indice filtrato e incorporare solo le colonne necessarie affinché Query Optimizer scelga l'indice filtrato per il piano di esecuzione della query. Query Optimizer può scegliere un indice filtrato per la query indipendentemente dal fatto che si tratti o meno della query. tuttavia è più probabile che venga scelto un indice filtrato che copre la query.

In alcuni casi, un indice filtrato copre la query senza includere le colonne nell'espressione che lo definisce come colonne chiave o incluse nella definizione dell'indice stesso. Le linee guida seguenti indicano quando una colonna nell'espressione che definisce l'indice filtrato deve essere una colonna chiave o inclusa nella definizione dell'indice filtrato stesso. Gli esempi si riferiscono all'indice filtrato FIBillOfMaterialsWithEndDate creato in precedenza.

Una colonna nell'espressione di indice filtrata non deve essere una colonna chiave o inclusa nella definizione dell'indice filtrato se l'espressione di indice filtrata equivale al predicato di query e la query non restituisce la colonna nell'espressione di indice filtrata con i risultati della query. Ad esempio, FIBillOfMaterialsWithEndDate viene illustrata la query seguente perché il predicato della query è equivalente all'espressione di filtro e EndDate non viene restituito con i risultati della query. FIBillOfMaterialsWithEndDate non è necessaria EndDate come chiave o colonna inclusa nella definizione dell'indice filtrato.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

Una colonna nell'espressione di indice filtrata deve essere una chiave o una colonna inclusa nella definizione dell'indice filtrato se il predicato di query usa la colonna in un confronto che non equivale all'espressione di indice filtrata. L'indice FIBillOfMaterialsWithEndDate , ad esempio, è valido per la query seguente perché seleziona un subset di righe dall'indice filtrato. Tuttavia, non copre la query seguente perché EndDate viene usata nel confronto EndDate > '20040101', che non è equivalente all'espressione di indice filtrata. Query Processor non può eseguire questa query senza cercare i valori di EndDate. Di conseguenza, EndDate deve essere una colonna chiave o inclusa nella definizione dell'indice filtrato.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate > '20040101';

Una colonna nell'espressione che definisce l'indice filtrato deve essere una colonna chiave o inclusa nella definizione dell'indice se è presente nel set di risultati della query. Ad esempio, FIBillOfMaterialsWithEndDate non copre la query seguente perché restituisce la EndDate colonna nei risultati della query. Di conseguenza, EndDate deve essere una colonna chiave o inclusa nella definizione dell'indice filtrato.

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

La chiave di indice cluster della tabella non deve essere una chiave o una colonna inclusa nella definizione dell'indice filtrato. poiché viene inclusa automaticamente in tutti gli indici non cluster, inclusi quelli filtrati.

Per eliminare gli FIBillOfMaterialsWithEndDate indici e FIProductAccessories , eseguire le istruzioni seguenti:

DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
DROP INDEX FIProductAccessories
    ON Production.Product;
GO

Operatori di conversione dei dati nel predicato del filtro

Se l'operatore di confronto specificato nell'espressione di indice filtrato dell'indice filtrato genera una conversione implicita o esplicita dei dati, si verifica un errore se la conversione si verifica sul lato sinistro di un operatore di confronto. Una soluzione consiste nello scrivere l'espressione che definisce l'indice filtrato con l'operatore di conversione dei dati (CAST o CONVERT) sul lato destro dell'operatore di confronto.

Nell'esempio seguente viene creata una tabella con tipi di dati diversi.

CREATE TABLE dbo.TestTable (a int, b varbinary(4));
GO

Nella definizione dell'indice filtrato seguente la colonna b viene convertita implicitamente a un tipo di dati integer per eseguire il confronto con la costante 1. Viene generato il messaggio di errore 10611 perché la conversione si verifica sul lato sinistro dell'operatore nel predicato filtrato.

CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = 1;
GO

La soluzione consiste nel convertire la costante sul lato destro in modo che sia dello stesso tipo di colonna b, come illustrato nell'esempio seguente:

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = CONVERT(Varbinary(4), 1);
GO

Lo spostamento della conversione dei dati dal lato sinistro a quello destro di un operatore di confronto potrebbe modificare il significato della conversione. Nell'esempio precedente, quando l'operatore CONVERT è stato aggiunto al lato destro, il confronto è stato modificato da un confronto di un tipo integer in un confronto di tipo varbinary .

Eliminare gli oggetti creati in questo esempio eseguendo l'istruzione seguente:

DROP TABLE TestTable;
GO

Architettura degli indici columnstore

L'indice columnstore è una tecnologia per l'archiviazione, il recupero e la gestione dei dati tramite un formato di dati in colonna, detto columnstore. Per altre informazioni, vedere Cenni preliminari sugli indici columnstore.

Per informazioni sulla versione e per scoprire le novità, vedere Indici Columnstore - Novità.

Conoscere queste nozioni di base semplifica la comprensione di altri articoli columnstore che spiegano come usarli in modo efficace.

Per l'archiviazione dei dati viene usata la compressione di columnstore e rowstore

Quando si parla di indici columnstore, si usano i termini rowstore e columnstore per enfatizzare il formato per l'archiviazione dei dati. Gli indici columnstore usano entrambi i tipi di archiviazione.

• Un indice columnstore è costituito da dati organizzati logicamente in una tabella con righe e colonne e archiviati fisicamente in un formato di dati a colonne.

  Un indice columnstore archivia fisicamente la maggior parte dei dati nel formato columnstore. Nel formato columnstore i dati vengono compressi e decompressi come colonne. Non è necessario decomprimere altri valori in ogni riga che non sono richiesti dalla query. Ciò consente di analizzare in modo veloce un'intera colonna di una tabella di grandi dimensioni.

• Un indice rowstore è costituito da dati organizzati logicamente in una tabella con righe e colonne e archiviati fisicamente in un formato di dati a righe. Questo è stato il modo tradizionale per archiviare dati di tabella relazionali, ad esempio un heap o un indice albero B+ cluster.

  Un indice columnstore archivia inoltre fisicamente alcune righe in un formato rowstore, denominato archivio differenziale. Il deltastore, detto anche rowgroup differenziale, è una posizione di attesa per le righe che sono troppo poche in numero per qualificarsi per la compressione nel columnstore. Ogni rowgroup differenziale viene implementato come indice albero B+ cluster.

• Il deltastore è una posizione di archiviazione per le righe in numero troppo limitato per essere compresse nel columnstore. Il deltastore archivia le righe in formato rowstore.

Per altre informazioni sui termini e sui concetti columnstore, vedere Indici columnstore: Panoramica.

Le operazioni vengono eseguite sui rowgroup e sui segmenti di colonna

L'indice columnstore raggruppa le righe in unità gestibili. Ognuna di queste unità viene chiamata rowgroup. Per ottenere prestazioni ottimali, il numero di righe nel rowgroup deve essere sufficientemente grande da migliorare il tasso di compressione e sufficientemente ridotto da poter trarre vantaggio dall'esecuzione delle operazioni in memoria.

Ad esempio, l'indice columnstore esegue queste operazioni sui rowgroup:

• Compressione dei rowgroup nel columnstore. La compressione viene eseguita su ogni segmento di colonna all'interno di un rowgroup.
• Unisce i rowgrup durante un'operazione ALTER INDEX ... REORGANIZE, che include la rimozione dei dati eliminati.
• Crea nuovi rowgroup durante un'operazione ALTER INDEX ... REBUILD.
• Restituzione di informazioni sull'integrità e la frammentazione dei rowgroup nelle viste a gestione dinamica (DMV).

L'archivio differenziale è costituito da uno o più rowgroup detti rowgroup differenziali. Ogni rowgroup differenziale è un indice albero B+ cluster che archivia piccoli caricamenti bulk e inserimenti fino a quando il rowgroup non contiene 1.048.576 righe, in cui un processo denominato tuple-mover comprime automaticamente il rowgroup chiuso nel columnstore.

Per altre informazioni sugli stati dei rowgroup, vedere sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Suggerimento

La presenza di un numero eccessivo di rowgroup di piccole dimensioni riduce la qualità dell'indice columnstore. Un'operazione di riorganizzazione unisce i rowgroup più piccoli in base a un criterio di soglia interna, che determina come rimuovere le righe eliminate e combinare i rowgroup compressi. Dopo un'operazione merge, la qualità dell'indice risulterà migliore.

A partire da SQL Server 2019 (15.x), il tuple-mover è aiutato da un'attività di unione in background che comprime automaticamente rowgroup differenziali OPEN più piccoli esistenti per un certo periodo di tempo, come determinato da una soglia interna o unisce rowgroup COMPRES edizione Standard D da cui è stato eliminato un numero elevato di righe.

Ogni colonna dispone di alcuni dei relativi valori in ogni rowgroup. Questi valori sono denominati segmenti di colonna. Ogni rowgroup contiene un segmento di colonna per ogni colonna della tabella. Ogni colonna ha un segmento di colonna in ogni rowgroup.

Quando l'indice columnstore comprime un rowgroup, ogni segmento di colonna viene compresso separatamente. Per decomprimere un'intera colonna, l'indice columnstore deve semplicemente decomprimere un segmento di colonna da ogni rowgroup.

Per altre informazioni sui termini e sui concetti columnstore, vedere Indici columnstore: Panoramica.

I caricamenti e gli inserimenti di dimensioni contenute vengono indirizzati all'archivio differenziale

Un indice columnstore migliora le prestazioni e la compressione del columnstore comprimendo almeno 102.400 righe alla volta nell'indice columnstore. Per eseguire la compressione bulk delle righe, l'indice columnstore accumula caricamenti e inserimenti di dimensioni contenute nell'archivio differenziale. Le operazioni deltastore sono gestite in modo automatico. Per tornare ai risultati della query corretti, l'indice columnstore cluster combina i risultati della query da columnstore e deltastore.

Le righe vengono indirizzate all'archivio differenziale nei casi seguenti:

• Inserimento con l'istruzione INSERT INTO ... VALUES.
• Alla fine di un caricamento bulk e quando sono meno di 102.400.
• Quando vengono aggiornate. Ogni aggiornamento viene implementato come un'eliminazione e un inserimento.

L'archivio differenziale archivia anche un elenco di ID per le righe eliminate contrassegnate come eliminate ma non ancora eliminate fisicamente dal columnstore.

Per altre informazioni sui termini e sui concetti columnstore, vedere Indici columnstore: Panoramica.

Quando i rowgroup differenziali sono completi, vengono compressi nel columnstore

Gli indici columnstore cluster acquisiscono fino a 1.048.576 righe in ogni rowgroup differenziale prima di comprimere il rowgroup nel columnstore. migliorando così la compressione dell'indice columnstore. Quando un rowgroup delta raggiunge il numero massimo di righe, passa dallo stato OPEN allo stato CLOSED. Un processo in background denominato motore di tuple controlla la presenza di rowgroup chiusi. Se il processo trova un rowgroup chiuso, lo comprime e lo archivia nel columnstore.

Quando un rowgroup delta è stato compresso, il rowgroup delta esistente passa allo stato TOMBSTONE (rimozione definitiva) per essere poi rimosso dal processo tuple-mover quando non riceve nessun riferimento, mentre il nuovo rowgroup compresso viene contrassegnato come COMPRESSED (compresso).

Per altre informazioni sugli stati dei rowgroup, vedere sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

È possibile forzare l'inserimento dei rowgroup differenziali nel columnstore usando ALTER INDEX per ricostruire o riorganizzare l'indice. Se si verifica un utilizzo elevato della memoria durante la compressione, l'indice columnstore potrebbe ridurre il numero di righe nel rowgroup compresso.

Per altre informazioni sui termini e sui concetti columnstore, vedere Indici columnstore: Panoramica.

Ogni partizione di tabella ha i propri rowgroup e rowgroup differenziali

Il concetto di partizionamento è lo stesso per un indice cluster, un indice heap e un indice columnstore. Il partizionamento di una tabella suddivide la tabella in piccoli gruppi di righe in base a un intervallo di valori di colonna e viene spesso usato per la gestione dei dati. Ad esempio, è possibile creare una partizione per ogni anno dei dati e quindi usare il cambio della partizione per archiviare i dati in una soluzione di archiviazione meno costosa. Il cambio della partizione si applica agli indici columnstore e rende più semplice spostare una partizione di dati in un'altra posizione.

I rowgroup sono sempre definiti all'interno di una partizione di tabella. Quando un indice columnstore viene partizionato, ogni partizione ha rowgroup compressi e rowgroup differenziali propri.

Suggerimento

Se è necessario rimuovere i dati dal columnstore, è consigliabile usare il partizionamento delle tabelle. La disattivazione e il troncamento delle partizioni non più necessarie è una strategia efficace per eliminare i dati senza generare la frammentazione introdotta con rowgroup più piccoli.

Ogni partizione può avere più rowgroup differenziali

Ogni partizione può avere più di un rowgroup differenziale. Quando l'indice columnstore deve aggiungere dati a un rowgroup differenziale e il rowgroup differenziale è bloccato, l'indice columnstore tenterà di ottenere un blocco su un rowgroup differenziale diverso. In assenza di rowgroup differenziali disponibili, l'indice columnstore creerà un nuovo rowgroup differenziale. Ad esempio, una tabella con 10 partizioni potrebbe avere facilmente 20 o più rowgroup differenziali.

Combinare indici columnstore e rowstore nella stessa tabella

Un indice non cluster contiene una copia totale o parziale di tutte le righe e colonne della tabella sottostante. L'indice è definito sotto forma di una o più colonne della tabella e ha una condizione facoltativa che consente di filtrare le righe.

È possibile creare un indice columnstore non cluster aggiornabile in una tabella rowstore. L'indice columnstore archivia una copia dei dati, pertanto è necessario spazio di archiviazione aggiuntivo. Tuttavia, i dati nell'indice columnstore verranno compressi fino a ottenere dimensioni minori rispetto a quanto richiesto per la tabella del rowstore. In questo modo è possibile eseguire allo stesso tempo analisi sull'indice columnstore e transazioni sull'indice rowstore. Il columnstore viene aggiornato quando i dati vengono modificati nella tabella rowstore, quindi entrambi gli indici funzionano con gli stessi dati.

È possibile avere uno o più indici rowstore non cluster in un indice columnstore. Ciò consente di eseguire ricerche efficienti all'interno delle tabelle del columnstore sottostante. Sono disponibili anche altre opzioni. È possibile, ad esempio, applicare un vincolo di chiave primaria tramite un vincolo UNIQUE nella tabella rowstore. Poiché non è possibile inserire un valore non univoco nella tabella rowstore, il motore di database non può inserire il valore nel columnstore.

Considerazioni sulle prestazioni

• La definizione degli indici columnstore non cluster 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 cluster solo sui dati usati meno di frequente del carico di lavoro operativo.

• Una tabella in memoria può avere un solo indice columnstore. È possibile crearlo quando la tabella viene creata o aggiungerla in un secondo momento con ALTER TABLE (Transact-SQL). Prima di SQL Server 2016 (13.x), solo una tabella basata su disco potrebbe avere un indice columnstore.

Per altre informazioni, vedere Indici columnstore - Prestazioni delle query.

Indicazioni per la progettazione

• Una tabella rowstore può avere un solo indice columnstore non cluster aggiornabile. Prima di SQL Server 2014 (12.x), l'indice columnstore non cluster era di sola lettura.

Per altre informazioni, vedere Indici columnstore - Linee guida per la progettazione.

Linee guida per la progettazione dell'indice hash

Tutte le tabelle ottimizzate per la memoria devono contenere almeno un indice in quanto gli indici consentono l'interconnessione delle righe. In una tabella con ottimizzazione per la memoria, ogni indice è anche ottimizzato per la memoria. Gli indici hash sono uno dei tipi di indice possibili in una tabella ottimizzata per la memoria. Per altre informazioni, vedere Indici per le tabelle ottimizzate per la memoria.

Si applica a: SQL Server, database SQL di Azure e Istanza gestita di SQL di Azure.

Architettura dell'indice hash

Un indice hash è costituito da una matrice di puntatori e ogni elemento della matrice viene definito bucket di hash.

• Ogni bucket è costituito da 8 byte, che vengono usati per archiviare l'indirizzo di memoria di un elenco di collegamenti delle voci della chiave.
• Ogni voce rappresenta un valore per una chiave di indice, oltre all'indirizzo della riga corrispondente nella tabella ottimizzata per la memoria sottostante.
• Ogni voce punta alla voce successiva in un elenco di collegamenti di voci, tutte concatenate per il bucket corrente.

Il numero di bucket deve essere specificato in fase di definizione dell'indice:

• Minore è il rapporto tra bucket e righe di tabella o valori distinct, maggiore sarà la lunghezza dell'elenco di collegamenti bucket medio.
• Gli elenchi di collegamenti brevi risultano più veloci rispetto agli elenchi di collegamenti lunghi.
• Il numero massimo di bucket negli indici hash è 1.073.741.824.

Suggerimento

Per determinare il diritto BUCKET_COUNT per i dati, vedere Configurare il numero di bucket dell'indice hash.

La funzione hash si applica alle colonne chiave dell'indice e il risultato della funzione determina il bucket in cui rientra la chiave. Ogni bucket ha un puntatore per righe con valori di chiave hash. Per questi valori è eseguito il mapping al bucket.

La funzione di hashing utilizzata per gli indici hash presenta le caratteristiche seguenti:

• Il motore di database ha una funzione hash usata per tutti gli indici hash.
• La funzione hash è deterministica. Nell'indice hash viene sempre eseguito il mapping del valore di chiave di input allo stesso bucket.
• È possibile che venga eseguito il mapping di più chiavi di indice allo stesso bucket di hash.
• La funzione hash viene bilanciata, pertanto la distribuzione dei valori di chiave di indice in bucket di hash segue in genere una distribuzione di probabilità di Poisson o a campana e non una distribuzione lineare piana.
• La distribuzione di Poisson non è una distribuzione uniforme. I valori delle chiavi di indice non vengono distribuiti uniformemente nei bucket hash.
• Se viene eseguito il mapping di due chiavi dell'indice allo stesso bucket di hash, si verifica una collisione hash. Un numero elevato di collisioni hash può influire negativamente sulle prestazioni nelle operazioni di lettura. Un obiettivo realistico è per il 30% dei bucket che contengono due valori chiave diversi.

Nell'immagine seguente è riepilogata l'interazione tra l'indice hash e i bucket.

Configurare il numero di bucket dell'indice hash

Il numero di bucket dell'indice hash viene specificato al momento della creazione dell'indice e può essere modificato tramite la sintassi ALTER TABLE...ALTER INDEX REBUILD.

Nella maggior parte dei casi, il numero di bucket dovrebbe essere in teoria impostato su un valore compreso tra una e due volte il numero di valori distinct della chiave di indice.
Non è sempre possibile stimare quanti valori ha o potrebbe avere una particolare chiave di indice. Le prestazioni sono in genere ancora buone se il BUCKET_COUNT valore è compreso tra 10 volte il numero effettivo di valori chiave e l'overstima è in genere migliore rispetto alla sottostima.

Un numero di bucket troppo ridotto presenta i seguenti svantaggi:

• Più collisioni hash di valori di chiave distinct.
• Ogni valore distinct è costretto a condividere lo stesso bucket con un valore distinct diverso.
• La lunghezza media di catena per bucket aumenta.
• Più è lunga la catena di bucket, più lente saranno le ricerche di uguaglianza nell'indice.

Un numero di bucket troppo elevato presenta i seguenti svantaggi:

• Un numero di bucket troppo elevato può comportare la presenza di più bucket vuoti.
• I bucket vuoti influiscono sulle prestazioni delle analisi complete degli indici. Se le analisi vengono eseguite regolarmente, è consigliabile scegliere un numero di bucket simile al numero dei valori di chiave di indice distinct.
• I bucket vuoti utilizzano memoria, anche se ogni bucket utilizza solo 8 byte.

Nota

L'aggiunta di ulteriori bucket non determina in alcun modo la riduzione del concatenamento di voci che condividono un valore duplicato. La frequenza di duplicazione dei valori viene usata per stabilire se un hash è del tipo di indice appropriato, non per calcolare il numero di bucket.

Considerazioni sulle prestazioni

Le prestazioni di un indice hash sono:

• Eccellenti quando il predicato nella clausola WHERE specifica un valore esatto per ogni colonna della chiave di indice hash. Se viene fornito un predicato di disuguaglianza, l'indice hash verrà ripristinato in un'analisi.
• Scarse quando il predicato nella clausola WHERE cerca un intervallo di valori nella chiave di indice.
• Scarsa quando il predicato nella WHERE clausola prevede un valore specifico per la prima colonna di una chiave di indice hash a due colonne, ma non specifica un valore per altre colonne della chiave.

Suggerimento

Il predicato deve includere tutte le colonne nella chiave di indice hash. L'indice hash richiede una chiave per eseguire l'hashing ovvero eseguire ricerche nell'indice.
Se una chiave di indice è costituita da due colonne e la WHERE clausola fornisce solo la prima colonna, il motore di database non dispone di una chiave completa per l'hash. Verrà pertanto generato un piano di query per l'analisi di indice.

Se si usa un indice hash e il numero di chiavi di indice univoco supera di 100 volte (o più) il numero di righe, è necessario aumentare il numero di bucket per evitare catene di righe troppo grandi oppure usare un indice non cluster.

Considerazioni sulla dichiarazione

Un indice hash può essere presente solo in una tabella ottimizzata per la memoria. Non può esistere in una tabella basata su disco.

Un indice hash può essere dichiarato:

• UNIQUE, oppure per impostazione predefinita Nonunique.
• NONCLUSTERED, che è l'impostazione predefinita.

Di seguito è riportato un esempio della sintassi per creare un indice hash al di fuori dell'istruzione CREATE TABLE:

ALTER TABLE MyTable_memop
ADD INDEX ix_hash_Column2 UNIQUE
HASH (Column2) WITH (BUCKET_COUNT = 64);

Versioni delle righe e Garbage Collection

In una tabella ottimizzata per la memoria, quando una riga è interessata da un'istruzione UPDATE, la tabella crea una versione aggiornata della riga. Durante la transazione di aggiornamento, altre sessioni potrebbero riuscire a leggere la versione precedente della riga e quindi evitare il rallentamento delle prestazioni associato a un blocco di riga.

L'indice hash potrebbe anche avere versioni diverse delle relative voci per includere l'aggiornamento.

In seguito, quando le versioni precedenti non sono più necessarie, un thread di Garbage Collection (GC) attraversa i bucket e i relativi elenchi di collegamenti per eliminare le voci obsolete. Il thread GC offre prestazioni migliori se le catene degli elenchi di collegamenti sono brevi. Per altre informazioni, vedere Garbage Collection OLTP in memoria.

Linee guida per la progettazione di indici non cluster ottimizzati per la memoria

Gli indici non cluster sono uno dei tipi di indice possibili in una tabella ottimizzata per la memoria. Per altre informazioni, vedere Indici per le tabelle ottimizzate per la memoria.

Si applica a: SQL Server, database SQL di Azure e Istanza gestita di SQL di Azure.

Architettura dell'indice non cluster in memoria

Gli indici non cluster in memoria vengono implementati usando una struttura di dati denominata albero Bw, originariamente prevista e descritta da Microsoft Research nel 2011. Un albero Bw è una variante bloccata e senza latch di un albero B. Per altri dettagli, vedere L'albero Bw: un albero B per le nuove piattaforme hardware.

A un livello molto elevato, l'albero Bw può essere compreso come una mappa di pagine organizzate in base all'ID pagina (PidMap), una funzionalità per allocare e riutilizzare gli ID pagina (PidAlloc) e un set di pagine collegate nella mappa della pagina e tra loro. Questi tre componenti secondari di alto livello costituiscono la struttura interna di base di un albero Bw.These three high level sub-components make up the basic internal structure of a Bw-tree.

La struttura è simile a un normale albero B nel senso che ogni pagina ha un set di valori chiave ordinati e che sono presenti livelli nell'indice che puntano a un livello inferiore e i livelli foglia puntano a una riga di dati. Tuttavia sono presenti delle differenze.

Proprio come negli indici hash, possono essere collegate tra loro (versioni) più righe di dati. I puntatori di pagina tra i livelli sono l'ID di pagine logiche, ovvero gli offset in una tabella di mapping di pagina, che a sua volta contiene l'indirizzo fisico per ogni pagina.

Non sono disponibili aggiornamenti sul posto di pagine di indice. A questo scopo, vengono introdotte nuove pagine delta.

• Per gli aggiornamenti della pagina, non sono necessari blocchi o latch.
• Le pagine di indice non sono di dimensioni fisse.

Il valore della chiave in ogni pagina di livello non foglia illustrato è il valore più alto che l'elemento figlio che punta a contiene e ogni riga contiene anche l'ID pagina logica della pagina. Nelle pagine a livello di foglia, oltre al valore chiave, è contenuto l'indirizzo fisico della riga di dati.

Le ricerche dei punti sono simili agli alberi B, ad eccezione del fatto che poiché le pagine sono collegate in una sola direzione, SQL Server motore di database segue i puntatori a pagina destra, in cui ogni pagina non foglia ha il valore più alto del relativo elemento figlio, anziché il valore più basso come in un albero B.

Se è necessario modificare una pagina a livello foglia, il motore di database di SQL Server non modifica la pagina stessa. Invece, l'motore di database SQL Server crea un record differenziale che descrive la modifica e lo aggiunge alla pagina precedente. Aggiorna quindi anche l'indirizzo della tabella della mappa di pagine per la pagina precedente, all'indirizzo del record differenziale che ora diventa l'indirizzo fisico per questa pagina.

Per gestire la struttura di un albero Bw sono necessarie tre diverse operazioni: consolidamento, suddivisione e unione.

Consolidamento differenziale

Le prestazioni della ricerca potrebbero risultare ridotte in presenza di una lunga catena di record delta, in quanto potrebbe significare l'attraversamento di lunghe catene durante una ricerca in un indice. Se un nuovo record delta è aggiunto a un catena che ha già 16 elementi, le modifiche apportate nel record delta saranno consolidate nella pagina di indice di riferimento e la pagina sarà ricompilata, includendo le modificate indicate dal nuovo record delta che ha avviato il consolidamento. La nuova pagina avrà lo stesso ID ma un nuovo indirizzo di memoria.

Dividere la pagina

Una pagina di indice nell'albero Bw aumenta in base alle esigenze a partire dall'archiviazione di una singola riga all'archiviazione di un massimo di 8 KB. Una volta che la pagina di indice ha raggiunto 8 KB, un nuovo inserimento di una riga singola causa la divisione della pagina di indice. Per una pagina interna, ciò significa che quando non c'è più spazio per aggiungere un altro valore chiave e puntatore e per una pagina foglia, significa che la riga sarebbe troppo grande per adattarsi alla pagina una volta incorporati tutti i record delta. Le informazioni sulle statistiche nell'intestazione della pagina per una pagina foglia tengono traccia della quantità di spazio necessaria per consolidare i record differenziali. Queste informazioni vengono modificate in base all'aggiunta di ogni nuovo record differenziale.

Un'operazione di divisione viene eseguita in due passaggi atomici. Nel diagramma seguente si presuppone che una pagina foglia forza una divisione perché viene inserita una chiave con valore 5 e una pagina non foglia punta alla fine della pagina a livello foglia corrente (valore chiave 4).

Passaggio 1: Assegnare due nuove pagine P1 e P2 e suddividere le righe dalla pagina P1 precedente in queste nuove pagine, inclusa la riga appena inserita. Viene usato un nuovo slot nella tabella di mapping delle pagine per archiviare l'indirizzo fisico della pagina P2. Queste pagine, P1 e P2 non sono ancora accessibili alle operazioni simultanee. Il puntatore logico da P1 a P2 è impostato. Quindi, in un passaggio atomico aggiornare la tabella di mapping della pagina per modificare il puntatore da P1 precedente a nuovo P1.

Passaggio 2: la pagina non foglia punta a P1, ma non esiste un puntatore diretto da una pagina non foglia a P2. P2 è raggiungibile solo tramite P1. Per creare un puntatore da una pagina non foglia a P2, allocare una nuova pagina non foglia (pagina indice interna), copiare tutte le righe dalla pagina non foglia precedente e aggiungere una nuova riga in modo che punti a P2. Al termine, in un unico passaggio atomico aggiornare la tabella di mapping della pagina per modificare il puntatore dalla pagina non foglia precedente alla nuova pagina non foglia.

Unire le pagine

Quando un'operazione DELETE restituisce una pagina con dimensioni inferiori al 10% della pagina massima (attualmente 8 KB) o con una singola riga, tale pagina viene unita a una pagina contigua.

Quando viene eliminata una riga da una pagina, viene aggiunto un record delta per l'eliminazione. Viene inoltre effettuato un controllo per determinare se la pagina di indice (pagina non foglia) è idonea per Merge. Questo controllo verifica se lo spazio rimanente dopo l'eliminazione della riga sarà inferiore al 10% delle dimensioni massime della pagina. Se i requisiti sono soddisfatti, l'unione viene eseguita in tre passaggi atomici.

Nell'immagine seguente si supponga che un'operazione DELETE elimini il valore della chiave 10.

Passaggio 1: viene creata una pagina differenziale che rappresenta il valore della chiave 10 (triangolo blu) e il puntatore nella pagina non foglia Pp1 viene impostato sulla nuova pagina delta. Viene anche creata una pagina speciale delta di tipo unione (triangolo verde) e viene collegata in modo da puntare alla pagina delta. In questa fase, entrambe le pagine (pagina differenziale e pagina delta di merge) non sono visibili ad alcuna transazione simultanea. In un passaggio atomico, il puntatore alla pagina a livello foglia P1 nella tabella di mapping delle pagine viene aggiornato in modo da puntare alla pagina delta di merge. Dopo questo passaggio, la voce relativa al valore chiave 10 in Pp1 punta alla pagina delta dell'unione.

Passaggio 2: La riga che rappresenta il valore della chiave 7 nella pagina non foglia Pp1 deve essere rimossa e la voce per il valore della chiave 10 aggiornata in modo che punti a P1. A tale scopo, viene allocata una nuova pagina non foglia Pp2 e tutte le righe di Pp1 vengono copiate ad eccezione della riga che rappresenta il valore chiave 7; quindi la riga per il valore chiave 10 viene aggiornata in modo che punti alla pagina P1. Al termine, in un passaggio atomico, la voce della tabella di mapping delle pagine che punta a Pp1 viene aggiornata in modo che punti a Pp2. Pp1 non è più raggiungibile.

Passaggio 3: Le pagine a livello foglia P2 e P1 vengono unite e le pagine differenziali rimosse. A tale scopo, viene allocata una nuova pagina P3 e le righe da P2 a P1 sono unite. Le modifiche apportate alla pagina delta sono incluse nella nuova pagina P3. Quindi, in un passaggio atomico, la tabella di mapping delle pagine che punta alla pagina P1 viene aggiornata in modo da puntare alla pagina P3.

Considerazioni sulle prestazioni

Quando si esegue una query su una tabella ottimizzata per la memoria con predicati di disuguaglianza, gli indici non cluster offriranno prestazioni migliori rispetto agli indici hash non cluster.

Una colonna di una tabella ottimizzata per la memoria può far parte sia di un indice hash che di un indice non cluster.

Quando una colonna chiave in un indice non cluster ha molti valori duplicati, le prestazioni possono peggiorare per gli aggiornamenti, gli inserimenti e le eliminazioni. Un modo per migliorare le prestazioni in questa situazione consiste nell'aggiungere una colonna con una migliore selettività nella chiave di indice.

Passaggi successivi

• CREATE INDEX (Transact-SQL)
• Riorganizzare e ricompilare gli indici
• Tabelle e indici partizionati
• Indici per tabelle con ottimizzazione per la memoria
• Indici columnstore - Panoramica
• Indici per le colonne calcolate
• Ottimizzare gli indici non cluster con suggerimenti di indici mancanti