Blog

Uge 10: Plan Caching

I denne uge vil jeg, som lovet i sidste uge, forsøge at hjælpe dig godt i gang med at forstå Plan Caching og de afledte effekter den mekanisme har på SQL serverens performance. Fra sidste uge kan du måske huske at enhver logisk query mod SQL serveren kræver en fysisk Execution Plan. Denne Execution Plan bliver gemt i Plan Cache for fremtidigt brug.

Ad-hoc SQL queries

Hver gang man eksekverer en ad-hoc SQL query mod SQL serveren, vil SQL serveren lave en unik Execution Plan for hver unik query. Men hvad er en unik query?

SQL serveren beregner en hash værdi af hele SQL querien (inkl. evt. hard-kodede parametre og disses værdier), og bruger denne hash værdi til at finde eksisterende Execution Plans i Plan Cache. Hvis en eksisterende plan eksisterer, vil denne blive brugt, ellers vil en ny plan blive udarbejdet og denne bliver så gemt i Plan Cache. Et eksempel:

select * from Sales.SalesOrderHeader
where CustomerID = 11000

GO

select * from Sales.SalesOrderHeader
where CustomerID = 30052

GO

select * from Sales.SalesOrderHeader
where CustomerID = 11223

GO

De 3 ovenstående queries vil hver generere en ny Execution Plan, fordi der er hard-kodede parametre. Og heraf bliver hash værdien for de 3 queries unik. Den afledte effekt er at der nu eksisterer 3 execution planer til næsten identiske queries. Dette problem bliver kaldt Plan Cache Pollution.

Plan Cachen bliver forurenet med nye Execution Plans som er svære at genbruge og der går værdifuld hukommelse til spilde som ellers kunne bruges på andre processser i SQL serveren. Målet er at have en så høj re-use count på Execution Plans fra Plan Cache.

Plan Stability

En måde at optimere genbrugen af Execution Plans fra Plan Cache, er at lave parameter værdier i SQL queries (med @parameternavn) eller, og endnu bedre, lave stored procedures som skal returnere data. På den måde kan SQL serveren genbruge Execution Plans fra Plan Cache meget effektivt.

Lad mig tage samme eksempel query som fra uge 8 som var den der udførte et Bookmark Lookup:

Som kendt fra uge 8, så giver Bookmark Lookup kun mening hvis man skal bruge få rækker fra sin tabel. Hvis man kommer over Tipping Point, så er det mere cost effektivt at lave hele tabel- eller clustered index scan. Men hvis SQL serveren genbruger en execution plan fra Plan Cache, så bliver denne beslutning slet ikke overvejet længere – SQL serveren genbruger blindt de gemte execution plans, også selv om performance bliver utrolig langsom.

Plan caching

Fra ovenstående eksempel, kan vi se at SQL serveren blindt genbruger en cached execution plan med Bookmark Lookup. Prøv at se forskellen på Estimated Number of Rows og Actual Number of Rows. SQL serveren genbruger en execution plan hvor der var 1 række i resultatsættet, mens der reelt i denne query er 1.499 rækker.

Årsan til dette er at der ikke er Plan Stability. Baseret på det estimerede antal rækker, anvender SQL serveren Bookmark Lookup i stedet for det mere effektive Table/Clustered Index Scan (hvis Tipping Point er nået). Dette er et ret udbredt og generelt problem for mange installationer af SQL serveren og disses performance problemer.

Men hvordan kan man så undgå disse Bookmark Lookups og genbrug af execution plans fra Plan Cache? Det kan du måske allerede gætte: Ved at lave et covering Non-Clustered Index som passer med den query der eksekveres. På den måde får du altid den bedste execution plan for din query og uanset hvordan du baserer dine parametre, vil SQL serveren altid svare hurtigt og effektivt. Men pas på med at lave for mange indexes for at tilgodese alle queries. Det er ret så vigtigt!

Opsummering – Plan Caching

En kort introduktion til Plan Cache og hvilke fordele og ulemper dette kan medføre på SQL serveren. Det er et tve-ægget sværd at arbejde med – på den ene side er Plan Cache et meget effektivt værktøj, da man kan genbruge allerede eksisterende execution plans, på den anden side et meget farligt værktøj, da man kan komme til at genbruge allerede eksisterende execution plans…

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 9: Execution Plans

Denne uge er også starten på 3. måned i denne følgeton. Og i dag skriver jeg om Exectution Plans i SQL Server. Execution Plans er det vigtigste værktøj for at forstå og arbejde med Performange Tuning på SQL Serveren, og for at kunne lave effektive ændringer i queries. Så i dag starter jeg ud med en introduktion til Execution Plans og hvordan man kan fortolke og læse dem.

Hvorfor Execution Plans?

Mange har spurgt mig hvorfor der overhovedet er Execution Plans i SQL serveren. Eks. “Vi har en query og hvorfor eksekverer SQL serveren ikke selv denne?”

