Guida per la progettazione e l'architettura degli indici di SQL Server e di Azure SQL

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

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 conosca i tipi di indice disponibili. Per una descrizione generale dei tipi di indice, vedere Indici.

In questa guida vengono illustrati i tipi di indici seguenti:

Formato di archiviazione principale	Tipo di indice
Rowstore basato su disco
	Cluster
	Non cluster
	Unica
	Filtrato
Columnstore
	Columnstore in cluster
	Columnstore non cluster
Ottimizzate per la memoria
	Hash
	Non cluster ottimizzato per la memoria

Per informazioni sugli indici XML, vedere Indici XML (server SQL) e Indici XML selettivi (SXI).

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

Per informazioni sugli indici full-text, vedere Popolare indici full-text.

Nozioni fondamentali sulla progettazione di indici

Si pensi a un libro: alla fine del libro è disponibile un indice che consente di trovare 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 funziona in modo simile: è 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. Quindi si seguono i puntatori che rimandano 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 extra eliminerà il passaggio di consultazione dell'intero indice per trovare il punto di partenza. Questa pagina non esiste nei libri normali, 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 usata da un indice. Per continuare con l'esempio dell'albero, le pagine finali contenenti puntatori ai dati effettivi sono 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 costituite da una o più colonne della tabella o della vista. Per gli indici rowstore, queste chiavi vengono archiviate in una struttura ad albero (albero B+) che consente al motore di database di trovare con rapidità ed efficienza la riga o le righe associate ai valori di chiave.

Un indice rowstore archivia dati organizzati logicamente, come una tabella con righe e colonne, e archiviati fisicamente in un formato di dati a livello di riga denominato rowstore ¹, o archiviati in un formato 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 opportuno sperimentare il funzionamento di vari tipi di indice.

Query Optimizer nel motore di database consente di scegliere in modo affidabile l'indice più efficace nella maggior parte dei casi. La strategia globale di progettazione dell'indice deve offrire a Query Optimizer vari indici tra cui scegliere e fare affidamento su questo strumento per la scelta. 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 dal menu Query scegliere Includi piano di esecuzione effettivo.

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. L'attività di Query Optimizer consiste pertanto nel selezionare un indice, o una combinazione di indici, solo quando questo comporta un miglioramento delle prestazioni e nell'evitare il recupero indicizzato quando ciò potrebbe avere conseguenze negative sulle 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 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 di indici

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

1. Comprendere le caratteristiche del database.

   • Ad esempio, si tratta di un database di elaborazione di transazioni online (OLTP) con modifiche frequenti dei dati che deve sostenere una produttività elevata? Gli indici e le tabelle ottimizzate per la memoria sono particolarmente adatti per questo scenario, in quanto offrono una progettazione priva di latch. Per ulteriori informazioni, vedere Indici per tabelle ottimizzate per la memoria, oppure linee guida per la progettazione di indici non cluster ottimizzati per la memoria e linee guida di progettazione per gli indici hash presenti in questa guida.
   • Oppure si tratta di un esempio di database DSS (Decision Support System) o di database (OLAP) di archiviazione dati che deve elaborare rapidamente grandi set di dati? Gli indici columnstore sono particolarmente adatti per gli insiemi di dati tipici dell'archiviazione dati. 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 ulteriori informazioni, vedere Indici columnstore: panoramica, o Linee guida per la progettazione di indici columnstore presenti in questa guida.

2. Comprendere le caratteristiche delle query utilizzate più di frequente. Se, ad esempio, si stabilisce che una query utilizzata di frequente unisce in join due o più tabelle, è possibile determinare il tipo più adatto di indici da utilizzare.

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

4. Determinare quali opzioni dell'indice possono 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. L'opzione ONLINE 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 colonne. Per impedire la creazione di indici duplicati, esaminare gli indici esistenti nella tabella insieme ai suggerimenti per l'indice mancanti. Per altre informazioni, vedere Ottimizzare gli indici non cluster con suggerimenti di indici mancanti.

Linee guida generali per la progettazione di indici

