A FORCESEEK hint SQL Server 2008 óta létezik. Ezzel azt lehet súgni a query optimizernek, hogy egy lekérdezés kiértékelése során inkább használjon nonclustered index seeket, table vagy clustered index scan helyett. Ez ekkor hasznos, ha egy paraméterezett lekérdezés a tipikus paraméterekre kevés sort ad vissza, így a nonclustered index seek az optimális adatelérő stratégia, de időnként becsusszannak olyan paraméterek is, amelyek scant igényelnek, mert már nem éri meg a sok indirekt adatelérés, ami a nonclustered index seek velejárója. Ilyenkor a szerver helyesen scant választ, azaz inkább lineáris kereséssel végigmegy az egész táblán. Mivel a szerver eltárolja és újrahasznosítja a végrehajtási terveket, ha pont elsőre egy ilyen terv generálódott, akkor a további lekérdezéseket is ezzel hajtja végre. Ez azonban nem optimális sebességet ad a tipikus, pár sort visszahozó lekérdezésekre. Ezt a bizonytalanságot tudja stabilizálni a FORCESEEK hint. A hint hatására szívesebben használja a nonclustered index seeket az SQL Server. Ez rossz hatással lesz az atipikus, ritkán beeső, de sok sort visszaadó lekérdezésekre, de a tipikus, kevés sort visszaadókra stabilabb tervet és válaszidőket kapunk.
SQL Server 2012-ben a FORCESEEK újdonsága, hogy meg lehet adni egy összetett index oszlopait vagy azok egy részhalmazát, hogy pontosabban specifikáljuk, mely index oszlopokat szeretnénk, ha használná az optimizer.
Nézzünk egy példát. Van egy új akciója az internetes kutyatápboltnak, a Bodri májkonzerv, amely 2-es SpecialOfferID-val fut, és szeretnénk másodpercenként frissítve látni egy 50 colos plazma képernyőn azokat az megrendeléseket, amelyben 10-nél több dobozzal rendeltek. A lekérdezésnek nagyon hatékonynak kell lenni, de nem akarunk új indexet létrehozni a táblákon.
Adott egy ilyen index a SalesOrderDetail táblán:
CREATE NONCLUSTERED INDEX IX_Comp1
ON Sales.SalesOrderDetail (OrderQty, SpecialOfferID, UnitPrice);
A lekérdezés így néz ki:
select * from [Sales].[SalesOrderDetail]
where OrderQty = 10 and SpecialOfferID = 2;
Az SQL Server által választott végrehajtási terv NEM használja a fenti indexet, Clustered Index Scant használ:
A lekérdezés becsült költsége 1.18mp, és 1240 logikai IO művelettel, 8 kByte-os lap olvasásával oldotta meg a szerver (végignézte a teljes táblát).
A FORCESEEK eddig is ismert alakjával rávehetjük az indexünk használatára:
select * from [Sales].[SalesOrderDetail]
with(forceseek)
where OrderQty = 10 and SpecialOfferID = 2;
A Properties ablakban megnézve azonban látszik, hogy az Index Seek műveletben csak az OrderQty oszlopra használta ki az indexet, pedig a lekérdezésben benne van egy másik szűrt oszlop is:
Seek Keys[1]: Prefix: [AdventureWorks2012].[Sales].[SalesOrderDetail].OrderQty = Scalar Operator((10))
Ennek becsült költsége 1.91mp, azaz a FORCESEEK hinttel csak rontottunk a dolgon, az IO is felment 2365-re. Ennek oka, hogy mivel csak az OrderQty-re szűrt az indexben, a szűrés után még elő kell venni az adatsorokat (Key Lookup, 768 sor), és azokat tovább szűrni a SpecialOfferID = 2 feltételre (Filter operátor).
Az SQL Server 2012-ben kibővített FORCESEEK-kel (amúgy 2008R2 SP1-ben is benne van) meg lehet mondani a szervernek, hogy márpedig próbálja meg használni az indexet mindkét oszlop szűrésére:
select * from [Sales].[SalesOrderDetail]
with(forceseek (IX_Comp1 (OrderQty, SpecialOfferID)))
where OrderQty = 10 and SpecialOfferID = 2;
A végrehajtási tervből eltűnt a Filter, ami jó jel, az Index Seek szűrése pedig magában foglalja mindkét oszlopot:
Seek Keys[1]: Prefix: [AdventureWorks2012].[Sales].[SalesOrderDetail].OrderQty, [AdventureWorks2012].[Sales].[SalesOrderDetail].SpecialOfferID = Scalar Operator((10)), Scalar Operator((2))
Az így kapott lekérdezés becsült költsége továbbra is 1.91mp, azonban az IO leesett 4-re! Az eredeti 1240 helyett 4 lett az IO, azaz több mint 300-ad részére csökkent!
A végső verdiktet az mondja ki, hogy ha megmérjük a tényleges végrehajtási időket. 10000 végrehajtás alapján az első, hint nélküli lekérdezés ideje 238mp, az egyszerű hinttel 38mp, az új hinttel 2mp.
120-szoros gyorsulást értünk el az új hint formátummal!
A FORCESCAN teljesen új hint, ezzel scanre vehetjük rá a szervert akkor is, ha seekelne magától. Ennek haszna akkor van, ha tudjuk, hogy a lekérdezés sok sor érint, ezért a seek nem lenne optimális, így mindenképpen scant akarunk. időnként miért választ az SQL Server mégis seeket? Vagy, mert nem frissek a statisztikái, ezért kevés sort vár el, vagy, mert valami oknál fogva nem tudja jól megbecsülni a várható sorok számát. Főleg join-os lekérdezéseknél nehéz a dolga, mert az oszlopszintű eloszlási statisztikáit ilyenkor nem lehet pontosan használni.
Az ilyen eseteket könnyű megismerni, mert ilyenkor Index Seek-et használó Nested Loop Joinokat hajt végre a szerver sok ezer vagy millió soron, Hash Join helyett. Ezekben az esetekben a FORCESCAN hinttel rá lehet venni, hogy inkább ne használjon indexet, ne szaladjon neki sokszor a táblának, hanem inkább egyszer menjen végig az egész táblán, mert a még mindig kisebb költségű.
Nem csak joinos lekérdezéseknél, hanem sima szűréseknél is előfordulhat, hogy rosszul becsüli meg a szerver a feldolgozandó sorok számát, így seekel scan helyett, akkor stabilizálhatjuk a tervet, hogy mindig scant használjon a FORCESCAN hinttel.
Összegezve, ha tudjuk, hogy a seek vagy a scan előnyös a lekérdezésünknek, akkor határozottá tehetjük a várakozásunkat a szerver felé, ezáltal kiszámíthatóbb tervet, így kiszámíthatóbb válaszidőket kapunk.
Update a cikkhez: amikor a fenti demót írtam, akkor még csak RC1 volt. Miután upgradeltem az RTM-re, hirtelen magától is tudta a jó tervet, nem kellett hint. Vagy okosodott, vagy csak nem voltak frissek a statisztikák, amikor a demót írtam. Az elv mindenesetre látszik a cikkből, és örülünk, ha a szerver okos. :)