For at svare på de spørgsmål, bliver jeg nødt til at starte med en lidt tætte gennemgang af SQL som kodesprog (og ja, nogen vil mene det ikke er et kodesprog, men den snak kan vi tage en anden dag 😀). SQL sproget (og også T-SQL på SQL serveren) er et declarative language (deklarativt sprog). Man beskriver logisk hvilken data man skal bruge fra databasen (SELECT) eller hvilken data der skal ændres (UPDATE, INSERT, DELETE). Et eksempel på nedenstående query:

select a.*, b.* from a
inner join b on a.id = b.id
where a.field = 'EnVærdi'

Med denne query fortæller man databasen og den underliggende engine (se uge 1) at:

  • Jeg vil gerne have al data fra tabel a og b
  • Begge tabeller skal sættes sammen via id-kolonnen
  • Rækkerne fra tabel a skal filtreres på ‘field’ ud fra ‘EnVærdi’

Vi beskriver kun, gennem SQL kode, hvordan resultatet af det data som querien henter skal se ud. Med en SQL query, kan du kun specificere resultatet – ikke andet. Man kan ikke fortælle SQL serveren hvordan den skal tilgå data fysisk/logisk og hvordan den ellers skal eksekvere og behandle det data, der skal arbejdes med.

Man tilgår med andre ord altid data på SQL serveren via et logisk lag, beskrivende hvilke elementer af data, der skal anvendes eller ændres. Men SQL serveren skal bruge en fysisk execution plan som i steps beskriver hvordan data skal hentes eller ændres. Execution Plan er en form for strategi udvalgt og planlagt af Query Optimizeren til at hente og behandle data.

En analogi: Forestil dig at fuldføre en rejse mellem to byer – fra Kolding til Odense, så har du bestemt dig for det logiske udtryk. Dette logiske udtryk, kan have mange forskellige fysiske fortolkninger:

  • Man kan gå
  • Køre på cykel
  • Tage toget/bilen/rulleskøjter

Det er underordnet hvilken fortolkning man anvender her, uanset hvilken der vælges er der igen flere forskellige fortolktninger af den del. Mulighederne eksploderer. Opgaven er at finde den mulighed som koster mindst og samtidig kræver mindst energi.

Vi vil som mennesker nok vælge bilen, men den koster jo mere end at gå.

Det samme problem har Query Optimizeren i SQL serveren. Query Optimizeren skal vælge den Execution Plan som kræver mindst arbejde, men som stadig tilgodeser kravene fra querien. Udfordringen for Query Optimizeren er at finde en Execution Plan der er god nok fra det overordnede Search Space – og dette Search Space kan være ret så stort hvis der er mange tabeller og indexes i den specifikke query.

Hvordan man læser en Execution Plan

Første gang man overhovedet kigger på en Execution Plan kan man godt blive blæst bagover og synes de er underlige. Det kan være svært ved første øjekast at forstå og fortolke dem korrekt. Et eks:

Execution Plans

Som du kan se af ovenstående, så indeholder alle Execution Plans flere trin, også kaldet Operators på SQL serveren. Disse operators er row-by-row fra SQL serveren, hvilket betyder at rækker fra data flyder fra højre mod venstre i Execution Plan.

I ovenstående tilfælde starter SQL serveren med at ekekvere det første trin Index Seek (NonClustered) operator på tabellen CustomerAddress. Hver enkelt række fra dette skan løber videre til Nested Loop operator, som kommer efter et scan. Og for hver række heri, foretager SQL serveren en Key Lookup (Clustered) operator (Bookmark Lookup, måske du kan huske den) på den samme tabel. Hvis der er en række der matcher, bliver denne række sendt videre til SELECT operator, som er den der endeligt returnerer resultatet tilbage til brugeren.

Som du måske allerede nu kan se, er det lettere at læse en Execution Plan fra højre mod venstre, fordi det er også denne vej rækker med data flyder. En Execution Plan er stadig fysisk eksekveret fra venstre mod højre. Vi læser den blot fra højre mod venstre for at følge data.

Jeg håber denne tilgang giver dig lige mere blod på tanden til at forsøge at forstå og læse Execution Plans. Hvis du vil mere i dybden med operators kan du læse mere om dem her:

Opsummering – Execution Plans

Jeg har skrevet lidt om og introduceret Execution Plans til dig i denne udgave af SQL Performance Tuning, og hvordan de kan blive læst og fortolktet. Vi kommukerer altid med SQL serveren via et logisk lag; vi beskriver det data vil skal bruge eller vil ændre.

Query Optimizeren har til opgave at bygge en god nok Execution Plan til disse queries. Og Execution Plan er den der slutteligt beskriver de fysiske trin der skal udføres på SQL serveren for at tilgodese queries. I næste uge kommer der mere om Execution Plans og Plan Caching – og hvorfor det kan være både godt og skidt.

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 8: Covering Indexes og Tipping Point

Med dette indlæg er vi allerede 2 måneder inde i forløbet og denne gang vil jeg skrive lidt mere om Non-Clustered Index og nogen flere negative følger der kan komme ved at anvende dem. Herunder Covering Indexes og Tipping Point.

I sidste uge skrev jeg lidt om Bookmark Lookup og at de kan være farlige for performance. Det var den mekanisme der skal lave en Table Scan hvis ikke alle kolonnerne fra query er med i index. Så hvis man vil undgå Bookmark Lookups, skal vi til at lave såkaldte Convering Indexes.

