Uge 24: Vedligeholdelse af databaser

Så er der gået 24 uger siden jeg startede denne følgeton om Performance Tuning på SQL serveren. Så dette er sidste indlæg i rækken og kommer til at handle om vedligeholdelse af databaser.

Vedligeholdelse af databaser er en lige så kritisk opgave som at få den bedste performance ud af databaserne. En SQL database fungerer lidt som en bil; den skal have eftersyn en gang imellem for at tilsikre at den kører som den skal og yder bedst muligt. Een af de vigtigste elementer af denne del er vedligeholdelsen af index og deres relaterede objekter omkring statistics.

Vedligeholdelse af Index

Index bliver fragmenteret over tid og statistics skal opdateres så query optimizeren kan producere en execution plan som er “god nok”.

Fragmentation af index (både Clustered og Non-Clustered) betyder at den logiske og fysiske sortering ikke er ens. Hvis data er gemt på rotationsdiske afleder det vilkårlige læse-operationer på tværs af hele disken med en negativ I/O performance. Det samme gør sig også gældende på SSD diske, men ikke i så udbredt grad.

Microsoft har udstukket en række best practice omkring vedligeholdelse af index:

  • Fragmentation under 10%: Gør intet
  • Fragmentation mellem 10% og 30%: Udfør Index Reorganize operationer
  • Fragmentation over 30%: Udfør Index Rebuild opreationer

Hertil skal man huske på at Index Reorganize og Index Rebuild kun bør ske når index har mindst 10.000 pages på leaf-level. Hvis man har færre end det, vil man ikke opleve den den store negative effekt ved et fragmenteret index. En Index Rebuild genskaber hele index fra bunden af. Det er også en af de helt store transaktioner som er fuldt logget i transaktionsloggen. Derfor er det også ret skidt at lave en rollback ved 99% af færdiggørelsen. Statistics bliver også opdateret – af naturlige åsager, idet index bliver genskabt.

Hvis man anvender high availability opsætninger med database mirroring som baserer sig på transaktionsloggen, så vil man også opleve en stor performance nedgang ved en Index Rebuild. SQL Serveren skal “sende” alle transaktioner til replika og dermed kan man overfylde sit netværk med datastrømme og påvirke sin high-availability strategi.

Dets alternativ Index Reorganize tager kun fat i leaf-level af index og arrangerer disse pages i korrekt logisk rækkefølge. Derfor vil Index Reorganize heller ikke opdatere statistics. En sådan operation består af flere mindre transaktioner og vil derfor kun påvirke transaktionsloggen i mindre grad. Ligeledes vil det også have en mindre påvirkning af high-avaliability og netværk.

“How to” vedligeholdelse af databaser

Der er mange veje i mål her – een af dem jeg ikke vil anbefale er den indbyggede Database Maintenance Plans som kommer ud af boksen med SQL serveren. Denne tilgang arbejder med SQL serveren med en stor forhammer og vil altid lave en reorganize/rebuild uanset status på index og fragmentationen.

Det er set at jobbet til Database Maintenange som er kørt hele natten for at opdatere index uden eller med kun meget lidt fragmentation – det er lidt tosset at bruge alle de ressourcer på den slags unødigt arbejde.

Der findes andre og meget bedre løsninger som tilmed også er gratis. Bl.a. Brent Ozar og Ola Hallengren har lavet nogen ret lækre løsninger til dette formål. Begge løsnigner har en række stored procedures som man kan kalde og få returneret status på bl.a. consistency checks, backups, index maintenance osv osv.

Du kan finde løsningerne her (og det er ikke reklame, kun min egen lyst til at dele):

Opsummering og tak for denne gang – Vedligeholdelse af databaser

Det var så det sidste indlæg i denne række om SQL Server Performance Tuning.

Jeg har gennem de sidste 24 uger og indlæg, forsøgt at hjælpe dig, som læser, godt på vej med at forstå den underliggende struktur og motor i SQL serveren, og dermed blive bedre til at selv at finde og løse problemerne med performance.

