1

VII - MS- SQL Data Partitioning

Aspecte legate de modul de organizare al fişierelor bazelor de date

O bază de date poate fi compusă din mai multe fişiere (.mdf, .ndf, .ldf).

Se recomandă ca fişierele ce compun filegroupul primary (fişierele .mdf) să nu fie folosite pentru găzduirea obiectelor asupra cărora sunt aduse modificări în mod repetat (tabele, indecşi, etc.). Minimizând procesele de scriere asupra acestor fişiere (care totodată definesc şi starea bazei de date), se reduce riscul coruperii acestora în cazul întreruperilor neprevăzute sau din cauza problemelor hardware.

Fişierele cu extensia .ndf (secondary database file) sunt folosite în cazul optimizărilor şi vor găzdui tabelele şi indecşii bazei de date. Fişierele .ndf pot fi găzduite pe discuri diferite îmbunătăţind astfel timpii de răspuns ai SGBD-ului.

Transaction log files (.ldf) sunt cunoscute ca fiind fişierele asupra cărora MSSQL efectuează scrieri secvenţiale. Se recomandă ca aceste fişiere să fie izolate de fişierele filegroupurilor primary & secondary.

Decizia de a distribui baza de date pe mai multe fişiere (.mdf, .ndf, .ldf) este dictată în principal de estimări referitoare la dimensiunea bazei de date, frecvenţa interogărilor, frecvenţa operaţiilor de actualizare, etc.

Nu consider că este greşit a crea o bază de date compusă doar din două fişiere (.mdf şi .ldf), dar susţin ideea că în etapa de design a bazei de date este bine a organiza tabelele şi indecşii pe mai multe filegroupuri astfel încât să oferim posibilitatea administratorului serverului de baze de date să distribuie fişierele pe mai multe discuri.

În prima versiune, baza de date COMANDA este alcătuită din două fişiere (.mdf şi .ldf).

În a doua versiune, baza de date COMANDA este compusă din mai multe fişiere .mdf, .ndf, .ldf.

2

Aşadar, în a doua versiune, baza de date COMANDA este organizată pe mai multe fişiere, iar tabelele şi indecşii sunt grupaţi pe filegroupuri separate (în funcţie de dinamica înregistrărilor). La o astfel de organizare se poate ajunge chiar şi plecând de la o bază de date (aflată în producţie) distribuită doar pe două fişiere (.mdf şi .ldf), însă în prealabil administratorul SGBD-ului va trebui să efectueze o analiză a structurii bazei de date.

Pană în acest punct am vrut să subliniez câteva aspecte legate de modul de organizare al fişierelor bazelor de date. Mai departe am să demonstrez beneficiile partiţionării tabelelor. Am să evit pe cât de mult teoria (poate fi găsită pe MSDN, SQL Server Books Online, Training Kit, etc.) şi am să mă axez pe un exemplu concret.

Baza de date COMANDA (219.30 MB) este populată astfel:

Agent: 305 înregistrări (0.023 MB);

Client: 306 înregistrări (0.023 MB);

Produs: 17 înregistrări (0.008 MB);

Comanda: 500 000 înregistrări (84.992 MB);

LinieComanda: 2 426 916 înregistrări (127.258 MB);

În cazul în care doriţi să urmaţi exemplul, puteţi descărca o copie a bazei de date, sau puteţi popula baza de date cu ajutorul MSSQLDataPartitioning_02.sql (presupune existenţa celei de-a doua versiuni a bazei de date COMANDA).

3

Înainte de a efectua partiţionarea tabelelor Comanda şi LinieComanda, am verificat viteza de execuţie a două interogări SQL.

Fraza SELECT-SQL Interval execuţie (secunde)

Totalul vânzărilor produselor comercializate în 2009 13

Totalul vânzărilor produselor comandate în 2009 7

