Linee guida specifiche per gli indici partizionati
Benché sia possibile implementare gli indici partizionati indipendentemente dalle relative tabelle di base, in genere è consigliabile progettare una tabella partizionata e quindi creare l'indice nella tabella. In questo modo, SQL Server esegue automaticamente il partizionamento dell'indice utilizzando lo stesso schema di partizione e la stessa colonna di partizionamento della tabella. L'indice viene pertanto partizionato nello stesso modo della tabella e risulta allineato con la tabella.
SQL Server non allinea l'indice alla tabella se al momento della creazione si specifica uno schema di partizione diverso o un filegroup distinto nel quale inserire l'indice.
L'allineamento di un indice a una tabella partizionata è particolarmente importante se si prevede che verrà ampliato tramite l'aggiunta di altre partizioni o che sarà interessato da frequenti cambi di partizione. Per ulteriori informazioni, vedere Progettazione di partizioni per la gestione di subset di dati. Se una tabella e i relativi indici sono allineati, in SQL Server è possibile cambiare le partizioni in modo rapido ed efficiente, mantenendo inalterata la struttura delle partizioni della tabella e degli indici.
Nota
Per poter essere allineato alla relativa tabella di base, non è necessario che un indice sia inserito nella stessa funzione di partizione denominata. Le funzioni di partizione dell'indice e della tabella di base devono tuttavia condividere alcune caratteristiche, ad esempio i tipi di dati degli argomenti devono essere uguali, il numero di partizioni definito deve essere uguale e i valori limite delle partizioni devono essere uguali.
Nella scheda Opzioni di ottimizzazione di Ottimizzazione guidata motore di database è disponibile un'opzione Partizionamento allineato che consente di specificare che i nuovi indici saranno allineati alle relative tabelle di base. L'opzione Mantieni partizionamento allineato consente di ottenere lo stesso risultato e inoltre di eliminare gli indici non allineati. Per ulteriori informazioni, vedere Ottimizzazione guidata motore di database (scheda Opzioni di ottimizzazione). In generale, è possibile utilizzare Ottimizzazione guidata motore di database per consigliare l'utilizzo di indici, sia allineati che non allineati. Per ulteriori informazioni, vedere Panoramica su Ottimizzazione guidata motore di database.
Può risultare utile progettare un indice partizionato in modo indipendente dalla relativa tabella di base, ovvero un indice non allineato, se sono valide le condizioni seguenti:
La tabella di base non è stata partizionata.
La chiave dell'indice è univoca e non contiene la colonna di partizionamento della tabella.
Si desidera che la tabella di base sia inserita in join collocati con più tabelle che utilizzano colonne di join diverse.
Nota
Per attivare il cambio di partizione, è necessario che tutti gli indici della tabella siano allineati.
Per creare un indice partizionato, è consigliabile tenere presenti le informazioni riportate nelle sezioni seguenti.
Partizionamento di indici univoci
Durante il partizionamento di un indice univoco (cluster o non cluster), è necessario selezionare la colonna di partizionamento tra quelle utilizzate nella chiave dell'indice univoco.
Nota
Questa restrizione consente a SQL Server di dover esaminare una sola partizione per verificare che nella tabella non esista già un duplicato di un nuovo valore di chiave.
Se non è possibile inserire la colonna di partizionamento nella chiave univoca, per imporre l'unicità è necessario utilizzare invece un trigger DML.
Partizionamento di indici cluster
Per il partizionamento di un indice cluster, è necessario che la chiave di clustering includa la colonna di partizionamento. Per il partizionamento di un indice cluster non univoco, se la colonna di partizionamento non è specificata in modo esplicito nella chiave di clustering, SQL Server aggiunge per impostazione predefinita la colonna di partizionamento all'elenco delle chiavi dell'indice cluster. Se l'indice cluster è univoco, è necessario specificare in modo esplicito che la chiave dell'indice cluster include la colonna di partizionamento.
Partizionamento di indici non cluster
Per il partizionamento di un indice non cluster univoco, è necessario che la chiave dell'indice includa la colonna di partizionamento. Per il partizionamento di un indice non cluster e non univoco, SQL Server aggiunge per impostazione predefinita la colonna di partizionamento come una colonna non chiave dell'indice allo scopo di verificare che l'indice sia allineato con la tabella di base. SQL Server non aggiunge la colonna di partizionamento all'indice se è già presente.
Limiti di memoria e indici partizionati
I limiti di memoria possono influire sulle prestazioni o sulla capacità di SQL Server di creare un indice partizionato, specialmente se l'indice non è allineato alla relativa tabella di base o al relativo indice cluster eventualmente applicato.
Quando SQL Server esegue l'ordinamento per creare indici partizionati, crea innanzitutto una tabella di ordinamento per ogni partizione. Crea quindi le tabelle di ordinamento nel filegroup di ogni partizione o in tempdb se viene specificata l'opzione per gli indici SORT_IN_TEMPDB.
Per poter creare una tabella di ordinamento, è necessaria una quantità di memoria minima che varia in base alla tabella. Quando si crea un indice partizionato allineato alla relativa tabella di base, le tabelle di ordinamento vengono create una alla volta e la quantità di memoria necessaria è minore. Quando invece si crea un indice partizionato non allineato, le tabelle di ordinamento vengono create simultaneamente.
È pertanto necessaria una quantità di memoria sufficiente a gestire simultaneamente tali ordinamenti. Maggiore è il numero di partizioni e maggiore sarà la quantità di memoria necessaria. La dimensione minima di ogni tabella di ordinamento per ogni partizione è di 40 pagine, ognuna delle quali contiene 8 kilobyte. Ad esempio, per un indice partizionato non allineato con 100 partizioni è necessaria una quantità di memoria sufficiente per ordinare simultaneamente in modo seriale 4.000 (40 * 100) pagine. Se la memoria è disponibile, l'indice verrà creato anche se è possibile che l'operazione influisca negativamente sulle prestazioni. Se la memoria non è disponibile, l'indice non verrà creato. Per un indice partizionato allineato con 100 partizioni è invece necessaria solo la memoria per l'ordinamento di 40 pagine, perché gli ordinamenti non vengono eseguiti simultaneamente.
È possibile che i requisiti di memoria per gli indici allineati e non allineati siamo maggiori se SQL Server applica i gradi di parallelismo alla creazione dell'indice in un computer multiprocessore. Ciò dipende dal fatto che maggiori sono i gradi di parallelismo e maggiore sarà la quantità di memoria richiesta. Ad esempio, se SQL Server imposta i gradi di parallelismo su 4, per un indice partizionato non allineato con 100 partizioni sarà necessaria una quantità di memoria che consenta a quattro processori di ordinare simultaneamente 4.000 pagine, ovvero 16.000 pagine. Se l'indice partizionato è allineato, la quantità di memoria si riduce alla quantità necessaria a quattro processori per ordinare 40 pagine, ovvero 160 (4 * 40) pagine. Per ridurre in modo manuale i gradi di parallelismo, è possibile utilizzare l'opzione per gli indici MAXDOP. Per ulteriori informazioni, vedere Configurazione di operazioni parallele sugli indici.
Per ulteriori informazioni sulle operazioni di ordinamento eseguite da SQL Server durante la creazione degli indici, vedere tempdb e creazione dell'indice.