SQL Server index design
SQL Server index design er en av de viktigste faktorene for god ytelse. Likevel er det også et av områdene hvor jeg ser flest feil. Problemet er sjelden at det mangler indekser. Problemet er nesten alltid feil design eller en haug av indekser som aldri eller veldig sjelden brukes.
Resultatet:
- unødvendige scans
- høy CPU
- dårlig responstid
Hva en index gjør
En index gjør at SQL Server slipper å lese hele tabellen.
Det gir:
- mindre IO
- lavere CPU
- raskere queries
Men indekser er ikke gratis:
- dyrere skriveoperasjone (INSERT, UPDATE og DELETE)
- mer minne behov
- mer vedlikehold
Den vanligste feilener at indekser lages basert på kolonner man tenker er fornuftige å indeksere på – ikke på bruk.
Det fungerer dårlig.
Start med queryene
Se på:
- hva filtreres på
- hva joines det på
- hva som returneres
Indeksen skal støtte queryene som kjøres «ofte nok». En query som kjører en gang en gang i døgnet på stille tid på natta, vurder total kostnaden og om den er nødvendig.
Eksempel (logg / aktivitet)
Typisk query:
SELECT TOP (100) UserId, EventType, CreatedAt
FROM UserActivity
WHERE EventType = ‘LOGIN’
ORDER BY CreatedAt DESC
Typisk løsning:
CREATE INDEX IX_UserActivity_EventType
ON UserActivity(EventType)
Ser greit ut, men gir :
- sortering etterpå
- unødvendig IO
- ofte dårlig ytelse
Bedre:
CREATE INDEX IX_UserActivity_EventType_CreatedAt
ON UserActivity(EventType, CreatedAt DESC)
INCLUDE (UserId)
- Queryen filrerer det først på EventType som er første kolonne i indexen. (LOGIN i vårt tilfelle)
- CreatedAt er andre kolonne i indeksen. Innenfor EventType er det allerede sortert på CreatedAt kolonnen. Det gjør «ORDER BY CreatedAt DESC» billig.
- SELECT statmentet returnere kolonnene UserId, EventType, CreatedAt. INCLUDE’en på (UserId) gjør at UserId kolonnen ligger lagret i selve indeksen. Vi slipper dermed en kostbar KEY LOOKUP mot den clustrede indeksen for å hente kolonnen.
INCLUDE bør brukes oftere
Mange ytelsesproblemer skyldes lookups.
INCLUDE løser ofte det, men husk at det har en tilleggs kostnad ved skriveoperasjoner.
- mindre IO
- færre reads
- bedre ytelse
Clustered index er annerledes
Den skal ikke bare optimalisere for én query. Key kolonnene i den clustrede indexen blir en del av alle non-clustered indekser. Den skal fungere for hele tabellen.
En god clustered index er:
- smal
- stabil
- økende
- unik (hvis ikke gjøres den unik av SQL Server ved å legge til en ekstra kolonne som gjør den unik)
En løpende ID eller timestamp fungerer ofte bedre enn tilfeldig nøkkel (f.eks. GUID) som øker page splits, fragmentering og oftest minne behov.
Likevel, en regel har alltid unntak, men det må bli en annen post.
Frekvens betyr noe
Ikke alle queries skal optimaliseres.
Spør:
- hvor ofte kjører den
- hvor viktig er den
- hva koster indeksene for å understøtte queryen
Eksempel:
En sjelden admin-query:
SELECT *
FROM UserActivity
WHERE UserId = 999999
er ikke alltid verdt en egen index hvis indeksen:påvirker alle writes (altså ikke en filtered index)
og gir liten gevinst
Dropp den.
Indekser har en kostnad
En index påvirker hele systemet:
- alle writes (med mindre filtered index og filteret filtrerer bort skrive operasjonen)
- all vedlikehold
- total ressursbruk
Spør ikke bare om dette gjør dette queryen raskere. Spør oftere , er dette verdt det totalt?
Kort sagt
- start med queryene
- bruk INCLUDE der det gir mening
- velg riktig clustered index
- optimaliser det som betyr noe
- ikke alt som kan eller bør optimaliseres
- vurder alltid total effekt på systemet