T-SQL Arkiv » Brian Bønk

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.

Uge 15: Problemer med Cardinality Estimation

Så blev det fredag igen og de sidste to uger har jeg skrevet om statistics – i uge 13 om indledningen til statistics og i sidste uge lidt mere om indholdet i statistics.

Jeg har været omkring vigtigheden af statistics og hvordan de reelt “ser ud” internt i SQL serveren. I denne uge kommer så en gennemgang af nogen af begræsningerne som Cardinality Estimation har, og hvordan man kan komme omkring nogen af dem.

Cardinality Estimation Errors

Fra sidste uge. husker du måske at SQL serveren anvender Histogram og Density Vector til at lave Cardinality Estimation når en execution plan bliver oprettet. Modellem som SQL serveren anvender her, er den samme som den har været i rigtig mange år frem til SQL server 2014 og som kommer med en del ulemper og faldgruber. Fra 2014 og frem har Microsoft gjort rigtig meget ud af at tage fat i problemerne. Disse afdækker jeg lidt af i næste uge.

Så resten af dette indlæg, tager sit udgangspunkt i versioner før SQL server 2014 – de er ikke af den grund mindre nødvendige at kende og vide noget om, når man skal optimere på SQL performance.

Et konkret eksempel for Cardinality Estimation har problemer – forestil dig to tabeller: Ordre og Land. Hver række i Ordre-tabellen repræsenterer en ordre afgivet af en kunde (som en fact-tabel i et data warehouse) og denne tabel har en relation gennem en fremmednøgle (foreign key contraint) til tabellen Land (som kunne være en dimensionstabel i data warehouse).

Med nedenstående query mod de to tabeller for at få alle salg fra England:

SELECT SalesAmount FROM Country
INNER JOIN Orders ON Country.ID = Orders.ID
WHERE Name = 'UK'

Ved et kig på Execution Planen for denne query, så fremgår det ret tydeligt at SQL Server har et problem med cardinality estimation:

SQL Server estimterer 501 rækker, mens Clustered Index Seek operator returnerer 1000 rækker. SQL Server anvender her Density Vector fra statistics objeket idx_ID_SalesAmout (fra AdventureWorks databasen fra sidste uge) til at lave estimatet. Density Vector er 0,5 (der er kun 2 unikke værdier i kolonnen), og derfor er estimatet 501 (1001 x 0,5).

Dette problem kan løses ved at tilføje Filtered Statistics til tabellen. Dette vil give SQL serveren mere information om data distributionen og også hjælpe med Cardinality Estimation. Dette kunne se sådan ud:

CREATE STATISTICS Country_UK ON Country(ID)
WHERE Name = 'UK'

Ved at køre ovenstående query og genkøre query’en omkring England, så kan du nu se at både de estimerede og de reelle antal rækker er de samme. Det giver SQL serveren en meget bedre mulighed for allokere ressourcer til queryen og dermed give et hurtigere resultat.

Korrelerede kolonner

Et andet problem som kan opstå med Cardinality Estimation i SQL serveren er at såkaldte search predicate som korrelerer hinanden. En imaginær query kunne se ud som denne:

SELECT * FROM Products
WHERE Company = 'Nokia'
AND Product = 'Tesla Model 3'

Som menneske er det ret simpelt at Nokia ikke sælger Tesla-biler og dermed er resultatet af denne query 0 rækker. Men når man eksekverer ovenstående query mod en SQL server, så til Query Optimizeren kigge på hvert search predicate individuelt:

Step 1: Cardinality Estimation vil ske for Company = ‘Nokia’
Step 2: Cardinality Estimation vil ske for Product = ‘Tesla Model 3’

Og slutteligt vil begge estimater blive ganget med hinanden for at give det endelige resultat. Hvis, f.eks. step 1 giver en cardinality estimation på 0,3 og step 2 giver 0,4, så bliver resultatet 0,12 (0,4 x 0,3). Query Optimizeren håndterer hver search predicate som sit eget uden at lave nogen korrelation imellem dem.

Opsummering – Problemer med Cardinality Estimation

