Uge 20: Deadlocks

Så er det fredag igen og tid til endnu et indlæg i rækken om SQL Server performance tuning. Denne gang om Deadlocks, som er situationen hvor 2 samtidige queries begge venter på hinanden og ingen kan fortsætte uden den anden stopper. Jeg skriver her lidt om hvordan SQL serveren håndterer deadlocks, hvilke typer der findes og hvad man evt. kan gøre for at undgå dem.

Deadlock håndtering

Det gode ved Deadlocks er at SQL serveren automatisk og helt selv finder dem og løser dem. For at løsne en deadlock bliver SQL serveren nødt til at stoppe og rulle en transaktion tilbage. Her er det kodet fra Microsofts side at det altid er den billigste transaktion som vælges. Og den billigste her, er den der har skrevet færrest bytes til transaktionsloggen.

SQL serveren har fået implementeret deadlock overvågningen i en baggrundsproces kaldet Deadlock Monitor. Denne proces kører hvert 5. sekund og kontrollerer den nuværende status for deadlocks. I værste fald kommer en deadlock situation højst til at vare 5 sekunder. Den query som bliver valgt til at være offeret (Deadlock Victim) får fejlkoden 1205 og bliver rullet tilbage. Det lækre ved den metode, er at man altid ender med en status quo.

Processen for at komme videre fra en oplevet deadlock kunne se sådan her ud:

  • Kontroller for fejlkode 1205 når der sker en fejlmeddelelse
  • Sæt den overordnede applikation på pause for at give plads til alle andre transaktioner at rulle færdigt og dermed løsne alle locks
  • Genkør Deadlock Victim query som blev rullet tilbage

Efter denne proces, bør alt kunne rulle videre igen uden problemer. Herefter skal der selvfølgelig ses efter og findes årsagen og få den løst, så det ikke sker igen.

Man kan finde og fejlsøge på deadlocks ad flere metoder på SQL serveren. SQL Server Profiler (den gamle metode) giver en Deadlock Graph event, som sker lige så snart en deadlock bliver fundet. I disse moderne tider med Azure SQL og SQL server 2019 (seneste version ved denne indlægs oprettelse) så er der også mulighed for at anvende Extended Events. Her kan man opsætte en overvågning på system_health event, som holder øje med og historisk gemmer deadlocks siden seneste genstart af SQL serveren. En sidste mulighed er at enable trace flag 1222 som gør at SQL serveren skriver deadlock information direkte i error loggen.

Typer af deadlocks

Der findes flere forskellige typer af deadlocks, her kommer lidt mere om de mest gængse typer.

En typisk deadlock, som man ofte kan støde på, er Bookmark Lookup Deadlock (et nørdet blogindlæg fra Dimitri Korotkevitch kan læses her), som opstår når der kommer flere på hinanden samtidige Reads og Writes aktiviteter til et Clustered eller Non-Clustered Index. Det sker ofte pga. en lidt uhensigtsmæssig indexeringsstrategi. En bookmark lookup deadlock kan imødekommes og elimineres ved f.eks. at lave et covering Non-Clustered Index som jeg skrev lidt om i uge 8.

En anden meget typisk deadlock er Cycle Deadlock, hvor flere queries forsøger at tilgå den samme tabel i forskellig rækkefølge. For at undgå denne deadlock er det vigtigt at tilsikre at flere samtidige queries tilgår den samme tabel på den samme måde.

Den, måske, flotteste deadlock på SQL serveren er Intra-Parallelism Deadlock, hvor en paralellism operator (Distribute Streams, Gather Streams eller Repartition Streams) har ført til en intern deadlock. En sådan deadlock kan vises på den tidligere nævnte Deadlock Graph – og de er næsten som kunst på SQL serveren – her et eksempel:

SQLVYSE – Work of Art

Der er flere eksempler i billedteksten – prøv at se dem også.

