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 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.

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:

Statistics

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.

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.

Importing flatfiles to a SQL server with a varying number of columns

Ever been as frustrated as I when importing flatfiles to a SQL Server and the format suddenly changes in production?

The mostly used integration tools (like SSIS) are very dependent on the correct, consistent and same metadata when working with flatfiles.

I’ve come up with a solution that I would like to share with you.

When implemented, the process of importing flatfiles with changing metadata is handled in a structured, and most important, flawless way. Even if the columns change order or existing columns are missing.

Background

When importing flatfiles to SQL server almost every standard integration tool (including TSQL bulkload) requires fixed metadata from the files in order to work with them.

This is quite understandable, as the process of data transportation from the source to the destination needs to know where to map every column from the source to the defined destination.

Let me make an example:

A source flatfile table like below needs to be imported to a SQL server database.

This file could be imported to a SQL Server database (in this example named FlatFileImport) with below script:

create table dbo.personlist (
	[name] varchar(20),
	[gender] varchar(10),
	[age] int,
	[city] varchar(20),
	[country] varchar(20)
);

BULK INSERT dbo.personlist
FROM 'c:\source\personlist.csv'
WITH
(
	FIRSTROW = 2,
	FIELDTERMINATOR = ';',  --CSV field delimiter
	ROWTERMINATOR = '\n',   --Use to shift the control to next row
	TABLOCK,
	CODEPAGE = 'ACP'
);

select * from dbo.personlist;

The result:

If the column ‘Country’ would be removed from the file after the import has been setup, the process of importing the file would either break or be wrong (depending on the tool used to import the file) The metadata of the file has changed.

-- import data from file with missing column (Country)
truncate table dbo.personlist;
 
BULK INSERT dbo.personlist
FROM 'c:\source\personlistmissingcolumn.csv'
WITH
(
	FIRSTROW = 2,
	FIELDTERMINATOR = ';',  --CSV field delimiter
	ROWTERMINATOR = '\n',   --Use to shift the control to next row
	TABLOCK,
	CODEPAGE = 'ACP'
);
 
select * from dbo.personlist;

With this example, the import seems to go well, but upon browsing the data, you’ll see that only one row is imported and the data is wrong.

The same would happen if the columns ‘Gender’ and ‘Age’ where to switch places. Maybe the import would not break, but the mapping of the columns to the destination would be wrong, as the ‘Age’ column would go to the ‘Gender’ column in the destination and vice versa. This due to the order and datatype of the columns. If the columns had the same datatype and data could fit in the columns, the import would go fine – but the data would still be wrong.

-- import data from file with switched columns (Age and Gender)
truncate table dbo.personlist;
 
BULK INSERT dbo.personlist
FROM 'c:\source\personlistswitchedcolumns.csv'
WITH
(
	FIRSTROW = 2,
	FIELDTERMINATOR = ';',  --CSV field delimiter
	ROWTERMINATOR = '\n',   --Use to shift the control to next row
	TABLOCK,
	CODEPAGE = 'ACP'
);
Importing flatfiles to a sql server

When importing the same file, but this time with an extra column (Married) – the result would also be wrong:

-- import data from file with new extra column (Married)
truncate table dbo.personlist;
 
BULK INSERT dbo.personlist
FROM 'c:\source\personlistextracolumn.csv'
WITH
(
	FIRSTROW = 2,
	FIELDTERMINATOR = ';',  --CSV field delimiter
	ROWTERMINATOR = '\n',   --Use to shift the control to next row
	TABLOCK,
	CODEPAGE = 'ACP'
);
 
select * from dbo.personlist; 

The result:

Above examples are made with pure TSQL code. If it was to be made with an integration tool like SQL Server Integration Services, the errors would be different and the SSIS package would throw more errors and not be able to execute the data transfer.

The cure

When using the above BULK INSERT functionality from TSQL the import process often goes well, but the data is wrong with the source file is changed.

There is another way to import flatfiles. This is using the OPENROWSET functionality from TSQL.

In section E of the example scripts from MSDN, it is described how to use a format file. A format file is a simple XML file that contains information of the source files structure – including columns, datatypes, row terminator and collation.

Generation of the initial format file for a curtain source is rather easy when setting up the import.

But what if the generation of the format file could be done automatically and the import process would be more streamlined and manageable – even if the structure of the source file changes?

From my GitHub project you can download a home brewed .NET console application that solves just that.

If you are unsure of the .EXE files content and origin, you can download the code and build your own version of the GenerateFormatFile.exe application.
Another note is that I’m not hard core .Net developer, so someone might have another way of doing this. You are very welcome to contribute to the GitHub project in that case.

The application demands inputs as below:

Example usage:

generateformatfile.exe -p c:\source\ -f personlist.csv -o personlistformatfile.xml -d ;

Above script generates a format file in the directory c:\source\ and names it personlistFormatFile.xml.