Tak for at følge med – og måske vi “læses ved” en anden god gang.

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.

Uge 22: Wait og I/O statistics

Jeg lovede jer i sidste uge at skrive lidt mere om Wait og I/O statistics.

De to metrikker og målinger er, for mig at se, nogen af de vigtigste elementer i performance monitoring

Wait Statistics

Hver gang en query bliver eksekveret på SQL serveren, kommer den query til at vente. Ved første øjekast lyder det som en dårlig ting, men når man kender årsagen, giver det faktisk mening. Hver gang en query venter, holder SQL serveren styr på ventetiden gennem Wait Statistics. Ventetiden skyldes to hovedårsager:

  • Asynchronous Ressource Waiting
  • Cooperative Scheduling

Lidt mere i detaljer om hver af disse.

Hver gang en query venter på noget på SQL serveren, det kan være en ressource som ikke er ledig, som f.eks. en datapage som er låst eller endnu ikke i buffer pool, så kommer querien i Suspended status på SQL serveren. Query’en venter i denne status indtil de fornødne ressourcer bliver tilgængelige igen.

Når en ressource bliver tilgængelig, skifter queryen status til Runnable og venter igen indtil CPU bliver tilgængelig. Når CPU er tilgængelig skifter den igen status til Running og forløber indtil en evt. påkrævet ressource ikke er tilgængelig. Når det sker kommer querien tilbage til suspended status og processen starter forfra.

Wait og I/O statistics

I tillæg hertil vil en query også vente på baggrund af Cooperative Scheduling som SQL serveren implementerer i SQLOS (det underliggende operativ system). SQL serveren tilsikrer at flytte queries væk fra CPU når disse har forløbet i ca. 4 ms. Grundet denne implementering, vil queries altid vente på et eller andet tidspunkt. Så snart en ressource er optaget eller så snart den har forløbet i 4 ms på CPU. Så man skal ikke være opmærksom på at man har Wait statistics på sin SQL server – det har alle – det er et spørgsmål om hvor højt det tal er.

Hver gang en Wait tilstand opstår bliver ventetiden tracket automatisk af SQL serveren gennem Wait Statistics. Man kan se disse metrikker gennem det dynamic mangement view som hedder sys.dm_os_wait_stats. Hver række repræsenterer en specifik Wait Type. Ved at gennemgå disse Wait Types og resten af indholdet i dette view, kan du få et indblik i hvad der kan være galt med performance på SQL serveren.

I/O Latency Statistics

Den anden del af statistics er I/O Latency Statistics som SQL serveren også holder styr på og man kan få fingrene i data omkring. Med denne metrik kan man få indblik i hvilke filer, der har problemer og giver ventetider. SQL serveren rapporterer disse metrikker gennem sys.dm_io_virtual_file_stats. Hertil skal man indsætte et database_id og file_id. Hvis man angiver null returnerer funktionen resultatet fra alle underliggende filer på SQL serveren.

En af de vigtigste elementer af resultatet er kolonnen io_stall_read_ms og io_stall_write_ms. Disse angiver i summeret tal ventetiderne siden den sidste genstart af SQL serveren. Ved at dele disse tal med num_of_reads og num_of_writes kan man få den gennemsnitlige ventetid for reads og writes til disk.

Hvis man modtager høje tal i dette resultatsæt, kan man købe hurtigere diske, men det er ikke altid løsningen. Og slet ikke på en Azure SQL. I stedet bør man kigge på eks. TempDb anvendelsen og om man kan nedbringe dette. F.eks gennem en anden indexeringsstrategi eller ved at skrive de underliggende queries om.

Wait og I/O Statistics giver dig svar på symptomer. De underliggende årsager er op til dig at finde frem til.

Oprummering – Wait og I/O statistics

Pyha – sikke en omgang – men ikke desto mindre en vigtig viden at have med sig i arbejdet med performance tuning på SQL serveren.

I næste uge skriver jeg lidt om TempDb – og hvad tanker er bag denne set fra et SQL servers synspunkt.

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.

en_USEnglish