Grafen er som sagt næsten kunst og opstår fordi SQL serveren selv har ramt en bug. Uheldigvis bliver denne bug ikke løst af Microsoft, da en løsning potentielt kan skabe regressioner. Derfor kan man kun komme uden om disse deadlocks ved at tilsikre at disse queries (udvalgte, ikke alle queries), anvender single-threaded execution plans:

  • Med en indexeringsplan så query cost kommer under Cost Threshold for Parallelism
  • Query hint så SQL serveren kun anvender en tråd – MAXDOP 1

En anden tilgang, og næsten en mirakelkur, er at enable optimistic concurrency med Read Committed Snapshot Isolation (RCSI).

Opsummering – deadlocks

Som du har læst i ovenstående, så bliver deadlocks automatisk håndteret af SQL serveren ved at rulle den billigste transaktion tilbage. Det er naturligvis stadig vigtigt at nedbringe antallet af deadlocks i et system, så brugerne ikke oplever fejl eller manglende gennemførte transaktioner i deres applikation.

I næste uge starter de sidste 4 uger af denne række af indlæg, og her tager jeg hul på hvordan man kan lave performance overvågning og fejlsøgning på SQL serveren.

Husk at skrive dig på maillisten nedenfor, så du ikke misser næste udgave af denne blogserie. Jeg lover dig kun at sende en mail, når der er nyt i denne serie.

Uge 19: Lock Escalation

Så er vi i gang med endnu et afsnit af denne følgeton. Hvis du er med stadig, så er der gode ting på programmet. Denne gang skriver jeg lidt om Lock Escalations som er en af fænomenerne man kan opleve ved SQL serveren. Et såkaldt lock-hierarki er bygget dybt ind i maven på SQL serverens motor.

Lock Hierarkiet

Da jeg skrev om pessimistisk concurrency, skrev jeg også om at SQL serveren anvender Shared og Exlusive Locks på række-niveau. Desværre er det kun en del af hele billedet. Det samlede billede af Locks kræver at man også kigger på den granilaritet hvori locks bliver sat. Det kan være på alle niveau’er fra database til række.

Lock Escalation

Så snart en database tages i brug, vil den givne session mod databasen få en Shared Lock. Denne Lock betyder at ingen kan slette databasen eller genskabe fra en backup, så længe denne Lock er aktiv. Operationerne vil simpelhen blive blokkeret grundet den aktive session. Ligesom de nævnte Shared Locks og Exclusive Locks på række-niveau, eksisterer der også såkaldte Intent-Locks på tabel- og page-niveau.

  • Med en Shared Lock på række-niveau, kommer også en (IS) Intent Shared Lock på tabellen og tilhørende page
  • Med en Exclusive Lock på række-niveau, kommer også en (IX) Intent Exclusive Lock på tabellen og tilhørende page

Intent Locks anvendes til at signalere at en inkompatibel Lock allerde er givet på en lavere niveau i Lock-hierarkiet. Intent Locks er mestendels en performance optimering for den relationelle motor. Uden dem skulle Lock Manager (intern service i SQL serveren) gennemgå samtlige locks på lavere niveau, for at tilsikre at en lock på et højere niveau kan tildeles. Hvis der for eksemplets skyld eksisterer en IX Lock på en tabel, så kan man ikke få en X Lock på samme tabel, idet nogen data-rækker allerede er låst med Exclusive Lock fra tabellen selv.

Uheldigvis koster denne multi-level lock metode også ressourcer på SQL Serveren. Hver enkelt Lock “koster” 96 bytes af SQL serverens hukommelse (RAM). Derfor er det vigtigt for SQL serveren at tilsikre at queries kun anvender så få Lock bytes som overhovedet muligt.

Lock Escalations

