SQL Server Index fragmentatie

SQL Server gebruikt indexen om de gegevens in de database snel terug te kunnen vinden. Om de snelheid optimaal te houden, moeten we zorgen dat ze zo min mogelijk gefragmenteerd zijn. Maar hoe kan het dat soms onmogelijk is om de fragmentatie op 0% te krijgen, ondanks een rebuild van de index?? SQL Server gebruikt indexen om de gegevens in de database snel terug te kunnen vinden. Om de snelheid optimaal te houden, moeten we zorgen dat ze zo min mogelijk gefragmenteerd zijn. Maar hoe kan het dat soms onmogelijk is om de fragmentatie op 0% te krijgen, ondanks een rebuild van de index?? Wat is index fragmentatie? Kort samen gevat, kun je indexen beschouwen een lijstje met -een deel van- de informatie uit een tabel al in de juiste volgorde. Omdat de informatie geordend is, kan de informatie gevonden en opgevraagd worden. Als je een nieuwe index aanmaakt staat alles nog netjes achter elkaar, maar als je records gaat toevoegen of aanpassen in de tabel -en dus de index- moet de index worden aangepast en uitgebreid. Aangezien indexen op de harde schijf worden opgeslagen, kunnen ze door deze aanpassingen onderhevig zijn aan fragmentatie. Ofwel, niet alle data van de index staat netjes achter elkaar op de harde schijf. De kop van de harde schijf zal dus naar verschillende plekken op de harde schijf moeten springen en dat kost natuurlijk tijd. Rebuid / Reorganize De informatie wordt in stukjes van 8KB opgeslagen. Deze stukjes noemen we “pages”. Om de fragmentatie zo klein mogelijk te houden is het nodig om regelmatig te controleren hoeveel een index gefragmenteerd is en om deze eventueel te defragmenteren. Hier onder kun je zien dat deze index 50% gefragmenteerd is. Nu heb ik 2 mogelijkheden: Reorganize, de pages worden in de juiste volgorde geplaatst, maar er worden geen andere pages van de harde schijf gebruikt. Rebuild, feitelijk de hele index weggooien en opnieuw aanmaken. Als het nodig is kan de index ook in andere pages op de harde schijf worden opgeslagen. Microsoft adviseert om bij fragmentatie onder de 30% een reorganize uit te voeren en daarboven een rebuild. Vreemd probleem Nu liep ik tegen een erg vreemd probleem aan. Na het rebuilden van bovenstaande index, was de fragmentatie nog steeds 50%! Dat zou niet moeten mogen aangezien de index compleet opnieuw wordt aangemaakt. Ook bij andere indexen kwam ik het probleem tegen dat relatief kleine indexen vaak niet op 0% fragmentatie uitkomen, maar op waardes als 50%, 66,67%, 25%, 12,5%. Na veel zoeken kwam ik tot de volgende conclusie: Pages worden in groepjes van 8 opgeslagen, wat we een ‘extent’ noemen. Dus een extent is 8 pages * 8 KB = 64KB. Als je een object hebt dat bijvoorbeeld 80 -=640KB- pages beslaat, komt het dus in 10 extents te staan. Maar als je een klein object hebt wordt dit niet in een eigen extent opgeslagen, maar samen met andere kleine objecten in een “mixed-extent”. Stel mijn index is 2 pages groot, dan kan het dous voorkomen dat 1 page in de ene mixed extent terecht komt en 1 page in een andere mixed extent. SQL server ziet dat dan als 50% gefragmenteerd! Hoe kun je dit nu achterhalen? In onderstaande afbeelding zie je het resultaat van de query “DBCC SHOWCONTIG” Je ziet dat er inderdaad 2 pages zijn en dat er ook 2 extents zijn gebruikt, wat dus resulteert in 50% fragmentatie. Ongeveer het zelfde kan ik halen uit de dynamic management view in de MSDB. Uit deze view kan ik veel informatie halen over mijn index zoals de fragmentatie, het aantal pages, het aantal records en het aantal fragmenten waaruit de index bestaat. Mocht je dus tegen het probleem aanlopen dat de index fragmentatie niet minder wil worden, zelfs niet na rebuilden, kijk dan eens naar het aantal pages en extents. Waarschijnlijk ligt daar de oorzaak. Een oplossing is er eigenlijk niet, maar gelukkig is het ook niet echt een probleem. Het probleem is zit ‘m namelijk in het feit dat de index te klein is om een eigen extent te vullen, maar omdat de index zo klein is. Dus eigenlijk geen performance problemen kan opleveren! Ben ik toch weer opgelucht .