Gli amministratori di database esperti in genere sono in grado di progettare un set di indici adeguato, ma questa attività è complessa, richiede tempo ed è soggetta ad errori anche nel caso di database e carichi di lavoro di media complessità. 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ò contribuire a migliorare le prestazioni delle query che non modificano i dati, ad esempio delle istruzioni di SELECT, perché Query Optimizer può scegliere fra un numero maggiore di indici per determinare il metodo di accesso più rapido.

• L'indicizzazione di tabelle di piccole dimensioni potrebbe non risultare ottimale, perché Query Optimizer impiega più tempo per scorrere l'indice cercando i dati che per eseguire una semplice scansione di tabella. 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 che venga fatto riferimento esplicito alla vista nella query affinché Query Optimizer la utilizzi.

• I database nelle repliche primarie nel database SQL di Azure generano automaticamente raccomandazioni sulle prestazioni di Advisor per database 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 che registrano gli indici selezionati dall'optimizer.

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 composto creato nelle colonne A, B e C può recuperare i dati specificati dall'indice solo.

  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 Search ARGument-able (ovvero un predicato che supporta argomenti di ricerca) che può usare un indice per accelerare l'esecuzione delle 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, è consigliabile creare gli indici cluster su 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 di 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 ulteriori 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 ulteriori informazioni, vedere Indici XML (SQL Server). In SQL Server 2012 SP1 viene introdotto un nuovo tipo di indice XML noto come indice XML selettivo. Grazie al nuovo indice possono essere migliorate le prestazioni di esecuzione delle query sui dati archiviati come XML, pertanto è possibile un'indicizzazione più rapida di carichi di lavoro di dati XML di grandi dimensioni, nonché un miglioramento della scalabilità riducendo i costi di archiviazione dell'indice stesso. Per ulteriori 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 ulteriori 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. Questo problema fondamentale relativo ai dati e alle query in genere non può essere risolto senza identificare questa situazione specifica. Ad esempio, in un elenco telefonico ordinato alfabeticamente in base al cognome non sarà possibile velocizzare la ricerca se i nomi di tutti gli abitanti della città sono Bianchi o Rossi. 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.

• Se l'indice contiene più colonne, tenere conto dell'ordine delle colonne. La colonna che viene utilizzata nella clausola WHERE in una condizione di ricerca uguale a (=), maggiore di (>), minore di (<) o BETWEEN oppure che partecipa a un join deve essere al primo posto. 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 risulta utile se il criterio di ricerca è WHERE LastName = 'Smith' o WHERE LastName = Smith AND FirstName LIKE 'J%'. Query Optimizer, tuttavia, non utilizzerebbe l'indice per una query che effettuasse la ricerca solo in base a FirstName (WHERE FirstName = 'Jane').

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

Caratteristiche degli indici

Dopo avere stabilito che un indice è adeguato a una query, è possibile scegliere 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 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.

Posizione degli indici nei filegroup o negli 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. Se invece tramite la query viene eseguita l'analisi di Table_A ma non è presente un riferimento a Index_A, verrà utilizzato solo il filegroup f1. e non si otterrà un miglioramento delle prestazioni.

Non potendo prevedere il tipo di accesso né il momento in cui questo si verifica, risulta più appropriato scegliere di distribuire le tabelle e gli indici tra 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 scegliere di partizionare 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 diverse partizioni di un indice, Query Optimizer è in grado di elaborare le singole partizioni contemporaneamente e di escludere quelle non interessate dalla query.

Per ulteriori informazioni, vedere Tabelle e indici partizionati.

Linee guida per la progettazione dell'ordinamento dell'indice

Quando si definiscono gli indici, stabilire se la colonna chiave dell'indice debba essere archiviata in ordine crescente o decrescente. L'ordine crescente rappresenta l'impostazione predefinita e consente di garantire 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 (crescente) e DESC (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. Gli acquirenti nel reparto acquisti di Adventure Works Cycles, ad esempio, 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 rispetto al database di esempio AdventureWorks, per recuperare i dati che soddisfano questo criterio è necessario che la colonna RejectedQty della tabella Purchasing.PurchaseOrderDetail sia disposta in ordine decrescente, ovvero dal valore più grande al più piccolo, e che la colonna ProductID sia disposta in ordine crescente, ovvero dal valore più piccolo al più grande.

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

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