Et eksempel er altid godt at fortælle med, så forestil dig følgende scenarie: Et update statement der rammer 1 million rækker spredt på 200.000 data pages. I dette eksempel skal SQL serveren tilsikre 1 million Locks på data-rækkerne, 200.000 IX Locks på data pages og en enkelt IX Lock på tabellen. Alene disse 1.200.001 Locks fylder ca 110 MB i memory fra Lock Manageren – kun for denne ene query. Den slags hukommelsesforbrug er uhensigtsmæssig, hvorfor der er en ekstra feature i SQL serveren Lock Escalations som træder i kraft ved mere end 5.000 Locks på eet niveau – som her på række-niveau. Her vil SQL serveren eskallere de individuelle række Locks til en mere grovkornet Lock på tabellen:

  • Individuelle X Locks bliver eskalleret til 1 tabel X Lock
  • Individuelle S Locks bliver eksalleret til 1 tabel S Lock

Nedenståene illustrerer hvordan Locks ser ud før og efter Lock Escalation.

Forbruget af hukommelse er nedbragt betragteligt ved denne tilgang, men det påvirker også muligheden for parallelle queries på databasen. En X Lock på en tabel betyder at ingen andre kan læse eller skrive til den tabel – En S Lock på en tabel betyder at tabellen er read-only. Databasens gennemstrømning af queries falder.

SQL Serveren trigger Lock Escalation når der er mere end 5.000 Locks på samme niveau. Dette er hard-coded ind i maven på SQL serveren og kan desværre ikke ændres ved en konfiguration eller hack. Siden SQL server 2008 har det været muligt at styre lidt af Lock Escalations gennem et ALTER TABLE DDL statement.

ALTER TABLE MyTableName
SET
(
   LOCK_ESCALATION = TABLE -- or AUTO or DISABLE
)

Pr. default bliver Lock Escalations fra rækker altid forhøjet til tabel (fra ovenstående LOCK_ESCALATION = TABLE). Hvis man sætter den til AUTO vil SQL serveren eskallere til partitions niveau – men pas på med denne feature, da det kan føre til ekstra Deadlocks, hvis man tilgår partitionerne i vilkårlig rækkefølge. Muligheden for DISABLE fjerner helt Lock Escalation på tabellen – med forhøjet forbrug af RAM som tidligere nævnt. Det gode spørgsmål her er så hvordan man mest effektivt kan lave en UPDATE eller DELETE query på mere end 5.000 rækker i en tabel?

Her 3 forslag:

  1. Delvis opdatering af de 5.000 rækker i mindre dele – eks. gennem en WHILE loop
  2. Anvend partition switching – hvis tabellen er partitioneret
  3. Midlertidigt LOCK_ESCALATIONS = DISABLE – men pas på med RAM forbruget i perioden

Opsummering – Lock Escalation

Lock Escalation er en form for sikkerhedsnet på SQL serveren. Med god grund, men det introducerer også en række afledte følger. Derfor er det vigtigt at man meget velovervejet skriver T-SQL kode som håndterer mere end 5.000 rækker i samme transaktion. Måske skal det gøres i mindre dele i stedet for een stor UPDATE eller DELETE query.

I næste uge skriver jeg lidt videre om Locks og Blockings på SQL serveren og tager fat på Deadlocks, hvad det er og hvordan SQL serveren arbejder med disse.

Husk at skrive dig på maillisten nedenfor, så du ikke misser næste udgave af denne blogserie. Jeg lover dig kun at sende en mail, når der er nyt i denne serie.

Uge 18: Optimistic Concurrency

Velkommen tilbage til denne følgeton. I sidste uge skrev jeg lidt om Pessimistic Concurrency og lovede at skrive lidt om det modsatte i dag – Optimistic Concurrency.

Row Versioning

Optimitic Concurrency blev introduceret med SQL Server 2005 og baserede sin metodik på princippet Row Versioning. Ideen bag Row Versioning er at SELECT queries ikke påkræver de Locks som jeg skrev om i sidste uge. I stedet for at vente på at en Shared Lock er tildelt, så kan SELECT statements få en tidligere committed version af rækken. Disse versioner er gemt i Version-Store, som er en del af TempDb. INSERT, UPDATE og DELETE statements kopierer “gamle” versioner til Version-Store i TempDb og lader en pegepind fra den nye række linke til den gamle række i TempDb. En illustration som nedenstående forsøger at beskrive det.

