[MS SQL] Fillfactor

[Verwijderd]

Momenteel ben ik mij aan het verdiepen in MS SQL en indexes. Eén van de opties die je met create index kan meegegeven is de FILLFACTOR. Als ik het goed heb begrepen bewaard MS-SQL de index data in een index page, welke 2KB is. Zodra een update/insert op een tabel plaats vindt, en de index page is vol, dan wordt de index page gespitst. Dit splitsen kost veel system overhead.

Met fillfactor kan je aangeven hoeveel procent van die 2KB gebruikt dient te worden. Standaard is deze fillfactor 100, wat optimaal is voor het snel lezen van gegevens.

Goed... en dan nu de praktijk :-). Welke fillfactor dien je nu bv te gebruiken voor een order tabel? Of is het hele fillfactor verhaal niet zo spannend?

[whoami]

Tja, dat hangt ervanaf wat je vooral met die Order tabel gaat doen natuurlijk.
Wordt er vooral uit gelezen, en slechts sporadisch in geschreven ? Dan zou je er kunnen voor kiezen dat je index pages zo veel mogelijk gevuld worden.
Wordt er vooral in geschreven, dan zal je je index page wat minder kunnen laten vullen.

Ik geloof dat de fillfactor default 80% is, en meestal laat ik deze ook gewoon op z'n default waarde staan.

Zowiezo kan je ook een maintenance plan maken dat bv om de 2 weken wordt uitgevoerd , waarbij je de indexen herbouwd, zodanig dat je index pages voldoen aan de fill factor die je gespecifieerd hebt.

[Acid__Burn]

whoami schreef op woensdag 21 januari 2009 @ 14:16:
Zowiezo kan je ook een maintenance plan maken dat bv om de 2 weken wordt uitgevoerd , waarbij je de indexen herbouwd, zodanig dat je index pages voldoen aan de fill factor die je gespecifieerd hebt.

Wederom: dat ligt aan je insert / update acties. Als je vaak schrijft, is 2 weken ook lang voor het index rebuilden...

Persoonlijk werk ik veel met databases, waarop er veeeeel (trust me ) schrijfacties plaatsvinden. Dan is het meestal beter om iedere week (hier standaard zaterdag) de indexen te rebuilden.

[Verwijderd]

Ik geloof dat de fillfactor default 80% is, en meestal laat ik deze ook gewoon op z'n default waarde staan.

De default waarde is 0, wat gelijk is aan 100%(bron). Maar ik lees net dat het gebruik niet aan te raden is :-) (bron).

[Verwijderd]

Acid__Burn schreef op woensdag 21 januari 2009 @ 14:26:
[...]
Persoonlijk werk ik veel met databases, waarop er veeeeel (trust me ) schrijfacties plaatsvinden. Dan is het meestal beter om iedere week (hier standaard zaterdag) de indexen te rebuilden.

Moet je die indexen dan nog wel initieel definieeren? Maw: worden alleen bestaande indexen aangepast? Ik heb helaas alleen SQL express geinstalleerd en hier zit het maintenance plan niet in. Morgen eens naar een MSDN versie vragen :-)

[whoami]

Acid__Burn schreef op woensdag 21 januari 2009 @ 14:26:
[...]


Wederom: dat ligt aan je insert / update acties. Als je vaak schrijft, is 2 weken ook lang voor het index rebuilden...

Persoonlijk werk ik veel met databases, waarop er veeeeel (trust me ) schrijfacties plaatsvinden. Dan is het meestal beter om iedere week (hier standaard zaterdag) de indexen te rebuilden.

Dat was dan ook een voorbeeld he, ter illustratie. Hence the 'vb'.
Hoevaak je die indexen rebuilt hangt idd ook af van situatie tot situatie. (Hier ook iedere zondag).

[Acid__Burn]

Verwijderd schreef op woensdag 21 januari 2009 @ 14:35:
[...]
Moet je die indexen dan nog wel initieel definieeren? Maw: worden alleen bestaande indexen aangepast? Ik heb helaas alleen SQL express geinstalleerd en hier zit het maintenance plan niet in. Morgen eens naar een MSDN versie vragen :-)