Se un indice rowstore basato su disco viene creato con colonne chiave che corrispondono a quelle della clausola ORDER BY nella query, è possibile eliminare l'operatore SORT nel piano di query, migliorando l'efficienza della query.

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 consente di spostarsi con la stessa efficienza in entrambe le direzioni. Un indice definito come (RejectedQty DESC, ProductID ASC) può comunque essere utilizzato per una query in cui l'ordinamento delle colonne nella clausola ORDER BY viene invertito. 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 seguente codice Transact-SQL:

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.

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

• sys.column_store_dictionaries
• sys.column_store_row_groups
• sys.column_store_segments
• sys.dm_column_store_object_pool
• sys.dm_db_column_store_row_group_operational_stats
• sys.dm_db_column_store_row_group_physical_stats
• sys.dm_db_index_operational_stats
• sys.dm_db_index_physical_stats
• sys.dm_db_xtp_hash_index_stats
• sys.dm_db_xtp_index_stats
• sys.dm_db_xtp_nonclustered_index_stats
• sys.dm_db_xtp_object_stats
• sys.dm_db_xtp_table_memory_stats
• sys.hash_indexes
• sys.index_columns
• sys.indexes
• sys.internal_partitions
• sys.memory_optimized_tables_internal_attributes
• sys.partitions

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, è opportuno definire un indice cluster sulla colonna o sulle colonne di tutte le tabelle che presentano le caratteristiche seguenti:

• Possono essere utilizzate per query frequenti.

• Garantiscono un elevato livello 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 proprietà UNIQUE, tramite il motore di database alla tabella viene aggiunta automaticamente una colonna uniqueifier a 4 byte. Se necessario, tramite il motore di database a una riga viene aggiunto automaticamente un valore uniqueifier per rendere univoca ciascuna chiave. Questa colonna e i relativi valori sono per uso interno e non sono visualizzati o accessibili dagli utenti.

Architettura dell'indice cluster

Gli indici rowstore sono organizzati come alberi B+. Ogni pagina dell'albero B+ di un indice viene definita nodo dell'indice. Il nodo di livello superiore dell'albero B+ viene definito nodo principale. 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 di chiave e un puntatore a una pagina di livello intermedio nell'albero B+ o a una riga di dati nel livello foglia dell'indice. Le pagine di ogni livello dell'indice sono collegate in un elenco collegato doppiamente.

Gli indici cluster includono 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 in un indice cluster sono incluse più partizioni, ogni partizione ha una struttura ad albero B+ contenente i dati per la partizione specifica. Se, ad esempio, un indice cluster include quattro partizioni, vi sono quattro strutture ad albero B+, una in ogni partizione.

In base al tipo di dati nell'indice cluster, ogni struttura dell'indice cluster avrà una o più unità di allocazione in cui archiviare e gestire i dati per una partizione specifica. Ogni indice cluster contiene almeno un'unità di allocazione IN_ROW_DATA per partizione. L'indice cluster dispone anche di un'unità di allocazione LOB_DATA per partizione se contiene colonne Large Object (LOB). Contiene anche un'unità di allocazione ROW_OVERFLOW_DATA per partizione, se include colonne a lunghezza variabile che superano il limite della lunghezza 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, è consigliabile conoscere la modalità di accesso ai dati. Utilizzare ad esempio un indice cluster per query che eseguono le operazioni seguenti:

• Restituiscono un intervallo di valori usando operatori quali 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.

  L'utilizzo di un indice basato sulle colonne specificate nella clausola ORDER BY o GROUP BY consente di evitare operazioni di ordinamento dei dati nel motore di database 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 diversamente, durante la creazione di 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 utile eseguire il clustering della tabella, ovvero ordinarla fisicamente, in base a tale colonna per evitare il costo di un'operazione di ordinamento ogni volta che si esegue la query sulla colonna.

