I dag skal vi se på filtered indexes i SQL Server – når de er nyttige, hvordan de kan forbedre ytelsen, og hvordan de kan redusere både lagringsbehov og ressursbruk.
Jeg har laget en liten demo basert på Stack Overflow-databasen. Her har vi tabellen `Posts`, som inneholder omtrent 56 millioner rader. Her ser vi antall rader i index ID 1, som i dette tilfellet er den clustrede indeksen på tabellen.
La oss tenke oss at vi har en batchjobb som med jevne mellomrom leter etter poster som skal slettes. For eksempel kan en bruker markere en post som slettet, og så kommer en bakgrunnsjobb senere og rydder den fysisk bort.
For å simulere dette legger jeg til en kolonne som heter `Deleted`. Det er en bit-kolonne med defaultverdi 0, altså ikke slettet. Deretter markerer vi 20 tilfeldige rader som slettet ved å sette `Deleted = 1`.
Vi har altså en tabell med omtrent 56 millioner rader, men bare 20 av dem er markert som slettet. Foreløpig har vi ingen indeks på `Deleted`.
Hvis vi nå spør etter radene hvor `Deleted = 1` og ser på eksekveringsplanen, får vi som forventet en **clustered index scan**. SQL Server må i praksis lese gjennom hele tabellen for å finne disse 20 radene. Det betyr at vi må lese millioner av rader for å finne bare 20.
Et naturlig første tiltak er derfor å opprette en vanlig nonclustered index på `Deleted`. I vårt tilfelle har vi bare 20 rader med `Deleted = 1`, mens nesten alle de andre 56 millionene har `Deleted = 0`.
En vanlig nonclustered index vil likevel inneholde en entry for hver eneste rad i tabellen. Hver indeksrad inneholder verdien fra `Deleted`, i tillegg til nøkkelen fra den clustrede indeksen, som SQL Server bruker som row locator. Det betyr at også denne indeksen får omtrent 56 millioner entries.
Hvis vi nå kjører den samme spørringen igjen og ser på eksekveringsplanen, får vi en **index seek**. SQL Server finner de 20 radene svært raskt. Så for selve SELECT-spørringen fungerer denne løsningen veldig bra.
Men den har også en kostnad. Når nye rader legges inn i `Posts`, må SQL Server vedlikeholde denne indeksen. Siden alle radene skal være representert i indeksen, vil en INSERT i `Posts` også føre til at det legges inn en entry i denne nonclustered indeksen. Det samme gjelder relevante UPDATE- og DELETE-operasjoner.
For batchjobben som leter etter slettede rader er indeksen altså veldig effektiv, men vi betaler for den hver gang dataene i tabellen endres.
Hvis vi ser på antall rader i indeksene, blir dette tydelig. Den clustrede indeksen inneholder omtrent 56 millioner rader. Den vanlige nonclustered indeksen på `Deleted` inneholder også omtrent 56 millioner rader. Alle radene er altså representert i begge indeksene.
Men her har vi et annet alternativ. Hvis det vi hovedsakelig er interessert i, er radene hvor `Deleted = 1`, kan vi i stedet bruke en **filtered index**.
Vi oppretter i praksis den samme indeksen, men legger til et filter:
`WHERE Deleted = 1`
Det betyr at SQL Server bare lagrer de radene som faktisk oppfyller filterbetingelsen.
Hvis vi nå ser på indeksene igjen, har vi tre indekser. Den clustrede indeksen inneholder omtrent 56 millioner rader. Den vanlige nonclustered indeksen inneholder også omtrent 56 millioner rader. Men den filtrerte indeksen inneholder bare de 20 radene hvor `Deleted = 1`.
Forskjellen blir enda tydeligere hvis vi ser på hvor mange datapages indeksene bruker. SQL Server bruker pages på 8 KB. Den clustrede indeksen er naturligvis stor, fordi den inneholder selve tabelldataene. Den vanlige nonclustered indeksen bruker rundt 76 000 pages, mens den filtrerte indeksen, som bare inneholder de 20 aktuelle radene, bruker bare noen få pages.
Det er en dramatisk forskjell. En mindre indeks betyr mindre lagringsplass på disk, mindre plass i buffer cache og mindre data som SQL Server må håndtere.
Hvis vi nå kjører spørringen med `WHERE Deleted = 1` og ser på eksekveringsplanen, ser vi at SQL Server bruker den filtrerte indeksen. Vi får altså fortsatt en effektiv index seek, men nå mot en indeks som er ekstremt mye mindre enn den vanlige nonclustered indeksen.
Det gir oss også en annen fordel. Hvis vi setter inn en ny rad i `Posts` med `Deleted = 0`, skal denne raden ikke inn i den filtrerte indeksen. SQL Server må fortsatt evaluere filterbetingelsen, men selve indeksstrukturen trenger ikke å få en ny entry for denne raden.
Først dersom raden senere endres fra `Deleted = 0` til `Deleted = 1`, må den legges inn i den filtrerte indeksen. Dermed reduserer vi indeksvedlikeholdet betydelig når det store flertallet av radene ligger utenfor filteret.
Filtered indexes er derfor spesielt interessante når vi har en stor tabell, men ofte søker etter en liten og tydelig avgrenset del av dataene.
Typiske eksempler kan være:
– rader som er markert for sletting
– aktive eller inaktive rader
– ubehandlede jobber i en kø
– rader med en bestemt status
– eller kolonner hvor vi ofte søker etter `IS NOT NULL`
I eksemplet vårt går vi altså fra en indeks med rundt 56 millioner entries til en indeks med bare 20. Vi beholder den raske oppslagsytelsen, samtidig som vi reduserer både lagringsbehov, cache-bruk og vedlikeholdskostnad.
Det er nettopp i slike situasjoner at filtered indexes kan være et veldig effektivt verktøy for SQL Server performance tuning.