Covering Indexes og Tipping Point - Uge 8

07. maj 2021
Med dette indlæg er vi allerede godt 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.
Brian Bønk Rueløkke
PRINCIPAL ARCHITECT
Uge 8 Covering Indexes og Tipping Point

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

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.

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.

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:

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:

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

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