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:

en_USEnglish