Narcis Miclaus

5 min di lettura

Come funziona davvero il partizionamento in SQL Server

Una guida pratica alle partition function, ai partition scheme, al pattern sliding window e alle insidie che nessuno ti racconta finché il job notturno non va a fuoco.

Se hai mai ereditato una tabella dei fatti con due miliardi di righe e ti hanno detto “basta che aggiungi una partizione”, sai che la documentazione lo fa sembrare più semplice di quanto sia. Questa è la versione che avrei voluto qualcuno mi mettesse in mano il primo giorno.

Cos’è il partizionamento — e cosa non è

Il partizionamento divide una tabella logica in tante porzioni fisiche in base al valore di una colonna. Per chiunque faccia una SELECT, la tabella sembra ancora una sola tabella. Per lo storage engine è una fila di secchielli, e le query che filtrano sulla colonna di partizionamento devono aprire solo i secchielli che servono.

Due cose che il partizionamento non è:

  • Una vittoria gratuita di performance. Uno schema di partizionamento sbagliato è più lento di nessun partizionamento, perché ora l’optimizer deve gestire la partition elimination in cima a tutto il resto.
  • Un sostituto degli indici. Il partizionamento ti aiuta a escludere interi blocchi della tabella. Gli indici ti aiutano a trovare le righe. Quasi sempre vuoi entrambi.

La grande vittoria è operativa: puoi spostare un miliardo di righe dentro o fuori da una tabella partizionata in millisecondi con ALTER TABLE ... SWITCH, perché tutto quello che SQL Server deve fare è ri-puntare un puntatore di metadati. Nessun movimento di dati.

I tre pezzi

Ogni tabella partizionata in SQL Server è costruita da tre cose:

  1. Una partition function — definisce i confini tra i secchielli.
  2. Un partition scheme — mappa quei secchielli sui filegroup.
  3. Una tabella o un indice costruiti sullo scheme.

Ecco l’esempio end-to-end più piccolo possibile. Stiamo partizionando una tabella Orders per mese su OrderDate.

-- 1. Partition function: un confine al mese
CREATE PARTITION FUNCTION pf_OrdersByMonth (DATETIME2(0))
AS RANGE RIGHT FOR VALUES (
    '2025-01-01', '2025-02-01', '2025-03-01',
    '2025-04-01', '2025-05-01', '2025-06-01'
);

-- 2. Partition scheme: manda ogni partizione su PRIMARY
--    (in produzione di solito le distribuiresti su filegroup diversi)
CREATE PARTITION SCHEME ps_OrdersByMonth
AS PARTITION pf_OrdersByMonth
ALL TO ([PRIMARY]);

-- 3. La tabella vera e propria, costruita sullo scheme
CREATE TABLE dbo.Orders (
    OrderId     BIGINT       NOT NULL,
    CustomerId  INT          NOT NULL,
    OrderDate   DATETIME2(0) NOT NULL,
    Amount      DECIMAL(18,2) NOT NULL,
    CONSTRAINT pk_Orders PRIMARY KEY CLUSTERED (OrderDate, OrderId)
        ON ps_OrdersByMonth (OrderDate)
);

Due cose da notare:

  • RANGE RIGHT vs RANGE LEFT. RANGE RIGHT mette il valore di confine nella partizione superiore. '2025-02-01' vive nel secchiello di febbraio, non in quello di gennaio. Quasi sempre è quello che vuoi per il partizionamento per data, perché puoi descrivere un mese come un intervallo semiaperto pulito [start, next_start). Usare RANGE LEFT con le date crea bug off-by-one che ti rovinano un pomeriggio intero.
  • La colonna di partizionamento deve stare nella chiave clustered. Per questo la primary key qui sopra è (OrderDate, OrderId) invece che solo OrderId. Lo vedrai in ogni tutorial sul partizionamento di SQL Server e la prima volta sembra sempre sbagliato. Fattene una ragione.

Lo sliding window

Il pattern che fa sì che il partizionamento si guadagni il pane è lo sliding window: ogni mese (o giorno, o ora), aggiungi una nuova partizione vuota sul fronte avanzato e fai uscire la più vecchia.

-- Aggiungi una nuova partizione per il mese prossimo.
-- Prima dici allo scheme su quale filegroup vivrà la nuova partizione.
ALTER PARTITION SCHEME ps_OrdersByMonth
    NEXT USED [PRIMARY];

