Condividi tramite

Tabelle e indici partizionati

  • Articolo

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

SQL Server, Database SQL di Azure e Istanza gestita di SQL di Azure supportano il partizionamento di tabelle e indici. I dati di tabelle e indici partizionati vengono divisi in unità che possono essere distribuite in più filegroup all'interno di un database o archiviate in un singolo filegroup. Quando sono presenti più file in un filegroup, i dati vengono distribuiti tra i file usando l'algoritmo di riempimento proporzionale. I dati sono partizionati in senso orizzontale, in modo che per gruppi di righe venga eseguito il mapping in singole partizioni. Tutte le partizioni di un singolo indice o di una singola tabella devono trovarsi nello stesso database. La tabella o indice viene gestito come singola entità logica quando si eseguono query o aggiornamenti sui dati.

Prima di SQL Server 2016 (13.x) SP1, le tabelle e gli indici partizionati non erano disponibili in ogni edizione di SQL Server. Per un elenco delle funzionalità supportate dalle varie edizioni di SQL Server, vedere Edizioni e funzionalità supportate di SQL Server 2022. Le tabelle e gli indici partizionati sono disponibili in tutti i livelli di servizio di Database SQL di Azure e Istanza gestita di SQL di Azure.

Il partizionamento delle tabelle è disponibile anche nei pool SQL dedicati in Azure Synapse Analytics, con alcune differenze di sintassi. Per altre informazioni, vedere Partizionamento di tabelle nel pool SQL dedicato.

Importante

Il motore di database supporta un massimo di 15.000 partizioni per impostazione predefinita. Nelle versioni precedenti di SQL Server 2012 (11.x), il numero di partizioni era limitato a 1.000 per impostazione predefinita.

Vantaggi del partizionamento

Il partizionamento di tabelle o indici di grandi dimensioni può offrire i vantaggi in termini di gestibilità e prestazioni descritti di seguito.

  • È possibile trasferire o accedere a subset di dati in modo rapido ed efficiente, salvaguardando al contempo l'integrità della raccolta di dati. Operazioni quali il caricamento di dati da un sistema OLTP a un sistema OLAP richiedono ad esempio solo pochi secondi, al contrario di minuti o addirittura ore quando i dati non sono partizionati.

  • È possibile eseguire più rapidamente operazioni di manutenzione o conservazione dei dati su una o più partizioni. Le operazioni risultano più efficienti perché vengono applicate solo a subset di dati e non all'intera tabella. Ad esempio è possibile scegliere di comprimere i dati in una o più partizioni oppure ricompilare una o più partizioni di un indice o troncare i dati in una singola partizione. È anche possibile passare singole partizioni da una tabella a una tabella di archivio.

  • È possibile ottenere migliori prestazioni con le query in base alle tipologie eseguite con maggiore frequenza. Ad esempio, Query Optimizer può elaborare più velocemente le query di tipo equijoin tra due o più tabelle partizionate quando le colonne di partizionamento corrispondono alle colonne in cui le tabelle sono unite in join. Per altre informazioni, vedere Query più avanti.

È possibile migliorare le prestazioni abilitando l'escalation blocchi a livello di partizione invece che di intera tabella. Ciò consente di ridurre gli effetti di contesa dei blocchi per la tabella. Per ridurre la contesa tra blocchi consentendo l'escalation blocchi alla partizione, impostare l'opzione LOCK_ESCALATION dell'istruzione ALTER TABLE su AUTO.

Componenti e concetti

I termini seguenti sono applicabili al partizionamento di tabelle e indici.

Funzione di partizione

Una funzione di partizione è un oggetto di database che definisce la modalità con cui viene eseguito il mapping delle righe di una tabella o di un indice a un set di partizioni in base ai valori di una determinata colonna, denominata colonna di partizionamento. Ogni valore nella colonna di partizionamento è un input per la funzione di partizionamento, che restituisce un valore di partizione.

La funzione di partizione definisce il numero di partizioni e i limiti delle partizioni che la tabella avrà. Ad esempio, data una tabella che contiene i dati degli ordini di vendita, si può decidere di partizionare la tabella in 12 partizioni (mensili) basate su una colonna di tipo datetime, come ad esempio una data di vendita.

