De: Brady Upton / actualizat: 2016-06-22 / Comentarii (5)/Related: mai multe > fragmentarea și Index de întreținere
problemă
am auzit cu toții despre fragmentarea bazei de date / index (și dacă nu ați, continuereading), dar ceea ce este? Este o problemă? Cum știu dacă se află în baza mea de date?Cum o repar? Aceste întrebări ar putea fi un sfat în sine, dar voi încerca să vă ofer o idee despre fiecare din acest sfat.
soluție
fără a intra într-o mulțime de detalii, SQL Server stochează date pe 8KB pagini. Whenwe introduce date într-un tabel, SQL Server va aloca o pagină pentru a stoca că dataunless datele introduse este mai mult de 8KB în care s-ar întinde pe mai multe pagini.Fiecare pagină este atribuită unui tabel. Dacă vom crea 10 tabele, atunci vom avea 10 diferitepagini.
pe măsură ce introduceți date într-un tabel, datele vor merge mai întâi la fișierul jurnal de tranzacții. Thetransaction log file este o înregistrare secvențială însemnând pe măsură ce inserați, actualizați și ștergeți înregistrărilelogul va înregistra aceste tranzacții de la început până la sfârșit. Fișierul de date de pe cealaltă parte nu este secvențial. Fișierul jurnal va spăla datele în fișierul de date creând pagini peste tot.
acum că avem o idee despre modul în care sunt stocate datele, Ce legătură are acest lucrufragmentare?
există două tipuri de fragmentare: fragmentarea internă și fragmentarea externă.
fragmentarea internă SQL Server
fragmentarea internă SQL Server este cauzată de pagini care au prea mult spațiu liber.Să ne prefacem că la începutul zilei avem un tabel cu 40 de pagini care sunt100 % pline, dar până la sfârșitul zilei avem un tabel cu 50 de pagini care sunt doar80% pline din cauza diferitelor declarații de ștergere și inserare pe tot parcursul zilei.Acest lucru provoacă o problemă, deoarece acum, când trebuie să citim din acest tabel, avem toscan 50 de pagini în loc de 40, ceea ce ar trebui să ducă la o scădere a performanței.Să vedem un exemplu rapid și murdar.
Să presupunem că am următorul tabel cu o cheie primară și un index non-cluster pe firstname și LastName:
voi vorbi despre modalități de analiză a fragmentării mai târziu în acest sfat, dar deocamdată putem face clic dreapta pe index, faceți clic pe Proprietăți și fragmentare pentru a vedea fragmentareași plinătatea paginii. Acesta este un indice nou, deci este la 0% fragmentare.
să inserăm 1000 de rânduri în acest tabel:
INSERT INTO Person VALUES('Brady', 'Upton', '123 Main Street', 'TN', 55555)GO 1000
acum, să verificăm din nou indexul nostru:
puteți vedea că indexul nostru devine fragmentat 75%, iar procentul mediu al paginilor complete (plinătatea paginii) crește la 80%. Acest tabel este încă atât de mic încât fragmentarea de 75% probabil că nu ar cauza probleme de performanță, dar pe măsură ce Tabelul crește în dimensiuneși numărul de pagini crește, este posibil să vedeți degradarea performanței. De asemenea, puteți vedea dinecranfierbinte de mai sus că acest tabel a trecut de la 0 pagini la 4.
fragmentarea externă SQL Server
fragmentarea externă este cauzată de pagini care nu sunt în ordine. Haidepretinde la începutul zilei avem o masă perfect ordonată. În timpul zilei, emitem sute de declarații de actualizare, lăsând eventual un spațiu gol pe o pagină și încercând să încadrăm spațiul în alte pagini. Aceasta înseamnă că stocarea noastră trebuie să sară pentru a obține datele necesare în loc să citească într-o singură direcție.
analiza fragmentării SQL Server
deci fragmentarea este o problemă? Cred că este. Dacă vă puteți stoca întreaga bază de date în memorie sau dacă baza dvs. de date este citită numai atunci nu mi-aș face griji, dar majoritatea dintre noi nu avem acest lux.Am lucrat pe mii de servere și analiza nivelurilor de fragmentare este unul dintre primele lucruri la care mă uit. De fapt, doar prin remedierea fragmentării, am văzut îmbunătățiri de până la200% în performanța interogării.
vorbind de analiza nivelurilor de fragmentare s-ar putea să vă întrebați cum putem face acest lucru. Ei bine, există câteva moduri…
DBCC SHOWCONTIG – această caracteristică este veche și va fi eliminată în versiunile viitoarede SQL Server, dar dacă utilizați în continuare SQL Server 2000 sau mai jos, acest lucru vă va ajuta. În loc să scriu despre asta, te voi arăta aici sau poți verifica tiphere-ul lui Chad Boyd. Ambele sunt resurse bune.
sys.dm_db_index_physical_stats – introdus în SQL Server 2005, acest dynamic managementview (DMV) returnează dimensiunea și fragmentarea informațiilor pentru datele și indexurile tabelului sau vizualizării specificate.
SELECT OBJECT_NAME(ips.OBJECT_ID) ,i.NAME ,ips.index_id ,index_type_desc ,avg_fragmentation_in_percent ,avg_page_space_used_in_percent ,page_countFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ipsINNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)ORDER BY avg_fragmentation_in_percent DESC
aceasta este probabil cea mai utilizată metodă de analiză a fragmentării. Youpot vedea din captura de ecran de mai sus că am un index numit IX_RPCust_1 pe theRPCust tabel care este 98.83% fragmentat. Puteți vedea mai multe informații despre acest DMVherefrom Arshad Ali.
software terță parte – software-ul terță parte va ajuta la identificarea fragmentăriideci, nu trebuie să. Greg Robidoux oferă un sfat bun pe SQL Defrag managera lui Idera.
fixarea fragmentării SQL Server
acum că am găsit fragmentarea în baza de date, cum o remediem? Likeanalizing indici, există mai multe moduri.
SQL Server Maintenance plans – planurile de întreținere sunt livrate cu SQLServer și sunt frumos pentru unele sarcini. Întreținerea indexului în opinia mea nu este una dintreele. Puteți adăuga o activitate index rebuild sau reorganiza activitate index în maintenanceplan, dar problema este că nu se poate aplica într-adevăr orice logică a planului. Withinlogic, Planul va reconstrui / reorganiza fiecare indice. Este posibil ca unii indici să nu fie fragmentați, astfel încât să nu fie nevoie să fie reconstruiți. Unii indici pot avea fragmentare minimăastfel încât acestea ar fi bine cu o reorganiza. Nu există nici o modalitate de a specifica acest lucru într-un maintenanceplan. InSQL Server 2016 există noi opțiuni pentru gestionarea fragmentării.
custom Script – script-uri personalizate sunt, probabil, Mod de a merge pentru că youcan aplica logica pentru a verifica fragmentarea index și apoi decide dacă să skipthe index, reorganiza, sau reconstrui. Folosesc scenariile Olei Hallengren și fac exact ceea ce am nevoie să facă. Sfaturi mssql.com are, de asemenea, acestearesurse-SQL Server fragmentare și Index sfaturi de întreținere.
software terță parte – software-ul terță parte va ajuta la defragmentarea indexurilorastfel încât să nu trebuie.
pașii următori
- acest sfat vă oferă o idee despre câteva dintre componentele implicate în indexfragmentare. MSSQLTips.com oferă sfaturi mai cuprinzătoare axate pe fiecare dintre subiectele pe care le-am menționat aici.
- asigurați-vă că pentru a verifica OUTCHAD Boyd lui SQL Server fragmentarea tutorial care oferă mare insightinto numeroase subiecte cu privire la fragmentarea
Ultima actualizare: 2016-06-22
About the author
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.View all my tips
- More SQL Server DBA Tips…