T-SQL Arkiv » Brian Bønk

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.

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.

Uge 21: Performance Monitoring

Men hvordan kan du så bruge disse elementer og den nye viden til at komme i gang med at monitorere og afhjælpe performance problemer på SQL serveren? Det tager jeg lidt hul på i dag med performance monitoring.

Så er det ved at være sidste udkald i denne rækkefølge af indlæg. Og over de sidste 5 måneder har jeg forsøgt at hjælpe dig godt i gang med at forstå nogen af de forskellige aspekter af performance tuning på SQL serveren.

En baseline

Mange sidder foran deres computer og har performance problemer på deres SQL server. Nogen i højere grad end andre og de har måske ingen ide om hvordan man kommer i gang, og finder den underliggende årsag til problemet, eller hvordan man løser det.

Svaret kan være irriterende for nogen, men det gælder om at indsamle og monitorere information om den nuværende situation og sammenligne den med en baseline for at identificere de mest fremtrædende årsager til problemerne. Ja, det er lidt irriterende, der skal være en baseline – ellers bliver det en famlen i blinde for at løse problemerne.

Ideen bag denne tilgang er den jeg synes er den mest simple. I første step indsamles data og nøgletal for SQL serveren og dens installation og konfiguration. Denne indsamling bliver til baseline. I næste afsnit herunder, kommer jeg lidt ind på forslag til hvilke data der skal indsamles.

Efter baseline er etableret, er det muligt at gennemse disse for performance problemer – der findes en række værktøjer til dette som jeg kommer ind på lidt senere i dette indlæg.

En lille ændring ad gangen – jeg skriver det lige igen – en lille ændring ad gangen. Det er nok det vigtigste at huske. Hvis der bliver ændret for mange ting på een gang, ved du ikke, hvad der er afhjulpet problemet, eller, hvis det skulle ske, om en af delene har givet en dårligere performance. Det er her hele arbejdet ligger.

Efter ændringen er blevet implementeret skal man måle på data igen – de samme som ligger til grund for baseline. Disse skal så sammenlignes for at se om det har givet den forventede performance optimering. De nye indsamlede data bliver så den nye baseline og processen starter forfra. Det er også vigtigt at bestemme sig for, hvornår godt er godt nok. Man kan hurtigt blive vugget ind i en proces, hvor man hele tiden finder små tilpasninger, ændringer, tweaks og andet godt, der liiiiige giver det sidste.

Indsamling af baseline data

Når man skal indsamle baseline data og metrikker for performance, er det vigtigt at vide hvilke elementer der bør være med. Der findes så mange forskellige metrikker og målinger i SQL serveren gennem forskellige værktøjer, at man ikke kan tage dem alle med. For ikke at komplicere tingene for meget, kunne et bud på de første elementer til en baseline være:

Specifikke SQL Server performance målinger
Wait Statistics
I/O statistics

De specifikke performance målinger kommer herunder og i næste uge zoomer jeg ind på de to andre (wait statistics og I/O statistics). At få fat i de specifikke (og relevante) performance målinger kan gøres bl.a. via Performance Analysis og Logs (PAL) værktøjet som du kan finde her. Det er et værktøj, som er bedst til on-prem installationer, og med nogen år på bagen, men virker fint til formålet.

PAL værktøjet har nogen forberedte skabeloner som man kan anvende. Disse skabeloner kan importeres direkte til Windows Performance Monitor og output bliver en meget stor HTML fil med rappoter der viser mulige elementer med performance problemer. Et eksempel kunne se ud som nedenstående.

Ved opstart af arbejdet med performance problemer, kan det være godt at starte med det samme værktøj hver gang og kende det til fulde.

Til brug for azure installationer kan man anvende azures indbyggede performance counters fra portalen og her analysere sig frem til problemerne. Microsoft har også gjort rigtig meget ud af at komme med forbedringsforslag til bl.a. manglende index eller index der ikke bør være der.

I azure er det samme fremgangsmåde – etabler baseline – og her kan man direkte også gemme sine performance analyser og genkøre dem – ligesom med PAL værktøjet ovenfor.

Opsummering – performance monitoring

