Free Press

Scopul creării de indexuri SQL pe tabelele bazei de date este de a îmbunătăți performanța interogărilor care citesc date din tabelul de bază prin accelerarea proceselor de recuperare a datelor. Dar aceste tabele tranzacționale nu sunt statice și se schimbă foarte frecvent în timp. Aceste modificări care se efectuează asupra tabelului bazei de date includ adăugarea de noi înregistrări, modificarea sau ștergerea înregistrărilor existente. Deoarece aceste modificări ar trebui să fie reproduse în indexurile tabelelor aferente, indexul tabelului va deveni fragmentat în timp.

SQL Server Index Fragmentation Overview

Există două tipuri principale de fragmentare a indexurilor SQL; Internal and External Fragmentation. Fragmentarea internă este cauzată de inserarea unei noi înregistrări sau de actualizarea unei înregistrări existente cu valori care nu se potrivesc în spațiul liber curent din pagina de date și rezultă cu împărțirea paginii în două pagini pentru a se potrivi cu noua valoare. În acest caz, motorul SQL Server va muta aproximativ 50% din datele din pagina curentă în noua pagină de date generată în urma operațiunii de divizare, pentru a menține echilibrat arborele de indexare SQL. Fragmentarea internă poate fi cauzată, de asemenea, de operațiile de ștergere aleatoare care generează spații goale pe paginile de date. Acest tip de operații lasă paginile de date necompletate și are ca rezultat o dimensiune mai mare a indexului SQL Server și o performanță mai lentă din cauza necesității de a citi mai multe pagini de date pentru a prelua datele solicitate.

Fragmentarea externă este cauzată atunci când paginile de date ale indexului SQL Server sunt împrăștiate pe fișierul fizic al bazei de date, din cauza faptului că ordinea logică a indexului SQL Server nu corespunde cu ordinea fizică de pe unitatea de disc subiacentă. Fragmentarea externă poate fi cauzată de operațiunile de ștergere aleatoare care lasă un număr de pagini de extensie goale, în timp ce extensia însăși este rezervată. Acest tip de fragmentări duce la degradarea performanțelor, deoarece va dura mai mult timp și va consuma mai multe resurse în saltul între paginile de date împrăștiate pentru a prelua datele solicitate de pe unitatea de disc subiacentă.

Motivul pentru care ne interesează foarte mult fragmentarea indexului SQL este acela că indexul pe care l-ați creat pentru a îmbunătăți performanța sistemului, poate degrada performanța interogărilor dumneavoastră după ce este foarte fragmentat. Imaginați-vă diferența de timp și numărul de citiri pe disc între citirea unui anumit număr de rânduri dintr-un index SQL sănătos, în care datele sunt stocate în pagini de date contigue în cadrul fișierului de bază de date, și citirea aceluiași număr de rânduri dintr-un index SQL Server foarte fragmentat, în care datele sunt împrăștiate în pagini de date necontinue în cadrul fișierului de bază de date.

Dacă se constată că indexul SQL al tabelei este fragmentat, trebuie să remediați problema fragmentării prin efectuarea unei reorganizări a indexului SQL Server sau a unei reconstrucții a indexului, în funcție de procentul de fragmentare a indexului SQL. De exemplu, un index SQL Server cu un procent de fragmentare mai mic de 30% poate fi reorganizat, în cazul în care indexul SQL cu un nivel de fragmentare mai mare de 30% ar trebui reconstruit.

• Notă: Consultați Întreținerea indexurilor SQL Server pentru mai multe informații.

Puteți depăși în mod proactiv problemele de fragmentare a indexului SQL Server și de divizare a paginilor prin proiectarea corespunzătoare a indexului și prin setarea opțiunilor de creare a indexului Fill Factor și pad_index cu valorile corespunzătoare.

• Notă: Consultați Operațiuni cu indexuri SQL Server pentru mai multe informații.

SQL Server Index Fragmentation Report

Nu este considerată cea mai bună practică să creați un index SQL în tabelul din baza de date doar pentru a profita de avantajele aduse de îmbunătățirea operațiilor de recuperare a datelor și să lăsați acel index SQL Server pentru totdeauna fără a monitoriza în mod continuu fragmentarea acestuia și a-l întreține.

SQL Server ne pune la dispoziție mai multe metode care pot fi utilizate pentru a verifica procentul de fragmentare a indexului SQL.