Covering Indexes

Et Covering Index er et traditionelt Non-Clustered Index på SQL Serveren. Forskellen ligger i at et Covering Index indholder alle de nødvendige kolonner til en given query. Dette betyder at Bookmark Lookup ikke er nødvendige.

Et eksempel – nedenstående query vil lave et Bookmark Lookup da postnummeret ikke er den af at det definerede Non-Clustered Index.

create nonclustered index nci_dbo_adresse on dbo.adresse (
regionId, adresseId
)
GO

select
   AdresseId
  ,Postnummer
from dbo.Adresse
where regionId = 1

Ovenstående query laver Bookmark Lookup til tabellen dbo.Adresse for at finde data til kolonnen Postnummer.

Det kan vi komme omkring ved, ved at tilpasse det tidligere Non-Clustered Index som nedenfor.

create nonclustered index nci_dbo_adresse on dbo.adresse (
regionId, adresseId
)
include (Postnummer)
GO

Når den samme query eksekveres igen, vil du se at Execution Plan ikke længere har en Bookmark Lookup og at SQL serveren anvender et Index Seek (NonClustered). Logical Reads vil også være kraftigt mindre end ved med det første index. Det er ret vilde performance-optimeringer, der er sket med en lille ændring.

Husk dog på, at det er ikke alle Bookmark Lookups der er dårlige for performance. Målet er ikke at eliminere alle Bookmark Lookups – kun dem der indvirker negativt på performance.

Tipping Point

I nogen tilfælde, når SQL serveren skal lave en Bookmark Lookup for en specifik query, så kan det ske at SQL serveren vælger at en Bookmark Lookup er for dyr (i forhold til Logical Reads). I disse tilfælde vil SQL serveren lave en hel Table Scan og blot udelade alle rækker som ikke passer med query’ens filter. Det punkt hvor det bliver valgt hedder Tipping Point på SQL serveren. Tipping Point definerer om SQL serveren laver en Bookmark Lookup eller en fuld Table Scan.

Covering Indexes

Tipping Point ligger et sted mellem 1/4 og 1/3 af det totale antal Data Pages som query’en skal læse. Det har ikke noget med antallet af rækker der skal læses. Fordi, som du måske husker, så er det størrelsen på hver række, der definerer hvor mange rækker, der kan være på de 8 kb Data Pages.

Hvis jeg for eksempel har en tabel på SQL serveren med hver række fylder 400 bytes. Så kan der være 20 rækker på en Data Page. Hvis der også er et Non-Clustered Index på kundeId kolonnen. Tabellen indeholder måsle 2000 rækker. Så vil nedenstående query lave et Bookmark Lookup:

select * from kunder
where kundeid < 1062

Hvis jeg så retter min query til at have bare en række mere med, så rammer jeg Tipping Point og SQL serveren scanner hele tabellen:

select * from kunder
where kundeid < 1063

To næsten ens queries, men to vidt forskellige Execution Plans. Dette kan lede til store problemer, da man så ikke længere har en stabil Execution Plan for sine queries (også kaldet Plan Stability).

Opsummering – Covering Indexes og Tipping Point

Jeg har forsøgt her at ramme lidt dybere ned i Non-Clustered Index og Tipping Point mekanismerne ved at kigge på Covering Indexes. De næste 4 uger kommer til at være meget mere om Indexes og de “magiske” ting der kan ske med disse.

Og det kan godt være at Indexes kan optimere dine queries til at levere data hurtigt når der skal læses, men det kan så give en negativ indvirkning på performance, når der skal skrives til tabellerne. Et index skal vedligeholdes efter hver endt INSERT, UPDATE og DELETE transaktion. Så husk at balancere indexeringstrategien på både læse og skrive operationer.

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 7: Non-Clustered Indexes

I sidste uge var emnet omkring Clustered Indexes på SQL Server. Og her blev der beskrevet at data fysisk sorteres på disk via de definerede kolonner, når man implementerer et Clustered Index. I tillæg hertil kan man tilføje flere Non-Clustered Indexes på SQL server.

Et Non-Clustered Index er et sekundært index som kan defineres på en eller flere kolonner af tabellen. Igen kan et Non-Clustered Index sammenlignes med en bog. Men denne gang er bogen som et reference-index til hele leksikonnet. Lidt ligesom der var reference-index til Lademanns leksikon (til dem af jer der kender det og kan huske det). Indexet findes også for nogen bagerst i bøgerne – sammenligningen er den samme. Når man skal finde et specifikt navn eller ord, slår man op i index og bliver henvist til en eller flere sider.

Indexet fra bogen giver dig en “lookup value”, side-nummeret, hvor du kan finde flere detaljer. Det samme gælder for SQL serverens Non-Clustered Index og dens anvendelse i Execution Plan. Her returnerer SQL Serveren de Data Pages på Leaf niveau, som indeholder det efterspurgte data. SQL Serveren fremfinder så de specifikke Data Pages og returnerer de efterspurgte kolonner og rækker. Hvis kolonnerne ikke er direkte en del af det definerede Non-Clustered Index, så kalder man det for et Bookmark Lookup.