Per gli attributi seguenti non sono consigliati gli indici cluster:

• Colonne che vengono modificate di frequente

  Ciò determina lo spostamento dell'intera riga in quanto il motore di database deve mantenere i valori dei dati di una riga nell'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 sono 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 non cluster rowstore basato su disco contiene i valori della chiave di indice e gli indicatori di posizione delle 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 consentono di migliorare le prestazioni di query utilizzate di frequente che non sono coperte da un 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 non cluster rowstore basati su disco hanno la stessa struttura ad albero B+ degli indici cluster, con due differenze significative:

• Le righe di dati della tabella sottostante non vengono ordinate e archiviate con ordinamento basato sulle relative 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, ovvero non include un indice cluster, l'indicatore di posizione è un puntatore riferito 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.

Gli indicatori di posizione delle righe garantiscono anche l'univocità per le righe di indice non cluster. Nella tabella seguente viene descritto come il motore di database aggiunge a indici non cluster gli indicatori di posizione delle righe:

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

Il motore di database non archivia mai in un indice non cluster una determinata colonna due volte. L'ordine della chiave di indice specificato dall'utente quando crea un indice non cluster viene sempre rispettato: tutte le colonne dell'indicatore di posizione delle righe che devono essere aggiunte alla chiave di un indice non cluster vengono aggiunte alla fine della chiave, seguendo le colonne specificate nella definizione dell'indice. In un indice non cluster le colonne dell'indicatore di posizione delle righe basate sulla chiave dell'indice 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 gli indicatori di posizione delle righe vengono implementati in indici non cluster:

Indice cluster	Definizione di indice non cluster	Definizione di indice non cluster con indicatori di posizione 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 l'indicatore di posizione delle righe deve essere presente nelle chiavi di indice. Le colonne B e A dall'indicatore di posizione delle righe sono già presenti, pertanto solo la colonna c viene aggiunta. La colonna c viene aggiunta alla fine dell'elenco delle 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 alla chiave viene aggiunto l'indicatore di posizione delle righe. La colonna A non è già specificata come colonna chiave, quindi viene aggiunta alla fine dell'elenco delle colonne chiave. La colonna A è ora nella chiave, quindi non è necessario archiviarla come una 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 alle colonne incluse viene aggiunto l'indicatore di posizione delle righe.

Negli indici non cluster è inclusa 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 in un indice non cluster sono incluse più partizioni, ogni partizione ha una struttura ad albero B+ contenente le righe di indice per la partizione specifica. Se, ad esempio, un indice non cluster include quattro partizioni, vi sono quattro strutture ad albero B+, una in ogni partizione.

In base ai tipi di dati nell'indice non cluster, ogni struttura dell'indice non cluster dispone di una o più unità di allocazione in cui archiviare e gestire i dati per una partizione specifica. Ogni indice non cluster dispone di almeno un'unità di allocazione IN_ROW_DATA per partizione in cui sono archiviate le pagine relative all'albero B+ dell'indice. L'indice non cluster include anche un'unità di allocazione LOB_DATA per partizione se contiene colonne Large Object (LOB). L'indice include anche un'unità di allocazione ROW_OVERFLOW_DATA per partizione se contiene colonne a lunghezza variabile che superano le dimensioni massime 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. In questo modo, per Query Optimizer sono disponibili più indici tra cui scegliere per determinare il metodo di accesso più rapido e la bassa frequenza di aggiornamento del database significa che la manutenzione dell'indice non intacca 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, è consigliabile analizzare la modalità di accesso 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 estesi.

  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.

