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.

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:

SQLVYSE – Work of Art

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.

Lock Escalation

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:

  1. Delvis opdatering af de 5.000 rækker i mindre dele – eks. gennem en WHILE loop
  2. Anvend partition switching – hvis tabellen er partitioneret
  3. 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.

Optimistic Concurrency

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.

SQL serveren kan tegne

Vidste du at en SQL server kan tegne ud fra polygoner?

En SQL server har en særlg datatype geometry som reelt blot er polygoner i et koordinatsystem. Overordnet set er geometry en spacial datatype (du kan læse meget mere som spacial datatypes i links nederst).

Polygoner kræver to punkter for at tegne en skreg. De to punkter indgår, på SQL serveren, i et autogenereret koordinatsystem og serveren sørger selv for, ved denne datatype, at tegne en streg imellem de to punkter.

Lad os tegne

Et eksempel på en samling polygoner der tilsammen danner en tegning på SQL serveren, kan ses nedenfor.

SELECT geometry::STPolyFromText('POLYGON ((104 -222, 173 -222, 174 -174, 171 -160, 163 -147, 150 -137, 136 -128, 123 -123, 110 -117, 82 -116, 61 -122, 41 -134, 17 -150, 6 -173, 1 -194, 0 -232, 9 -259, 21 -276, 32 -289, 52 -302, 69 -312, 88 -320, 105 -335, 110 -375, 102 -390, 84 -395, 75 -385, 76 -330, 5 -333, 7 -390, 11 -411, 25 -428, 42 -442, 67 -451, 105 -453, 126 -446, 144 -439, 162 -424, 173 -404, 180 -382, 182 -337, 178 -311, 167 -296, 153 -279, 138 -268, 89 -234, 75 -222, 71 -208, 73 -188, 88 -178, 100 -190, 105 -220, 104 -222))',0) AS Drawing
UNION ALL
SELECT geometry::STMPolyFromText('MULTIPOLYGON (((324 -127, 404 -127, 405 -488, 322 -490, 322 -421, 311 -432, 291 -446, 277 -452, 259 -453, 248 -446, 239 -440, 228 -429, 221 -419, 215 -402, 215 -386, 213 -188, 216 -174, 219 -159, 226 -148, 235 -140, 245 -132, 261 -127, 278 -127, 294 -134, 306 -143, 322 -158, 324 -127)),((296 -191, 300 -186, 308 -182, 319 -188, 324 -196, 322 -384, 317 -391, 311 -395, 305 -395, 300 -395, 293 -388, 296 -191)))',0) AS Drawing
UNION ALL
SELECT geometry::STPolyFromText('POLYGON ((447 -62, 532 -65, 532 -450, 447 -450, 447 -62))',0) AS Drawing

Ved at eksekvere denne, fås en særlig fane i resultatvinduet, kaldet Spacial Results – her kan du nu se en illustration af ovenstående query:

SQL Serveren kan tegne

Kan jeg bruge det til noget?

Både og – for en del år siden, da SQL Server Reporting services var sit højeste og havde sin storhedstid, var det en rigtig god feature at have, når man gerne ville tegne f.eks. kort via dynamiske opslag i en database.

Det kunne være et klikbart kort over Danmark, hvor data til at tegne kortet, ligger direkte på SQL serveren som rækker i en tabel.

SQL serveren er god til at regne på polygoner og spacial data. Eks kan man finde skæringspunkter for to linjer – se eks fra Microsoft nedenfor.

Power BI kan ikke direkte supportere spacial datatypes (endnu – pr. feb 2021). Men mon ikke det kommer på et tidspunkt.

Opsummering

Nu ved du at SQL serveren kan tegne – har du fået blod på tanden til at lære mere om polygoner på SQL serveren og geometry datatypen, så kan du se mere i nedenstående links:

De udokumenterede funktioner du altid har haft brug for

Der findes mange dokumenterede funktioner i SQL serveren – men der findes også nogen udokumenterede funktioner, som andre har gjort et stort arbejde for at finde og dele med resten af verden. Funktioner som ikke er dokumenterede og som dermed ikke er supporteret.

Måske du har prøvet at sidde med noget SQL kode og manglet en given funktion eller metode til at udføre en specifik opgave.

Nogen af dem jeg for noget tid siden faldt over er funktionerne least og greatest.

Rækkebaseret i stedet for kolonnebaseret

udokumenterede funktioner rækker mod kolonner

De to funktioner least og greatest er rækkebaseret modsat dem vi kender omkring størst og mindst, nemlig min og max.

Normalt når man anvender min eller max funktionerne, så får man valideret datasættet for en given kolonne og for den mindste eller største værdi (kan også være tekst) for den givne kolonne.

Eks:

NavnVærdiLængde
Brian1012
Bent3220
Arne45100
Sofus21

Med ovenstående tabel til dette statement:

select Navn, max(Værdi) from tabel group by Navn

Fås følgende resultat:

results:
Arne | 45

Med de nævnte funtioner least og greatst, kan man nu få værdier (størst eller mindst) pr. række.

Eks med ovenstående tabel:

select Navn, least(Værdi, Længde) as least, greatest(Værdi, Længde) as greatest from tabel

Fås følgende resultat:

results:
Navn    |  least   | greatest
Brian   |   10     |   12
Bent    |   20     |   32
Arne    |   45     |  100
Sofus   |    1     |    2

Bemærk at SQL Server mangement studio ikke kan finde intellisense for disse udokumenterede funktioner, så du vil opleve røde markeringer ved anvendelse af dem. Men de kan trygt anvendes alligevel – også i Azure.

Opsummering på udokumenterede funktioner

Der er altså en del flere funktioner at bruge end dem, der er dokumenteret og supporteret af Microsoft.

Der er lidt flere at finde på de nedenstående links:

Hvis du kender til flere funktioner som ikke er dokumenteret af Microsoft, men som du bruger, så skriv dem gerne som kommentar nedenfor.

en_USEnglish