.jpg)
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 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.
Rebuid / Reorganize
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:
- Reorganize, de pages (die nu gebruikt worden door de index) 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 framentatie 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 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 ;-)
Maintenance plans
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!
SQL Server trainingen volgen
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.
Gerelateerde trainingen
Troubleshooting Windows Server 2016 Core TechnologiesNetworking Fundamentals
MOC On Demand 10991 Troubleshooting Windows Server 2016 Core Technologies
MOC On Demand 20744 Securing Windows Server 2016
MOC On Demand 20742 Identity with Windows Server 2016
MOC On Demand 20740 Installation, Storage, and Compute with Windows Server 2016
MOC On Demand 20741 Networking with Windows Server 2016
Securing Windows Server 2016
PowerShell 5.0 and Desired State Configuration
MOC On Demand 20743 Upgrading Your Skills to Windows Server 2016 MCSA