Partiţionarea permite distribuţia unui tabel, index, index view pe mai multe filegroupuri, împărţirea efectuându-se pe baza unor reguli stabilite de administratorul SGBD-ului. În forma actuală, tabelele Comanda şi LinieComanda sunt găzduite de filegroupul SECONDARY2.

Datorită numărului relativ mare de înregistrări al tabelelor Comanda şi LinieComanda, ne propunem să efectuăm partiţionarea pe baza anului în care comenzile au fost create.

Partiţionarea presupune:

Definirea unei funcţii de parţionare – prin intermediul căreia se definesc limitele de partiţionare;

Definirea / stabilirea filegroupurilor folosite la partiţionare;

Definirea schemei de partiţionare – pe baza schemei de partiţionare vor fi specificate filegroupurile implicate în partiţionare precum şi funcţia de partiţionare pe baza căreia datele vor fi distribuite.

Având în vedere că în baza de date avem comenzi din anii 2008, 2009, 2010, vom crea o funcţie de partiţionare ale cărei limite vor defini trei partiţii, partiţii ce vor fi mapate pe trei filegrupuri.

Una, mai multe, sau chiar toate partiţiile pot fi mapate pe un singur filegroup. În acest exemplu am decis ca fiecare partiţie să fie găzduită pe filegroupuri separate.

4

Partiţionarea tabelei Comanda

În cazul partiţionării tabelei Comanda se va crea o funcţie de partiţionare cu limitele incluse în partiţia din dreapta. Partiţionarea tabelei se va face în funcţie de data la care comanda a fost creată (tip de date DATETIME2).

Pe baza acestei funcţii de partiţionare vom putea partiţiona comenzile (înregistrările) per ani.

Deci conform acestei funcţii:

Partiţia numărul Minimum Maximum

1 0001-01-01 00:00.0000000 2008-12-31 23:59:59.9999999

2 2009-01-01 00:00.0000000 2009-12-31 23:59:59.9999999

3 2010-01-01 00:00.0000000 9999-12-31 23:59:59.9999999

sys.partition_range_values, sys.partition_functions, sys.partition_parameters, sys.types MSSQLDataPartitioning_06.sql

Înainte de a crea schema de partiţionare (a defini cum anume se va face efectiv partiţionarea) este necesar a crea filegroupurile. Având în vedere că vor fi trei partiţii, am decis crearea a trei filegroupuri.

5

sys.filegroups, sys.database_files

Creăm schema de partiţionare.

6

Deoarece tabela Comanda deja conţine înregistrări va fi necesar recrearea indexului clustered – pentru ca partiţionarea să se realizeze efectiv.

Înainte de a recrea indexul, haideţi să vedem care este structura actuală.

Aşadar, tabela Comanda este încă găzduită de fişierele ce compun filegroupul SECONDARY2. Dacă ne uităm şi la dimensiunea pe disc a fişierelor COMANDA_SECONDARY3_DATA1.ndf, COMANDA_SECONDARY4_DATA1.ndf, COMANDA_SECONDARY5_DATA1.ndf vom observa că acestea au rămas la dimensiunea specificată în momentul creării lor (512 KB).

Dacă vom recrea indexul clustered va trebui să avem următoarea distribuţie.

7

Deci conform rezultatului obţinut putem afirma că tabela Comenzi va fi partiţionată astfel:

Partiţia numărul

Minimum Maximum Detalii

1 0001-01-01 00:00.0000000

2008-12-31 23:59:59.9999999

25000 de înregistrări (ultima comandă din an: 25000)

2 2009-01-01 00:00.0000000

2009-12-31 23:59:59.9999999

375000 de înregistrări (ultima comandă din an: 400000)

3 2010-01-01 00:00.0000000

9999-12-31 23:59:59.9999999

100000 de înregistrări (ultima comandă din an: 500000)

Ştergerea indexului clustered PK_Comanda.