Uiteraard moet je die indexes initieel definieren. Die indexes worden niet automagisch gemaakt door SQL .

Maar zoek eens op google / MSDN naar Indexes / Index Rebuild / Online Index Rebuild / Tuning en Optimizing SQL Server.

Daar staan een hoop handige tips in waar je zeker op moet letten bij index create en maintenance.

whoami schreef op woensdag 21 januari 2009 @ 14:52:
[...]

Dat was dan ook een voorbeeld he, ter illustratie. Hence the 'vb'.
Hoevaak je die indexen rebuilt hangt idd ook af van situatie tot situatie. (Hier ook iedere zondag).

Ik denk dat we hier te maken hebben met een DBA (o.i.d.). Maar het is misschien ook niet verstandig om op productie indexes aan te maken. Ligt helemaal aan de situatie. Dus is het ook niet altijd de beste keus om 1x in de week de indexes te rebuilden. Dat is per situatie anders.


Maar hebben jullie geen DBA in het bedrijf, of is dit een prive "cursus"? Anders zou je een DBA / System Engineer kunnen vragen wat de beste keus is.

offtopic
@ Joda: gebruik de edit button voor je posts, en niet 2x een message posten

[ Voor 32% gewijzigd door Acid__Burn op 21-01-2009 15:44 ]

[DamadmOO]

Hier rebuilden we dagelijks de indexen die te ver afwijken van de gewenste fillfactor en de indexes die de laatste 7 dagen niet gereindexed zijn. We hebben er toen voor gekozen om op een zondag een force reindex op alle indexen te doen zodat ze standaard op zondag opnieuw geindexeerd worden.

Persoonlijk zet ik ook liever het reindexen op 1 keer per week, maar door het brakke database model moeten we performance technisch wel elke dag herindexeren.

[Soultaker]

Zodra een update/insert op een tabel plaats vindt, en de index page is vol, dan wordt de index page gespitst. Dit splitsen kost veel system overhead.

Mwoah.... Een page split is op zich niet zo boeiend; als alle pagina's vol zitten en je wil veel updates doen is het een ander verhaal natuurlijk, maar de overhead per insert is nou niet echt schokkend. Pagina's worden bij normale operatie van de database ook wel eens gesplitst.

Of is het hele fillfactor verhaal niet zo spannend?

De fill factor bepaalt alleen hoe vol de pagina's zijn wanneer de index wordt aangemaakt; als je dus één keer een index aanlegt en er verder niet meer naar omkijkt (wat prima moet kunnen) dan is de initiële fill factor inderdaad niet echt boeiend.

DamadmOO schreef op woensdag 21 januari 2009 @ 15:51:
Hier rebuilden we dagelijks de indexen die te ver afwijken van de gewenste fillfactor en de indexes die de laatste 7 dagen niet gereindexed zijn.

Scheelt dit veel dan? Het verschil tussen 80% en 100% gevulde pages lijkt mij persoonlijk nogal weinig. Het is vast meetbaar, maar kan toch niet het verschil maken tussen een vlot werkend systeem en een onwerkbaar traag systeem?

[Acid__Burn]

Soultaker schreef op woensdag 21 januari 2009 @ 16:45:
[...]
De fill factor bepaalt alleen hoe vol de pagina's zijn wanneer de index wordt aangemaakt; als je dus één keer een index aanlegt en er verder niet meer naar omkijkt (wat prima moet kunnen) dan is de initiële fill factor inderdaad niet echt boeiend.

Nee, zeker niet. Iedere index die gemaakt wordt, moet ook onderhouden worden. Als je random indexes gaat maken, en niet onderhoudt, zal je dus de meest snelle applicatie om zeep helpen.

Daarom is het dus een goed idee om een maintenance plan te hebben. Juist om de performance van een applicatie te behouden.

Soultaker schreef op woensdag 21 januari 2009 @ 16:45:
[...]

Scheelt dit veel dan? Het verschil tussen 80% en 100% gevulde pages lijkt mij persoonlijk nogal weinig. Het is vast meetbaar, maar kan toch niet het verschil maken tussen een vlot werkend systeem en een onwerkbaar traag systeem?