Optimistic Concurrency

En afledt effekt af at tilføje disse pegepinde er at alle rækker bliver udvidet med 14 bytes. Dette kan betyde følgende:

  • Forwarding records i Heap Tabeller
  • Page Splits i Clustered Tabeller (dem med clustered index)

Dertil bør man også overveje at tilpasse TempDb størrelsen tilsvarende, da man kommer til at introducere ekstra I/O som kan lede til flaskehalse i et default setup.

Der er overordnet to Isolation Levels i Optimistic Concurrency på SQL Serveren – dem gennemgår jeg nedenfor.

Optimistic Isolation Levels

Siden SQL server 2005 har der været to Isolation Levels omkring Optimistic Concurrency som baserer sig på den før omtalte row versioning.

  • Read Committed Snapshot Isolation (RCSI)
  • Snapshot Isolation (SI)

Et mere detaljeret blik på disse to kommer her.

RCSI giver Statement Level Snapshot Isolation. Med andre ord, så vil SQL Serveren altid returnere den række som var committed og valid på det tidspunkt hvor querien startede sin eksekvering. Det er en optimistisk implementering af Read Committed Isolation level fra sidste uge. Derfor vil man også opleve Non-Repeatable Reads med denne Isolation Level.

En af de gode ting ved RCSI er at det er fuldstændig transperant overfor databasen og applikationen. Det er kun et spørgsmål om at slå det til på databasen, så vil alle fremtidige queries få deafult Isolation Level Ream Committed Snapshop. Derfor kan man også komme forbi Locking og Blocking problemer – selv Deadlocks kan komme til livs ved at bruge RSCI. Nedenstående kodestumper slår RSCI til på en given database.

-- Enable Read Committed Snapshot Isolation (RCSI)
ALTER DATABASE AdventureWorks2012 SET READ_COMMITTED_SNAPSHOT ON
GO
-- Check if RCSI is now enabled
SELECT
     name,
     is_read_committed_snapshot_on
FROM sys.databases
WHERE database_id = DB_ID('AdventureWorks2012')

Hvis du derimod ønsker Repeatable Reads for alle SELECT statements i en Optimisitc Concurrency, så kan man anvende Snapshop Isolation. Dette giver databasen Repeatable Reads Out-Of-The-Box som dermed returnerer den række som var gældende ved starten af querien (transaktionen). Desværre er Snapshop Isolation ikke helt transperant.

  • Snapshot Isolation kan kun sættes på enkelte sessioner og skal slås til eksplicit. Derfor skal der kodeændringer til for applikationer, hvis de skal bruge denne funktion.
  • Queries kan komme i såkaldt Update Confllicts, hvor SQL serveren ruller en transaktion tilbage, selv om den vil være valid nok hvis der ikke var andre samtidige queries der ramte de helt samme rækker. Denne problemstilling skal også håndteres i applikationen.

Nedenstående kodestump viser hvordan man kan sætte Snapshop Isolation på en specifik database, og hvordan man derefter sætter det på en transaktion.

-- Enable Snapshot Isolation (SI)
ALTER DATABASE AdventureWorks2012 SET ALLOW_SNAPSHOT_ISOLATION ON
GO
-- Check if SI is now enabled
SELECT
     name,
     snapshot_isolation_state,
     snapshot_isolation_state_desc
FROM sys.databases
WHERE database_id = DB_ID('AdventureWorks2012')
GO
USE AdventureWorks2012
GO
-- Setting the Isolation Level to Snapshot Isolation
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO

Opsummering – Optimistic Concurrency

I dag har jeg gennemgået de 2 optimistic isolation levels som SQL serveren supporterer og har gjort siden 2005. Read Commited Shapshot Isolation giver statement-baseret isolation, mens Snapshot Isolation giver transaktions-baseret isolation. Fordi begge isolation levels anvender Version-Store i TempDb, skal man være meget opmærksom på specifikation af denne.

I næste uge skriver jeg lidt om Lock Escalations.