Una din condiţiile unei chei primare este aceea de a nu conţine valori nule printre valorile sale. Conform comenzii DDL de creare a tabelei Comanda, indexul clustered era realizat pe baza valorilor atributului ce definea cheia primară (atributul CodComanda).

În momentul în care se partiţionează o tabelă, indexul clustered obligatoriu va avea în componenţă atributul pe baza căruia se va face partiţionarea. Implicit un index clustered are o singură partiţie. Atunci când o tabelă este împărţită pe mai multe partiţii, indexul clustered este la rândul său partiţionat, fiecare partiţie având structura B-tree pentru datele din respectiva partiţie.

În cazul nostru, atributul pe baza căruia se efectuează partiţionarea este DataComanda – deci, indexul de tip clustered va trebui să aibă în componenţă şi acest atribut.

8

Totuşi, cheia primară trebuie să identifice în mod unic o înregistrare dintr-o tabelă, iar atributul CodComanda este

ideal în cazul tabelei Comanda (în plus de asta, între tabela Comanda şi tabela LinieComanda este stabilită relaţia 1 - n).

Deci, va trebui să redefinim cheia primară - dar de data aceasta vom avea grijă ca indexul cheii primare să fie nonclustered. Indexul cheii primare îl vom găzdui în filegroupul SECONDARY 2 (indexul nu va putea fi partiţionat deoarece nu are în componenţa sa atributul DataComanda).

Indexul realizat în urma redefinirii cheii primare va avea o selectivitate ridicată - deci, în cazul operaţiilor SELECT-SQL, SGBD-ul va prefera să se folosească de acest index (în special în cazul joncţiunilor cu tabela copil – LinieComanda).

Având în vedere că acest index nu îl putem partiţiona pe mai multe partiţii, se recomandă găzduirea lui într-un filegroup dedicat (filegroup ale cărui fişiere să fie găzduite pe discuri cu performanţe ridicate în privinţa operaţiunilor scriere/citire). Pentru acest exemplu am decis găzduirea lui în filegroupul SECONDARY2 (deoarece tabelele Comanda şi LinieComanda vor fi partiţionate şi vor "face loc", permiţând astfel să transformăm filegroupul SECONDARY 2 într-un filegroup dedicat indecşilor).

Următorul pas constă în crearea indexului clustered, moment în care va avea loc şi mutarea datelor din filegroupul SECONDARY2 în filegroupurile schemei de partiţionare PartitionComanda (deci procesul de recreare a indexului va fi ceva mai costisitor în privinţa consumului de resurse).

Am să fac o mică discuţie pe baza acestui index. Este evident de ce anume am ales atributul DataComanda în componenţa indexului clustered, dar oare o combinaţie de atribute (care să includă şi atributul CodComanda) nu ar fi mai avantajoasă?

Răspuns: Depinde! Depinde foarte mult de scopul tabelei partiţionate, de tipul de operaţii care se execută asupra datelor respectivei tabele, de frecvenţa lor, etc.

Indecşii îmbunătăţesc considerabil accesul la date (SELECT-SQL), dar dacă e să cădem în extrema creării multor indecşi, eventual indexarea pe baza unui număr mare de atribute, etc., cu siguranţă vom reduce performanţele operaţiunilor INSERT, UPDATE, DELETE, BULK INSERT, BCP.

Să presupunem că am fi ales combinaţia de atribute CodComanda, DataComanda.

9

Cel puţin pe baza frazelor SELECT SQL ale căror performanţă a fost testată în cadrul acestui post, cu siguranţă acest

index nu ar aduce niciun fel de imbunătăţiri interogărilor, asta deoarece indexul nonclustered PK_Comanda creat în urma redefinirii cheii primare va avea o selectivitate mai ridicată decât IDX_Comanda.

Redefinim constrângerea referenţială FK_LinieComanda_Comanda (care am fost nevoiţi să o ştergem înainte de a redefini indexul clustered al tabelei Comanda).

Vizualizăm modul în care tabela Comanda a fost partiţionată.

