Mint minden optimizer hint előtt figyelmeztetés: ne használd, csak ha érted mit csinál, és biztosan tudod, hogy az adott helyzetben segít, és hogy az adatok eloszlása nem változik meg jelentősen az idők folyamán, ami miatt már ront, és nem javít a hint.
A szerver lekérdezés optimalizálója általában ügyesen kiválasztja a szükséges indexet egy szűrés hatékony végrehajtásához, azonban vannak esetek, amikor
1. nem elég bevállalós
2. felhasználja a cache-elt tervet, de ezzel rosszul járunk.
Ezekben az esetekben hasznos lehet a forceseek. Némi háttér a megértéshez.
A nonclustered (NC) index egy B* fa, amelyben csak az indexhez megadott oszlop(ok) adata szerepel rendezetten, a tényleges adatok az indexfától elkülönült adatterületen helyezkednek el. Emiatt, ha mondjuk egy where feltételhez szeretné használni a szerver az indexet, először végigszalad az NC index fán, ami nagyon gyors művelet, hisz rendezett a fa. A levélszinten talált egy vagy több sort pedig úgy szedi fel, hogy átnyúl az indexben található pointer mentén az adatlapokhoz, és kiolvassa az ott található sorokat. Ezt Bookmark Lookup operátorként láthattuk az SQL 2000-ben, 2005-ben JOIN-ként jelenik meg, most 2008-ban pedig Key Lookupként látom (ez a legtalálóbb eddig).
Sok sor esetén ez jó nagy ugrabugrával jár, hisz pl. 100 sor esetén extrém esetben 100 lapon vannak szétszórva az adatok, így 100 lapot be kell tölteni a memóriába, 1-1 nyomorult sor miatt. Ezért a szerver nem lelkesedik alapban az NC indexért, igen konzervatívan él csak vele. Sokszor inkább végigolvassa a teljes táblát, read ahead módon, néhány nagy harapással, semmint hogy apró 8k-s laponként zötyögtesse a vinyók fejét. A visszafogottsága sokszor megalapozott, de néha nem.
Az elején felvetett 1. esetben például lehet, hogy egy 10e soros táblából 200 sort leválogatva már nem index seek-el (azaz az előbb leírt módon használja az indexet), hanem table scan-t csinál. Ennek lehet oka egy rossz statisztika is persze, ami miatt azt hiszi, hogy sokkal több sor jön vissza, ezért mindig KELL frissíteni a statisztikákat, mielőtt hintelnénk (sp_updatestats és UPDATE STATISTICS). Viszont lehet, hogy csak egyszerűen nem merte bevállalni az index seeket, helyette scan-elt.
A 2. eset nem egyenletes eloszlású adatok vagy nagyon változó paraméterű szűrések esetén okozhat problémát. Tegyük fel, hogy egy táblában megrendelések vannak és ezek dátumára nagyon gyakran szűrnek, de mindig úgy, hogy csak egy adott nap megrendeléseire kíváncsiak. Mivel ez kis részét hozza vissza a táblának index seeket használ a szerver, ha van a megrendelés dátuma oszlopon egy akármilyen index. Cél, hogy ezek a napi lekérdezések nagyon gyorsak legyenek, a nagyobb tartományra lekérdezés sebessége első körben nem fontos (értsd, nem baj, ha rosszabb lesz a bűvészkedésünk hatására, mint volt).
Ha azonban egy kolléga szerver újraindulás vagy cache ürülés után valamiért mondjuk egy évet válogat le, akkor teljes táblaolvasást fog használni a szerver. Ez rendben is van, jó döntés, de a végrehajtási terv letárolódhat a cache-ben, így ezek után lehet, hogy az 1 napi szűrések is scan-elni fognak, azaz piszok lassúk lesznek. Erre persze sokféle megoldás adható, a forcessek csak az egyik ezek közül.
Lássuk az alapproblémát:
create nonclustered index idx_orderdate on Purchasing.PurchaseOrderHeader(OrderDate) go create proc GetOrdersByOrderDate @start datetime, @end datetime as SELECT * FROM Purchasing.PurchaseOrderHeader where OrderDate >= @start and OrderDate < @end go [/source] [source='sql'] exec GetOrdersByOrderDate '20030317', '20040318' [/source] Ez egy évi adat, a tábla majdnem fele lejön, persze, hogy table scan lesz a vége, 44 IO művelettel, ennyi lapból áll a tábla: <a href='http://soci.hu/blog/wp-content/uploads/2008/03/scan1.png' title='Index scan sok adat miatt'><img src='http://soci.hu/blog/wp-content/uploads/2008/03/scan1.png' alt='Index scan sok adat miatt' /></a> A baj az, hogy a következő lekérdezés is scan-el, nem seekel, mert felhasználja az előbbi tervet, pedig csak 8 sort hoz vissza: [source='sql'] exec GetOrdersByOrderDate '20030317', '20030318'
Mit lehet tenni? A legegyszerűbb, ha kikényszerítjük az újrafordítást minden egyes futtatáskor vagy az sp létrehozásakor (régi fícsör):
exec GetOrdersByOrderDate '20030317', '20030318' with recompile
Viszont sokszor pont azért hoztunk létre sp-t, hogy ne legyen recompile. SQL Server 2005-től használható az optimize for hint:
alter proc GetOrdersByOrderDate
@start datetime,
@end datetime
as
SELECT * FROM Purchasing.PurchaseOrderHeader
where OrderDate >= @start and OrderDate < @end
option(optimize for (@start = '20030317', @end = '20030318'))
[/source]
Azt mondtuk a szervernek, tök mindegy milyen paraméterrel hívják meg az sp-t, a két paraméter értékét tekintsd annak, amit odaírtam, és ennek megfelelően optimalizálj. Direkt egy napnyi, pár sornyi adatot adtam meg, ami miatt index seek-et használ a szerver, ami stabilan gyorsan tartja a napi lekérdezéseket, de nagyon lelassítja a nagy tartományt, mert arra is seek lesz, ami miatt az előbbi 44 helyett 3458 IO műveletet kapunk.
Az előbbi megoldás tehát akkor jó, ha nem nagyon vannak nagy tartománybeli lekérdezések. Ha abból is sok van, akkor vagy recompile kell mindig, vagy kell két sp, más paraméterekre optimalizálva, és a kliens okosan a megfelelőt hívja meg.
Másképp is ki lehet kényszeríteni a seeket:
[source='sql']
alter proc GetOrdersByOrderDate
@start datetime,
@end datetime
as
SELECT * FROM Purchasing.PurchaseOrderHeader with(index(idx_orderdate))
where OrderDate >= @start and OrderDate < @end
[/source]
Azaz rákényszerítjük, hogy használja az NC indexünket. Az optimize for megoldás hátránya, hogy ha az adatok változnak a táblában, lehet, hogy érvénytelen lesz a mintaként megadott érték, így rosszul dönt a szerver az adatelérés módjáról. A with index hint pedig belevarrja az index nevét a lekérdezésbe, így ha az megváltozik vagy az index törlése kerül, a lekérdezés hibázni fog.
No, lássuk most már, mire is jó a FORCESEEK:
[source='sql']
alter proc GetOrdersByOrderDate
@start datetime,
@end datetime
as
SELECT * FROM Purchasing.PurchaseOrderHeader with(forceseek)
where OrderDate >= @start and OrderDate < @end
[/source]
Ez ugyanúgy ráveszi a szervert a korábbi indexünk használatára, seek-elve, de nem mondja meg, hogy milyen indexet használjon. Történetesen adtunk neki olyat, amit használhat, él is vele. Azaz ezzel egyfajta noszogatást adtunk neki, hogy nézz szét jól, van ott olyan index, ami jó neked seek-elve is.
Ha gonosz módon ledobjuk az indexet, nagyot néz a szerver, és ezt mondja:
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
No, igen, ennek is vannak kötöttségei, ha egyszer nincs megfelelő index, akkor mi a francot erőltetjük, nem?
Összegezve, az FORCESEEK egy újabb eszköz az optimalizálási repertoárunkban, semmiképpen se csodafegyver, illetve látható, hogy a hatását másképp is el lehet érni. Egy újra trükk, amit bevetünk, HA INDOKOLT. De csak akkor.
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.