Un tipo di intervallo (LEFT o RIGHT) specifica come verranno inseriti i valori limite della funzione di partizione nelle partizioni risultanti:

  • Un intervallo LEFT specifica che il valore limite appartiene al lato sinistro dell’intervallo di valori limite quando i valori dell'intervallo vengono ordinati dal motore di database in ordine crescente da sinistra a destra. In altre parole, il valore limite più alto verrà incluso all'interno di una partizione.
  • Un intervallo RIGHT specifica che il valore limite appartiene al lato destro dell’intervallo di valori limite quando i valori dell'intervallo vengono ordinati dal motore di database in ordine crescente da sinistra a destra. In altre parole, il valore limite più basso verrà incluso in ogni partizione.

Se non è specificato LEFT o RIGHT, l'intervallo LEFT è di impostazione predefinita.

Ad esempio, la funzione di partizione seguente suddivide una tabella o un indice in 12 partizioni, una per ogni mese dei valori di un anno in una colonna datetime. Viene usato un intervallo RIGHT, che indica che i valori limite verranno usati come valori limite inferiori in ogni partizione. Gli intervalli RIGHT sono spesso più semplici da usare quando si partiziona una tabella in base a una colonna dati di tipo datetime o datetime2, poiché le righe con un valore di mezzanotte verranno archiviate nella stessa partizione delle righe con valori successivi nello stesso giorno. Analogamente, se si usa il tipo di dati date e si usano partizioni di un mese o più, un intervallo RIGHT mantiene il primo giorno del mese nella stessa partizione dei giorni successivi dello stesso mese. Ciò facilita l’accurata eliminazione delle partizioni durante l'esecuzione di query sui dati di un intero giorno.

CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)  
AS RANGE RIGHT FOR VALUES ('2022-02-01', '2022-03-01', '2022-04-01',  
               '2022-05-01', '2022-06-01', '2022-07-01', '2022-08-01',   
               '2022-09-01', '2022-10-01', '2022-11-01', '2022-12-01');

La tabella seguente illustra come viene suddivisa una tabella o un indice che usa questa funzione di partizione nella colonna di partizionamento datecol. Il 1° febbraio è il primo punto limite stabilito nella funzione, quindi funge da limite inferiore della partizione 2.

Partizione 1 2 ... 11 12
Valori datecol<2022-02-01 12:00AM datecol>= 2022-02-01 12:00AM AND datecol<2022-03-01 12:00AM datecol>= 2022-11-01 12:00AM AND col1<2022-12-01 12:00AM datecol>= 2022-12-01 12:00AM

Per RANGE LEFT e RANGE RIGHT, la partizione all'estrema sinistra ha il valore minimo del tipo di dati come limite inferiore e la partizione più a destra ha il valore massimo del tipo di dati come limite superiore.

Altri esempi di funzioni di partizione LEFT e RIGHT sono disponibili in CREATE PARTITION FUNCTION (Transact-SQL).

Schema di partizione

Uno schema di partizione è un oggetto di database che esegue il mapping delle partizioni di una funzione di partizione a un singolo filegroup o a più filegroup.

Trovare la sintassi di esempio per creare schemi di partizione in CREATE PARTITION SCHEME (Transact-SQL).

Filegroup

Il motivo principale per cui inserire le partizioni in più filegroup è che in questo modo è possibile eseguire operazioni di backup e ripristino sulle partizioni in modo indipendente. in quanto è possibile eseguire backup in filegroup singoli. Quando si usa l'archiviazione a livelli, l'uso di più filegroup consente di assegnare partizioni specifiche a livelli di archiviazione specifici, ad esempio per inserire partizioni meno recenti e a cui si accede meno frequentemente in una risorsa di archiviazione più lenta e meno costosa. Tutti gli altri vantaggi del partizionamento si applicano indipendentemente dal numero di filegroup usati o dal posizionamento delle partizioni in filegroup specifici.

La gestione di file e filegroup per le tabelle partizionate può complicare significativamente le attività amministrative nel tempo. Se le procedure di backup e ripristino non traggono vantaggio dall'uso di più filegroup, è consigliabile usare un singolo filegroup per tutte le partizioni. Le stesse regole per la progettazione di file e filegroup si applicano agli oggetti partizionati applicati a oggetti non partizionati.

Nota

