Een steeds terugkerend pijnpunt bij mijn SQL Server reviews betreft het inrichten van een maintenance plan: ik ben nog geen goede tegen gekomen! Waarom niet, Er zit toch zo’n prachtige maintenance plan wizard in SQL Server Management Studio?! Klopt, maar juist daarom verbaast het me niet dat ik nog geen goede ben tegen gekomen. Want die maak je daar niet mee! Ik zal me nader verklaren.

De Maintenance Plan Wizard leidt je langs een aantal bouwblokken die je aan en uit kunt zetten. Elk van die bouwblokken heeft zijn nut. Maar je krijgt vreselijk de neiging om alles aan te vinken wat aan mogelijkheden geboden wordt. Wat resulteert is een draak van een ding:

  • Shrink Database: doe dat alsjeblieft nooit, want dit leidt tot hevige fragmentatie in je database. Waarom? Omdat het ongeveer zo werkt: het data bestand wordt doorlopen van achter naar voren. Gealloceerde pagina’s worden verplaatst naar nog vrije pagina’s voor in het bestand, zodat na afloop alle vrije ruimte achterin het bestand zit. Die vrije ruimte wordt dan vervolgens aan het OS terug gegeven. Aldus krimpt het bestand. Wat rest is een hevig gefragmenteerde database, omdat de index en data paginas niet meer fysiek in sleutelvolgorde in het bestand zijn opgeslagen. Wat wel nuttig kan zijn is om handmatig een shrink van de logfile te doen na een beetje uit de hand gelopen transactie die het logebstand uit zijn voegen heeft doen barsten. De enige keer dat ik schoorvoetend accoord zou gaan met het shrinken van de database is na een rigorieuze opschoonactie in de database, zodat er relatief veel ruimte is vrijgekomen. Maar vergeet daarna niet de indexen de herbouwen om de fragmentatie op te heffen. Herbouwen van de index zorgt ervoor dat je tijdelijk extra ruimte in de database nodig hebt. Tijdelijk, dus na afloop houd je die extra ruimte weer over. Begin je al te zien hoe nutteloos het shrinken van een database is?
  • Een Index Rebuild na een Index Reorganize (want je hebt ze allebei aangevinkt in de Wizard, weet je nog?). Onzinnig! Een Index Reorganize schuift de paginas in het onderste niveau in de index boom in elkaar, om achteraan vrije ruimte over te houden. Dit is een soort shrink, maar iets intelligenter, omdat de sleutelvolgorde gehandhaafd blijft. Maar goed: waarom zou je dit doen als je direct daarna de index boom volledig opnieuw opbouwt met een Index Rebuild? Nadat de nieuwe index boom is gebouwd, wordt er in de meta data van de tabel de verwijzing van de oude index boom verlegd naar de nieuwe index boom en vervolgens wordt de oude index boom gedealloceerd. Geef toe: het was een hele mooie gereorganiseerde oude index boom :-)
  • Het index rebuild bouwblok in het Maintenance Plan is een alles-of-niets optie; er wordt geen rekening gehouden met het fragmentatieniveau in de index. Het herbouwen van een index is een kostbare aangelegenheid in termen van resources; resources die dan niet gebruikt kunnen worden voor het uitvoeren van business vraagstukken. Kortom: doe het vooral niet onnodig vaak. Maar wat is onnodig vaak? De dynamic management function sys.dm_db_index_physical_stats geeft daar deels een antwoord op. Deze functie geeft het fragmentatie-niveau per index.
    Een veelgebruikte richtlijn is: minder dan 5% fragementatie: niks aan doen; tussen de 5 en 30% fragmentatie: reorganiseren en meer dan 30% fragmentatie: herbouwen.
  • Een update statistics na een index rebuild. Wist je al dat het zij-effect van een index rebuild is dat de statistics op de sleutelattributen van de index volledig zijn bijgewerkt? En wist je ook dat een update statistics bij een grote tabel standaard niet alle rijen beschouwt, maar steeksproefgewijs werkt en dus minder nauwkeurige statistics oplevert? Dus is het zonde om de perfecte statistics die het resultaat waren van de index rebuild ongedaan te maken met een update statistics! Natuurlijk zijn er ook nog auto-created stats op niet sleutel-attributen en die mogen wel onderhouden worden. Ik doe dat dan liever met de stored procedure sp_updatestats, omdat deze stored procedure detecteert dat de index op de sleutelattributen van indexen al up-to-date zijn.
  • Een standaard maintenance plan maakt wel netjes SQL Server Agent jobs aan voor de scheduling daarvan, maar er worden geen notificaties op de jobs gezet. Dus onlangs kreeg een klant de schrik van zijn leven toen ik hem meedeelde dat de backup job van zijn belangrijkste database al drie maanden lang consequent faalde! Vergeet dus zelf geen job-notificaties te configureren. Hoe dat moet zal ik beschrijven in een volgende blog post.

Hoe moet het dan wel? Ik maak gebruik van een maintenance solution die een van onze vakbroeders in elkaar heeft gezet en tot ons aller nut gratis beschikbaar stelt, zie: ola.hallengren.com. Deze oplossing is al meerdere malen bekroond in de SQL Server community. De oplossing bestaat uit een viertal intelligente stored procedures die via SQL Server Agent worden gescheduled. Ook te gebruiken in combinatie met populaire backup tools, zoals Lightspeed. Ik zou zeggen: laat de Maintenance Plan Wizard links liggen en ga direct naar ola.hallengren.com, maar vergeet de job notifications niet!