Correct instellen van SQL Server tempdb (best-practice)

Nog zeer regelmatig kom ik SQL Servers tegen, waarbij de tempdb niet optimaal is ingericht. Af en toe bekruipt mij het gevoel dat de tempdb database het sluitstuk van een SQL Server installatie is. Aan die database administrators geef ik graag mee dat tempdb een essentiële bouwsteen van de SQL Server is, die de performance van SQL Server kunnen maken of breken.

Waar tempdb het meest voor wordt gebruikt

  • frequent maken en verwijderen van tijdelijke tabellen
  • tijdelijke objecten die bij CURSORS worden gebruikt
  • tijdelijke objecten die in combinatie met ORDER BY worden gebruikt
  • tijdelijke objecten die in combinatie met GROUP BY worden gebruikt
  • tijdelijke objecten die verband houden met het samenvoegen van data uit meerdere tabellen (HASH PLANS)

Bovenstaande acties kunnen tot opstoppingen in de tempdb leiden en daarmee de doorlooptijd van query’s.

Hoe wordt tempdb het beste ingericht

  • Verhoog het aantal data bestanden. Voor machines met minder dan 8 processoren: houd het aantal databestanden gelijk aan het aantal processoren; bij meer dan 8 processoren, gebruik om te beginnen 8 data bestanden. Mocht dat niet voldoende zijn om opstopping tegen te gaan, verhoog dan het aantal data bestanden met een veelvoud van vier totdat het probleem met opstoppingen is opgelost óf het aantal data bestanden gelijk is aan het aantal processoren. Het advies is, zeker wanneer er meer dan acht data bestanden voor tempdb nodig blijken, de opzet van de gebruikte query’s nog een keer na te lopen.
  • Zorg ervoor dat de databestanden van de tempdb altijd even groot zijn.
  • Het is beter om meerdere kleinere databestanden in de tempdb te hebben, dan één grote
  • Tot SQL Server 2014 kan het aanzetten van trace flag 1118 een significante verbetering in de performance betekeken. Trace flag 1118 verlicht de druk op de zogenoemde (S)GAM pagina’s, omdat alle enkelvoudige pagina toewijzingen worden uitgeschakeld. Dit gedrag is standaard vanaf SQL Server 2016; trace flag 1118 hoeft op deze versie en hoger niet te worden aangezet.
  • Zet de databestanden en logbestanden van de tempdb bij voorkeur op een aparte fysieke schijf; niet zijnde de C-schijf
  • Zorg voor een ruime autogrowth (in plaats van de standaard 64Mb die Microsoft gebruikt)
  • Monitor de groei van de tempdb

Gebruikte bronnen vanuit Microsoft:
Microsoft Docs: tempdb database
Support Microsoft: Recommendation to reduce allocation contention
Micorsoft Docs: Working with tempdb in SQL Server 2005