Statistics og Cardinality Estimation er en af de vigtigste elementer for en korrekt execution plan og dermed en god performance i SQL Serveren. Uheldigvis er deres brug også begrænset – især i disse grænsetilfælde som jeg har listet ovenfor i dag.

Jeg har forsøgt at give nogen af måderne for at komme omkring nogen af problemerne på – og har du fået blod på tanden for at lære mere om Cardinality Estimation så er der her lidt links:

I næste uge vil jeg tage fat på hvad der sker fra SQL server 2014 og frem (inkl Azure).

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 14: Statistics Inside

I denne uge tager jeg et spadestik dybere fra sidste uges Statistics indlæg og indledning og dermed åbne for motorhjelmen og se på hvordan SQL server håndterer Statistics. Med et tænkt eksempel på en operator i en Execution plan som har en metrik “Estimated Number og Rows” til at være 42 (ja, endnu en henvisning), og du ved at 42 ikke er korrekt. Men hvordan kan man så bruge Statistics til at finde ud af hvor det går galt?

Det er her Histogram og Density Vector kommer ind i billedet.

Histogram

Første skridt på vejen er at kigge lidt på Histogram i Statistics. Ideen med det indbyggede histogram er at holde styr på data distributionen af en kolonne – dette på en meget effektiv og kompakt måde. Hver gang et index bliver oprettet på en tabel (Clustered eller Non-Clustered), så vil SQL serveren under motorhjelmen oprette et statistics objekt. Og dette objekt beskriver data distributionen i en specifik kolonne. Forestil dig at du har en ordre-tabel og en kolonne heri med navnet Land. Der er noget salg i forskellige lande og dermed kan man visualisere salget i disse lande som nedenstående illustration.

Af histogrammet ses en data distribution med nogen søjler og akser. Jo højere søjlen er, jo flere rækker er der med den specifikke værdi – i dette tilfælde “DE”. Det samme koncept og format bliver brugt i SQL serveren. Et lidt mere konkret eksempel:

I demo databasen AdventureWorks fra Microsoft er der en tabel ved navn SalesOrderDetail og i denne tabel en kolonne med navn ProductId. Denne kolonne indeholder ID for alle produkterne som er en del af de specifikke salg.

Der eksisterer også et index på den tabel og kolonne – hvilket betyder at SQL serveren har oprettet et statistics objekt som indeholder et histogram med beskrivelse af data distributionen i den kolonne. Man kan tilgå disse statistics i SQL ved to metoder – ved at højreklikke på en kolonne og vælge properties – eller ved at eksekvere denne kommando:

DBCC SHOW_STATISTICS

Ovenstående kommando giver nedenstående resultat:

Der fremkommer 3 resultatsæt:

Generel information om statistics objektet
Density Vector
Histogram

Sidstnævnte er vist som tabel-element, men vil hurtigt, via Excel eller Power BI kunne omsættes til en visualisering ala mit eksempel ovenfor med søjlerne.

Density Vector

Den mystiske Density Vector – hvad er nu det for noget? Jo, når man f.eks kigger på et Non-Clustered Index fra den tidligere SalesOrderDetail tabel IX_SalesOrderDetail_ProductID, kan man se at index kun er defineret på een kolonne – nemlig ProductID. Men alle Non-Clustered Index på SQL serveren kræver også at have Clustered Key med sig – som minimum som en logisk “pegepind” til leaf level af index (Data Pagen med data). Når der er defineret et Non-Unique Non-Clustered Index, så bliver Clustered Key en del af strukturen i indexet. Clustered Key på SalesOrderDetail tabelle er en composit version og består af kolonnerne SalesOrderID og SalesOrderDetailID.

Dette betyder at vores Non-Unique Non-Clustered Index i virkeligheden består af kolonnerne ProductID, SalesOrderID og SalesOrderDetailID. Så under motorhjelmen på SQL serveren er der nu en composit index key. Dette betyder også at SQL serveren har oprettet en Density Vector for de andre kolonner, fordi det er kun den første kolonne (ProductID) som er en del af Histogrammet.

Density Vector er 2. resultatsæt i DBCC kommandoen fra tidligere.