Partiţionarea tabelei LinieComanda

Intenţionat am ales această structură a tabelelor bazei de date - pentru uşurinţa exemplificării şi înţelegerii, abordarea unor aspecte particulare, precum şi discutarea cazurilor generale.

Până în acest punct am reuşit de am partiţionat tabela Comanda (tabela părinte tabelei LinieComanda). Logic ar fi să efectuăm şi partiţionarea tabelei LinieComanda astfel încât să respectăm regula impusă de la bun început (reorganizarea înregistrărilor per ani), însă atributele tabelei LinieComanda nu au în componenţă un atribut de legătură cu Comanda.DataComanda.

10

Totuşi, dacă ne uităm mai atent asupra modului de organizare a înregistrărilor din tabela LinieComanda, putem

spune că înregistrările sunt ordonate ascendent pe baza atributului LinieComanda.CodComanda - acest lucru se datorează faptului că ordinea de inserare a înregistrărilor din tabela LinieComanda coincide cu ordinea în care au fost inserate înregistrările în tabela Comanda.

Dacă e să privim din punct de vedere tranzacţional, o comandă (sub formă de înregistrări ale unei baze de date) ar fi compusă:

dintr-o înregistrare în tabela Comanda (înregistrare identificată prin CodComanda - valoare atribuită secvenţial de SGBD);

una sau mai multe înregistrări în tabela LinieComanda (înregistrări identificate prin combinaţia de atribute CodComanda, CodProdus).

Între DataComanda şi CodComanda există o relaţie dată de faptul că înregistrările asociate comenzilor sunt introduse în ordine cronologică, iar atributul CodComanda este incrementat la fiecare înregistrare adăugată.

Altfel spus, pe baza atributului DataComanda pot calcula ultima comandă din an.

Bingo! Pe baza ultimei comenzi din an voi putea defini funcţia şi schema pe baza căreia voi putea partiţiona tabela LinieComanda.

Deci conform rezultatului obţinut putem afirma că tabela LinieComanda va fi partiţionată astfel:

Partiţia numărul

Minimum Maximum Detalii

1 -9223372036854775808

25000 121427 de înregistrări (corespunzătoare comenzilor din 2008)

2 25001 400000 1819069 de înregistrări (corespunzătoare comenzilor din 2009)

3 400001 9223372036854775807 486420 de înregistrări (corespunzătoare comenzilor din 2010)

11

Vizualizăm modul în care tabela LinieComanda a fost partiţionată.

După ce am efectuat partiţionarea celor două tabele (Comanda şi LinieComanda), am verificat din nou viteza de execuţie a celor două interogări SQL.

Fraza SELECT-SQL Interval execuţie (secunde)

Inainte de partiţionare Dupa partiţionare

Totalul vânzărilor produselor comercializate în 2009 13 4

Totalul vânzărilor produselor comandate în 2009 7 3

Grafic, organizarea fişierelor şi tabelelor bazei de date COMANDA ar putea fi prezentată astfel:

12

Pentru a nu crea confuzie – filegroupul SECONDARY2 găzduieşte indexul PK_Comanda, dar în imaginea alăturată este prezentată doar organizarea fişierelor şi tabelelor bazei de date COMANDA.

Extinderea schemei de partiţionare (exemplul 1)

Schema de partiţionare poate fi modificată ulterior. În cele ce urmează am să prezint un prim exemplu de extindere a schemei de partiţionare.

Să prespunem că ne aflăm în anul 2011 şi dorim să creăm încă un filegroup pentru a stoca înregistrările anului calendaristic 2011.

13

Să listăm noile modificări aduse partiţiei PartitionComanda.

Însă nu este suficient doar a modifica schema de partiţionare. Pentru a salva înregistrările din 2011 în filegroupul SECONDARY6, trebuie să modificăm şi funcţia de partiţionare.

Listăm noile modificări aduse partiţiei PartitionComanda.