SQL Server Index Properties

Prima metodă este pagina Fragmentation (Fragmentare) din fereastra SQL Server Index Properties (Proprietăți index SQL Server). Faceți clic dreapta pe indexul pe care trebuie să îl verificați și alegeți opțiunea Properties (Proprietăți). Din pagina SQL Server Index Properties (Proprietăți index SQL Server), navigați pe pagina Fragmentation (Fragmentare) și veți vedea procentul de fragmentare a indexului și cât de pline sunt paginile indexului SQL Server în secțiunea Fragmentation (Fragmentare), așa cum se arată mai jos:

În câțiva pași, puteți verifica cu ușurință procentul de fragmentare a indexului SQL Server al indexului selectat din fereastra de proprietăți a indexului. Dar ce se întâmplă dacă aveți nevoie de o imagine de ansamblu a procentului de fragmentare pentru indicii tuturor tabelelor sau pentru indicii tuturor tabelelor din baza de date? În acest caz, trebuie să mergeți și să îl verificați unul câte unul. Ca urmare, o singură săptămână de lucru nu va fi suficientă pentru un singur raport privind baza de date!

sys.dm_db_index_physical_stats

O altă metodă care poate fi utilizată pentru a verifica procentul de fragmentare pentru indicii bazei de date este interogarea funcției de gestionare dinamică sys.dm_db_index_physical_stats, care a fost introdusă pentru prima dată în SQL Server 2005, ca înlocuitor al comenzii depreciate DBCC SHOWCONTIG. Aceasta oferă informații despre dimensiunea și procentul de fragmentare pentru indicii bazei de date.

Pentru a obține informații semnificative despre procentul de fragmentare al tuturor indicilor din baza de date specificată din DMF sys.dm_db_index_physical_stats, trebuie să o alăturați cu funcția sys.indexes DMV, ca în scriptul T-SQL de mai jos:

SELECT OBJECT_NAME(Index_Info.OBJECT_ID) AS TableName ,Index_Info.name AS IndexName ,Index_Stat.index_type_desc AS IndexType ,Index_Stat.avg_fragmentation_in_percent IndexFragmPercentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) Index_StatINNER JOIN sys.indexes Index_Info ON Index_Info.object_id = Index_Stat.object_id AND Index_Info.index_id = Index_Stat.index_idORDER BY IndexFragmPercent DESC

În funcție de abilitățile dumneavoastră de dezvoltare SQL, puteți efectua filtrarea de care aveți nevoie pentru a prelua fragmentarea indicilor SQL pe domeniul de aplicare necesar, fără opțiunea de a obține un rezumat pentru toate bazele de date de instanță, luând în considerare faptul că datele afișate sunt de la ultima repornire a serviciului SQL Server. Rezultatul în cazul nostru va fi de genul:

SQL Server Index Physical Statistics Standard Report

SQL Server ne pune la dispoziție mai multe rapoarte standard care conțin informații statistice utile despre baza de date selectată. Raportul Index Physical Statistics, este unul dintre rapoartele standard SQL Server care returnează informații statistice despre partițiile de index SQL Server, procentul de fragmentare și numărul de pagini de pe fiecare partiție de index SQL la nivelul bazei de date, de la ultima repornire a serviciului SQL Server, cu recomandarea modului de remediere a problemei de fragmentare a indexului, dacă există, pe baza procentului de fragmentare a indexului SQL Server.

Pentru a vizualiza raportul SQL Server Index Physical Statistics, faceți clic dreapta pe baza de date, alegeți Reports -> Standard Reports, apoi selectați raportul Index Physical Statistics, așa cum se arată mai jos:

Iar raportul generat la nivelul bazei de date, care nu poate fi filtrat și necesită extinderea manuală a rezultatului indexurilor fiecărei tabele, va fi așa cum se arată mai jos:

ApexSQL Defrag

Instrumentul ApexSQL Defrag este un instrument de defragmentare a indicilor SQL terță parte care poate fi utilizat cu ușurință pentru a verifica informațiile privind fragmentarea și utilizarea indicilor SQL Server și pentru a efectua sau programa remedierea adecvată a problemei de fragmentare a indicilor.