Bookmark Lookups

Hver gang SQL serveren anvender et Non-Clustered Index i Execution Planen og man efterspørger en kolonne som ikke er en del af indexet, så bliver SQL Serveren nødt til at lave et Bookmark Lookup.

Nedenfor vises et eksempel på en sådan Bookmark Lookup.

non-clustered index

Af ovenstående kan læses at SQL serveren laver en Non-Clustered Index seek på Address tabellen. Hertil laver SQL serveren en anden søgning for at finde resten af de adspurgte kolonner via Key Lookup (Clustered) på den underliggende Clustered Table. Umiddelbart er det ret så smart at kunne dette, men det kan også være meget farligt og tungt at arbejde med, hvis man ikke tager sine forholdsregler.

Bookmark Lookups kan lede til Deadlocks og performance vil være tydelig dårlig hvis tabellen har dårlige eller out-of-date statistics eller der er sker Parameter Sniffing (to nye begreber som jeg kommer tilbage til en senere uge).

Bookmark Lookups kan kun ske i samarbejde med Non-Clustered Index, og næste gang graver jeg lidt mere i årsagen og hvordan vi kan komme omkring denne performance nedgang.

Afhængigheder til Clustered Key

Som jeg har skrevet tidligere, så gemmer SQL serveren Leaf Level referencer i Non-Clustered Index som peger på Data Pages i enten Clusted Table eller Heap Table.

Når man definerer et Non-Clustered Index på en Heap Table, så vil lookup værdien til referencen være Row-Identifier. Dette er en 8 bit lang værdi, som gemmer Page nummeret (4 bytes), file id (2 bytes) og slot nummeret (2 bytes) som tilsammen henviser til den fysiske placering på disk hvor data er gemt.

Hvis man definerer et Non-Clustered Index på en Clustered Table, så anvender SQL serveren den definerede Index nøgle (Clustered Key) til at finde lookup værdien. Dette betyder at din nøje udtænkte Clustered Key er en del af alle Non-Clustered Index. Der er dermed en stor afhængighed mellem Clustered og Non-Clustered Index. Og Clustered Key er dermed også det mest redundante element i din tabel. Det er derfor man skal vælge sine kolonner med omhu, og man bør have denne liste med i tankerne når man laver sin Clustered Key – den bør være:

  • Unik
  • Smal
  • Statisk

Opsummering – Non-Clustered Index

Non-Clustered Index er et centralt element i optimering af queries. Og hvis man kommer til at introducere Bookmark Lookups i sit design, kan man også introducere side-effekter ifm. performance på databasen.

I næste uge kommer jeg til at skrive om Covering Non-Clustered Indexes som, ofte, kan eliminere Bookmark Lookups. Og her introducerer jeg også Tipping Point, som definerer som SQL serveren anvender et Non-Clustered Index eller ej.

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 6: Clustered Indexes

I sidste uge skrev jeg og introducerede Heap Tables, og her nævnte jeg at tabeller på en SQL server kan være to typer, Heap Tables og Clustered Table – sidstnævnte defineret ved at have et clustered index tilknyttet. Og i dag dykker jeg lidt mere ned i Clustered Indexes og skriver lidt om hvordan man bl.a. kan vælge den rigtige Clustered Key.

Under normale omstændigheder kan man meget ofte gå ud fra at en tabel allerede har et Clustered Index defineret, for hver gang der tilføjes en Primary Key Contraint på SQL server, så er denne contraint, som udgangspunkt opretholdt af et Unique Clustered Index. Det betyder at man i denne kolonne, eller flere kolonner hvis det er en sammensat nøgle, skal have unikke værdier for alle rækkerne. Hvis der ikke allerede eksisterer en Primary Key på tabellen, så kan man selv oprette et Clustered Index via CREATE CLUSTERED INDEX statement og her specificere sin(e) kolonne(r).

Ckustered Indexes

Så snart en tabel har et Clustered Index defineret, så er data fysisk sorteret ud fra de(n) definerede kolonne(r). Herunder kommer lidt fordele og ulemper ved at arbejde med Clustered Indexes på SQL serveren.

Fordele ved Clustered Indexes

En af de største fordele ved at anvende Clustered Indexes er at data er fysisk sorteret på det underliggende storage (disk) ud fra kolonnerne som defineret i indexet. Lidt sammenligneligt med en gammeldags telefonbog (den vi rev i stykker i sidste uge). Her er navnene sorteret efter efternavn. Clustered Tables afviger derfor betydeligt fra Heap Tables som ikke har nogen fysisk sortering på disk.

Det er en virkelig stor fordel at få fra Clustered Tables. Forestil dig at du skal søge efter en specifik række i tabellen, og ved at finde den række kan du søge (WHERE) direkte på den kolonne som er angivet i Clustered Index. Her vil SQL Serveren direkte anvende en execution plan med Clustered Index Seek operator. Seek operationer er ultra hurtige og effektive, fordi SQL serveren anvender B-tree stukturer til at fremfinde de relevante rækker.

