Performance Tuning

Uge 23: TempDb

Som lovet i sidste uge, kommer her et indlæg om TempDb og dens tilstædeværelse i SQL serverens infrastruktur.

Alle anvender TempDb fra tid til anden, nogen bruger den direkte, andre bruger den inddirekte uden at vide det.

Anvendelse af TempDb

Som skrevet ovenfor, så anvender alle, som anvender SQL server, TempDb. Det er et faktum som vi ikke kan komme uden om. Derfor er det også vigtigt at forstå hvordan den virker og hvordan vi som udviklere kan bruge den mest optimalt. TempDb har til formål, bl.a. at gemme følgende elementer:

  • User Objects
  • Internal Objects
  • Version Store

User Objects er temp-tables (dem med # eller ## foran), table variables og table valued functions. Temp tabellerne med # foran er lokale tabeller og kun tilgængelige i den specifikke session og forsvinder i samme takt som sessionerne lukkes. Dermed kan man også komme forbi problemet med Locks og Blocks for de eksisterer kun i den ene session. Hvorimod dem med ## foran er tilgængelig på tværs af sessioner og dermed globalt defineret. Slutteligt er der table variables (dem med @ foran) som også gemmes fysisk i TempDb og som er scopet til at ramme det nuværende batch i koden som eksekveres. Det er en gængs misforståelse at table variables gemmes i hukommelsen – de gemmes altid på TempDb. Tabeller som returneres fra en table valued function er også altid gemt i TempDb.

Foruden objekter defineret at slutbrugeren, anvender SQL serveren også selv internt TempDb til objekter. Ved f.eks at eksekvere DBCC CHECKDB eller DBCC CHECKTABLE, vil SQL serveren allokere plads i TempDb til tabeller som retuneres til slutbrugeren. Sort og Hash operationer i Execution planer som har Spill over, gemmer også data i TempDb. Cursore og Service Brokeren allokerer også plads på TempDb – ok jeg stopper nu 🙂 I kan se der er mange elementer hvor TempDb bliver anvendt.

Endeligt er der Version Store, som jeg skrev om for nogen uger siden, da emnet om Optimistic Concurrency blev gennemgået. Internt deler SQL serveren version store op i 2 dele – en Common Version Store til triggers, Snaphot Isolation, Read Committed Snapshot Isolation og Multiple Active Result Sets (MARS) og en Online Index Rebuild Version Store som bliver anvendt til at styre online index opreationer i SQL serveren.

TempDb konfiguration (on-prem)

Dette afsnit kommer til kun at handle om om-prem installationer af SQL serveren, Managed Instances i Azure og SQL server på VMs. Azure SQL som service, kan man ikke pille ved via nedenstående metoder og forslag.

At anvene TempDb med standard opsætning er ikke altid det bedste. Standard opsætningen er bedste gæt fra installationen af SQL serveren og er blevet bedre og bedre med tiden hen over de seneste versioner af SQL serveren.

F.eks er standard file-growth sat til 10% – dette kan medføre en række problemer:

  • For mange auto-growth handlinger over tid
  • Fragmentation i Log filen
  • Latch contention

Hvis man ved at TempDb skal bruge x MB plads på disken, så ville jeg som ændre indstillingen på størrelsen, så man ikke kommer ud i for mange auto-growth events – og vil man gerne basere sin TempDb på autogrowth, så sæt den til en fast størrele i stedet for en %-sats.

Transaktionsloggen for TempDb skal man også lige besøge. Autogrowth på denne er meget dyre og SQL serveren kan ikke anvende Instant File Initiation til transaktionsloggen. Dette betyder at SQL serveren ikke kan modtage transaktioner når autogrowth event er i gang. Autogrowth operationer i transaktionsloggen er som hovedregel et no-go scenarie.

Latch retention problemer har været store i tidligere versioner af SQL serveren, men de seneste versioner er installationen af SQL serveren begyndt at foreslå flere datafiler til TempDb baseret på serverens bestykning. Det betyder at SQL serveren kan benytte round robin allokeringsalgoritmer på tværs af datafiler.

Opsummering – TempDb

Som I har set i dag, så anvender alle som anvender SQL serveren også TempDb. Uanset om de vil det eller ej. Enten dirkete eller inddirekte. Derfor er det også vigtigt at have den i mente når man taler performance optimering af SQL serveren.

TempDb bør blive håndteret på samme niveau som en hver anden database på SQL serveren og have den samme opmærksomhed og vedligeholdelse.

Næste gang er sidste indlæg – så vi ses om en uge til afslutningen på dette forløb.

Husk at skrive dig på maillisten nedenfor, så du ikke misser næste udgave af denne blogserie. Jeg lover dig kun at sende en mail, når der er nyt i denne serie.

Få besked om næste indlæg

Skriv dig gerne op til at modtage en mail, ved næste indlæg. Det kan du gøre nedenfor.



Marketing stuff

Our emails contain marketing stuff, so we need to give you some fine quality fine print: brianbonk will use the information you provide on this form to email you with updates and marketing. You can change your mind at any time by clicking the unsubscribe link in the footer of any email you receive from us, or by contacting us at help@brianbonk.dk. We use Mailchimp as our marketing platform. By checking the box to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing, and that we may process your information in accordance with these terms.

Følg mig på Instagram

Leave a Reply

Your email address will not be published. Required fields are marked *

en_USEnglish