Dintr-o singură locație centrală, puteți analiza și revizui informațiile privind fragmentarea și utilizarea la diferite niveluri, inclusiv informațiile privind fragmentarea și utilizarea indexului SQL la nivelul indexului SQL Server, la nivelul tabelei, la nivelul bazei de date și, în cele din urmă, la nivelul general al instanței SQL Server. Ne pune la dispoziție variante de tipuri de filtre care pot fi utilizate pentru a afișa și menține doar indicii țintă.

ApexSQL Defrag ne permite, de asemenea, să configurăm setarea Fill Factor pentru indicii de tabel, să creăm alerte pentru toate lucrările de defragmentare și politicile de defragmentare și să trimitem notificări prin e-mail pentru lucrările reușite sau eșuate.

Informațiile afișate vor oferi o bună vizualizare a dimensiunii indicilor SQL și a procentului de fragmentare, care nu vor fi afectate de nicio repornire a serviciului SQL Server sau de repornirea serverului. Acest lucru se datorează faptului că aceste informații vor fi stocate într-o bază de date centrală de depozit pentru instrumentul ApexSQL Defrag.

Instalarea ApexSQL Defrag

ApexSQL Defrag poate fi descărcat cu ușurință de la ApexSQL Download Center și instalat pe serverul dvs. urmând asistentul de instalare simplu, așa cum se arată mai jos:

În primul rând, vi se va cere să specificați contul de serviciu care va fi utilizat ca context de execuție pentru agentul ApexSQL Defrag și care transmite comenzi către instanța SQL Server, după cum urmează:

După ce ați specificat contul de serviciu ApexSQL Defrag, vi se va cere să specificați unde să instalați instrumentul ApexSQL Defrag și dacă doriți să creați o pictogramă de comandă rapidă pentru instrument pe desktop, după cum se arată mai jos:

Verificând că traseul furnizat conține minimum 127 MB de spațiu liber, ApexSQL Defrag va fi instalat cu succes pe serverul dumneavoastră. Iar următoarea notificare va fi afișată, oferindu-vă posibilitatea de a porni ApexSQL Defrag direct:

Dacă rulați ApexSQL Defrag pentru prima dată, vi se va cere să confirmați crearea bazei de date de depozit central, unde vor fi stocate aceste date istorice și de configurare, după cum urmează:

Add New Server

După crearea bazei de date de depozit, ApexSQL Defrag va fi pornit fără instanță SQL. Pentru a verifica procentul de fragmentare a indexului SQL pe o anumită instanță SQL Server, trebuie să adăugați instanța respectivă la instrumentul respectiv. Pentru a realiza acest lucru, faceți clic pe butonul Add (Adăugare), sub fila Home (Pagina principală), așa cum se arată mai jos:

În fereastra Connect to SQL Server (Conectare la SQL Server), vi se va cere să furnizați numele instanței SQL Server care va fi utilizată și acreditările necesare pentru a vă conecta la acel server, după cum urmează:

SQL Server Index Analysis

Atunci când serverul este adăugat, puteți efectua o nouă verificare a tuturor informațiilor privind fragmentarea indicilor SQL din fila Fragmentation de mai jos:

ApexSQL Defrag vă oferă posibilitatea de a analiza toți indicii din acea instanță pe baza a trei moduri:

• DETAILED: în care toate paginile de date și indexul SQL vor fi citite în timpul procesului de scanare
• SAMPLED: în care doar 1% din pagini vor fi citite, luând în considerare faptul că indexul SQL Server conține mai mult de 10.000 de pagini
• LIMITED: în care vor fi citite numai paginile situate la nivelul părinte al arborelui b

Pentru a analiza informațiile despre indexul SQL Server de la instanța SQL Server conectată, faceți clic pe butonul Analyze (Analiză), sub fila Fragmentation (Fragmentare), și alegeți să efectuați o analiză rapidă sau profundă. Vom efectua Deep analyzing (Analiză profundă) pentru a obține informații complete, după cum se arată mai jos:

Continuați să monitorizați bara de progres din partea de jos a instrumentului pentru a verifica progresul analizei, după cum urmează:

Și va fi afișat un raport cuprinzător, care conține informații complete despre toți indicii SQL creați sub fiecare bază de date a acestei instanțe SQL Server, după cum se arată mai jos:

Cu posibilitatea de a trece de la un nivel la altul; instanță SQL Server, bază de date, tabel sau index, cu un singur clic în cadrul aceleiași ferestre centrale, și de a filtra rezultatul recuperat pe baza unui anumit nivel sau prag de fragmentare, după cum se arată mai jos:

În plus, puteți glisa orice coloană din coloanele raportului pentru a grupa raportul general pe baza valorilor coloanei respective, cum ar fi exemplul de mai jos, în care am grupat datele raportului pe baza numelui tabelului:

ApexSQL Defrag Reports

ApexSQL Defrag ne pune la dispoziție o serie de rapoarte care prezintă informații statistice despre indexurile bazei de date. Aceste rapoarte pot fi configurate și verificate în fila Reports (Rapoarte) de mai jos:

Primul raport este Total report (Raport total), care arată o reprezentare grafică pentru fragmentarea indexului SQL și utilizarea spațiului pe disc la nivelul selectat. De exemplu, dacă faceți clic pe numele instanței, raportul va desena graficul de fragmentare procentuală și graficul de utilizare a spațiului la nivelul instanței SQL Server, așa cum se arată mai jos:

Dacă faceți clic pe orice bază de date din fereastra Server Explorer, raportul va schimba automat vizualizarea pentru a arăta graficul la nivelul bazei de date, așa cum se arată mai jos:

Al doilea raport, care poate fi vizualizat, de asemenea, la diferite niveluri, este raportul Statistics (Statistici). Acest raport afișează informații statistice despre dimensiunea și fragmentarea totală a indexurilor și despre distribuția tipurilor de index la nivelul selectat, după cum se arată mai jos:

Al treilea raport este raportul Top 10, care vizualizează primii 10 indexuri clusterizate și non-clusterizate cu cea mai mare dimensiune și cel mai mare procent de fragmentare a indexurilor SQL, la nivelul specificat. De exemplu, următorul raport afișează primii 10 indici scumpi, pe baza dimensiunii și a fragmentării, la nivelul instanței SQL Server:

Coborând la o bază de date specifică, raportul va afișa automat primii 10 indici scumpi, pe baza dimensiunii și a fragmentării, la nivelul bazei de date selectate, așa cum se arată mai jos:

Plonjând în profunzime până la o anumită tabelă din baza de date respectivă, raportul va afișa primii 10 indici scumpi, pe baza dimensiunii și a fragmentării, după cum urmează:

ApexSQL Defrag Report Export

ApexSQL Defrag ne permite să exportăm rapoartele de fragmentare în diferite formate de fișiere, inclusiv PDF, IMG, HTML, CS și XML, cu posibilitatea de a personaliza setările raportului înainte de a fi generat. Sub fila Reports (Rapoarte), faceți clic pe butonul Options (Opțiuni):

Din fereastra Edit Export Options (Editare opțiuni de export), puteți configura diferitele opțiuni, începând de la convenția de denumire a fișierului generat, calea în care va fi salvat fișierul, orientarea paginii și, în final, conținutul fiecărui raport, așa cum se arată mai jos:

De exemplu, pentru a exporta raportul Total generat la nivelul specificat, faceți clic pe butonul Export (Export), din fila Reports (Rapoarte), și alegeți tipul de fișier, în care va fi exportat acest raport Total. În câteva secunde, raportul va fi generat și vă va întreba dacă doriți să deschideți raportul generat. Faceți clic pe Yes (Da) pentru a deschide direct raportul respectiv, după cum se arată mai jos:

În plus, puteți exporta raportul Top 10 la nivelul specificat, făcând clic pe butonul Export (Export), sub fila Reports (Rapoarte), și specificați tipul de fișier de export, iar raportul va fi generat rapid, întrebându-vă dacă doriți să vizualizați direct raportul generat. Faceți clic pe Yes pentru a deschide fișierul generat, așa cum se arată mai jos:

Din exemplele anterioare reiese clar că putem utiliza cu ușurință instrumentul ApexSQL Defrag pentru a revizui și analiza informațiile privind procentul de fragmentare a indexului SQL și pentru a ne scufunda la diferite niveluri, începând de la nivelul instanței SQL Server până la nivelul indexului SQL Server și pentru a le afișa în diferite formate exportabile, fără ca informațiile afișate să fie afectate de repornirea vreunui server sau serviciu, deoarece acestea vor fi stocate într-o bază de date de tip repository.