• Query che contengono colonne interessate di frequente da 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. Potrebbe 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 della colonna all'interno dell'indice. Poiché non viene effettuato l'accesso ai dati della tabella o dell'indice cluster, il numero di operazioni di I/O su disco risulta inferiore. Usare un indice con colonne incluse per aggiungere colonne di copertura invece di creare una chiave di indice esteso.

  Se la tabella include un indice cluster, la colonna o le colonne definite in tale indice vengono automaticamente aggiunte ad 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. Se, ad esempio, in una tabella sono inclusi un indice cluster non univoco nella colonna C, un indice non cluster nelle colonne B e A, le colonne B, A e C della tabella in questione rappresentano i relativi valori chiave. Per altre informazioni, visitare 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 soltanto pochi valori distinct, ad esempio solo 1 e 0, la maggior parte delle query non utilizzerà l'indice in quanto una scansione di tabella risulta in genere più efficiente. Per questo tipo di dati, creare un indice filtrato su un valore distinct che si presenta solo in alcune 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.

Utilizzare 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 prese in esame dal motore di database durante il calcolo del numero di colonne chiave indice o della dimensione delle chiavi 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. I vantaggi nelle prestazioni si ottengono poiché Query Optimizer può individuare tutti i valori delle colonne all'interno dell'indice. In questo modo, la quantità di operazioni di I/O su disco è inferiore dato che non viene eseguito alcun accesso ai dati delle tabelle o degli indici cluster.

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 aggirare i limiti alle 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 prende in esame le colonne non chiave durante il calcolo del numero di colonne chiave dell'indice o della dimensione delle chiavi dell'indice.

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

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

Dal momento che i tipi di dati nchar e nvarchar richiedono 2 byte per ogni carattere, un indice contenente queste tre colonne supererebbe di 10 byte il limite di 900 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, la dimensione della chiave dell'indice sarebbe pari a 110 byte (55 * 2) e l'indice conterrebbe ancora 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 di Transact-SQL:

DROP INDEX IX_Document_Title
ON Production.Document;
GO

Linee guida sull'utilizzo degli indici con 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.

• Non è possibile specificare i nomi delle colonne sia nell'elenco INCLUDE sia nell'elenco delle colonne chiave.

• Non è possibile ripetere i nomi delle colonne nell'elenco INCLUDE.

Linee guida sulle dimensioni delle colonne

• È necessario definire almeno una colonna chiave. Il numero massimo di colonne non chiave è 1.023. 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 sulla modifica delle colonne

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

• Non è possibile eliminare dalla tabella le colonne non chiave se non si è prima eliminato l'indice.

• Non è possibile modificare le colonne non chiave se non per eseguire le operazioni seguenti:

  • 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 si hanno tutte le colonne necessarie per coprire la query, contenendo al tempo stesso le dimensioni della chiave dell'indice e mantenendone l'efficienza.

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 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 accertarsi che l'indice copra la query, creare l'indice, quindi visualizzare il piano di esecuzione stimato.

Se il piano di esecuzione mostra solo un operatore SELECT e un operatore Index Seek per l'indice IX_Address_PostalCode, la query è coperta dall'indice.

Per eliminare l'indice, è possibile seguire attenersi alla seguente istruzione:

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:

• In una pagina rientrano meno righe di indice. 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 può 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 guadagni in termini di prestazioni delle query offrano maggiore vantaggio rispetto all'effetto sulle prestazioni durante la modifica dei dati e per i requisiti aggiuntivi di spazio su disco.

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 nella colonna lo stesso valore per più dipendenti, verrà visualizzato un messaggio di errore e il valore duplicato non verrà 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, nella stessa tabella è possibile creare sia un indice cluster univoco che più indici non cluster univoci.

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 differenzia un indice univoco creato da un vincolo da un indice creato in modo manuale. 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 risulta chiaro.

Considerazioni

• Se nei dati sono presenti valori di chiave duplicati, non è possibile creare un indice univoco, un vincolo UNIQUE o un vincolo PRIMARY KEY.

• 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 lo spazio di archiviazione su disco per gli indici non cluster nel caso in cui non sia 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 sottoinsiemi di dati

Quando una colonna dispone solo di un numero ridotto di valori rilevanti per le query, è possibile creare un indice filtrato sul sottoinsieme 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 sarà di dimensioni inferiori e sarà possibile gestirlo con costi ridotti rispetto a un indice non cluster di tabella completa definito sulle stesse colonne chiave.

Nel database di esempio AdventureWorks, ad esempio, è disponibile una tabella Production.BillOfMaterials con 2.679 righe. La colonna EndDate dispone solo di 199 righe che contengono un valore non NULL, mentre le altre 2.480 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 la stime il piano di esecuzione della query per determinare se in Query Optimizer è stato utilizzato 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 poiché i valori di colonna relativi nella tabella Production.Product non sono strettamente correlati. Ad esempio, le colonne Color, ReorderPoint, ListPrice, Weight, Classe Style dispongono di caratteristiche univoche per ogni categoria di prodotti. Se vengono eseguite query frequenti sugli accessori con sottocategorie 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 in 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 poche 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. Sebbene Query Optimizer possa scegliere un indice filtrato per la query indipendentemente dal fatto che la copra o meno, 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.

Non è necessario che una colonna nell'espressione che definisce l'indice filtrato sia una colonna chiave o sia inclusa nella definizione dell'indice stesso, se l'espressione che definisce l'indice filtrato è equivalente al predicato della query e la query non restituisce la colonna in tale espressione con i risultati della query. L'indice FIBillOfMaterialsWithEndDate, ad esempio, copre la query seguente perché il predicato della query è equivalente all'espressione filtro ed EndDate non viene restituito con i risultati della query. FIBillOfMaterialsWithEndDate non ha bisogno di EndDate come colonna chiave o inclusa nella definizione dell'indice filtrato.

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

Una colonna nell'espressione che definisce l'indice filtrato deve essere una colonna chiave o inclusa nella definizione dell'indice se il predicato della query la utilizza in un confronto non equivalente all'espressione che definisce l'indice filtrato. L'indice FIBillOfMaterialsWithEndDate , ad esempio, è valido per la query seguente perché seleziona un subset di righe dall'indice filtrato. Tale indice tuttavia non copre la query seguente poiché EndDate viene utilizzato nel confronto EndDate > '20040101', che non è equivalente all'espressione che definisce l'indice filtrato. Poiché Query Processor non è in grado di 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. L'indice FIBillOfMaterialsWithEndDate, ad esempio, non copre la query seguente perché restituisce la colonna EndDate 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;

Non è necessario che la chiave di indice cluster della tabella sia una colonna chiave o inclusa nella definizione dell'indice filtrato. poiché viene inclusa automaticamente in tutti gli indici non cluster, inclusi quelli filtrati.

Per eliminare gli indici FIBillOfMaterialsWithEndDate 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 che definisce l'indice filtrato determina una conversione dei dati implicita o esplicita, si verificherà un errore se la conversione viene eseguita 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 diversi tipi di dati.

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. Verrà generato un messaggio di errore 10611 poiché la conversione viene eseguita 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 della 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 Informazioni generali sugli indici columnstore.

Per informazioni sulla versione e per scoprire le novità, visitare Novità degli Indici Columnstore.

La conoscenza di questi nozioni di base semplificherà la comprensione di altri articoli dedicati agli indici 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, se 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 metodo tradizionale per archiviare dati relazionali di tabella, come un indice heap o un indice ad albero B+ cluster.

  Un indice columnstore archivia inoltre fisicamente alcune righe in un formato rowstore, denominato archivio differenziale. L'archivio differenziale, detto anche rowgroup differenziale, è una posizione di archiviazione per le righe in numero troppo limitato per essere considerata idonea alla compressione nel columnstore. Ogni rowgroup differenziale viene implementato come indice ad 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 dei columnstore, vedere Indici columnstore: informazioni generali.

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 ad albero B+ cluster che archivia caricamenti bulk e inserimenti di piccole dimensioni fino a quando il rowgroup non contiene 1.048.576 righe, dopodiché un processo detto motore di tuple comprime automaticamente il rowgroup chiuso nel columnstore.

Per altre informazioni sugli stati dei rowgroup, vedere sys.dm_db_column_store_row_group_physical_stats.

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.

In SQL Server 2019 (15.x) e nelle versioni successive, il motore di tuple viene affiancato da un'attività di unione in background, che comprime automaticamente i rowgroup differenziali OPEN di dimensioni più piccole che sono esistiti per un dato periodo di tempo (in quanto stabilito da una soglia interna) oppure unisce i rowgroup COMPRESSED 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 dei columnstore, vedere Indici columnstore: informazioni generali.

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 dei columnstore, vedere Indici columnstore: informazioni generali.

Quando sono pieni, i rowgroup differenziali 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.

Per altre informazioni sugli stati dei rowgroup, vedere sys.dm_db_column_store_row_group_physical_stats.

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

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

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 tenta di ottenere un blocco su un rowgroup differenziale diverso. In assenza di rowgroup differenziali disponibili, l'indice columnstore crea 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 sono 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 nella tabella rowstore vengono modificati. In questo modo entrambi gli indici possono usare gli stessi dati.

È possibile avere uno o più indici rowstore non cluster per 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. Dato che 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 durante la creazione della tabella o aggiungerlo in un secondo momento con ALTER TABLE (Transact-SQL). Prima di SQL Server 2016 (13.x), solamente una tabella basata su disco poteva 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 un indice di sola lettura.

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

Linee guida per la progettazione di indici 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 ulteriori 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 è 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 corretto BUCKET_COUNT per i propri 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 include una funzione hash che viene 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 probabilità di Poisson non è uniforme. I valori delle chiavi di indice non vengono distribuiti in modo uniforme nei bucket di 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 è che il 30% dei bucket contengano due valori di 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 può avere una particolare chiave di indice. Le prestazioni sono di norma ancora soddisfacenti se il valore BUCKET_COUNTè al massimo 10 volte superiore al numero effettivo di valori chiave e la stima di un valore superiore è in genere migliore della stima di un valore inferiore.

Un numero di bucket troppo ridotto può avere 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 può avere 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 ripristina in un'analisi.
• Scarse quando il predicato nella clausola WHERE cerca un intervallo di valori nella chiave di indice.
• Scarse quando il predicato nella clausola WHERE specifica un determinato valore 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 clausola WHERE specifica solo la prima colonna, nel motore di database non sarà disponibile una chiave completa di cui eseguire l'hashing. Questo genera 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, ovvero 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 per 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 ulteriori 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 sono implementati mediante una struttura di dati chiamata albero Bw, inizialmente concepita e descritta da Microsoft Research nel 2011. Un albero Bw è una variazione priva di latch e blocco di un albero B. Per ulteriori informazioni, vedere Albero Bw: un albero B per nuove piattaforme Hardware.

A un livello elevato l'albero Bw può essere interpretato come una mappa di pagine organizzate in base all'ID della pagina (PidMap), una struttura per allocare e riusare gli ID di pagine (PidAlloc) e un set di pagine collegate nella mappa di pagine e tra di loro. Questi tre sottocomponenti di alto livello costituiscono la struttura interna di base di un albero Bw.

La struttura è simile a quella di un normale albero B in quanto ogni pagina ha una raccolta di valori di chiave ordinati e sono presenti livelli di indice che puntano a un livello inferiore e livelli foglia che 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 hanno dimensioni fisse.

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

Le ricerche di punti frequenti sono simili a quelle dell'albero B tranne per il fatto che, poiché le pagine sono collegate solo in una direzione, il motore di database di SQL Server segue i puntatori di pagina a destra, in cui ogni pagina che non è una pagina foglia corrisponde al valore massimo del relativo elemento figlio, anziché al valore più basso come accade in un albero B.

Se si deve modificare una pagina foglia, il motore di database di SQL Server non modifica la pagina stessa. Il motore di database di SQL Server crea un record delta che descrive la modifica e lo accoda alla pagina precedente. In seguito aggiorna l'indirizzo di tabella della mappa per la pagina precedente all'indirizzo del record delta che diventa l'indirizzo fisico per questa pagina.

Per la gestione della struttura di un albero Bw sono possibili tre opzioni diverse: consolidamento, divisione e unione.

Consolidamento del delta

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 sono consolidate nella pagina di indice di riferimento e la pagina è ricompilata, includendo le modificate indicate dal nuovo record delta che ha avviato il consolidamento. La nuova pagina ha 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 fino 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 non è disponibile più spazio per aggiungere altri valori chiave e puntatori. Per una pagina foglia, ciò significa che, una volta incorporati tutti i record delta, la riga risulta troppo grande per la pagina. Le informazioni statistiche nell'intestazione pagina per la pagina foglia tengono traccia della quantità di spazio necessaria per consolidare i record delta. Queste informazioni vengono regolate quando un nuovo record delta viene aggiunto.

Un'operazione di divisione è effettuata in due passaggi atomici. Nel diagramma seguente, si supponga che una pagina foglia forzi una divisione in quanto una chiave con valore 5 viene inserita e che sia presente una pagina non foglia che punta alla fine della pagina a livello foglia attuale (valore chave 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. Un nuovo slot nella tabella di mapping della pagina viene usato per archiviare l'indirizzo fisico della pagina P2. Le due pagine, P1 e P2, non sono ancora accessibili ad alcuna operazione simultanea. Il puntatore logico da P1 a P2 è impostato. Quindi, per modificare il puntatore dal vecchio P1 al nuovo P1, aggiornare la tabella di mapping della pagina in un unico passaggio atomico.

Passaggio 2: la pagina non foglia punta P1 ma non è presente alcun 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 di indice interno), copiare tutte le righe dalla pagina foglia precedente e aggiungere una nuova riga in modo che punti a P2. Una volta terminata questa operazione, eseguita 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 meno del 10% della dimensione massima della pagina (attualmente 8 KB) o con una sola riga, la pagina viene unita a una pagina contigua.

Quando viene eliminata una riga da una pagina, viene aggiunto un record delta per l'eliminazione. Viene anche eseguito un controllo per determinare se la pagina di indice (pagina non foglia) soddisfa i criteri per l'unione. Questo controllo verifica se lo spazio rimanente dopo l'eliminazione della riga è 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 chiave 10.

Passaggio 1: viene creata una pagina delta che rappresenta il valore di chiave 10 (triangolo blu) e il suo puntatore nella pagina non foglia Pp1 è impostato per la 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 le due pagine (pagina delta e pagina delta di tipo unione) non sono visibili per le transazioni simultanee. In un unico passaggio atomico, il puntatore alla pagina di livello foglia P1 nella tabella di mapping della pagina viene aggiornato per puntare alla pagina delta di tipo unione. 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 chiave 7 nella pagina non foglia Pp1 deve essere rimossa e la voce per il valore chiave 10 deve essere aggiornato per puntare a P1. A tale scopo, viene allocata una nuova pagina non foglia Pp2 e vengono copiate tutte le righe a partire da Pp1, tranne la riga che rappresenta il valore chiave 7. La riga per il valore chiave 10 viene quindi aggiornata per puntare alla pagina P1. Dopo questa operazione, eseguita in un unico passaggio atomico, la voce della tabella di mapping di pagina che punta a Pp1 viene aggiornata per puntare a Pp2. Pp1 non è più raggiungibile.

Passaggio 3: le pagine a livello foglia P2 e P1 vengono unite e le pagine delta 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. In un unico passaggio atomico, la voce della tabella di mapping di pagina che punta alla pagina P1 viene aggiornata per 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 in un indice non cluster una colonna chiave contiene molti valori duplicati, le prestazioni per le operazioni di aggiornamento, inserimento ed eliminazione potrebbero risultare ridotte. Un modo per migliorare le prestazioni in questa situazione consiste nell'aggiungere una colonna che abbia una migliore selettività nella chiave di indice.

Contenuto correlato

• CREATE INDEX (Transact-SQL)
• Ottimizzare la manutenzione dell'indice per migliorare le prestazioni delle query e ridurre il consumo di risorse
• Tabelle e indici partizionati
• Indici in tabelle con ottimizzazione per la memoria
• Indici columnstore: Panoramica
• Indici per le colonne calcolate
• Ottimizzare gli indici non cluster con suggerimenti di indici mancanti