Husk at skrive dig på maillisten nedenfor, så du ikke misser næste udgave af denne blogserie. Jeg lover dig kun at sende en mail, når der er nyt i denne serie.

SQL serveren kan tegne

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

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

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

Lad os tegne

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

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

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

SQL Serveren kan tegne

Kan jeg bruge det til noget?

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

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

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

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

Opsummering

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

De udokumenterede funktioner du altid har haft brug for

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

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

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

Rækkebaseret i stedet for kolonnebaseret

udokumenterede funktioner rækker mod kolonner

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

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

Eks:

NavnVærdiLængde
Brian1012
Bent3220
Arne45100
Sofus21

Med ovenstående tabel til dette statement:

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

Fås følgende resultat:

results:
Arne | 45

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

Eks med ovenstående tabel:

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

Fås følgende resultat:

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

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

Opsummering på udokumenterede funktioner

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

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

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

Uge 2: Data Pages – grundlaget i SQL Serveren

Sidste uge var det hårde indløb og alle de nye termer omkring SQL serveren. Jeg skrev om hvordan en SQL server eksekverer de queries, vi sender afsted til den. Gik du glip af det afsnit, kan du læse det her:

Uge 1 – Hvordan en SQL Server eksekverer en forespørgsel

Jeg berørte også meget kort omkring Data Pages, som er buffere på 8kb (kilobyte). I dette indlæg tager jeg fat i dette begreb yderligere og får beskrevet hvad de betyder for performance på SQL serveren.

Pages er fundamentet for en SQL server, alt omkring en SQL server handler i bund og grund om Pages. Når man gerne vil forbedre performance af de queries, der arbejdes med, forsøger man altid at mindske det antal Page reads SQL serveren skal foretage. Senere i denne blogserie kommer jeg ind på index og at de også er baseret på Pages.

Så at forstå hvad en Page er, giver grobund for god performance tuning på SQL serveren.

Data Pages og deres struktur

En Page på SQL serveren fylder altid 8kb, og der er forskellige typer Pages, som f.eks. Data Pages, Index Pages, System Pages osv. I dette indlæg vil jeg kigge i detaljer på Data Pages, som SQL serveren anvender til at gemme data fra tabellerne. En Data Page består af 3 dele:

  • Page Header
  • Payload
  • Row Offset Array

Page Header er altid 96 bytes i længde på en SQL server (også uanset hvilken type Page det er). Header indeholder oplysninger om f.eks. Page ID og Object ID. Payload området i en Data Page er der hvor data fra tabellerne bliver fysisk gemt. SQL serveren har allokeret 8060 bytes til dette formål. Den skarpe læser, vil nu kunne se, at man kan regne på hvor mange rækkker der kan være i en enkelt Data Page, ved at se på hvor meget en række fylder (alle kolonnernes datatyper i længde (bytes)) og dele den værdi op i de 8060 bytes. Evt. decimaler i den udregning “kappes af”.

Målet med opbygningen af data på en SQL server, er altid at have så mange rækker som muligt i en enkelt Data Page, for som du måske husker fra sidste indlæg, så skal SQL serveren have fat i alle de Data Pages, der skal til for at arbejde med det fornødne data i forbindelse med en given forespørgsel. SQL Serveren kan ikke kun læse en delmængde af en Data Page. I/O operationer sker altid (som minimum) på Page niveau.

Den sidste del af en Data Page er Row Offset Array. Denne lille del af en Data Page indeholder 2 bytes for hver række i pagen med henvisning til hvor i pagen den givne række er placeret. Lidt som et mini-index i en bog.

Den første række på en page begynder altid umiddelbart efter Page Headeren – altså efter byte placering 96. De efterfølgende rækkers placering er givet ud fra rækkernes længde, som beskrevet lidt tidligere.

På nedenstående billede, kan du se opbygningen af en Data Page, som jeg har forsøgt at beskrive ovenfor.

Data Pages

Data Page Internals

En nogen lunde simpel tabel på SQL server kunne se ud som nedenfor.

