Ez egy igen durva optimalizálási lehetőség azoknak (is), akik nagy táblákkal dolgoznak.
Normál esetben egy indexfa a teljes tábla adatainak egy részhalmazát, a kulcsként kijelölt oszlopok tartalmát tárolják rendezetten egy fában, így nagyon gyorsan lehet a kulcs(ok) alapján keresni. A hangsúly a teljes táblán van. Mi van, ha mondjuk több évnyi adat van egy táblában, de mi sokszor (de nem mindig) csak az utóbbi egy hónappal dolgozunk? Egyrészt lehet particionálni, ebbe most nem megyek bele, bár fontos téma.
Ha NC indexet rakunk a dátum oszlopra és kevés sort szűrünk le van esélyünk a hatékony szűrésre, de ha sok adatunk van jó nagy lesz az indexfa, feleslegesen, hisz mint állítottam, a lekérdezéseink tipikusan NEM érintik a régi adatokat, azaz az indexfa csak kis részét használjuk ki. Ráadásul sokszor úgy hozunk létre NC indexet, hogy jó sok oszlopot pakolunk bele, ezzel lefedve bizonyos lekérdezéseket, amelyeknek nagyon gyorsnak kell lenni. Azaz az index minden oszlopot tartalmaz, amire a lekérdezés hivatkozik, bármely részében, ekkor csak az indexfából is végrehajtható a lekérdezés, nem kell az adatlapokhoz nyúlni a szervernek. Ezt hívják cover indexnek és querynek. A cover indexek sokszor piszok nagyok, hisz lehet, hogy tucatnyi oszlopot is tartalmaznak. 2005-ben megjelent az included columns az indexben, ezzel a nem kulcs adatokat, azaz amelyekre nem szűrünk az index támogatásával csak a lefedettség miatt kell csak levélszinten rakja be a szerver az indexbe, közbenső szinten nem. Ennek örültünk, egyfajta mini clustered indexet hozhattunk létre az oszlopok egy részhalmazából. Piszok gyors lekérdezéseket lehetett így összehozni, de marha nagy lett az indexfa.

Jöhetnek a filtered indexek.

Filtered index esetén az index létrehozáskor meg lehet adni egy where feltételt, így az indexfa csak az általunk fontosnak vélt adatokat tartalmazza. Az előbbi példában mondjuk csak az utolsó hónap adatait. Vagy csak egy adott megye adatait. Vagy a nem NULL adatokat. Így az index nem tud megtámogatni minden lekérdezést, hisz nincs benne minden adat, viszont kisebb és gyorsabb lehet, mint egy nem szűrt index. Ráadásul a statisztikák is az adatok részhalmazára vonatkozik, így pontosabbak, mint a teljes táblásak.
Az index karbantartása is kisebb költségű adatmódosítások esetén, hisz lehet, hogy az adott sor nincs is az indexben, illetve kisebb az indexfa is, így a statisztika frissítése is kisebb költségű.

Lássunk egy példát a BOL-ból. Kiindulási táblánk az AdventureWorks BillOfMaterials táblája. Nem nagy, 4e soros, ezért nem lesznek látványosak a gyorsulások, de legalább kéznél van. Ebben az EndDate oszlop NULL-os, vannak is benne NULL értékek:

select 
sum(case when EndDate is not null then 1 else 0 end) NotNullos,
sum(case when EndDate is null then 1 else 0 end) Nullos
from Production.BillOfMaterials

NotNullos   Nullos
----------- -----------
199         2480

Sok gyakorlati lekérdezést láttam már, amely úgy néz ki, hogy a where feltételben van valamilyen szűrési feltétel AND valamilyen oszlop IS NOT NULL. No, ezeket jól meg tudja támogatni egy filtered index, ami csak a nem nullos adatokat indexeli.

Nézzük ezt a lekérdezést a BOL-ból:

select ProductAssemblyID, ComponentID, StartDate 
from Production.BillOfMaterials
where EndDate is not null;

Index nélkül. Cost: 20ms, IO: 22. Nem sok, dehát kicsi a tábla.

A BOL ezt a filtered indexet rakja rá:

create nonclustered index idx_FIBillOfMaterialsWithEndDate
on Production.BillOfMaterials (ComponentID, StartDate)
where EndDate IS NOT NULL;

