În acest articol, vom învăța cum să identificăm și să rezolvăm fragmentarea indexului în SQL Server. Identificarea fragmentării indexului și întreținerea indexului sunt părți importante ale sarcinii de întreținere a bazei de date. Microsoft SQL Server continuă să actualizeze statisticile indexului odată cu activitatea de inserare, actualizare sau ștergere asupra tabelului. Fragmentarea indexului este valoarea procentuală a performanței indexului, care poate fi obținută prin SQL Server DMV. În funcție de valoarea de performanță a indexului, utilizatorii pot lua indexurile în întreținere prin revizuirea procentului de fragmentare cu ajutorul operației Rebuild sau Reorganize.
De ce variază procentul de fragmentare a indexului?
Procentul de fragmentare a indexului variază atunci când ordinea paginilor logice nu se coordonează cu ordinea paginilor fizice în alocarea de pagini a unui index. Odată cu modificarea datelor din tabel, informațiile pot fi redimensionate pe pagina de date. Pagina era plină la maxim înainte de operațiunea de actualizare a tabelului. Cu toate acestea, a putut fi găsit spațiu liber pe pagina de date cu o operațiune de actualizare a tabelului. Utilizatorii pot observa ordinea deranjantă a paginii cu operațiunea de ștergere masivă pe tabel. Împreună cu operațiile de actualizare și ștergere, pagina de date nu va fi o pagină plină în partea superioară sau goală. Prin urmare, spațiul liber neutilizat crește neconcordanța de ordine între pagina logică și pagina fizică odată cu creșterea fragmentării, ceea ce poate cauza o performanță mai slabă a interogării și, de asemenea, consumă mai multe resurse ale serverului.
Este mai esențial să se precizeze că fragmentarea indexului afectează performanța interogării numai cu scanarea paginii. În astfel de cazuri, crește șansele unei performanțe slabe și a altor cereri SQL, deoarece interogarea cu indicele fragmentat ridicat asupra tabelului necesită mai mult timp pentru a fi executată și consumă mai multe resurse, cum ar fi Cache, CPU și IO. Prin urmare, restul cererilor SQL întâmpină dificultăți în finalizarea operațiunii cu resursele inconsistente ale serverului. Chiar și blocarea poate apărea prin operația de actualizare și ștergere, deoarece optimizatorul nu va aduna informațiile privind fragmentarea indexului în timp ce generează planul de execuție pentru interogare.
Pot exista mai mulți indici creați pe o singură tabelă cu combinația de diverse coloane, iar fiecare index poate avea un procent de fragmentare diferit. Acum, înainte de a o face adecvată sau de a lua un index în întreținere, utilizatorii trebuie să găsească acea valoare de prag din baza de date. Instrucțiunea T-SQL de mai jos este o modalitate eficientă de a o găsi cu detalii despre obiect.
Găsește starea de fragmentare a indexului folosind T-.SQL statement
|
1
2
3
4
5
6
5
6
. 7
8
9
10
11
12
13
13
14
|
SELECT S.name as ‘Schema’,
T.name as ‘Table’,
I.name as ‘Index’,
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc
|
Aici, putem observa că procentul mediu maxim de fragmentare se remarcă ca fiind de 99%, care trebuie să fie angajat cu o acțiune de reducere a fragmentării, cu opțiunile REBUILD sau REORGANIZE. REBUILD sau REORGANIZE este comanda de întreținere a indexului care poate fi executată cu instrucțiunea ALTER INDEX. Utilizatorii pot executa această comandă și cu ajutorul SSMS.
Rebuild and Reorganize Index using SQL Server Management Studio (SSMS)
Găsiți și extindeți tabelul în Object Explorer >> Open Indexes >> Faceți clic dreapta pe indexul țintă >> Rebuild or Reorganize.
După cum se poate vedea în imaginea de mai sus, REBUILD și REORGANIZE sunt cele două opțiuni disponibile pentru a reda operația de tăiere pe pagină. În mod ideal, această operațiune ar trebui să fie efectuată în afara orelor de vârf pentru a evita impactul acesteia asupra altor tranzacții și utilizatori. Microsoft SQL Server Enterprise Edition suportă funcțiile de indexare online și offline cu index REBUILD.
REBUILD INDEX
INDEX REBUILD renunță întotdeauna la index și îl reproduce cu noi pagini de index. Această activitate poate fi executată în paralel utilizând o opțiune online (Enterprise Edition) cu comanda ALTER INDEX, care nu afectează cererile și sarcinile în curs de execuție ale unui tabel similar.
REBUILD Indexul poate fi setat online sau offline cu ajutorul comenzilor SQL de mai jos:
|
1
2
3
4
5
|
–Comandă de bază de reconstruire
ALTER INDEX Index_Name ON Table_Name REBUILD
–REBUILD Index with ONLINE OPTION
ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)
|
Dacă un utilizator efectuează REBUILD INDEX offline, atunci resursa obiect (Table) a indexului nu va fi accesibilă până la sfârșitul finalizării procesului REBUILD. Aceasta afectează numeroase alte tranzacții, de asemenea, care sunt asociate cu acest obiect. Operațiunea de reconstruire a indexului recreează indexul. Prin urmare, generează noi statistici și anexează înregistrările de jurnal ale indexului și în fișierul jurnal de tranzacții al bazei de date.
De exemplu, înainte de a reconstrui indexul, să luăm alocarea actuală de pagini pentru indexul bazei de date AdventureWorks, tabelul Sales.OrderTracking și indexul numit IX_OrderTracking_CarrierTrackingNumber.
|
1
2
3
4
5
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id, previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Aici, 1961 pagini există în fișierul de bază de date pentru acest index, iar primele 5 pagini sunt 861, 862, 1627, 1628 și 1904 în ordinea numărului de pagină. Acum, să reconstruim indexul folosind SSMS.
Operațiunea Index REBUILD este finalizată cu succes și se iau din nou referințe de alocare a paginilor pentru același index cu ajutorul aceleiași interogări T-SQL.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
După reconstruirea indexului, numărul de pagini reîmprospătate este 1457, care era 1961 înainte. Dacă verificați primele 5 pagini alocate ale aceluiași index, acesta a fost modificat cu noile referințe de pagină. Se presupune că indexul este abandonat și realizat din nou. Ar trebui să verificăm procentul de fragmentare reîmprospătat pentru același index și, după cum se poate vedea mai jos, acesta este acum de 0,1%.
REBUILD indicele clusterizat peste tabel afectează și alți indici ai tabelului, deoarece indicele clusterizat REBUILD reconstruiește și indicele non-clustered al tabelului. Efectuați operațiunea de reconstrucție pe toți indicii din tabel sau baza de date împreună; un utilizator poate utiliza comanda DBCC DBREINDEX().
|
1
|
DBCC DBREINDEX (‘DatabaseName’, ‘TableName’);
|
REORGANIZED INDEX
Comanda REORGANIZE INDEX reordonează pagina de index prin expulzarea spațiului liber sau neutilizat de pe pagină. În mod ideal, paginile de index sunt reordonate fizic în fișierul de date. REORGANIZE nu elimină și nu creează indexul, ci doar restructurează informațiile de pe pagină. REORGANIZE nu are nicio alegere offline, iar REORGANIZE nu afectează statisticile în comparație cu opțiunea REBUILD. REORGANIZE se efectuează întotdeauna online.
De exemplu, înainte de a efectua REORGANIZE asupra indexului, să luăm citirea fragmentării pentru baza de date ‘AdventureWorks’, tabelul ‘Sales.OrderTracking’ și indexul numit ‘IX_OrderTracking_SalesOrderID’.
Aici, procentul de fragmentare a indexului este de 98,39 înainte de REORGANIZE. Lista de mai jos din imagine reprezintă paginile de alocare pentru index.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Aici, un total de 459 de pagini sunt listate în imaginea de mai sus, iar primele cinci pagini sunt 1065, 1068, 1069, 1944 și 1945. Acum, haideți să efectuăm comanda REORGANIZE asupra indexului utilizând instrucțiunea T-SQL de mai jos și să ne uităm din nou la alocarea paginilor.
|
1
|
ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE
|
Aici, numărul total de pagini a scăzut la 331, care era de 459 înainte. În plus, nu vedem pagini noi în lista primelor cinci pagini, ceea ce implică faptul că datele sunt doar restructurate – nu reumplute din nou. Chiar dacă ați putea vedea și pagini noi, acest lucru se întâmplă în situația în care indexul mare este puternic fragmentat, iar remanierea datelor utilizează o pagină nouă.
Pentru a efectua operația de REORGANIZARE a indexului pe toți indicii din tabel sau baza de date împreună, utilizatorul poate utiliza comanda DBCC INDEXDEFRAG():
|
1
|
DBCC INDEXDEFRAG(‘DatabaseName’, ‘TableName’);
|
După cum s-a văzut, există o diferență substanțială între operațiile de REBUILD și REORGANIZE a indicilor. Aici, utilizatorii au posibilitatea de a alege una dintre alternative în funcție de procentul de fragmentare a indexului. Putem înțelege că nu există standarde documentate; cu toate acestea, administratorul bazei de date urmează ecuația standard în funcție de cerința dimensiunii indexului și a tipului de informații.
Determinarea uzuală a utilizării ecuației :
- Atunci când procentul de fragmentare este cuprins între 15-30: REORGANIZE
- Când fragmentarea este mai mare de 30: REBUILD
Opțiunea REBUILD este mai utilă împreună cu opțiunea ONLINE atunci când baza de date nu este disponibilă pentru a face mentenanța indexului în afara orelor de vârf.
Concluzie
Fragmentarea indexului este o fragmentare internă în fișierul de date. Parametrii de bază ai performanței rapide a bazei de date sunt arhitectura bazei de date, proiectarea bazei de date și scrierea interogărilor. O bună proiectare a indexului cu întreținere stimulează întotdeauna performanța interogării în motorul bazei de date.
- Autor
- Postări recente
- Page Life Expectancy (PLE) in SQL Server – 17 iulie, 2020
- Cum se automatizează partiționarea tabelelor în SQL Server – 7 iulie 2020
- Configurarea grupurilor de disponibilitate SQL Server Always On pe AWS EC2 – 6 iulie 2020
Vezi toate postările lui Jignesh Raiyani
.