I dag har jeg forsøgt at hjælpe dig godt på vej i arbejdet med at etablere en baseline og hvilke værktøjer man kan anvende til det formål.

I næste uge skriver jeg som sagt lidt mere om Wait og I/O statistics.

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 20: Deadlocks

Så er det fredag igen og tid til endnu et indlæg i rækken om SQL Server performance tuning. Denne gang om Deadlocks, som er situationen hvor 2 samtidige queries begge venter på hinanden og ingen kan fortsætte uden den anden stopper. Jeg skriver her lidt om hvordan SQL serveren håndterer deadlocks, hvilke typer der findes og hvad man evt. kan gøre for at undgå dem.

Deadlock håndtering

Det gode ved Deadlocks er at SQL serveren automatisk og helt selv finder dem og løser dem. For at løsne en deadlock bliver SQL serveren nødt til at stoppe og rulle en transaktion tilbage. Her er det kodet fra Microsofts side at det altid er den billigste transaktion som vælges. Og den billigste her, er den der har skrevet færrest bytes til transaktionsloggen.

SQL serveren har fået implementeret deadlock overvågningen i en baggrundsproces kaldet Deadlock Monitor. Denne proces kører hvert 5. sekund og kontrollerer den nuværende status for deadlocks. I værste fald kommer en deadlock situation højst til at vare 5 sekunder. Den query som bliver valgt til at være offeret (Deadlock Victim) får fejlkoden 1205 og bliver rullet tilbage. Det lækre ved den metode, er at man altid ender med en status quo.

Processen for at komme videre fra en oplevet deadlock kunne se sådan her ud:

Kontroller for fejlkode 1205 når der sker en fejlmeddelelse
Sæt den overordnede applikation på pause for at give plads til alle andre transaktioner at rulle færdigt og dermed løsne alle locks
Genkør Deadlock Victim query som blev rullet tilbage

Efter denne proces, bør alt kunne rulle videre igen uden problemer. Herefter skal der selvfølgelig ses efter og findes årsagen og få den løst, så det ikke sker igen.

Man kan finde og fejlsøge på deadlocks ad flere metoder på SQL serveren. SQL Server Profiler (den gamle metode) giver en Deadlock Graph event, som sker lige så snart en deadlock bliver fundet. I disse moderne tider med Azure SQL og SQL server 2019 (seneste version ved denne indlægs oprettelse) så er der også mulighed for at anvende Extended Events. Her kan man opsætte en overvågning på system_health event, som holder øje med og historisk gemmer deadlocks siden seneste genstart af SQL serveren. En sidste mulighed er at enable trace flag 1222 som gør at SQL serveren skriver deadlock information direkte i error loggen.

Typer af deadlocks

Der findes flere forskellige typer af deadlocks, her kommer lidt mere om de mest gængse typer.

En typisk deadlock, som man ofte kan støde på, er Bookmark Lookup Deadlock (et nørdet blogindlæg fra Dimitri Korotkevitch kan læses her), som opstår når der kommer flere på hinanden samtidige Reads og Writes aktiviteter til et Clustered eller Non-Clustered Index. Det sker ofte pga. en lidt uhensigtsmæssig indexeringsstrategi. En bookmark lookup deadlock kan imødekommes og elimineres ved f.eks. at lave et covering Non-Clustered Index som jeg skrev lidt om i uge 8.

En anden meget typisk deadlock er Cycle Deadlock, hvor flere queries forsøger at tilgå den samme tabel i forskellig rækkefølge. For at undgå denne deadlock er det vigtigt at tilsikre at flere samtidige queries tilgår den samme tabel på den samme måde.

Den, måske, flotteste deadlock på SQL serveren er Intra-Parallelism Deadlock, hvor en paralellism operator (Distribute Streams, Gather Streams eller Repartition Streams) har ført til en intern deadlock. En sådan deadlock kan vises på den tidligere nævnte Deadlock Graph – og de er næsten som kunst på SQL serveren – her et eksempel:

Der er flere eksempler i billedteksten – prøv at se dem også.