Ez csak a not nullos EndDate értékű sorokat tartalmazza, azaz csak 199 sorból áll. Picike. De nem az EndDate szerint van rendezve, hanem ComponentID, StartDate szerint. Mit jelent ez az előbbi lekérdezésre nézve?
Felhasználja az indexet a szerver, Index Scan lesz (nem seek), Cost: 3.5ms, IO: 2. Nézzük csak. Miért használja egyáltalán az indexet, hisz nincs is EndDate szerint rendezve?
Ez egy olyan lekérdezés, amit az index lefed, mert minden oszlopa benne van az indexben. ProductAssemblyID, ComponentID, StartDate, EndDate, ezekre hivatkozik a select. ComponentID, StartDate triviálisan benne van az indexben, annak a kulcsa. Az EndDate nincs benne, de a szűrési feltétel pont azonos az indexével, így tudjuk, hogy az index pont azokat a sorokat tartalmazza, amelyek a feltételt kielégítik. És mi a helyzet a ProductAssemblyID-vel? Nos, mint tudjuk (tudjuk?) minden NC indexben benne vannak a CLU index oszlopai is, hisz az NC index levélszinten ilyenkor nem Row Locatorokat tartalmaz, hanem CLU kulcsokat, azaz az NC index használata után még a CLU indexen is végigmegy a szerver. Azaz az előbbi indexünk levélszinten a következőket tartalmazza: ComponentID, StartDate, ProductAssemblyID, ComponentID, StartDate, mert az utóbbi 3 alkotja a Clu indexet. Hogy mi, két oszlop is kétszer van az indexben? Igen, ezért nem szerencsés a példa. Csak zabálná a helyet. Szerencsére a szerver okosabb ennél (legalábbis a 2008, a DBCC PAGE-dzsel való vizsgálódásaim alapján), és csak egyszer tárolja le a többször szereplő oszlopokat. (Miután megírtam ezt a bejegyzést találtam egy linket, ami pont ezt a témát taglalja.)
No, a lekérdezést tehát lefedi az index, ezért nem kell az adatsorokhoz nyúlni, csak az indexből teljesíthető a lekérdezés. Azért Scan és nem Seek a művelet, mert a teljes index tartalmát bejárják, hisz nincs szűrés az index kulcs adataira. Így is hatékony a select, mert kicsi az indexfa. Hogy igazán elemében legyen az index, szűrjünk már egy kicsit a kulcsára is:

select ProductAssemblyID, ComponentID, StartDate 
from Production.BillOfMaterials
where ComponentID = 324 and EndDate is not null;

Ekkor használja az indexet, seekel. Cover query seek-kel, ez a leggyorsabb index művelet ami csak létezik a szerverben.

Nem akarom túlragozni a kérdést tovább, aki tudja, hogyan kell cover query-t készíteni NC index-szel, az most készíthet sokkal kisebb index fát is, filtered index segítségével, ha tudja, hogy a nagyon felgyorsítandó lekérdezés(ek) tudják használni a szűrt, kicsi indexfát.
Nem cover helyzetben is jó lehet ez, csak ekkor még elő kell szedni a sorokat az adatlapokról, amivel nő ugyan a költség, de még mindig gyors lehet.

Aki ismeri az SQL Server 2000-ben bevezetett indexelt nézeteket, az most azt mondja, mi ebben az új? Indexelt nézettel ugyanezt meg lehet csinálni. Nos, filtered indexben sokkal szűkebb a szűrési lehetőségek:

“The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.”

Emiatt gyorsabb lehet, mint az indexed view. Az indexelt view elképesztően fel tud gyorsítani pl. aggregáló műveleteket használó lekérdezéseket vagy előtárol join-okat, de finnyás a különböző set opciókra, emiatt viszont korlátozottabban lehet csak használni, ha nem stimmelnek lekérdezéskor, akkor nem használja az indexet, illetve az alaptáblák módosítása sem fog működni, hibát jelez. Emiatt jóval kevesebb helyen tudták bevetni az indexelt nézeteket, mint ahol be fogják a filtered indexet. Pont a nagyon behatárolt szűrések miatt nincsenek a set opciós megkötések mint a view-nál (a nullos kitételek is ezért vannak), ezért kényelmesebben lehet használni, noha butább, mint az indexelt nézet.

Pl. ha rendszeresen egyes kategóriákra kell szűrni egy nagy táblából, akkor azt jól meg lehet majd gyorsítani egy adott kategóriákat tároló filtered index-szel.

Szeressük a filtered indexet, mert az jó.

Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.