{"id":1230,"date":"2012-05-07T11:01:08","date_gmt":"2012-05-07T10:01:08","guid":{"rendered":"http:\/\/soci.hu\/blog\/?p=1230"},"modified":"2012-05-07T11:03:49","modified_gmt":"2012-05-07T10:03:49","slug":"sql-server-2012-ujdonsagok-3-forceseek-forcescan-hintek","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2012\/05\/07\/sql-server-2012-ujdonsagok-3-forceseek-forcescan-hintek\/","title":{"rendered":"SQL Server 2012 \u00fajdons\u00e1gok – 4. FORCESEEK, FORCESCAN hintek"},"content":{"rendered":"

A FORCESEEK hint SQL Server 2008 \u00f3ta l\u00e9tezik. Ezzel azt lehet s\u00fagni a query optimizernek, hogy egy lek\u00e9rdez\u00e9s ki\u00e9rt\u00e9kel\u00e9se sor\u00e1n ink\u00e1bb haszn\u00e1ljon nonclustered index seeket, table vagy clustered index scan helyett. Ez ekkor hasznos, ha egy param\u00e9terezett lek\u00e9rdez\u00e9s a tipikus param\u00e9terekre kev\u00e9s sort ad vissza, \u00edgy a nonclustered index seek az optim\u00e1lis adatel\u00e9r\u0151 strat\u00e9gia, de id\u0151nk\u00e9nt becsusszannak olyan param\u00e9terek is, amelyek scant ig\u00e9nyelnek, mert m\u00e1r nem \u00e9ri meg a sok indirekt adatel\u00e9r\u00e9s, ami a nonclustered index seek velej\u00e1r\u00f3ja. Ilyenkor a szerver helyesen scant v\u00e1laszt, azaz ink\u00e1bb line\u00e1ris keres\u00e9ssel v\u00e9gigmegy az eg\u00e9sz t\u00e1bl\u00e1n. Mivel a szerver elt\u00e1rolja \u00e9s \u00fajrahasznos\u00edtja a v\u00e9grehajt\u00e1si terveket, ha pont els\u0151re egy ilyen terv gener\u00e1l\u00f3dott, akkor a tov\u00e1bbi lek\u00e9rdez\u00e9seket is ezzel hajtja v\u00e9gre. Ez azonban nem optim\u00e1lis sebess\u00e9get ad a tipikus, p\u00e1r sort visszahoz\u00f3 lek\u00e9rdez\u00e9sekre. Ezt a bizonytalans\u00e1got tudja stabiliz\u00e1lni a FORCESEEK hint. A hint hat\u00e1s\u00e1ra sz\u00edvesebben haszn\u00e1lja a nonclustered index seeket az SQL Server. Ez rossz hat\u00e1ssal lesz az atipikus, ritk\u00e1n bees\u0151, de sok sort visszaad\u00f3 lek\u00e9rdez\u00e9sekre, de a tipikus, kev\u00e9s sort visszaad\u00f3kra stabilabb tervet \u00e9s v\u00e1laszid\u0151ket kapunk.
\nSQL Server 2012-ben a FORCESEEK \u00fajdons\u00e1ga, hogy meg lehet adni egy \u00f6sszetett index oszlopait vagy azok egy r\u00e9szhalmaz\u00e1t, hogy pontosabban specifik\u00e1ljuk, mely index oszlopokat szeretn\u00e9nk, ha haszn\u00e1ln\u00e1 az optimizer.
\nN\u00e9zz\u00fcnk egy p\u00e9ld\u00e1t. Van egy \u00faj akci\u00f3ja az internetes kutyat\u00e1pboltnak, a Bodri m\u00e1jkonzerv, amely 2-es SpecialOfferID-val fut, \u00e9s szeretn\u00e9nk m\u00e1sodpercenk\u00e9nt friss\u00edtve l\u00e1tni egy 50 colos plazma k\u00e9perny\u0151n azokat az megrendel\u00e9seket, amelyben 10-n\u00e9l t\u00f6bb dobozzal rendeltek. A lek\u00e9rdez\u00e9snek nagyon hat\u00e9konynak kell lenni, de nem akarunk \u00faj indexet l\u00e9trehozni a t\u00e1bl\u00e1kon.
\nAdott egy ilyen index a SalesOrderDetail t\u00e1bl\u00e1n:<\/p>\n

\r\nCREATE NONCLUSTERED INDEX IX_Comp1\r\nON Sales.SalesOrderDetail (OrderQty, SpecialOfferID, UnitPrice);\r\n<\/pre>\n
A lek\u00e9rdez\u00e9s \u00edgy n\u00e9z ki:<\/p>\n
\r\nselect * from [Sales].[SalesOrderDetail]\r\nwhere OrderQty = 10 and SpecialOfferID = 2;\r\n<\/pre>\n
Az SQL Server \u00e1ltal v\u00e1lasztott v\u00e9grehajt\u00e1si terv NEM haszn\u00e1lja a fenti indexet, Clustered Index Scant haszn\u00e1l:<\/p>\n
\"\"<\/a><\/p>\n
A lek\u00e9rdez\u00e9s becs\u00fclt k\u00f6lts\u00e9ge 1.18mp, \u00e9s 1240 logikai IO m\u0171velettel, 8 kByte-os lap olvas\u00e1s\u00e1val oldotta meg a szerver (v\u00e9gign\u00e9zte a teljes t\u00e1bl\u00e1t).
\nA FORCESEEK eddig is ismert alakj\u00e1val r\u00e1vehetj\u00fck az index\u00fcnk haszn\u00e1lat\u00e1ra:<\/p>\n
\r\nselect * from [Sales].[SalesOrderDetail]\r\nwith(forceseek)\r\nwhere OrderQty = 10 and SpecialOfferID = 2;\r\n<\/pre>\n
\"\"<\/a><\/p>\n
A Properties ablakban megn\u00e9zve azonban l\u00e1tszik, hogy az Index Seek m\u0171veletben csak az OrderQty oszlopra haszn\u00e1lta ki az indexet, pedig a lek\u00e9rdez\u00e9sben benne van egy m\u00e1sik sz\u0171rt oszlop is:<\/p>\n
\r\nSeek Keys[1]: Prefix: [AdventureWorks2012].[Sales].[SalesOrderDetail].OrderQty = Scalar Operator((10))\r\n<\/pre>\n
Ennek becs\u00fclt k\u00f6lts\u00e9ge 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\u0171rt az indexben, a sz\u0171r\u00e9s ut\u00e1n m\u00e9g el\u0151 kell venni az adatsorokat (Key Lookup, 768 sor), \u00e9s azokat tov\u00e1bb sz\u0171rni a SpecialOfferID = 2 felt\u00e9telre (Filter oper\u00e1tor).<\/p>\n
Az SQL Server 2012-ben kib\u0151v\u00edtett FORCESEEK-kel (am\u00fagy 2008R2 SP1-ben is benne van) meg lehet mondani a szervernek, hogy m\u00e1rpedig pr\u00f3b\u00e1lja meg haszn\u00e1lni az indexet mindk\u00e9t oszlop sz\u0171r\u00e9s\u00e9re:<\/p>\n
\r\nselect * from [Sales].[SalesOrderDetail]\r\nwith(forceseek (IX_Comp1 (OrderQty, SpecialOfferID)))\r\nwhere OrderQty = 10 and SpecialOfferID = 2;\r\n<\/pre>\n
\"\"<\/a><\/p>\n
A v\u00e9grehajt\u00e1si tervb\u0151l elt\u0171nt a Filter, ami j\u00f3 jel, az Index Seek sz\u0171r\u00e9se pedig mag\u00e1ban foglalja mindk\u00e9t oszlopot:<\/p>\n
\r\nSeek Keys[1]: Prefix: [AdventureWorks2012].[Sales].[SalesOrderDetail].OrderQty, [AdventureWorks2012].[Sales].[SalesOrderDetail].SpecialOfferID = Scalar Operator((10)), Scalar Operator((2))\r\n<\/pre>\n
Az \u00edgy kapott lek\u00e9rdez\u00e9s becs\u00fclt k\u00f6lts\u00e9ge tov\u00e1bbra is 1.91mp, azonban az IO leesett 4-re! Az eredeti 1240 helyett 4 lett az IO, azaz t\u00f6bb mint 300-ad r\u00e9sz\u00e9re cs\u00f6kkent!
\nA v\u00e9gs\u0151 verdiktet az mondja ki, hogy ha megm\u00e9rj\u00fck a t\u00e9nyleges v\u00e9grehajt\u00e1si id\u0151ket. 10000 v\u00e9grehajt\u00e1s alapj\u00e1n az els\u0151, hint n\u00e9lk\u00fcli lek\u00e9rdez\u00e9s ideje 238mp, az egyszer\u0171 hinttel 38mp, az \u00faj hinttel 2mp.
\n120-szoros gyorsul\u00e1st \u00e9rt\u00fcnk el az \u00faj hint form\u00e1tummal!<\/p>\n
A FORCESCAN teljesen \u00faj hint, ezzel scanre vehetj\u00fck r\u00e1 a szervert akkor is, ha seekelne mag\u00e1t\u00f3l. Ennek haszna akkor van, ha tudjuk, hogy a lek\u00e9rdez\u00e9s sok sor \u00e9rint, ez\u00e9rt a seek nem lenne optim\u00e1lis, \u00edgy mindenk\u00e9ppen scant akarunk. id\u0151nk\u00e9nt mi\u00e9rt v\u00e1laszt az SQL Server m\u00e9gis seeket? Vagy, mert nem frissek a statisztik\u00e1i, ez\u00e9rt kev\u00e9s sort v\u00e1r el, vagy, mert valami okn\u00e1l fogva nem tudja j\u00f3l megbecs\u00fclni a v\u00e1rhat\u00f3 sorok sz\u00e1m\u00e1t. F\u0151leg join-os lek\u00e9rdez\u00e9sekn\u00e9l neh\u00e9z a dolga, mert az oszlopszint\u0171 eloszl\u00e1si statisztik\u00e1it ilyenkor nem lehet pontosan haszn\u00e1lni.
\nAz ilyen eseteket k\u00f6nny\u0171 megismerni, mert ilyenkor Index Seek-et haszn\u00e1l\u00f3 Nested Loop Joinokat hajt v\u00e9gre a szerver sok ezer vagy milli\u00f3 soron, Hash Join helyett. Ezekben az esetekben a FORCESCAN hinttel r\u00e1 lehet venni, hogy ink\u00e1bb ne haszn\u00e1ljon indexet, ne szaladjon neki sokszor a t\u00e1bl\u00e1nak, hanem ink\u00e1bb egyszer menjen v\u00e9gig az eg\u00e9sz t\u00e1bl\u00e1n, mert a m\u00e9g mindig kisebb k\u00f6lts\u00e9g\u0171.
\nNem csak joinos lek\u00e9rdez\u00e9sekn\u00e9l, hanem sima sz\u0171r\u00e9sekn\u00e9l is el\u0151fordulhat, hogy rosszul becs\u00fcli meg a szerver a feldolgozand\u00f3 sorok sz\u00e1m\u00e1t, \u00edgy seekel scan helyett, akkor stabiliz\u00e1lhatjuk a tervet, hogy mindig scant haszn\u00e1ljon a FORCESCAN hinttel.
\n\u00d6sszegezve, ha tudjuk, hogy a seek vagy a scan el\u0151ny\u00f6s a lek\u00e9rdez\u00e9s\u00fcnknek, akkor hat\u00e1rozott\u00e1 tehetj\u00fck a v\u00e1rakoz\u00e1sunkat a szerver fel\u00e9, ez\u00e1ltal kisz\u00e1m\u00edthat\u00f3bb tervet, \u00edgy kisz\u00e1m\u00edthat\u00f3bb v\u00e1laszid\u0151ket kapunk.<\/p>\n
Update  a cikkhez: amikor a fenti dem\u00f3t \u00edrtam, akkor m\u00e9g csak RC1 volt. Miut\u00e1n upgradeltem az RTM-re, hirtelen mag\u00e1t\u00f3l is tudta a j\u00f3 tervet, nem kellett hint. Vagy okosodott, vagy csak nem voltak frissek a statisztik\u00e1k, amikor a dem\u00f3t \u00edrtam. Az elv mindenesetre l\u00e1tszik a cikkb\u0151l, \u00e9s \u00f6r\u00fcl\u00fcnk, ha a szerver okos. :)<\/p>\n","protected":false},"excerpt":{"rendered":"
A FORCESEEK hint SQL Server 2008 \u00f3ta l\u00e9tezik. Ezzel azt lehet s\u00fagni a query optimizernek, hogy egy lek\u00e9rdez\u00e9s ki\u00e9rt\u00e9kel\u00e9se sor\u00e1n ink\u00e1bb haszn\u00e1ljon…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,41,37,56,6,4,30,58,77,78,23],"tags":[],"class_list":["post-1230","post","type-post","status-publish","format-standard","hentry","category-ad","category-atl","category-blog","category-csillagaszat","category-adatbazisok","category-szakmai-elet","category-sql-server","category-sql-server-2008","category-sql-server-2008-r2","category-sql-server-2012","category-vista"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1230","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=1230"}],"version-history":[{"count":9,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1230\/revisions"}],"predecessor-version":[{"id":1241,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1230\/revisions\/1241"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=1230"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=1230"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=1230"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}