Grafen er som sagt næsten kunst og opstår fordi SQL serveren selv har ramt en bug. Uheldigvis bliver denne bug ikke løst af Microsoft, da en løsning potentielt kan skabe regressioner. Derfor kan man kun komme uden om disse deadlocks ved at tilsikre at disse queries (udvalgte, ikke alle queries), anvender single-threaded execution plans:

Med en indexeringsplan så query cost kommer under Cost Threshold for Parallelism
Query hint så SQL serveren kun anvender en tråd – MAXDOP 1

En anden tilgang, og næsten en mirakelkur, er at enable optimistic concurrency med Read Committed Snapshot Isolation (RCSI).

Opsummering – deadlocks

Som du har læst i ovenstående, så bliver deadlocks automatisk håndteret af SQL serveren ved at rulle den billigste transaktion tilbage. Det er naturligvis stadig vigtigt at nedbringe antallet af deadlocks i et system, så brugerne ikke oplever fejl eller manglende gennemførte transaktioner i deres applikation.

I næste uge starter de sidste 4 uger af denne række af indlæg, og her tager jeg hul på hvordan man kan lave performance overvågning og fejlsøgning på SQL serveren.

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 19: Lock Escalation

Så er vi i gang med endnu et afsnit af denne følgeton. Hvis du er med stadig, så er der gode ting på programmet. Denne gang skriver jeg lidt om Lock Escalations som er en af fænomenerne man kan opleve ved SQL serveren. Et såkaldt lock-hierarki er bygget dybt ind i maven på SQL serverens motor.

Lock Hierarkiet

Da jeg skrev om pessimistisk concurrency, skrev jeg også om at SQL serveren anvender Shared og Exlusive Locks på række-niveau. Desværre er det kun en del af hele billedet. Det samlede billede af Locks kræver at man også kigger på den granilaritet hvori locks bliver sat. Det kan være på alle niveau’er fra database til række.

Så snart en database tages i brug, vil den givne session mod databasen få en Shared Lock. Denne Lock betyder at ingen kan slette databasen eller genskabe fra en backup, så længe denne Lock er aktiv. Operationerne vil simpelhen blive blokkeret grundet den aktive session. Ligesom de nævnte Shared Locks og Exclusive Locks på række-niveau, eksisterer der også såkaldte Intent-Locks på tabel- og page-niveau.

Med en Shared Lock på række-niveau, kommer også en (IS) Intent Shared Lock på tabellen og tilhørende page
Med en Exclusive Lock på række-niveau, kommer også en (IX) Intent Exclusive Lock på tabellen og tilhørende page

Intent Locks anvendes til at signalere at en inkompatibel Lock allerde er givet på en lavere niveau i Lock-hierarkiet. Intent Locks er mestendels en performance optimering for den relationelle motor. Uden dem skulle Lock Manager (intern service i SQL serveren) gennemgå samtlige locks på lavere niveau, for at tilsikre at en lock på et højere niveau kan tildeles. Hvis der for eksemplets skyld eksisterer en IX Lock på en tabel, så kan man ikke få en X Lock på samme tabel, idet nogen data-rækker allerede er låst med Exclusive Lock fra tabellen selv.

Uheldigvis koster denne multi-level lock metode også ressourcer på SQL Serveren. Hver enkelt Lock “koster” 96 bytes af SQL serverens hukommelse (RAM). Derfor er det vigtigt for SQL serveren at tilsikre at queries kun anvender så få Lock bytes som overhovedet muligt.

Lock Escalations

Et eksempel er altid godt at fortælle med, så forestil dig følgende scenarie: Et update statement der rammer 1 million rækker spredt på 200.000 data pages. I dette eksempel skal SQL serveren tilsikre 1 million Locks på data-rækkerne, 200.000 IX Locks på data pages og en enkelt IX Lock på tabellen. Alene disse 1.200.001 Locks fylder ca 110 MB i memory fra Lock Manageren – kun for denne ene query. Den slags hukommelsesforbrug er uhensigtsmæssig, hvorfor der er en ekstra feature i SQL serveren Lock Escalations som træder i kraft ved mere end 5.000 Locks på eet niveau – som her på række-niveau. Her vil SQL serveren eskallere de individuelle række Locks til en mere grovkornet Lock på tabellen:

Individuelle X Locks bliver eskalleret til 1 tabel X Lock
Individuelle S Locks bliver eksalleret til 1 tabel S Lock

Nedenståene illustrerer hvordan Locks ser ud før og efter Lock Escalation.

Forbruget af hukommelse er nedbragt betragteligt ved denne tilgang, men det påvirker også muligheden for parallelle queries på databasen. En X Lock på en tabel betyder at ingen andre kan læse eller skrive til den tabel – En S Lock på en tabel betyder at tabellen er read-only. Databasens gennemstrømning af queries falder.

SQL Serveren trigger Lock Escalation når der er mere end 5.000 Locks på samme niveau. Dette er hard-coded ind i maven på SQL serveren og kan desværre ikke ændres ved en konfiguration eller hack. Siden SQL server 2008 har det været muligt at styre lidt af Lock Escalations gennem et ALTER TABLE DDL statement.

ALTER TABLE MyTableName
SET
(
   LOCK_ESCALATION = TABLE -- or AUTO or DISABLE
)

Pr. default bliver Lock Escalations fra rækker altid forhøjet til tabel (fra ovenstående LOCK_ESCALATION = TABLE). Hvis man sætter den til AUTO vil SQL serveren eskallere til partitions niveau – men pas på med denne feature, da det kan føre til ekstra Deadlocks, hvis man tilgår partitionerne i vilkårlig rækkefølge. Muligheden for DISABLE fjerner helt Lock Escalation på tabellen – med forhøjet forbrug af RAM som tidligere nævnt. Det gode spørgsmål her er så hvordan man mest effektivt kan lave en UPDATE eller DELETE query på mere end 5.000 rækker i en tabel?

Her 3 forslag:

Delvis opdatering af de 5.000 rækker i mindre dele – eks. gennem en WHILE loop
Anvend partition switching – hvis tabellen er partitioneret
Midlertidigt LOCK_ESCALATIONS = DISABLE – men pas på med RAM forbruget i perioden

Opsummering – Lock Escalation

Lock Escalation er en form for sikkerhedsnet på SQL serveren. Med god grund, men det introducerer også en række afledte følger. Derfor er det vigtigt at man meget velovervejet skriver T-SQL kode som håndterer mere end 5.000 rækker i samme transaktion. Måske skal det gøres i mindre dele i stedet for een stor UPDATE eller DELETE query.

I næste uge skriver jeg lidt videre om Locks og Blockings på SQL serveren og tager fat på Deadlocks, hvad det er og hvordan SQL serveren arbejder med disse.

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 18: Optimistic Concurrency

Velkommen tilbage til denne følgeton. I sidste uge skrev jeg lidt om Pessimistic Concurrency og lovede at skrive lidt om det modsatte i dag – Optimistic Concurrency.

Row Versioning

Optimitic Concurrency blev introduceret med SQL Server 2005 og baserede sin metodik på princippet Row Versioning. Ideen bag Row Versioning er at SELECT queries ikke påkræver de Locks som jeg skrev om i sidste uge. I stedet for at vente på at en Shared Lock er tildelt, så kan SELECT statements få en tidligere committed version af rækken. Disse versioner er gemt i Version-Store, som er en del af TempDb. INSERT, UPDATE og DELETE statements kopierer “gamle” versioner til Version-Store i TempDb og lader en pegepind fra den nye række linke til den gamle række i TempDb. En illustration som nedenstående forsøger at beskrive det.

En afledt effekt af at tilføje disse pegepinde er at alle rækker bliver udvidet med 14 bytes. Dette kan betyde følgende:

Forwarding records i Heap Tabeller
Page Splits i Clustered Tabeller (dem med clustered index)

Dertil bør man også overveje at tilpasse TempDb størrelsen tilsvarende, da man kommer til at introducere ekstra I/O som kan lede til flaskehalse i et default setup.

Der er overordnet to Isolation Levels i Optimistic Concurrency på SQL Serveren – dem gennemgår jeg nedenfor.

Optimistic Isolation Levels