SQL serveren gemmer heri en såkaldt Selectevity (eller densitet) af de forskellige kolonne-kombinationer. For eksempel er der en All density værdi for ProductID på 0,003759399. Denne værdi kommer ved at dele 1 med antallet af unikke værdier i kolonnen ProductID – T-SQL eksempel:

SELECT 
     1 / CAST(COUNT(DISTINCT ProductID) AS NUMERIC(18, 2))
FROM 
     Sales.SalesOrderDetail

Værdien for All density for kombinationen af ProductID, SalesOrderID og ProductID, SalesOrderID, SalesOrderDetailID er det samme og har værdien:

8,242867858585359018109580685312e-6

Dette kan igen verificeres ved en T-SQL kommando – måske du selv skal forsøge dig med denne kommando 😊.

Opsummering – Statistics Inside

Med denne indlæg har jeg forsøgt at løfte sløret lidt for hvordan SQL serveren håndterer og arbejder med Statistics under motorhjelmen. En kort gennemgang af Histogram og Density Vector som bliver brugt til SQL serverens Cardinality Estimation.

I næste uge vil jeg dykke ned i netop Cardinality Estimation og skrive lidt om nogen af de udfordringer det giver.

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 13: Statistics

Allerede uge 13 og dermed starten på den fjerde måned i denne blogserie. Denne gang tager jeg hul på Statistics på SQL Serveren og hvordan de hjælper Query Optimizeren med at producere en Execution Plan som er “god nok” (som gennemgået i uge 9). Statistics er hovedsageligt brugt af Query Optimizeren til at estimere hvor mange rækker der vil blive returneret fra en query. Kun et estimat – ikke andet.

Statistics overblik

SQL Server anvender et objekt fra Statistics kaldet et Histogram, som beskriver, via et maksimum på 200 steps, data distributionen for en given kolonne. En af de største begrænsninger på Statistics på SQL serveren er de 200 steps (som man kan komme omkring ved at lave filtrerede statistics, som blev introduceret med SQL Server 2008).

En anden begrænsning er Auto Update mekanismen ved Statistics – ved en tabel med mere end 500 rækker, bliver Statistics kun opdateret hvis 20% og 500 værdier i kolonner bliver ændret. Dette betyder at Statistics bliver opdateret mindre end hvad tabellen vokser i størrelse.

En tabel med 100.000 rækker vil Statistics dermed kun blive opdateret hvis der sker ændringer i 20.500 (20% + 500) værdier i kolonner i den underliggende tabel. Ligedan med en tabel på 1.000.000 rækker, så skal der 200.500 ændringer til, før Statistics bliver opdateret. Denne algoritme er baseret på eksponentiel fremskrivning og ikke liniært.

Fra SQL Server version 2016 er TraceFlag 2173 sat som default. Dette traceflag betyder at SQL serveren nu laver en dynamisk begrænsning baseret på det reelle antal rækker i tabellen og opdaterer Statistics. Denne indstilling giver visse I/O problemer, men fordelene opvejer meget tydeligt ulemperne.

Forestil dig at du har en Execution Plan som indeholder en Bookmark Lookup. Bookmark Lookup operatoren bliver kun valgt af Optimizeren hvis querien returnerer et udvalgt resultat – baseret på de nuværende Statistics. Hvis Statistics er “out of date” og Execution Planen stadig er valid, så vil SQL serveren blindt stole på Statistics og genbruge den cachede plan og data reads vil eksplodere.

Forældede Statistics

For at vise med et eksempel hvordan Statistics opfører sig, har jeg lavet nedenstående gennemgang.

Hvis du bruger SQL server 2016 eller senere, skal du, for at nedenstående virker, “slukke” for traceflad 2173 med nedenstående:

DBCC TRACEOFF (2371,-1)

Nedenstående script opretter en simpel tabel med 1.500 rækker, og hvor data distibutionen er lige i Column2 set over histogrammet. Der bliver også oprettet et Non-Clucstered Index på den kolonne.