Het maakt wel degelijk uit. Anders zou er geen verschil bestaan tussen 80% en 100%. Dat lijkt me vrij duidelijk.

Dit kan zeker het verschil maken tussen een "redelijke" applicatie, en een "snelle" applicatie.


Dus samenvattend: verdiep je in de functie van indexes, hoe in te richten en te onderhouden. Test daarna de database "met load". Insert desnoods 1 miljoen records in een tabel, om zo de performance te kunnen meten. Per applicatie verschilt de load, dus houdt het wel realistisch.

[ Voor 15% gewijzigd door Acid__Burn op 21-01-2009 17:06 ]

[Soultaker]

Acid__Burn schreef op woensdag 21 januari 2009 @ 17:03:
Nee, zeker niet. Iedere index die gemaakt wordt, moet ook onderhouden worden. Als je random indexes gaat maken, en niet onderhoudt, zal je dus de meest snelle applicatie om zeep helpen.

Ik weet niet hoe SQL Server het implementeert, maar elke standaard B(+)-tree index balanceert vanzelf uit zodat je pagina's gemiddeld 75% gevuld zijn. Dan is de performance nog steeds prima, en je hoeft er echt he-le-maal niets aan te onderhouden; er is geen enkele reden waarom de performance na verloop van tijd slechter zou worden.

Het maakt wel degelijk uit. Anders zou er geen verschil bestaan tussen 80% en 100%. Dat lijkt me vrij duidelijk.

Zoals ik al zei begrijp ik ook wel dat er een theoretisch verschil is. Maar B-trees (en aanverwante datastructuren) zijn nu juist ontworpen om goede performance te behouden ook als er wijzigingen plaatsvinden. Lijkt me dat als je database performance alleen toereikend is wanneer je net je indices opnieuw hebt aangemaakt, dat je database server dan eigenlijk al overbelast is, en je dat probleem beter op een andere manier kunt aanpakken.

Ik vind het periodiek heropbouwen van indices maar een lelijke hack eigenlijk, en als je daarvan uit gaat zie ik niet hoe je ooit een server gaat inrichten die 24/7 op volle capaciteit beschikbaar is, want het rebuilden van indices is behoorlijk tijdrovend.

[DamadmOO]

Soultaker schreef op woensdag 21 januari 2009 @ 16:45:

[...]

Scheelt dit veel dan? Het verschil tussen 80% en 100% gevulde pages lijkt mij persoonlijk nogal weinig. Het is vast meetbaar, maar kan toch niet het verschil maken tussen een vlot werkend systeem en een onwerkbaar traag systeem?

Tabellen met een veertigtal indexen en een klein miljoen aan updates/inserts per dag zijn geen uitzonderingen in de database. Op het moment dat deze tabellen een week lang totaal niet geherindexeerd worden dan vliegen de deadlocks je om de oren omdat het selecteren en schrijven van de data gewoon te lang duurt. Ook is de gemiddelde load na een week niet indexeren gemiddeld 10% hoger dan wanneer we dagelijks herindexeren. De tabellen met erg veel indexen zijn trouwens niet het enigste probleem. Ook zijn er tabellen zonder enige index waar een paar miljoen reads per dag op zijn. Dat doet het ook goed voor de performance

Het grootste probleem is dat wij niet de mogelijkheid hebben om fatsoenlijke indexen aan te maken (mompelt iets over een wurgcontract) en dat we zo vast aan het systeem zitten dat we het ook niet kunnen inruilen voor een ander systeem.

Ik heb dus niet echt een optie behalve het dagelijks herindexeren (daarom zei ik eerder ook dat ik het liever eenmaal per week doe).

[whoami]

Veertig indexen op één tabel .... dat doet me eerlijk gezegd in eerste instantie vermoeden dat er toch iets niet helemaal goed is aan je datamodel.

[leuk_he]

Verwijderd schreef op woensdag 21 januari 2009 @ 13:41:
Goed... en dan nu de praktijk :-). Welke fillfactor dien je nu bv te gebruiken voor een order tabel? Of is het hele fillfactor verhaal niet zo spannend?