Siden SQL server 2005 har der været to Isolation Levels omkring Optimistic Concurrency som baserer sig på den før omtalte row versioning.

Read Committed Snapshot Isolation (RCSI)
Snapshot Isolation (SI)

Et mere detaljeret blik på disse to kommer her.

RCSI giver Statement Level Snapshot Isolation. Med andre ord, så vil SQL Serveren altid returnere den række som var committed og valid på det tidspunkt hvor querien startede sin eksekvering. Det er en optimistisk implementering af Read Committed Isolation level fra sidste uge. Derfor vil man også opleve Non-Repeatable Reads med denne Isolation Level.

En af de gode ting ved RCSI er at det er fuldstændig transperant overfor databasen og applikationen. Det er kun et spørgsmål om at slå det til på databasen, så vil alle fremtidige queries få deafult Isolation Level Ream Committed Snapshop. Derfor kan man også komme forbi Locking og Blocking problemer – selv Deadlocks kan komme til livs ved at bruge RSCI. Nedenstående kodestumper slår RSCI til på en given database.

-- Enable Read Committed Snapshot Isolation (RCSI)
ALTER DATABASE AdventureWorks2012 SET READ_COMMITTED_SNAPSHOT ON
GO
-- Check if RCSI is now enabled
SELECT
     name,
     is_read_committed_snapshot_on
FROM sys.databases
WHERE database_id = DB_ID('AdventureWorks2012')

Hvis du derimod ønsker Repeatable Reads for alle SELECT statements i en Optimisitc Concurrency, så kan man anvende Snapshop Isolation. Dette giver databasen Repeatable Reads Out-Of-The-Box som dermed returnerer den række som var gældende ved starten af querien (transaktionen). Desværre er Snapshop Isolation ikke helt transperant.

Snapshot Isolation kan kun sættes på enkelte sessioner og skal slås til eksplicit. Derfor skal der kodeændringer til for applikationer, hvis de skal bruge denne funktion.
Queries kan komme i såkaldt Update Confllicts, hvor SQL serveren ruller en transaktion tilbage, selv om den vil være valid nok hvis der ikke var andre samtidige queries der ramte de helt samme rækker. Denne problemstilling skal også håndteres i applikationen.

Nedenstående kodestump viser hvordan man kan sætte Snapshop Isolation på en specifik database, og hvordan man derefter sætter det på en transaktion.

-- Enable Snapshot Isolation (SI)
ALTER DATABASE AdventureWorks2012 SET ALLOW_SNAPSHOT_ISOLATION ON
GO
-- Check if SI is now enabled
SELECT
     name,
     snapshot_isolation_state,
     snapshot_isolation_state_desc
FROM sys.databases
WHERE database_id = DB_ID('AdventureWorks2012')
GO
USE AdventureWorks2012
GO
-- Setting the Isolation Level to Snapshot Isolation
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO

Opsummering – Optimistic Concurrency

I dag har jeg gennemgået de 2 optimistic isolation levels som SQL serveren supporterer og har gjort siden 2005. Read Commited Shapshot Isolation giver statement-baseret isolation, mens Snapshot Isolation giver transaktions-baseret isolation. Fordi begge isolation levels anvender Version-Store i TempDb, skal man være meget opmærksom på specifikation af denne.

I næste uge skriver jeg lidt om Lock Escalations.

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 17: Pessimistic Concurrency

Den 5. måned starter ud med Locking, Blocking og Deadlocking på SQL Serveren.

SQL serveren anvender to modeller til at definere hvordan parallelle queries er eksekveret. Ikke dermed ment de queries som en enkelt query som eksekveres på flere tråde, men to forskellige queries som eksekveres samtidig.

I dette indlæg vil jeg give dig et overblik over de forskellige Isolation Levels som er den del af den pessimistiske model (pessimistic concurrency) for parallelle queries. I næste uge kommer en gennemgang af den optimiske model.

Pessimistiske Isolation Levels

Pessimistisk parallellitet betyder at SELECT queries blokerer for INSERT, UPDATE og DELETE queries, og vise versa. SQL serveren anvender Locks til dette formål.

SELECT får tildelt Shared Locks (S)
INSERT, UPDATE og DELETE for tildelt Exclusive Locks (X)