Il partizionamento non è completamente supportato in database SQL di Azure. Poiché solo il filegroup PRIMARY è supportato in database SQL di Azure, tutte le partizioni devono essere inserite nel filegroup PRIMARY.

Il codice di esempio per creare filegroup per SQL Server e Istanza gestita di SQL di Azure è disponibile in Opzioni file e filegroup ALTER DATABASE (Transact-SQL).

Colonna di partizionamento

Colonna di una tabella o di un indice utilizzata da una funzione di partizione per partizionare la tabella o l'indice. Quando si seleziona una colonna di partizionamento si considera quanto segue:

  • Le colonne calcolate che partecipano a una funzione di partizione devono essere create in modo esplicito come PERSISTED.
    • Poiché è possibile usare una sola colonna come colonna di partizione, in alcuni casi può essere utile concatenare più colonne con una colonna calcolata.
  • È possibile utilizzare come colonna di partizionamento le colonne di tutti i tipi di dati che possono essere utilizzati come colonne chiave di indice, eccetto timestamp.
  • Le colonne di tipo di dati LOB (Large Object) come ntext, text, image, xml, varchar(max), nvarchar(max), e varbinary(max) non possono essere specificate.
  • Non è possibile specificare colonne di tipo definito dall'utente Common Language Runtime (CLR) di Microsoft .NET Framework né colonne di tipo di dati alias.

Per partizionare un oggetto, specificare lo schema di partizione e la colonna di partizionamento nelle istruzioni CREATE TABLE (Transact-SQL), ALTER TABLE (Transact-SQL) e CREATE INDEX (Transact-SQL).

Quando si crea un indice non cluster, se non si specifica partition_scheme_name o filegroup e la tabella è partizionata, l'indice viene posizionato nello stesso schema di partizione, usando la stessa colonna di partizionamento della tabella sottostante. Per modificare la modalità di partizionamento di un indice esistente, usare CREATE INDEX con la clausola DROP_EXISTING. In questo modo è possibile partizionare un indice non partizionato, rendere un indice partizionato non partizionato o modificare lo schema di partizione dell'indice.

Indice allineato

Indice basato sullo stesso schema di partizione della relativa tabella corrispondente. Se una tabella e i relativi indici sono allineati, il motore di database è in grado di cambiare le partizioni dentro o fuori dalla tabella in modo rapido ed efficiente, mantenendo inalterata la struttura delle partizioni della tabella e degli indici. Per poter essere allineato alla relativa tabella di base, non è necessario che un indice sia inserito nella stessa funzione di partizione denominata. Tuttavia, la funzione di partizione dell'indice e della tabella di base deve essere essenzialmente uguale, in quanto:

  • Gli argomenti delle funzioni di partizione devono avere lo stesso tipo di dati.
  • Definiscono lo stesso numero di partizioni.
  • Definiscono gli stessi valori limite per le partizioni.

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, il motore di database 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. Per altre informazioni sugli indici cluster e sull'architettura degli indici, vedere Linee guida per la progettazione di indici cluster.

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, il motore di database aggiunge per impostazione predefinita la colonna di partizionamento come una colonna non chiave (inclusa) dell'indice allo scopo di assicurarsi che l'indice sia allineato con la tabella di base. Il motore di database non aggiunge la colonna di partizionamento all'indice se è già presente. Per altre informazioni sugli indici non cluster e sull'architettura degli indici, vedere Linee guida per la progettazione di indici cluster.

Indice non allineato

Un indice non allineato viene partizionato in modo diverso dalla tabella corrispondente. Ciò significa che l'indice presenta uno schema di partizione diverso oppure che si trova in un filegroup o set di filegoup separato rispetto alla tabella di base. La progettazione di un indice partizionato non allineato può risultare utile nei casi 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.

Eliminazione delle partizioni

Processo mediante il quale Query Optimizer accede solo alle partizioni rilevanti per soddisfare i criteri di filtro della query.

Altre informazioni sull'eliminazione delle partizioni e sui concetti correlati in Miglioramenti dell'elaborazione di query su tabelle e indici partizionati.

Limiti

  • L'ambito di una funzione e di uno schema di partizione è limitato al database in cui sono stati creati. All'interno del database le funzioni di partizione si trovano in uno spazio dei nomi separato rispetto ad altre funzioni.

  • Se le righe di una tabella partizionata hanno valori NULL nella colonna di partizionamento, queste righe vengono posizionate nella partizione più a sinistra. Tuttavia, se è specificato NULL come primo valore limite ed è indicato il RANGE RIGHT nella definizione di funzione di partizione, la partizione più a sinistra rimane vuota e i valori NULL vengono collocati nella seconda partizione.