ALTER PARTITION FUNCTION pf_OrdersByMonth ()
    SPLIT RANGE ('2025-07-01');

-- Sposta il mese più vecchio in una tabella di staging per archivio/cancellazione.
-- La tabella di staging deve avere lo stesso schema, gli stessi indici e gli
-- stessi vincoli, e vivere sullo stesso filegroup della partizione che stai
-- spostando.
CREATE TABLE dbo.Orders_Archive_2024_12 (
    OrderId     BIGINT       NOT NULL,
    CustomerId  INT          NOT NULL,
    OrderDate   DATETIME2(0) NOT NULL,
    Amount      DECIMAL(18,2) NOT NULL,
    CONSTRAINT pk_Orders_Archive_2024_12 PRIMARY KEY CLUSTERED (OrderDate, OrderId),
    CONSTRAINT ck_Orders_Archive_2024_12_Range
        CHECK (OrderDate >= '2024-12-01' AND OrderDate < '2025-01-01')
);

ALTER TABLE dbo.Orders SWITCH PARTITION 1 TO dbo.Orders_Archive_2024_12;

-- Ora collassa la partizione vuota che è rimasta indietro.
ALTER PARTITION FUNCTION pf_OrdersByMonth ()
    MERGE RANGE ('2025-01-01');

Quel SWITCH è il momento magico. È un’operazione di metadati — millisecondi — indipendentemente dal fatto che la partizione contenga 100 righe o 100 milioni. Poi puoi droppare, archiviare o fare backup di Orders_Archive_2024_12 con calma, senza tenere alcun lock sulla tabella in produzione.

Consigli che ti salvano davvero la vita

  • Tieni sempre una partizione vuota a ogni estremo. Splittare una partizione che contiene già righe logga ogni movimento di riga. Splittare una partizione vuota è istantaneo. Fai in modo che “la partizione vuota del mese prossimo” sia qualcosa che un job schedulato crea con largo anticipo.
  • Usa $PARTITION per verificare prima di agire. SELECT $PARTITION.pf_OrdersByMonth(OrderDate), COUNT(*) FROM dbo.Orders GROUP BY $PARTITION.pf_OrdersByMonth(OrderDate); ti dice esattamente quante righe vivono in ogni partizione. Lancialo prima di ogni SWITCH così non potrai mai archiviare il mese sbagliato per sbaglio.
  • Solo indici allineati. Ogni indice non clustered su una tabella partizionata dovrebbe essere partizionato nello stesso modo (ON ps_OrdersByMonth (OrderDate)). Indici non allineati bloccano completamente il partition switching — SQL Server rifiuterà lo SWITCH con un errore confuso e perderai 20 minuti per scoprire perché.
  • Tieni d’occhio le statistiche. Le tabelle partizionate possono sviluppare statistiche brutte sulla partizione “corrente” perché tutte le scritture vanno lì. Aggiorna le statistiche sulla partizione live spesso, non su tutta la tabella.
  • Testa il vincolo. Il CHECK constraint sulla tabella di staging è ciò che dice a SQL Server che lo switch è sicuro. Sbaglia la disuguaglianza (<= invece di < per un confine RANGE RIGHT) e lo switch fallisce. Incolla sempre la data di confine in entrambi i lati della tua function e del tuo check constraint — copia-incolla, non riscrivere a mano.

Quando non partizionare

Il partizionamento non è gratis. Ti costa complessità nei piani di esecuzione, più parti in movimento al deploy, e tutta una nuova classe di bug (indici non allineati, statistiche sbilanciate, check constraint sbagliati). Si ripaga quando:

  • Hai un pattern di accesso chiaramente basato sul tempo (l’ultimo mese è caldo, i mesi vecchi sono freddi).
  • Ti servono bulk load veloci o bulk delete veloci che non lockino la tabella.
  • La tabella è abbastanza grande che fare la scansione completa per archiviare fa male — di solito significa centinaia di milioni di righe o più.

Se la tua tabella ha 5 milioni di righe e la stai partizionando perché qualcuno ha letto un blog post, per favore non farlo. Un buon indice clustered e una query riscritta ti porteranno quasi tutto il risultato, senza nessuna delle complicazioni operative.