Begge Locks er ikke kompatible med hinanden. Dette betyder at man ikke kan læse fra og skrive til den samme datarække på samme tid. Hvis dette bliver forsøgt opstår der et såkaldt Blocking scenarie. Når man sætter en specifik Isolation Level på en transaktion, indvirker man direkte hvordan SELECT queries skal opretholde deres S locks. Man kan derimod ikke påvirke X locks – de bliver altid tildelt til INSERT, UPDATE og DELETE.

Pr. default bliver alle queries eksekveret i Isolation Level Read Commited. Read Commited betyder at SQL serveren tager en S Lock på en datarække i det tidsrum det tager at læse den. Lige så snart en datarække er læst og processeret, bliver S Lock frigivet øjeblikkeligt. Når man anvender en Scan Operator (i single tråd) bliver der kun anvendt 1 S Lock på et givnet tidspunkt. På baggrund af denne opsætning, er det muligt at en efterfølgende transaktion kan ændre i datarækken umiddelbart bagefter. Hvis man læser den samme datarække igen inden for den samme transaktion, opstår der såkaldt Non-Repeatable Read: man læser den samme datarække flere gange og man modtager forskellige værdier hver gang.

Hvis man gerne vil have en anden tilgang for læsning af data, kan man anvende en mere striks Isolation Level såsom Repeatable Read. Denne Isolation Level giver mulighed for at holde S Lock lige så længe som transaktionen forløber med læsning af datarækken. Derfor kan der heller ikke opnås X Locks (og dermed ikke ændres i data) i den periode – det ville skabe et scenarie af Blocking Queries. Denne tilgang har nogen fordele og ulemper. På den ene side får man mere korrekt data inden for samme transaktion og på den anden side vil man opleve flere blocking scenarier, fordi SELECT holder deres S Lock i længere tid.

Man kan være endnu mere striks i tilgangen og vælge Isolation Level Serializable. Med denne tilgang – den mest strikse på SQL serveren – kan man helt undgå såkaldte Phantom Records. En Phanton Record opstår og forsvinder igen når man tilgår det samme datasæt flere gange. For at undgå disse phantom records anvender SQL serveren en såkaldt Key Range Locking teknik ved at låse det subset af datarækker man som bruger først modtager.

Derfor kan ingen anden parallel query arbejde med disse datarækker. Det er derfor umuligt at lave INSERT, UPDATE og DELETE på de rækker. Disse queries vil blot blive blocket. Der er brug for et støttende index på search predicate som definerer datarækkerne. Med dette støttende index kan SQL serveren låse de individuelle index nøgler. Uden dette index bliver SQL serveren nødt til at låse hele tabellen, og dermed indvirke meget negativt på alle andre queries som tilgår denne tabel.

Slutteligt kan SQL serveren konfigureres til at bruge isolation level Read Uncommitted. Med denne isolation level bliver der ikke uddelt nogen S Locks ved læsning af data. Derfor er det muligt at læse data som endnu ikke er committed til databasen og som stadig er in-flight i en transaktion. Dette kaldes også for Dirty Reads. Hvis en sådan transaktion laver en Roll Back, vil man have læst data som aldrig har fandtes på databasen og som heller ikke kan genskabes på nogen måde. Dirty Reads opstår bl.a. når man bruger det berømte NOLOCK query hint.

Pessimistisk Isolation Levels er ikke så kompliceret – eller hvad synes du? 😀 Isolation level fortæller blot SQL serveren hvor længe en S Lock skal opretholdes ifm læsning af data. Og baseret på denne indstilling bliver der besluttet hvad der er tilladt og ikke tilladt under datalæsning. Jeg har forsøgt at lave et overblik som nedenstående illustration.

I tillæg til at opsætte en specifik Isolation Level, er det også muligt for SQL serveren midlertidigt at sætte Isolation Level på en query for at garantere rigtighed af data og resultatsættet.

Opsummering – Pessimistic Concurrency