I sidste uge skrev jeg lidt om kompleksisteten af Heap Table queries, og I skal ikke snydes denne gang – kompleksisteten af en Seek operation er defineret ved O(log N) – igen en referance til The Big O notation.

Det er det samme som når man søger i en telefonbog, du er garanteret at alle efternavne, som starter med A står først, og at du dermed kun behøver at kigge der. Du er fri for at scanne hele bogen for at finde alle med A – det er det samme SQL serveren kan med et Clustered Index og undgår dermed Data Page scanninger helt ud til leaf levels.

Så længe det definerede Clustered Index ikke har nogen index fragmentation, så vil man også opleve at få sequential I/O – dvs. at diskene kun flytter læsehovedet fremad under læsning. Det er langt hurtigere kun at læse fremad, i stedet for at læse begge veje – ligesom hvis vi selv skal læse en bog.

Indexfragmentation betyder at den logiske og fysiske rækkefølge på Data Pages på Leaf level ikke er den samme. Det kan løses ved at eksekvere Index Rebuild og/eller Index Reorganize. Jeg kommer tilbage til forskellen mellem disse to metoder i en senere gennemgang.

Om man er udsat for indexfragmentation kommer an på den/de valgte kolonner der er indeholdt i Clustered Key. Så længe man anvender en altid stigende værdi (såsom INT eller OrderDate), så vil nye rækker altid blive indsat sidst i Indexet. Vælger man en anden tilgang der ikke altid har stigende værdier ift seneste værdi, så bliver SQL serveren nødt til at sætte data ind midt i tabellen, muligvis lave et Page Break og tilmed tilføje en Forwarding Record for at ramme den næste Data Page korrekt.

Det kommer lidt mere i detaljer nedenfor.

Ulemper ved Clustered Index

Hvis data kun bliver indsat sidst i et Clustered Index, så kan det fremkalde et såkaldt Last Page Insert Latch Contention, fordi man har et enkelt hotspot i slutningen af Clustered Index, hvor risikoen for at flere simultane queries forsøger at skrive til det samme sted, som forsøgt illustreret nedenfor.

For at komme ud over denne fænomen, kan man vælge at anvende en randomiseret Clustered Key i Clustered Index, for så bliver rækkerne fra de forskellige INSERT statements distribureret over flere forskellige steder i det definerede Clustered Index. Men desværre introducerer det så en del Hard Page Splits, fordi SQL serveren bliver nødt til at allokere nye Data Pages midt i det samlede datasæt.

En Hard Page Split betyder at en eksisterende Data Page bliver delt i to for at gøre plads til det nye data midt i datasættet – det er utrolig meget tungere end det modsatte Soft Page Spilts, hvor en nye Data Page blot bliver allokeret i slutningen af tabellen og data bliver gemt herpå.

Som sideeffekt heraf, vil man også opleve en del index fragmentation hvis man anvender en randomiseret nøgle for sit Clustered Index. Igen vil det føre til Random I/O, som vil knække performanec på enhver traditionel rotations-harddisk ifm scan operationer, da læsehovedet skal skifte spor hele tiden rundt omkring på disk for at finde det samlede datasæt.

Opsummering – Clustered Index

Clustered Indexes skallerer utrolig godt, så længe man tænker sig om omkring den anvende nøgle for index. Clustered Index anvender B-tree strukturer til at søge og fremfinde det forespurgte data, og SQL serveren kan meget effektivt anvende data. Men husk at vælge den rigtige nøgle for dit Clustered Index, ellers så vil du opleve en kraftig nedgang i performance for din tabel. Det er nogen gange tidskrævende at finde den eller de rigtige kolonner til at være en del af det Clustered Index. Og når det valg er korrekt, vil man opleve en meget hurtig performance.

Næste uge skriver jeg videre på Indexes og tager fat i Non-Clustered Index – så hyg dig 7 dage endnu og så vender jeg tilbage med mere.

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 5: Heap Tables

Så ruller anden måned af SQL Performance Tuning, og denne måned bliver der skruet op for sværhedsgraden. Jeg kommer til at skrive rigtig meget om index, index og så lidt mere index på SQL Server. Her i første indlæg denne måned bliver om Heap Tables.

Selv om det kan være tungt og trættende, så er det det hele værd. Tro mig 🙂

Som skrevet, bliver der denne gang fokuseret på Heap Tables og hen over de næste 3 uger kommer jeg til at skrive om Clustered Index, Non-Clustered Index og generelle index-strategier på SQL Server databaser.

Men vi starter ved Heap Tables som er tabeller uden et Clustered Index. Hvis en tabel på SQL serveren har et Clustered Index så kaldes det for en Clustered Table og uden et sådan index, så er det en Heap Table.

heap tables

Det er også sådan Microsoft helt tilbage i starten af Azure’s spæde opstart, ville have os alle til at oprette tabeller på en Azure SQL Database, der skulle et Clustered Index på tabellen.

