Narcis Miclaus

5 min de citit

Cum funcționează de fapt partiționarea în SQL Server

Un ghid practic prin partition functions, partition schemes, pattern-ul sliding window și capcanele despre care nimeni nu te avertizează până nu îți ia foc job-ul de noapte.

Dacă ai moștenit vreodată o tabelă de fapte cu două miliarde de rânduri și ți s-a spus “doar adaugă o partiție”, știi că documentația face să sune mai simplu decât e. Asta e versiunea pe care aș fi vrut să mi-o pună cineva în mână în prima zi.

Ce e partiționarea — și ce nu e

Partiționarea împarte o tabelă logică în multe bucăți fizice pe baza valorii unei coloane. Pentru oricine rulează un SELECT, tabela arată tot ca o singură tabelă. Pentru storage engine, e un șir de găleți, iar query-urile care filtrează pe coloana de partiționare trebuie să deschidă doar găleți de care au nevoie.

Două lucruri pe care partiționarea nu le e:

  • O victorie gratuită de performanță. O schemă de partiționare proastă e mai lentă decât deloc partiționare, pentru că acum optimizatorul trebuie să se ocupe de partition elimination peste toate celelalte.
  • Un înlocuitor pentru un index. Partiționarea te ajută să elimini bucăți întregi de tabelă. Indexii te ajută să găsești rânduri. Aproape întotdeauna le vrei pe ambele.

Marele câștig e operațional: poți muta un miliard de rânduri în sau dintr-o tabelă partiționată în milisecunde cu ALTER TABLE ... SWITCH, pentru că tot ce trebuie să facă SQL Server e să repuncteze un pointer de metadate. Niciun fel de mutare de date.

Cele trei piese

Orice tabelă partiționată în SQL Server e construită din trei lucruri:

  1. O partition function — definește granițele dintre găleți.
  2. O partition scheme — mapează acele găleți peste filegroup-uri.
  3. O tabelă sau un index construit pe schemă.

Iată cel mai mic exemplu end-to-end posibil. Partiționăm o tabelă Orders lunar pe OrderDate.

-- 1. Partition function: o graniță pe lună
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: trimite fiecare partiție pe PRIMARY
--    (în producție le-ai răspândi de obicei pe filegroup-uri diferite)
CREATE PARTITION SCHEME ps_OrdersByMonth
AS PARTITION pf_OrdersByMonth
ALL TO ([PRIMARY]);

-- 3. Tabela propriu-zisă, construită pe schemă
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)
);

Două lucruri de observat:

  • RANGE RIGHT vs RANGE LEFT. RANGE RIGHT pune valoarea de graniță în partiția superioară. '2025-02-01' trăiește în găleata din februarie, nu cea din ianuarie. Aproape întotdeauna asta vrei pentru partiționare pe date, pentru că poți descrie o lună ca un interval semi-deschis curat [start, next_start). Folosirea RANGE LEFT cu date creează bug-uri off-by-one care îți strică o după-amiază întreagă.
  • Coloana de partiționare trebuie să fie în cheia clustered. De aceea primary key-ul de mai sus e (OrderDate, OrderId) în loc de doar OrderId. Vei vedea asta în orice tutorial despre partiționare în SQL Server și prima dată pare mereu greșit. Obișnuiește-te.

Sliding window-ul

Pattern-ul care face partiționarea să-și merite plata e sliding window: în fiecare lună (sau zi, sau oră) adaugi o partiție nouă goală la marginea avansată și o scoți pe cea mai veche.

-- Adaugă o partiție nouă pentru luna viitoare.
-- Întâi spune-i schemei pe ce filegroup va trăi noua partiție.
ALTER PARTITION SCHEME ps_OrdersByMonth
    NEXT USED [PRIMARY];

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

-- Scoate cea mai veche lună într-o tabelă de staging pentru arhivă/ștergere.
-- Tabela de staging trebuie să aibă aceeași schemă, aceiași indecși și aceleași
-- constrângeri, și să trăiască pe același filegroup ca partiția pe care o muți.
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;

-- Acum colapsează partiția goală care a rămas în urmă.
ALTER PARTITION FUNCTION pf_OrdersByMonth ()
    MERGE RANGE ('2025-01-01');

Acel SWITCH e momentul magic. E o operație de metadate — milisecunde — indiferent dacă partiția conține 100 de rânduri sau 100 de milioane. Apoi poți să dai drop, să arhivezi sau să faci backup la Orders_Archive_2024_12 în liniște, fără să ții niciun lock pe tabela live.

Sfaturi care chiar îți salvează ziua

  • Ține mereu o partiție goală la fiecare capăt. Splituirea unei partiții care conține deja rânduri loghează fiecare mișcare de rând. Splituirea unei partiții goale e instantanee. Fă în așa fel încât “partiția goală a lunii viitoare” să fie ceva ce un job programat creează cu mult timp înainte.
  • Folosește $PARTITION ca să verifici înainte să acționezi. SELECT $PARTITION.pf_OrdersByMonth(OrderDate), COUNT(*) FROM dbo.Orders GROUP BY $PARTITION.pf_OrdersByMonth(OrderDate); îți spune exact câte rânduri trăiesc în fiecare partiție. Rulează asta înainte de orice SWITCH ca să nu poți niciodată să arhivezi din greșeală luna greșită.
  • Numai indecși aliniați. Fiecare index non-clustered pe o tabelă partiționată ar trebui partiționat la fel (ON ps_OrdersByMonth (OrderDate)). Indecșii nealiniați blochează complet partition switching-ul — SQL Server va respinge SWITCH-ul cu o eroare confuză și vei pierde 20 de minute aflând de ce.
  • Urmărește statisticile. Tabelele partiționate pot dezvolta statistici prost înclinate pe partiția “curentă” pentru că toate scrierile se duc acolo. Actualizează statisticile pe partiția live des, nu pe toată tabela.
  • Testează constrângerea. Constrângerea CHECK pe tabela de staging e ce îi spune SQL Server-ului că switch-ul e sigur. Greșește inegalitatea (<= în loc de < pentru o graniță RANGE RIGHT) și switch-ul eșuează. Lipește mereu data de graniță pe ambele părți ale function-ului și ale check constraint-ului — copy-paste, nu rescrie de mână.

Când să nu partiționezi

Partiționarea nu e gratis. Te costă în complexitatea planurilor de execuție, mai multe piese în mișcare la deploy și o întreagă clasă nouă de bug-uri (indecși nealiniați, statistici înclinate, check constraint-uri nepotrivite). Se plătește singură când:

  • Ai un pattern de acces clar bazat pe timp (luna trecută e fierbinte, lunile vechi sunt reci).
  • Îți trebuie bulk load-uri rapide sau bulk delete-uri rapide care să nu blocheze tabela.
  • Tabela e suficient de mare încât scanarea ei completă pentru arhivare doare — de obicei sute de milioane de rânduri sau mai mult.

Dacă tabela ta are 5 milioane de rânduri și o partiționezi pentru că cineva a citit un blog post, te rog nu o face. Un index clustered bun și o rescriere de query îți vor da aproape tot, fără niciuna dintre complicațiile operaționale.