Med dette indlæg og opstarten af 5. måned har jeg forsøgt at give et overblik over de forskellige pessimistiske isolation levels på SQL serveren. Med dette fundament, har du også fået første viden omkring fejlsøgning på locking og blocking scenarier på SQL serveren. SELECT blokerer for INSERT, UPDATE og DELETE – INSERT, UPDATE og DELETE blokerer for SELECT.

I næste uge tager jeg fat på 2 andre isolation levels på SQL serveren som anvendes i forbindelse med optimistic concurrency.

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 16: Cardinality Estimation fra SQL Server 2014

I sidste uge skrev jeg om udfordringerne med Cardinality Estimation før SQL 2014 versionen og fremefter. I dag tager jeg hul på hvad der skete fra version 2014 og fremad – også i Azure.

Den nye Cardinality Estimator

En af de gode tiltag i SQL server 2014 og fremefter har været den nye metode og underliggende engine for Cardinality Estimation. I sidste uge skrev jeg om udfordringerne i den tidligere version og nogen af begrænsningerne, som kan give forkerte estimeringer. Dette leder igen til dårlig performance – baseret på en Cardinality Estimation som blev udviklet tilbage i SQL 7.0.

Der har været små bugfixes hen over årene fra version 7.0, men de har ikke været slået til som udgangspunkt. Disse skulle slås til manuelt ved at anvende Trace Flags i SQL serveren. Dette gjorde Microsoft for at tilsikre at der ikke blev introduceret såkaldte Plan-Quality Regressions. Så fra SQL server 2014 og frem kom den første store ændring på dette område siden SQL 7.0.

Målet med den nye Cardinality Estimator er at forbedre kvaliteten af execution planerne. Men, den er ikke helt 100%, og kan stadig give plan regressions. Derfor er det vigtigt at undersøge om den nye Cardinality Estimator giver mening for det specifikke workload der er på SQL serveren. Til det formål er der introduceret nye traceflags fra SQL server 2014, så man selv kan styre hvordan Query Optimizeren virker.

SQL Server 2014 databaser har Database Compatibility Level 120 og alle fremadrettede versioner har højere tal. For at få adgang til de nye traceflags skal Database Compatibility Level være 120 eller højere. Ligger databasen i Azure, er det som default (pr. d.d. 150). Forsøger man med en restore fra tidligere versioner skal man derfor også huske at rette Compatibility Level manuelt.

Man kan se Compatibility Level på flere måder – een er at se på sin Execution Plan og se på Properties i SQL Server Management Studio. Elementet CadinalityEstimationModelVersion skal være 120 eller højere for at have adgang til de nye features.

De to nye traceflags som blev introduceret med SQL server 2014 er:

2312 – Link til Microsoft dokumentation
9481- Link til Microsoft dokumentation

Med traceflag 2312 kan man angive om man ønsker at anvende den nye Cardinality Estimator fra version 2014 – hvis for eks ens database er i en lavere compatibility level). Hvis man derimod vil have den gamle mode, kan traceflag 9481 anvendes.

Disse traceflags kan sættes på instance niveau, session niveau og query niveau.

Instance niveau sættes på databasen under Properties
Session niveau sættes som en DBCC kald i den query session man er i
Query niveau sættes som nedenstående eksempel

SELECT * FROM Person.Person
OPTION (QUERYTRACEON 2312)

Den nye Cardinality Estimator giver en række ændringer som kan give bedre performance gennem bedre estimeringer og dermed bedre execution planer. Microsoft har gennemarbejdet Cardinality Estimator på følgende områder:

Estimations for Multi-column predicates
Ascending key problem
Estimations for JOINS predicates
Extended Events troubleshooting

Nn dybere gennemgang af disse ændringer kan læses i dette whitepaper fra Microsoft af Joseph Sack.

Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator | Microsoft Docs

Opsummering – Cardinality Estimation fra SQL Server 2014

Endnu en måned er gået og der er taget nogen dybe spadestik i statistics på SQL serveren og som du har læst over de sidste 4 uger, så er deres nøjagtighed og gentagende opdatering yderst vigtig for performance på SQL serveren.

I næste uge har vi hul på endnu en måned og jeg skriver lidt om Locking, Blocking og Deadlocking på SQL serveren.

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.