Om te beginnen, dit is een optimalisatie waar je normaal niet aankomt tenzij je juist problemen hebt/verwacht met die tabel. Met dit soort optimalisatie win je typisch niet heel veel performance, met index (niet te veel) op de juiste colummen en getune queiries win je applicatie win je vele malen meer.

Je speelt met deze parameter als je verwacht dat de waarden waarop je indexeert regelmatig van lengte veranderen.

Stel je waarde is eerst "12345' en je update hem naar '12345-A' . Als je ruimte over hebt in je blok kan die op dezelfde index positie blijven, als je blok 100% vol is moet hij het nieuwe key-veld naar een nieuwe blok overflowen/herbalanceren. Zoals eerder gesteld is dit een normale actie voor indexen. Heb je echter kennis van je data in je indexen dan kun je mogelijk dit soort optimalisaties uitvoeren om het herbalanceren uit te stellen.

Dit is dus met name op tabbelen die je vaker update dan leest.aangezien je wat meer slack creeerd.

[DamadmOO]

whoami schreef op woensdag 21 januari 2009 @ 20:26:
Veertig indexen op één tabel .... dat doet me eerlijk gezegd in eerste instantie vermoeden dat er toch iets niet helemaal goed is aan je datamodel.

Gelukkig is het ook niet mijn datamodel

[Acid__Burn]

Soultaker schreef op woensdag 21 januari 2009 @ 19:14:
[...]
Ik vind het periodiek heropbouwen van indices maar een lelijke hack eigenlijk, en als je daarvan uit gaat zie ik niet hoe je ooit een server gaat inrichten die 24/7 op volle capaciteit beschikbaar is, want het rebuilden van indices is behoorlijk tijdrovend.

Ben ik niet met je eens. In SQL is het nodig (verplicht is een te groot woord) om indexes te rebuilden. Doe je dit niet, daalt je performance gigantisch. Kijk naar de reacties in dit topic, en je ziet dat het echt nodig is.