The content of the format file is as follows:

The console application can also be called from TSQL like this:

-- generate format file
declare @cmdshell varchar(8000);
set @cmdshell = 'c:\source\generateformatfile.exe -p c:\source\ -f personlist.csv -o personlistformatfile.xml -d ;'
exec xp_cmdshell @cmdshell;

If by any chance the xp_cmdshell feature is not enabled on your local machine – then please refer to this post from Microsoft: Enable xp_cmdshell

Using the format file

After generation of the format file, it can be used in TSQL script with OPENROWSET.

Example script for importing the ‘personlist.csv’

-- import file using format file
select *  
into dbo.personlist_bulk
from  openrowset(
	bulk 'c:\source\personlist.csv',  
	formatfile='c:\source\personlistformatfile.xml',
	firstrow=2
	) as t;
 
select * from dbo.personlist_bulk;

This loads the data from the source file to a new table called ‘personlist_bulk’.

From here the load from ‘personlist_bulk’ to ‘personlist’ is straight forward:

-- load data from personlist_bulk to personlist
truncate table dbo.personlist;
 
insert into dbo.personlist (name, gender, age, city, country)
select * from dbo.personlist_bulk;
 
select * from dbo.personlist;
 
drop table dbo.personlist_bulk;

Load data even if source changes

Above approach works if the source is the same every time it loads. But with a dynamic approach to the load from the bulk table to the destination table it can be assured that it works even if the source table is changed in both width (number of columns) and column order.

For some the script might seem cryptic – but it is only a matter of generating a list of column names from the source table that corresponds with the column names in the destination table.

-- import file with different structure
-- generate format file
if exists(select OBJECT_ID('personlist_bulk')) drop table dbo.personlist_bulk
 
declare @cmdshell varchar(8000);
set @cmdshell = 'c:\source\generateformatfile.exe -p c:\source\ -f personlistmissingcolumn.csv -o personlistmissingcolumnformatfile.xml -d ;'
exec xp_cmdshell @cmdshell;
 
 
-- import file using format file
select *  
into dbo.personlist_bulk
from  openrowset(
	bulk 'c:\source\personlistmissingcolumn.csv',  
	formatfile='c:\source\personlistmissingcolumnformatfile.xml',
	firstrow=2
	) as t;
 
-- dynamic load data from bulk to destination
declare @fieldlist varchar(8000);
declare @sql nvarchar(4000);
 
select @fieldlist = 
				stuff((select 
					',' + QUOTENAME(r.column_name)
						from (
							select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'personlist'
							) r
							join (
								select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'personlist_bulk'
								) b
								on b.COLUMN_NAME = r.COLUMN_NAME
						for xml path('')),1,1,'');
 
print (@fieldlist);
set @sql = 'truncate table dbo.personlist;' + CHAR(10);
set @sql = @sql + 'insert into dbo.personlist (' + @fieldlist + ')' + CHAR(10);
set @sql = @sql + 'select ' + @fieldlist + ' from dbo.personlist_bulk;';
print (@sql)
exec sp_executesql @sql
 

The result is a TSQL statement what looks like this:

truncate table dbo.personlist;
insert into dbo.personlist ([age],[city],[gender],[name])
select [age],[city],[gender],[name] from dbo.personlist_bulk;

The exact same thing would be able to be used with the other source files in this demo. The result is that the destination table is correct and loaded with the right data every time – and only with the data that corresponds with the source. No errors will be thrown.

From here there are some remarks to be taken into account:

  1. As no errors are thrown, the source files could be empty and the data updated could be blank in the destination table. This is to be handled by processed outside this demo.

Further work

As this demo and post shows it is possible to handle dynamic changing flat source files. Changing columns, column order and other changes, can be handled in an easy way with a few lines of code.

Going from here, a suggestion could be to set up processes that compared the two tables (bulk and destination) and throws an error if X amount of the columns are not present in the bulk table or X amount of columns are new.

It is also possible to auto generate missing columns in the destination table based on columns from the bulk table.

Only your imagination sets the boundaries here.

Summary – importing flatfiles to a SQL server

With this blogpost I hope to have given you inspiration to build your own import structure of flatfiles in those cases where the structure might change.

As seen above the approach needs some .Net skills – but when it is done and the console application has been build, it is a matter of reusing the same application around the different integration solutions in your environment.

Happy coding 🙂

External links:

BULK INSERT from MSDN: https://msdn.microsoft.com/en-us/library/ms188365.aspx

OPENROWSET from MSDN: https://msdn.microsoft.com/en-us/library/ms190312(v=sql.130).aspx

XP_CMDSHELL from MSDN: https://msdn.microsoft.com/en-us/library/ms175046.aspx

GitHub link: https://github.com/brianbonk/GenerateFormatFile/releases/tag/v2.0

en_USEnglish