I en Heap Table er data ikke sorteret på nogen som helst måde. Al data ligger som en rodet masse – i uorden og som udstrukturerede rækker (nej, ikke som big-data terminologien). Bare i uorden og vilkårlige bunker. Når man tilgår data fra en Heap Table via et almindeligt SELECT statement, så vil SQL Serveren anvende en Table Scan operator i Execution Planen (mere om operators i Execution plans senere), når der heller ikke er et passende Non-Clustered Index tilstede. Der findes ikke en Table Seek operator. Det er vigtigt at huske.

Som jeg kommer til at skrive om i næste uge omkring Clustered Indexes, så bliver et sådan index tilgået via en Clustered Index Scan og via en Clustered Index Seek operator. På en Heap Table er der kun mulighed for at benytte sig af Table Scan. Og en Table Scan betyder at HELE tabellen gennemsøges for data og dermed bliver al data fra tabellen også læst. Så jo mere data der er i tabellen, jo længere tid vil en forespørgsel tage.

En Table Scan vil dermed altid være en O(n) operation (The Big O notation) – det kan ikke skallere efterhånden som tabellen bliver større og større.

Herunder gennemgår jeg fordele og ulemper ved at anvende Heap Tables på en SQL server.

Fordele ved Heap Tables

Heap Tables er meget meget hurtige – til at lave INSERT statements imod. Som skrevet ovenfor, så er data gemt som en stor bunke uden orden eller struktur. Ligesom hvis du tager en gammeldags telefonbog og river alle siderne ud af bogen og ligger dem foran dig – det er en Heap Table.

Det at tilføje nye rækker til denne bunke af data er meget hurtig – der skal blot findes en ny side frem (Data Page på 8kb), skrive de nye rækker på denne side og gemme den i bunken med alle de andre sider. Færdig – ingen sortering eller specifik placering er nødvendig.

Det er det samme for en SQL Server. En ny Data Page på de velkendte 8kb er allokeret og de nye rækker bliver skrevet til denne Data Page og denne er så assignet til den specifikke Heap Table. Done deal – det er en meget hurtig måde at gemme ny data på, for SQL serveren skal ikke garantere eller på anden vis tilsikre at data er sorteret eller struktureret. Det er det man kalder “ad letteste vej” i forsvaret.

Af denne årsag, kan det nogen gange være fordelagtigt at have nogen tabeller på SQL serveren som Heap Tables. Tabeller hvor der kan ske meget store og meget hurtige INSERT statements i. Et eks kunne være staging data til et Data Warehouse eller Log-tabeller til en OLTP løsning. Og de skal ikke bruges alle steder – det er kun i særlige tilfælde at de kan give mening at anvende.

Ulemper ved Heap Tables

Ud over fordelene ved at Heap Tables er super hurtige til at modtage data, så er der også en række ulemper ved dem. Disse ulemper bør du have med i din overvejelse, når du designer din database, og hver gang du opretter en Heap Table.

En af de første, som nogen af jer måske allerede har regnet ud, så har en Heap Table en negativ indvirkning på Random I/O på det underliggende storage system. Prøv at forestille dig en simpel forespørgsel (SELECT) mod en Heap Table. Hvis data ikke allerede er gemt i Buffer Pool’en bliver SQL serveren nødt til at starte fysiske indlæsninger af alle Data Pages fra disk. Disse indlæsninger er Random I/O da alle Data Pages er gemt “et eller andet sted” på disken, og ikke ved siden af hinanden.

Hvis man (stadig) bruger diske med skriver der roterer, så vil man opleve negativ performance på disk niveau’et, da Random I/O er langsomt, meget langsomt. Læsehovederne fra disken skal flytte sig rundt på skiverne hele tiden. SSD diske er meget bedre til at håndtere Random I/O, for der er det efterhånden ret ligegyldigt hvor data ligger på disken. SSD diske udjævner mere og mere den hastighedsnedgang der er ved Random I/O – og ja, der er stadig en forskel – selv på det hurtigste SSD diske i dag.

Et andet stort problem som man med sikkerhed vil have med store Heap Tables i en database, er mange Forwarding Records i Data Pages. Rækker som gemmes på en Heap Table kan i nogen tilfælde rykke sig til en anden Data Page – og i disse tilfælde så gemmer SQL serveren en henvisning til den nye Data Page på den originale Data Page, så data kan findes igen.

Når man så tilgår sine data fra en Heap Table, så læser SQL serveren stadig den originale Data Page og bliver herfra henvist til den nye ekstra Data Page som den skal finde frem og læse. Dette påvirker læsehastigheden gevaldigt.

Opsummering – Heap Tables

Heap Tables har deres berettigelse på en SQL server – i visse tilfælde. Anbefalingen er næsten altid at lave et Clustered Index på tabellen og dermed få en Clustered Table. Der hvor det giver mening at have en Heap Table, så skal alle fordele og ulemper overvejes.

Hvis du har fået blod på tanden til at læse mere om Heap Tables og Clustered Tables, så er Thomas Kejsers blogindlæg om det samme rigtig godt:

Næste uge kommer til at handle om Clustered Index på SQL serveren. Om hvordan man vælger det rigtige og om hvad man skal passe på omkring disse index.

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:

Uge 4: Data Page begrænsninger