Linee guida relative alle prestazioni

Il motore di database supporta fino a 15.000 partizioni per tabella o indice. Tuttavia, l'uso di oltre 1.000 partizioni ha implicazioni su memoria, indici partizionati, operazioni, comandi DBCC e query. In questa sezione vengono descritte le implicazioni relative alle prestazioni dell'uso di oltre 1.000 partizioni e vengono illustrate soluzioni alternative a seconda delle necessità.

Con un massimo di 15.000 partizioni consentite per ogni tabella o indice partizionato, è possibile archiviare i dati per durate lunghe in una singola tabella. Tuttavia, è consigliabile mantenere i dati solo per il tempo strettamente necessario e preservare l'equilibrio tra livello di prestazioni e il numero di partizioni.

Utilizzo della memoria e linee guida

È consigliabile disporre di almeno 16 GB di RAM se si utilizza un numero elevato di partizioni. Se il sistema non dispone di memoria sufficiente, le istruzioni DML (Data Manipulation Language) e DDL (Data Definition Language) nonché altri tipi di operazioni potrebbero avere esito negativo. Nei sistemi provvisti di 16 GB di RAM in cui vengono eseguiti numerosi processi che richiedono un'elevata quantità di memoria, quest'ultima potrebbe esaurirsi in caso di operazioni su un numero elevato di partizioni. Pertanto, la probabilità che si verifichino problemi di prestazioni o di memoria si riduce in funzione della quantità di memoria disponibile oltre i 16 GB.

I limiti di memoria possono influire sulle prestazioni o sulla capacità di motore di database di compilare un indice partizionato, Specialmente se l'indice non è allineato alla relativa tabella di base o al relativo indice cluster, se la tabella dispone già di indice cluster.

In SQL Server e Istanza gestita di SQL di Azure è possibile aumentare l'opzione di configurazione del server index create memory (KB). Per altre informazioni, vedere Configurare l'opzione di configurazione del server index create memory. Per database SQL di Azure, prendere in considerazione l'aumento temporaneo o permanente dell'obiettivo del livello di servizio per il database nel portale di Azure per assegnare più memoria.

Operazioni relative agli indici partizionati

La creazione e la ricompilazione di indici non allineati per una tabella con oltre 1.000 partizioni sono possibili, ma non supportate. Questo tipo di operazioni può causare riduzioni delle prestazioni e un eccessivo consumo della memoria.

La creazione e la ricompilazione di indici allineati possono richiedere più tempo di esecuzione a seconda del numero di partizioni. È consigliabile non eseguire più comandi di creazione e ricompilazione degli indici contemporaneamente poiché potrebbero verificarsi problemi di prestazioni e memoria.

Quando il motore di database esegue l'ordinamento per compilare indici partizionati, compila innanzitutto una tabella di ordinamento per ogni partizione. In seguito compila le tabelle di ordinamento nel filegroup di ogni partizione o in tempdb se è stata specificata l'opzione SORT_IN_TEMPDB per gli indici. Per poter compilare una tabella di ordinamento, è necessaria una quantità di memoria minima che varia in base alla tabella. Quando si compila un indice partizionato allineato alla relativa tabella di base, le tabelle di ordinamento vengono compilate una alla volta e la quantità di memoria necessaria è minore. Quando invece si compila un indice partizionato non allineato, le tabelle di ordinamento vengono compilate 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à compilato anche se è possibile che l'operazione influisca negativamente sulle prestazioni. Se la memoria non è disponibile, l'indice non verrà compilato. 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 siano maggiori se il motore di database applica il parallelismo della query all'operazione di compilazione in un computer multiprocessore. Ciò dipende dal fatto che maggiore è il grado di parallelismo (DOP), maggiore sarà la quantità di memoria richiesta. Ad esempio, se il motore di database imposta il grado 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 manualmente i gradi di parallelismo, è possibile utilizzare l'opzione di indice MAXDOP.

Comandi DBCC

