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.

da_DKDanish