De første 4 uger er allerede gået og vi tager nu fat på nogen af de lidt tungere emner. Alt herfra bygger videre på de tidligere indlæg.

Har du misset nogen af dem – så kan du så dem herunder:

I dag bliver emnet omkring de begrænsninger der er implementeret omkring data pages. Mange har delte meninger om disse begræsninger, og måske du også har din. Skriv den gerne som kommentar nedenfor, når du har læst indlægget.

Data Pages er, som kendt fra uge 2, altid 8kb data og man kan gemme 8060 bytes data på dem. Og størrelsen på den enkelte række i datasættet afgør hvor mange rækker, der kan være på en enkelt data page. Når man arbejder med faste datalængder (Eks. CHAR, INT, DATETIME osv) så er der en fast begræsning på størrelsen af en række, som ikke kan overstige 8060 bytes (inkl. den interne overhead fra SQL serveren).

begræsninger

Begrænsninger – de gode af dem

Lad os tage et eksempel med en tabel med mindre end 8 kolonner. Her skal man lægge 7 bytes til for den interne overhead for SQL serveren. Og for hver 8 kolonner herefter skal man lægge 1 byte. Så ved 17 kolonner skal man have 9 bytes som overhead.

Hvis man forsøger at lave en tabel, som indeholder rækker med mere data end der kan være på en database (inkl. overhead), så returner SQL serveren en besked til dig – eks:

CREATE TABLE LargeTable
(
   Kolonne1 CHAR(5000),
   Kolonne2 CHAR(3000),
   Kolonne3 CHAR(54)
)

Som du kan se nedenfor, så kræver denne tabel 8061 bytes for hver række (5000+3000+54+7). Det er mere end der kan være på en Data Page.

Msg 1701, Level 16, State 1, Line 1
Creating or altering table ‘LargeTable’ failed because the minimum row size would be 8061, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

Hvis du laver en tabel med mere end 8 kolonner, så skal man huske at tillægge de ekstra bytes for hver 8 kolonner. Eks:

CREATE TABLE LargeTable
(
   Kolonne1 CHAR(1000) NOT NULL,
   Kolonne2 CHAR(1000) NOT NULL,
   Kolonne3 CHAR(1000) NOT NULL,
   Kolonne4 CHAR(1000) NOT NULL,
   Kolonne5 CHAR(1000) NOT NULL,
   Kolonne6 CHAR(1000) NOT NULL,
   Kolonne7 CHAR(1000) NOT NULL,
   Kolonne8 CHAR(1000) NOT NULL,
   Kolonne9 CHAR(53) NOT NULL
)

Igen giver dette mere end de 8060 bytes der er tilladt på en data page og samme fejl som tidligere kommer fra SQL serveren:

Msg 1701, Level 16, State 1, Line 1
Creating or altering table ‘LargeTable’ failed because the minimum row size would be 8061, including 8 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

Begræsninger – de knapt så gode

I det ovenstående afsnit, var de gode begrænsninger. De er gode, for SQL serveren viser og fortæller dig allerede ved oprettelse af tabellen, at der er noget galt.

Men der er også nogen som du vil komme til at synes er knapt så gode. For her vil SQL serveren fint tillade dig at oprette tabellen, men idet du begyndet at indsætte data i den, vil fejlene komme væltende. Nogen gange kan du være glad for at det lykkes, andre gange fejler det.

Problemet er datatyper med variabel længde, som VARCHAR. Når disse felter ikke passer på en enkelt data page alene, så forsøger SQL serveren at offloade dem til en såkaldt off-row location på andre data pages. Dette kales også for Row-Overflow Page (en ny page type til listen…). På den originale data page sættes nu en markør på 24 bytes som peger på den nye row-overflow page. I nogen tilfælde kan denne markør sammen med længden på de andre kolonner godt overskride de 8060 tilladte bytes.

Et eksempel på en sådan tabeldefinition kunne være:

CREATE TABLE LargeTable
(
   Kolonne1 CHAR(5000),
   Kolonne2 CHAR(3000),
   Kolonne3 CHAR(30),
   Kolonne4 VARCHAR(3000)
)

Bemærk den sidste kolonne er VARCHAR(3000). Her giver SQL serveren dig en advarsel om at tabellen godt kan oprettes, men at INSERT/UPDATE handlinger måske kan fejle.

Warning: The table “LargeTable” has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

Den følgende insert statement vil kunne lade sig gøre:

INSERT INTO LargeTable VALUES
(
   REPLICATE('x', 5000),
   REPLICATE('x', 3000),
   REPLICATE('x', 30),
   REPLICATE('x', 19)
)

Her er en enkelt række 8056 bytes lang. Og her vil SQL serveren fortsat gemme al data på den samme data page. Men prøv at se nedenstående eksempel:

INSERT INTO LargeTable VALUES
(
   REPLICATE('x', 5000),
   REPLICATE('x', 3000),
   REPLICATE('x', 30),
   REPLICATE('x', 3000)
)