Maar vanaf SQL 2005 is het wel mogelijk om een 24/7 serverpark te hebben. Je kunt namelijk (correct me if i'm wrong, heb dit nog niet gebruikt) online indexen rebuilden. Dit zou dus een 100% uptime moeten garanderen...

[farlane]

Acid__Burn schreef op donderdag 22 januari 2009 @ 08:34:
Ben ik niet met je eens. In SQL is het nodig (verplicht is een te groot woord) om indexes te rebuilden. Doe je dit niet, daalt je performance gigantisch. Kijk naar de reacties in dit topic, en je ziet dat het echt nodig is.

Om die conclusie te trekken moet je toch wat meer informatie hebben over de situaties waarbij dat nodig is. Het zou bijv goed kunnen zijn dat de mensen die hier zeggen dat het absoluut nodig is met het rebuilden een brak datamodel of een "minder optimale oplossing" maskeren.

[DamadmOO]

Acid__Burn schreef op donderdag 22 januari 2009 @ 08:34:
[...]
Maar vanaf SQL 2005 is het wel mogelijk om een 24/7 serverpark te hebben. Je kunt namelijk (correct me if i'm wrong, heb dit nog niet gebruikt) online indexen rebuilden. Dit zou dus een 100% uptime moeten garanderen...

Dat kan inderdaad. Wel zijn er een aantal voorwaarden waar de index aan moet voldoen en ook moet je de enterprise versie hebben.
De datatypes varchar, nvarchar en varbinary mogen geen MAX as length hebben en er mag geen xml, image, text of ntext in de index zitten.

[Acid__Burn]

farlane schreef op donderdag 22 januari 2009 @ 09:11:
[...]
Om die conclusie te trekken moet je toch wat meer informatie hebben over de situaties waarbij dat nodig is. Het zou bijv goed kunnen zijn dat de mensen die hier zeggen dat het absoluut nodig is met het rebuilden een brak datamodel of een "minder optimale oplossing" maskeren.

Dit hoeft ook niet zo te zijn. Het kan best zijn dat je een uitstekend datamodel hebt, maar dat je toch indexes moet rebuilden. Het ligt namelijk ook aan het aantal reads/writes op de tabel/index.

DamadmOO schreef op donderdag 22 januari 2009 @ 09:53:
[...]
Dat kan inderdaad. Wel zijn er een aantal voorwaarden waar de index aan moet voldoen en ook moet je de enterprise versie hebben.
De datatypes varchar, nvarchar en varbinary mogen geen MAX as length hebben en er mag geen xml, image, text of ntext in de index zitten.

Ok, dat is dan wel weer jammer. MAX gebruik ik zo min mogelijk in tabellen, maar het komt wel eens voor helaas. Dan heb je dus niet de mogelijkheid om online indexes te rebuilden, en ben je dus afhankelijk van een dag in het weekend, of downtime in de avond uren...

[ Voor 35% gewijzigd door Acid__Burn op 22-01-2009 11:12 ]

[DamadmOO]

Acid__Burn schreef op donderdag 22 januari 2009 @ 11:07:
[...]
Ok, dat is dan wel weer jammer. MAX gebruik ik zo min mogelijk in tabellen, maar het komt wel eens voor helaas. Dan heb je dus niet de mogelijkheid om online indexes te rebuilden, en ben je dus afhankelijk van een dag in het weekend, of downtime in de avond uren...

Het gaat er alleen om dat de index geen MAX mag bevatten. De tabel mag dat volgens mij gewoon wel.

[Acid__Burn]

DamadmOO schreef op donderdag 22 januari 2009 @ 14:26:
[...]

Het gaat er alleen om dat de index geen MAX mag bevatten. De tabel mag dat volgens mij gewoon wel.

Ik ga me er toch eens in verdiepen. Klinkt namelijk best interessant dat online index rebuilden

[Soultaker]

Acid__Burn schreef op donderdag 22 januari 2009 @ 08:34:
Maar vanaf SQL 2005 is het wel mogelijk om een 24/7 serverpark te hebben. Je kunt namelijk (correct me if i'm wrong, heb dit nog niet gebruikt) online indexen rebuilden. Dit zou dus een 100% uptime moeten garanderen...

Ik mag aannemen dat dat nu ook al "online" kan (d.w.z. zonder dat de indices tijdens het rebuilden niet beschikbaar zijn)? Of bedoel je dat je database nu effectief onbruikbaar is terwijl je aan het herindiceren bent?

Dan is de situatie nog erger dan ik dacht, want waar ik op doelde was niet dat de indices tijdens het rebuilden onbeschikbaar zouden zijn, maar meer dat je door zo vaak te rebuilden een toch al overbelastte server nog eens meer gaat belasten. Dat lijkt me geen schaalbare oplossing.

[DamadmOO]

Soultaker schreef op vrijdag 23 januari 2009 @ 18:33:
[...]

Ik mag aannemen dat dat nu ook al "online" kan (d.w.z. zonder dat de indices tijdens het rebuilden niet beschikbaar zijn)? Of bedoel je dat je database nu effectief onbruikbaar is terwijl je aan het herindiceren bent?

Voorheen werd een index offline gehaald tijdens het rebuilden. Oftewel dan is hij tijdelijk niet bruikbaar. Met het online rebuilden blijft de index gewoon beschikbaar (wel iets trager, maar in ieder geval wordt het geen table scan).

Dan is de situatie nog erger dan ik dacht, want waar ik op doelde was niet dat de indices tijdens het rebuilden onbeschikbaar zouden zijn, maar meer dat je door zo vaak te rebuilden een toch al overbelastte server nog eens meer gaat belasten. Dat lijkt me geen schaalbare oplossing.

Ach.. wat is een overbelasting? Servers zitten nooit 24/7 op een piekverbruik. Er is dus altijd tijd om indexen te rebuilden. Als je geen tijd hebt voor een volledige run dan schedule je een job dat deze niet langer loopt dan een bepaalde tijd. En dat de belangrijkste indexen als eerste gerebuild worden (zodat deze in ieder geval klaar zijn als de tijdspan verloopt)