SQL Server Index fragmentatie

Kort samen gevat, kun je een 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 snel 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 onder hevig zijn aan fragmentatie. Of te wel, 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.

De data (tabellen en indexen) in SQL Databases wordt in stukjes van 8KB opgeslagen. Deze stukjes noemen we "pages”. Wanneer de pages van een index netjes achter elkaar staan, is er geen sprake van fragmentatie, maar wanneer de pages verspreid zijn spreken we dus van fragmentatie.

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:

 

Microsoft adviseert om bij framentatie onder de 30% een reorganize uit te voeren en daarboven een rebuild.

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 dus 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!

Bij een rebuild van de index, gaat het uiteraard nog steeds over een klein object, dus deze zal wederom verdeeld worden over beschikbare ruimte in mixed extents. Het gevolg is dat er nog steeds fragmentatie is.

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.

Gedetailleerde informatie is ook te halen uit de dynamic management functie "dm_db_index_physical_stats”.

Uit deze view kun je veel informatie halen over indexen 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 ;-)

Je kunt maintenance plans gebruiken om regelmatig je indexen te defragmenteren.

In deze task kun je aan geven welke indexen je wilt rebuilden. Sinds SQL Server 2016 zit er een extra mogelijkheid bij, die je de mogelijkheid geeft om aan te geven dat de index alleen gedefragmenteerd hoeft te worden als deze boven een bepaald percentage is gekomen én als de Page Count groter is dan een bepaalde hoeveelheid.

In voorgaande versies van SQL Server was dit niet mogelijk, maar deze settings zorgen er voor dat je niet een hoop tijd en resources steekt in het rebuilden van indexen die geen rebuild nodig hebben.

Mooie oplossing Microsoft!

Wil je meer weten over het ontwikkelen van een SQL database? Dan is de training Developing SQL databases (MS20762) wellicht iets voor jou. Is het beheren van een SQL database meer jouw ding? Overweeg dan de training Administering a SQL database infrastructure (MS20764). Ben jij verantwoordelijk voor het beheren en onderhouden van een SQL Server database en wil je de performance optimaliseren? Kijk dan bij de training Performance Tuning and Optimizing SQL databases (MS10987).

Twijfel je nog over welke training het beste bij jou past? Neem dan vrijblijvend contact met ons op! Wij kunnen je voorzien van een passend advies zodat jij je leerdoelen kunt bereiken.