SQL ServerT-SQL
4
0

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.

Wat is fragmentatie?

Kort samengevat, kun je indexen beschouwen als een lijstje met (een deel van) de informatie uit een tabel 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.

Rebuild / Reorganize

De informatie wordt in stukjes van 8 KB 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. Gebruik hiertoe het volgende script:

 

Bij fragmentatie zijn er 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”

Na het rebuilden van een index kan de fragmentatie echter nog steeds 50% zijn! Relatief kleine indexen komen vaak niet op 0% fragmentatie uit, maar op waardes als 50%, 66,67%, 25%, 12,5%.

De oorzaak hiervan is verholen in de interne werking van SQL Server. 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.

Als je echter 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!

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 hetzelfde kun je halen uit de dynamic management view (DMV) in de MSDB.

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. De index is feitelijk te klein om een eigen extent te vullen.

Zie ook dit artikel van Brent Ozar.

Bronnen:
https://www.master-it.nl/p/blog/sql-server-index-fragmentatie
https://social.technet.microsoft.com/wiki/contents/articles/40339.sql-server-what-is-fragmentation.aspx

Tags: , , , ,

Soortgelijke Berichten

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *

Fill out this field
Fill out this field
Geef een geldig e-mailadres op.
Je moet de voorwaarden accepteren voordat je het bericht kunt verzenden.

Meest Bekeken Berichten

Menu