Reindexare in SQL 2000

Multe aplicatii ASP .Net au in spate si un server de baze de date. Ce tre’ sa faci atunci cand pagini cu diverse rapoarte se incarca tot mai greu de la o saptamana la alta, primesti erori de timeout tot mai des pe mail, iar clientii incep sa devina din ce in ce frustrati? Incepi sa gugalesti:).

Presupun ca pe tabelele alea mari sunt deja construiti niste indecsi, iar indecsii au peste 1000 pagini (adica peste 8000 KB).

O idee ar fi sa se reverifice cum sunt construiti indecsii. Daca raportul e construit pe baza unei interogari care ordoneaza randurile dupa col1 ASC si col2 DESC si raportul e cerut destul de des, atunci indexul ar trebuie sa fie construit la fel (adica dupa col1 ASC si col2 DESC). Daca indexul are un fill factor de 100% (sau 0%) si pe tabela se fac la fel de des si operatii de insert,update sau delete atunci ar trebui reconsiderata valoarea acestui fill factor la una sub 100%. Cum un raport returneaza si alte coloane in afara de cele folosite in clause WHERE sau la ORDER BY ori GROUP BY, atunci ar fi bine ca indexul sa fie de tip CLUSTER (paginile frunza din B-TREE contin si celelalte coloane, nu numai datele din coloanele indexului).

Dupa verificarea asta, periodic trebuie facuta o reindexare, pentru ca ordinea fizica a paginilor se modifica o data cu stergerea, inserarea sau modificarea datelor din tabela si nu mai coincide cu ordinea paginilor din index.

Reindexarea se poate face prin trei feluri:

• stergere si recreare index (cam cu batai de cap atunci cand alte tabele referentiaza la coloanele din index)
• DBCC DBREINDEX – operatie offline, adica accesul la tabela e exclusiv
• DBCC INDEXDEFRAG – operatie online, utilizatorii sitului nu trebuie sa astepte pana se termina reindexarea

Voi exemplifica o metodologie de a verifica si reindexa anumiti indecsi pe o tabela dintr-un SQL SERVER 2000.

1. sp_helpindex

exec sp_helpindex ‘Products’

Afiseaza numele indexului, descrierea si cheile indecsilor din tabela Products. Se pot vedea rapid daca indecsii sunt construiti bine pentru interogarile folosite.

2. DBCC SHOWCONTIG

DBCC SHOWCONTIG('Products') WITH ALL_INDEXES

- Pages Scanned................................................: 7025
- Extents Scanned..............................................: 887
- Extent Switches..............................................: 4046
- Avg. Pages per Extent........................................: 7.9
- Scan Density [Best Count:Actual Count].......................: 21.72% [879:4047]
- Logical Scan Fragmentation ..................................: 33.84%
- Extent Scan Fragmentation ...................................: 51.86%
- Avg. Bytes Free per Page.....................................: 2247.2
- Avg. Page Density (full).....................................: 72.24% 

Sunt peste 7000 de pagini pentru primul index si dupa cum zice Microsoft, se merita facuta reindexarea, daca e nevoie, pentru ca numarul e mai mare de 1000. Ca se decizi daca e nevoie, urmatorii parametri trebuie sa aiba valorile:

• Scan Density: cat mai aproape de 100%
• Logical Scan Fragmentation si Extent Scan Fragmentation cat mai aproape de 0%

3. fill factor

E bine de stiut care e fill factor-ul inainte de a face reindexarea. Probabil ca cel care a creat indexul sau cel care a facut o reindexare in trecut, a ales o valoarea bine gandita.

select OrigFillFactor
from sysindexes
where name='IX_ProductID'

4. DBCC DBREINDEX

DBCC DBREINDEX (‘Products’, IX_ProductID,90);

Aleg DBREINDEX, o operatie offline, cum am zis, pentru ca reconstruieste si statisticile, iar daca serverul are mai multe procesoare, pentru indecsii foarte mari si foarte fragmentati, atunci operatia se executa mult mai repede.

5. se repeta SHOWCONTIG si DBREINDEX

Pentru ca e posibil ca DBREINDEX sa nu faca ceea ce ar trebui sa faca, adica sa-mi aduca parametrii la valorile care le doresc eu, trebuie repetate DBREINDEX si reverificat rezultatul cu SHOWCONTIG. In principiu, se mai scad sau se mai aduna la fill factor 2 puncte, ori cu putin noroc, reexecutata cu aceeasi valoare sa se obtina ceea ce s-a vrut de la inceput, un index cu paginile aranjate asa cum e ordinea lor fizica.