CREATE TABLE Table1
(
   Column1 INT IDENTITY,
   Column2 INT
)
GO
-- Insert 1500 records into Table1
SELECT TOP 1500 IDENTITY(INT, 1, 1) AS n INTO #Nums
FROM
master.dbo.syscolumns sc1
INSERT INTO Table1 (Column2)
SELECT n FROM #nums
DROP TABLE #nums
GO 
CREATE NONCLUSTERED INDEX idx_Table1_Colum2 ON Table1(Column2)
GO

Når du efterfølgende udfører en simpel SELECT * FROM Table1 WHERE Column2 = 2 mod tabellen ved at filtrere på Column2, vl du få en Execution Plan der ligner den nedenfor:

Der sker en Index Seek (Non-Clustered) operator og SQL serveren estimerer 1 række. I realiteten er det også det der sker, da der er filtrering på en enkelt værdi fra WHERE elementet. Dette er et eksempel på Statistics som er opdateret og hjælper Optimizeren med en god performance.

Tabellen er nu 1.500 rækker lang så SQL serveren vil automatisk opdatere Statistics hvis det underliggende data (i dette tilfælde er det faktisk indexet) bliver ændret med 20% + 500 rækker. Matematikken giver os dermed at der skal ske 800 dataændringer, før statistics bliver opdateret.

Det næste skridt gør at vi arbejder lidt imod SQL serveren (for eksemplets skyld), for vi indsætter kun 799 rækker i tabellen. Men værdien i Column2 er nu altid “2”. Dette betyder en helt skævvridning af Histogrammet for data distributionen i Statistics. Idet der ikke sker en opdatering af Statistics vil SQL serveren stadig tro at der kun er 1 række i vores query fra før.

SELECT TOP 799 IDENTITY(INT, 1, 1) AS n INTO #Nums
FROM
master.dbo.syscolumns sc1
INSERT INTO Table1 (Column2)
SELECT 2 FROM #nums
DROP TABLE #nums
GO

Hvis vi eksekverer den helt samme query fra før, vil SQL serveren genanvende den cachede execution plan og lave et Bookmark Lookup. Dette betyder at Bookmark Lookup nu bliver eksekveret 2.299 gange – een gang for hver række i tabellen. Det er mange Logical Reads! SQL serveren returnerer da også med 806 page reads – prøv at se nedenstående billede.

Med SQL server 2016 og frem (inkl naturligvis Azure) er det ikke længere noget stort problem med outdatede Statistics. Men hvis du oplever problemer med en underligt opstået Bookmark Lookup, så prøv alligevel at se om det skulle være statistics som er for “gamle”.

Hvis du har leget med traceflag fra ovenstående, så husk at rydde op igen med nedenstående:

DBCC TRACEON (2371,-1)

Opsummering – Statistics

Med dette indlæg har jeg forsøgt at give dig en indtroduktion til Statistics på SQL serveren. Som du har set kan Statistics som ikke afspejler virkeligheden være fatale for en SQL serverens leverede performance.

I næste uge graver jeg med lidt længere ned i detaljerne om Statistics, så “stay tuned”.

Kan du ikke vente med at læse mere til næste uge, så er der herunder lidt links at hygge med:

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 12: Parallelle Execution Planer

I denne uge stepper jeg lidt op af stigen i sværhedsgrad og vil gerne præsentere Parallelle Execution Planer til dig. En query med en Parallel Execution Plan er en query der bruger flere tråde (threads) på SQL serveren til at foretage de nødvendige operationer i den fysiske execution plan.

I første afsnit gennemgår jeg de mest gænge operatorer i parallelle execution planer og efterfølgende lidt flere detaljer om hvordan SQL serveren beslutter anvendelsen af disse.

Parallelle Operators

Mange gange oplever jeg en misforståelse omkring parallelle execution planer, at de anvender et givent sæt af tråde (f.eks 8 tråde på en 8 kerne server) til hele execution planen. Det er ikke helt sådan virkeligheden på SQL serveren er stykket sammen. SQL serveren tildeler mange såkaldte Worker tråde (worker threads) til alle operators som er bygget til parallel eksekvering. Dette betyder at en stor parallel execution plan kan bruge et meget stort antal tråde. SQL Serveren skelner mellem to typer operators i en parallel plan:

Parallelism-aware operators
- En stor del af de traditionelle operators kan også arbejde med flere tråde, de er parallelism-aware – Index Scan, Index Seek, Nested Loop, Hash Join og Sort – for bare at nævne nogen af dem
Exchange operators
- Disse operators bliver brugt til at fordele og sammensætte rækker mellem de forskellige tråde i en parallel execution plan

SQL Serveren har implementeret 3 Exchange Operators:

Distribute Streams: Bliver brugt til at fordele en enkelt tråds rækker ud på flere tråde i en såkalt Multi-threaded region i den parallelle execution plan
Repartition Streams: Bliver brugt til at gen-fordele rækkerne mellem trådene (hvis for eks den førliggende operator er Parallel Hash Join)
Gather Streams: Bliver brugt til at samle rækker fra flere tråde til en enkelt tråd – fra multi-threaded region til single-threaded region i execution planen

Når man ser en parallel execution plan, vil man altid se en kombination af begge typer operators. Enhver execution plan skal producere et single-threaded output, derfor vil man altid finde en Gather Streams omkring slutningen af en parallel execution plan (altså helt til venstre):

Om Optimizeren genererer en parallel execution plan eller ej, afhænger også af indholdet i den afsendte query – såsom:

T-SQL og SQLCLR UDFs (dog med undtagelse af de UDFs som kan compiles til Inline UDFs)
Indbyggede funktioner som OBJECT_ID(), ERROR_NUMBER(), @@TRANCOUNT

Der er også flere query elementer som forcerer en single-treaded region i den parallele execution plan_

System Table Scans
Sequence Funktioner
Backward Scans
Recursive Queries
Table Values Functions (igen med undtagelse af dem der kan inlines)
TOP operator

Og jo færre single-threaded regioner i en parallel execution plan, jo hurtigere vil queryen levere data. Prøv at huske på det næste gang du synes din query er for langsom.

Hvornår bliver en query parallel?

Enhver execution plan får en såkald Cost Factor tildelt fra SQL serveren. Denne Cost Factor er en værdi i form af et tal, der fortæller SQL serveren hvor “dyr” en execution plan er. Jo højere værdi, jo dyrere (i ressourcer) er det at ekekvere den specifikke execution plan.

På SQL serveren er der en konfiguration kaldet Cost-Threshold for Parallelism, som definerer græsen i cost factoren hvorved Optimizeren begynder at overveje parallelle execution planer. Default er 5 (hvilket er meget lavt) som betyder at en Cost Factor højere end 5 vil give optimizeren mulighed for at lave en parallel execution plan, så længe parallellisering er muligt.

Når en parallel execution plan bliver compilet af Query Optimizeren vil konfigurationen af Max Degree of Parallelism (MAXDOP) definere hvor mange tråde, der kan blive anvendt i hver enkelt parallelle operator i execution planen.

Som nævnt tidligere vil enhver operator i den parallelle execution plan køre med flere tråde, og ikke kun i den samlede plan. Tråde kan blive delt imellem operators når de ikke længere bruges, og låses derfor ikke til en enkelt operator i execution planen.

Som standard er MAXDOP sat til 0 ved installation. Derfor vil SQL serveren per default parallellisere en query på alle kerner som er assignet til SQL serveren. Dette kan give performance problemer hvis man arbejder i et NUMA system (Non Uniform Memory Accesss) – typisk eksempel er en viruel server. Derfor er det god skik at sætte MAXDOP til antallet af NUMA noder i systemet (inkl. kerner/NUMA noder fra Hyperthreading).

Opsummering – Parallelle Execution Planer

Dette indlæg har været et af de mere tunge af slagsen, men også meget interessant (synes jeg selv…). Jeg er kommet omkring de forskelle operatorer i en parallel execution plan og skrevet lidt om hvordan SQL serveren behandler disse.

Har du fået blod på tanden og vil læse mere om parallelle execution planer og deres arbejde på SQL serveren er herunder lidt links til nogen gode indlæg:

Og en note – vi er allerede 3 måneder inden i denne serie af blogindlæg – og stadig kun halvvejs. Jeg håber I er med på resten af serien.

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.