14

Conform listing-ului putem spune că noua partiţionare va arăta astfel:

Partiţia numărul Minimum Maximum

1 0001-01-01 00:00.0000000 2008-12-31 23:59:59.9999999

2 2009-01-01 00:00.0000000 2009-12-31 23:59:59.9999999

3 2010-01-01 00:00.0000000 2010-12-31 23:59:59.9999999

4 2011-01-01 00:00.0000000 9999-12-31 23:59:59.9999999

Trebuie să aducem modificări şi partiţiei PartitionLinieComanda astfel încât înregistrările asociate comenzilor din 2011 să fie salvate în filegroupul SECONDARY6.

Luând în calcul că "ne aflăm" în anul 2011 şi având în vedere modul în care datele sunt salvate în tabela LinieComanda, putem calcula care este ultima comandă din anul 2010, iar în funcţie de valoarea obţinută se va modifica funcţia de partiţionare PartitionLinieComandaByCodComanda.

15

Deci, toate comenzile ale căror CodComanda este mai mare de 500000 vor aparţine comenzilor create începând cu anul 2011.

Să listăm noile modificări aduse schemei şi funcţiei de partiţionare.

Testăm modificările adăugând ceva înregistrări pentru anul 2011.

16

Verificăm dacă am efectuat partiţionarea aşa cum ne-am propus.

Extinderea schemei de partiţionare (exemplul 2)

Din cauza numărului mare de comenzi înregistrate în perioada anului 2009, se ia decizia de a extinde schema de partiţionare (comenzile anului 2009 să fie distribuite pe două partiţii).

17

Verificăm dacă am efectuat partiţionarea aşa cum ne-am propus.

Restrângerea schemei de partiţionare

Se ia decizia de a unifica toate comenzile înregistrare în perioada anului 2009 într-o singură partiţie (a anula modificările aduse în exemplul 2)

Verificăm dacă am efectuat partiţionarea aşa cum ne-am propus.

Renunţarea la partiţionare

18

Se ia decizia de a renunţa la partiţionare şi de a aduce datele la forma iniţială (mutarea tabelelor în filegroupul

SECONDARY2).

Recomandări

Înainte de a efectua partiţionarea asiguraţi-vă că aveţi copii de siguranţă.

Înainte de a efectua partiţionarea treceţi baza de date în SINGLE_USER (la final, după partiţionare, treceţi baza de date în modul MULTI_USER). Având baza de date în modul SINGLE_USER vom fi siguri că doar o singură conexiune poate fi iniţiată cu respectiva baza de date (evident, respectiva conexiune va fi cea folosită de administratorul SGBD-ului şi va fi folosită la partiţionare).

Dacă trebuie să definiţi mai multe funcţii de partiţionare, încercaţi să definiţi toate funcţiile doar cu limită la stânga, sau doar cu limită la dreapta. Recomand acest lucru din simplul motiv că de foarte multe ori suntem nevoiţi să partiţionăm pe aceleaşi filegroupuri atât tabele părinte cât şi tabele copil (asemeni exemplului nostru), iar atunci când schema este extinsă, să putem păstra sincronizarea (ex: înregistrările tabelei Comanda din 2008 şi înregistrările LinieComanda din 2008 în acelaşi filegroup, înregistrările tabelei Comanda din 2009 şi înregistrările LinieComanda din 2009 în acelaşi filegroup, etc.).

Partiţionaţi tabelele cu un număr foarte mare de înregistrări (sau cele care veţi şti că vor avea multe înregistrări).

Analizaţi interogările înainte de a începe partiţionarea. O soluţie ar fi analiza cache-ului.

Dacă vă aşteptaţi la modificări repetate asupra atributului pe baza căruia s-a definit schema de partiţionare, se recomandă alegerea unor intervale mai mari de partiţionare (intervale care să nu determine mutarea efectivă a datelor dintr-un filegroup în altul).