create table dbo.Medarbejder (
Fornavn char(50)
,Efternavn char(50)
,Adresse char(100)
,Postnummer char(5)
,Alder int
,OprettetDato datetime
)

Ud fra ovennævnte tabel struktur kan vi nu regne på hvor mange rækker der kan være på en Data Page.

En rækker fylder 217 bytes (50+50+100+5+4+8) – int bruger 4 bytes og datetime gemmes reelt som bigint på en DataPage og bruger 8 bytes.

Ved at dele de allokerede 8060 bytes med 217, får vi 37 hele rækker og 31 bytes i overskud. Disse 31 bytes kan ikke bruges til andet, da SQL serveren ikke kan fylde andet i en specifik Data Page, end det er hører til en specifik tabel. Så hvis en række fylde 4031 bytes ville man have 4029 bytes som spildt plads i en Data Page – og denne fylder stadig 8kb på disk.

Tingene ændrer sig, hvis man anvender datatyper med variabel længde eks VARCHAR (alle dem med “var” foran), for her kan SQL serveren gemme kolonnerne på forskellige Data Pages og dermed optimere lidt på Page forbruget.

SQL serveren kan faktisk hjælpe med et samlet overblik over hvor meget plads der er spildt i alle Data Pages, og dermed spildt i det tabel design. Via det såkalde Dynamic Management View (DMV) sys.dm_os_buffer_descriptors, kan man se alle rækker fra resultatsættet som repræsenterer en Data Page (Pas dog på med at eksekvere denne på systemer med meget ram, da resultatsættet vil være tilsvarende stort). I kolonnen free_space_in_bytes vises hvor meget ledig plads der i hver enkelt Data Page.

En god standard forespørgsel at have ved hånden kunne være nedenstående:

select db_name(database_id), sum(free_space_in_bytes)/1024 as 'FreeSpaceInKb'
from sys.dm_os_buffer_descriptors
where database_id <> 32767
group by database_id
order by 2 desc

Opsummering – Data Pages

Jeg håber det har givet lidt mere klarhed over hvad en Data Page på en SQL server er, hvordan man bør tænke over sine tabellers struktur ifm. oprettelsen af dem og hvordan SQL serveren bruger dem til at gemme data på disk.

Skulle du have fået mere blod på tanden for at læse om Data Pages – kan jeg anbefale følgende sider:

Uge 1: Hvordan en SQL Server eksekverer en forespørgsel

Dette er første indlæg i en længere serie om SQL Performnace Tuning. Før vi hopper direkte ud i alle de lækre detaljer omkring performance tuning på en SQL server, er det vigtigt at have grundlaget på plads.

I dette indlæg kigger jeg derfor nærmere på hvordan en SQL server eksekverer en forespørgsel. Det er en vigtig del af det at forstå SQL serverens metoder, da det vil være herpå de næste indlæg vil bygge videre.

Som vist ovenfor er SQL serveren internt delt i en Relational Engine og en Storage Engine. Den største del af Relational Engine er Query Optimizeren (ofte blot kald optimizeren). Det eneste opgave for optimizeren er at generere en fysisk execution plan (mere herom i et senere indlæg) for de forespørgsler vi sender til SQL serveren.

Læsning af data

Alle forespørgsler – eller queries – som vi sender til SQL Serveren passerer igennem Protocol Layer til Command Parser. Command Parser kontrollerer den kode vi sender afsted – f.eks om det er valid TSQL kode og eksiterer alle anvendte tabeller og kolonner i databasen. Resultatet af denne opgave er et såkaldt Query Tree, en træstruktur som repræsenterer den afsendte query. Denne træstruktur bliver så anvendt af optimizeren til at generere en execution plan.

Den færdige execution plan bliver herefter sendt videre til Query Executor. Her er opgaven at udføre de handlinger som execution planen foreskriver. Inden dette sker, gemmes den modtage execution plan i Plan Chachen – her kan SQL serveren nemlig genbruge execution planer fra tidligere. Denne metode med at gemme og genbruge execution plans er på samme tid et meget stærkt og meget farligt koncept af SQL serveren. Mere herom i et senere indlæg omkring Plan Cache.

