Non-Clustered Indexes - Uge 7

23. apr 2021
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.
BRIAN BØNK RUELØKKE
PRINCIPAL ARCHITECT
Uge 7

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.

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.

Har du misset nogle af de tidligere indlæg, kan du finde dem herunder:

Data & AI

Fra enkle datavisualiseringer til mere komplekse Business Intelligence og Data Warehouse løsninger.

Data & AI
Læs mere om vores Data & AI løsninger
Data & AI