In presenza di un numero elevato di partizioni, l'esecuzione di comandi DBCC come DBCC CHECKDB e DBCC CHECKTABLE può richiedere un tempo proporzionalmente maggiore.

Query

Dopo la partizione di una tabella o un indice, le query per le quali si utilizza l'eliminazione di partizioni possono offrire prestazioni analoghe o migliorate con un numero elevato di partizioni. L'esecuzione di query che non utilizzano l'eliminazione di partizioni può richiedere più tempo a seconda del numero di partizioni.

Si supponga ad esempio che una tabella contenga 100 milioni di righe e le colonne A, Be C.

  • Nello scenario 1, la tabella è divisa in 1.000 partizioni per la colonna A.
  • Nello scenario 2, la tabella è divisa in 10.000 partizioni per la colonna A.

Una query eseguita su tale tabella con clausola WHERE per filtrare la colonna A eseguirà l'eliminazione di partizioni e analizzerà una sola partizione. Se eseguita nello scenario 2, la stessa query risulta più rapida in quanto è presente un numero minore di righe da analizzare in una partizione. Una query con clausola WHERE per filtrare la colonna B analizzerà tutte le partizioni. Nello scenario 1, tale query viene eseguita in meno tempo rispetto allo scenario 2 in quanto sono presenti meno partizioni da analizzare.

Le query che utilizzano operatori quali TOP o MAX/MIN su colonne diverse dalla colonna di partizionamento possono comportare prestazioni ridotte con il partizionamento perché tutte le partizioni devono essere valutate.

Analogamente, una query che esegue una ricerca a riga singola o un'analisi di intervallo di piccole dimensioni richiederà più tempo di una tabella partizionata rispetto a una tabella non partizionata se il predicato di query non include la colonna di partizionamento. Questo perché sarà necessario eseguire tutte le ricerche o le analisi in quanto sono presenti partizioni. Per questo motivo, il partizionamento raramente migliora le prestazioni nei sistemi OLTP in cui sono frequenti tali query.

Se si eseguono spesso query che comportano un equijoin tra due o più tabelle partizionate, è consigliabile che le relative colonne di partizionamento coincidano con le colonne in cui vengono unite le tabelle. È inoltre consigliabile collocare le tabelle o i relativi indici. Ciò significa che verrà utilizzata la stessa funzione di partizione denominata oppure funzioni di partizione diverse ma essenzialmente identiche, in quanto:

  • Hanno lo stesso numero di parametri utilizzati per il partizionamento e i parametri corrispondenti hanno gli stessi tipi di dati.
  • Definiscono lo stesso numero di partizioni.
  • Definiscono gli stessi valori limite per le partizioni.

In questo modo, Query Optimizer può elaborare più rapidamente il join, in quanto è possibile unire in join le partizioni stesse. Se una query unisce in join due tabelle che non sono collocate o partizionate nel campo di join, la presenza delle partizioni potrebbe in realtà rallentare l'elaborazione delle query anziché accelerarla.

Può risultare utile usare $PARTITION in alcune query. Altre informazioni sono disponibili in $PARTITION (Transact-SQL).

Per altre informazioni sulla gestione delle partizioni nell'elaborazione delle query, inclusa la strategia di esecuzione di query parallele per tabelle e indici partizionati e procedure consigliate aggiuntive, vedere Miglioramenti dell'elaborazione delle query su tabelle e indici partizionati.

Modifiche di comportamento nel calcolo delle statistiche durante operazioni su indici partizionati

In database SQL di Azure, Istanza gestita di SQL di Azure e SQL Server 2012 (11.x) e versioni successive, le statistiche non vengono create analizzando tutte le righe della tabella quando viene creato o ricompilato un indice partizionato. Query Optimizer utilizza invece l'algoritmo di campionamento predefinito per generare statistiche.

Dopo l'aggiornamento di un database con indici partizionati da una versione di SQL Server inferiore a 2012 (11.x), è possibile notare una differenza nei dati dell'istogramma relativi a questi indici. Tale cambiamento potrebbe influire sulle prestazioni di query. Per ottenere statistiche sugli indici partizionati analizzando tutte le righe nella tabella, usare CREATE STATISTICS o UPDATE STATISTICS con la clausola FULLSCAN.

Contenuto correlato

Per altre informazioni sulle tabelle partizionate e sulle strategie di indice, vedere gli articoli seguenti: