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à è molto 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 sui database

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

• Un numero elevato di indici ha ripercussioni sulle prestazioni delle istruzioni INSERT, UPDATE e DELETE perché quando vengono modificati i dati nella tabella, tutti gli indici devono essere modificati di conseguenza.
  • 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 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 può non risultare ottimale, perché Query Optimizer impiega più tempo per scorrere l'indice cercando i dati che per eseguire una semplice scansione della 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. Per ulteriori informazioni, vedere Progettazione di viste indicizzate.
• Utilizzare Ottimizzazione guidata motore di database per analizzare il database e raccogliere informazioni per gli indici. Per ulteriori informazioni, vedere Panoramica su Ottimizzazione guidata motore di database.

Considerazioni sulle query

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

• Creare indici non cluster in tutte le colonne che vengono utilizzate spesso in predicati e condizioni di join nelle query.

  Importante:
  Evitare di 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. Per recuperare i dati richiesti sono necessarie solo le pagine di indice e non le pagine di dati della tabella o dell'indice cluster. In questo modo si riduce l'I/O del disco complessivo. Ad esempio, una query delle colonne a e b di una tabella con un indice composto creato nelle colonne a, b e c può recuperare i dati specificati dal solo indice.
• 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. Per ulteriori informazioni, vedere Tipi di query e indici.

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. Per ulteriori informazioni, vedere Linee guida per la progettazione di indici cluster.
• Non è possibile specificare come colonne chiave indice le colonne di tipo ntext, text, image, varchar(max), nvarchar(max) e varbinary(max). Tuttavia, i tipi di dati varchar(max), nvarchar(max), varbinary(max) e xml possono partecipare a un indice non cluster come colonne non chiave dell'indice. Per ulteriori informazioni, vedere Indice con colonne incluse.
• Un tipo di dati xml può essere solo una colonna chiave solo in un indice XML. Per ulteriori informazioni, vedere Indici nelle colonne con tipo di dati XML.
• 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.
• 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 ulteriori informazioni sulla distribuzione dei dati, vedere Statistiche dell'indice.
• Esaminare l'ordine delle colonne se l'indice conterrà più 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 risulterà 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 ulteriori informazioni, vedere Creazione di indici per le colonne calcolate.

Caratteristiche degli indici

Dopo avere determinato che un indice è adeguato per una query, è possibile scegliere il tipo di indice più adatto a seconda della 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

È inoltre possibile personalizzare le caratteristiche iniziali dell'archiviazione dell'indice per ottimizzarne le prestazioni o la gestione impostando un'opzione quale FILLFACTOR. Per ulteriori informazioni, vedere Impostazione delle opzioni di un indice. È inoltre possibile determinare la posizione di archiviazione dell'indice utilizzando filegroup o schemi di partizione per ottimizzare le prestazioni. Per ulteriori informazioni, vedere Posizionamento di indici in filegroup.

Vedere anche

Concetti

Nozioni fondamentali sulla progettazione di indici

Altre risorse

Ottimizzazione delle query
Individuazione degli indici mancanti

Guida in linea e informazioni

Assistenza su SQL Server 2005