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.

Pessimistic Concurrency

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.

Cardinality Estimation

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

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:

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.

Statistics

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.

en_USEnglish