Her skal SQL Serveren flytte data fra den 4 kolonne til en row-overflow data page, fordi de 3000 bytes, sammen med de første kolonner, ikke kan være på den samme data page. Så her efterlader SQL serveren en markør på data pagen på 245 bytes som peger på den ekstra data page. Rækken er derfor nu 8061 bytes lang (5000+3000+30+24+7 bytes).

Så nu fylder datarækken mere end 8060 bytes og INSERT statement fejler.

Dette er den dårlige begræsning på data pages, som først rammer dig når de endelige transaktioner foregår på databasen.

Det er en god ide at huske på denne begrænsning når du opretter tabeller på din database.

Opsummering – Data Page begræsninger

Når man designer tabeller på SQL serveren, bliver man virkelig nødt til at tænke over hvad man laver. Som gennemgået ovenfor, så er der nogen begræsninger som gør at man kan ramme en masse fejl når først dataflow og det hele ruller.

Nogen gange er man heldig at det kan køre igennem, og andre gange hjælper SQL serveren med en fejlmeddelelse.

Selv om man bliver advaret, har jeg set installationer, som stadig har disse fejl. Så husk det nu 🙂

Næste gang vender jeg HEAP tables og hvad det er for en størrelse på en SQL database.

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 3: Extent Management på SQL Server

Med uge 3 allerede godt i gang og sidste uges gennemgang af Data Pages, kommer her denne uges indlæg. Denne gang om Extent Management i SQL Server.

Har du misset det sidste indlæg, så kommer det herunder

Uge 2: Data Pages – grundlaget i SQL serveren

På et overordnet plan er Extents en samling af 8 pages på hver 8kb. Dermed er de dataelementer på 64kb. SQL server har to forskellige typer Extents.

  • Mixed Extents
  • Uniform Extents

Mixed og Uniform Extents

I en Mixed Extent er de 8 pages fra forskellige databaseobjekter, såsom tabeller og indexes. Det betyder også at en Mixed Extent kan pege på 8 forskellige objekter. Modsat en Uniform Extent, som kun indeholder pages fra et og samme databaseobjekt.

Det spændende er nu, hvordan SQL Serveren arbejder med denne forskel og hvorfor det er sådan.

Før årtusindeskiftet var diskkapacitet dyrt, meget dyrt. Målet var den gang at optimere så meget som muligt på anvendelsen af denne kapacitet. Derfor bliver de første 8 pages af nye tabeller eller indexes altid allokeret til Mixed Extents. Det betyder også at tabeller og indexes vokser i størrelse med 8kb ad gangen. Resultatet bliver at små tabeller forbliver meget små – forbruget af kapaciteten er så effektiv som muligt.

Så snart et databaseobjekt overstiger 8 pages og den 9. page skal allokeres, bliver der også allokeret en hel Extent til det objekt. Størrelsen stiger derfor fra 8kb til 72kb (8kb + 64kb). Med den 17 page stiger den til 136kb (8kb + 64kb + 64kb) osv.

Med de nuværende priser på diskkapacitet, så er det lidt hovedrystende at have dette designvalg, men set historisk har det været et vigtigt valg for at spare diskplads.

Extent Management

Extent Management

Men hvordan håndterer SQL Serveren så alle disse Extents? Forestil dig en database på 1 TB – det er en kæmpe bunke Extents og endnu flere pages. SQL Serveren anvender her 2 særlige pages, som igen (og som altid) er 8kb:

  • Global Allocation Map Pages (GAM)
  • Shared Global Allocation Map Pages (SGAM)

Uniform Extents er altid håndteret af GAM pages. SQL serveren bruger GAM pages 8.000 bytes (64.000 bits) til at danne et bitmap som repræsenterer en Uniform Extent. Hvis en bit er tændt bliver den specifikke Extent ikke brugt (den er dermed ledig), hvis den er slukket bliver den brugt. Det betyder at en GAM page kan håndtere 4 GB data (64.000 x 64 / 1024 / 1024). Derfor ses det også at GAM pages ligger vel spredt ud over datafilen til databasen – for hver 4 GB. Det samme er gældende for SGAM pages. Den håndterer også 4 GB data, men for Mixed Extents.

Når man indsætter en række i en tabel, finder SQL serveren, via SGAM pages, de Mixed Extents som har mindst en ledig data page. Hvis en tabel eller index er større end 64kb, så finder SQL Serveren den næste ledige Uniform Extent via gennemsøgning af GAM pages.

Når jeg senere kommer til at tale om TempDb, så kommer jeg også til at tale om seriøse performanceproblemer, når det workload man arbejder med opretter store mængder data i TempDb. Jeg kommer også med tips og tricks til hvordan man kan komme omkring det med konfiguration af TempDb (on-premise og managed instance i Azure).

Opsummering – Extent Management

Med denne gennemgang af Extent Management og de tidligere indlæg om Pages og motorrummet, begynder grundlaget for performancetuning at være på plads.

Har du fået blod på tanden til at vide mere om Extents på SQL Serveren, så er der lidt links nedenfor.

Næste gang skriver jeg lidt om Data Page Restrictions.

Husk at skrive dig på maillisten nedenfor, så du ikke misser næste udgave af denne blogserie.

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.

en_USEnglish