Efter execution plan er gemt i cachen, begynder query executor at kommunikere med storage engine og eksekverer hver en lille del af execution plan – kaldet operators.

Når data tilgås fra execution plan (dette er det eneste sted vi kan få fat i data) er det Access Methods som kommunikerer med Buffer Manager for at læse specifikke pages – mere om pages i de næste indlæg, og lige nu er det nok at vide at en page er en buffer med data der fylder 8kb hvori data og index er gemt. Det er Buffer Manager der styrer Buffer Pool hvor pages er gemt. Det er Buffer Pool der er den fysiske anvendelse af memory (ram) som man kan se SQL serveren anvender i operativsystemet.

Når en page allerede er gemt i buffer pool, så bliver denne page øjeblikkeligt returneret. Når dette sker er det en Logical Read i SQL serveren. Hvis en page ikke allerede er gemt i buffer pool, så udfører buffer manager en asynkron I/O forespørgsel mod den fysiske disk og ind i buffer pool. Dette er en Physical Read. Under denne asynkrone handling venter querien indtil handlingen er færdig. Dette kaldes Waits og senere i et indlæg kommer jeg ind på dette emne og Wait Statistics.

Så snart en page er læst ind til buffer pool, bliver denne page sendt videre til den proces som forespurgte på den. Når execution plan er færdig, vil resultatet af det behandlede data blive returneret til brugeren eller applikationen gennem Protocol Layer.

Ændring af data

Når man arbejder med TSQL udtryk som ændrer på det eksisterende data (INSERT, DELTE, UPDATE og MERGE), så arbejder storage engine også med Transaction Manageren. Opgaven for denne proces er at skrive records til transaktionsloggen som beskriver de handlinger der sker i transaktionen. Så snart disse records er skrevet til loggen, kan transaktionen blive kørt færdig. Dette betyder også at SQL serveren kun kan være så hurtig som den tilhørende transaktionlog.

Pages som er ændret i hukommelsen på serveren bliver skrevet til disk gennem den såkaldte CHECKPOINT proces. Som udgangspunkt eksekveres checkpoint processen hvert minut og skriver dirty pages fra buffer manageren til disk. En dirty page er en page med ændringer som endnu ikke er skrevet til disk.

Når en page er blevet skrevet til disk markeres den som “clean” igen.

Opsummering – Performance Tuning

Som du sikkert allerede har erfaret af denne indledning til SQL Performance Tuning, så er der rigtig mange ting der sker på SQL serveren, når man eksekverer en forespørgsel. Mange af de ord og termer der er brugt i dette indlæg, vil blive gjort mere klar senere i andre indlæg.

Har du fået mere blod på tanden til at læse videre om hvordan SQL serveren håndterer forespørgsler og databehandling, så vil jeg anbefale dig at læse nogen af nedenstående blogs:

Næste gang gennemgår jeg lidt mere om Data Pages og deres berettigelse i SQL Server.

Har du kommentarer eller andet, er du velkommen til at skrive dem herunder.

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

Undelete object from database

undelete

Have you ever tried to delete an object from the database by mistake or other error? You can undelete object – sometimes.

Then you should read on in this short post.

I recently came across a good co-worker of mine who lost one of the views on the developer database. He called me for help.

Fortunately the database was in FULL RECOVERY mode – so I could extract the object from the database log and send the script to him for his further work that day. I think I saved him a whole day of work…

The undelete object script

Here is the script I used:

select 
	convert(varchar(max),substring([RowLog Contents 0], 33, LEN([RowLog Contents 0]))) as [Script]
from 
	fn_dblog(NULL,NULL)
where 1=1
	and [Operation]='LOP_DELETE_ROWS' 
	and [Context]='LCX_MARK_AS_GHOST'
and [AllocUnitName]='sys.sysobjvalues.clst'
en_USEnglish