Indexelt nézetek és statisztikák

Az indexelt nézetek az SQL Server bármely kiadásában létrehozhatók, de számos viselkedéssel tisztában kell lennie, ha a legtöbbet szeretné kihozni belőlük.

Az automatikus statisztikákhoz NOEXPAND súgó szükséges

A SQL Server képes automatikusan statisztikákat létrehozni, hogy segítse a lekérdezés optimalizálása során a kardinalitás becslését és a költségalapú döntéshozatalt. Ez a funkció indexelt nézetekkel és bázistáblákkal is működik, de csak akkor, ha a nézet explicit módon meg van nevezve a lekérdezésben, és a NOEXPAND hint meg van adva. (Egy nézet minden indexéhez mindig tartozik egy statisztikaobjektum, itt az indexhez nem kapcsolódó statisztikák automatikus generálásáról és karbantartásáról van szó.)

Ha Ön az SQL Server nem Enterprise kiadásaival szokott dolgozni, lehet, hogy ezt a viselkedést még soha nem vette észre. Az SQL Server alacsonyabb kiadásai az NOEXPAND súgót igénylik az indexelt nézethez hozzáférő lekérdezési terv előállításához. A NOEXPAND megadásakor az indexelt nézetekre automatikus statisztikák készülnek, pontosan úgy, ahogyan az a közönséges táblák esetében történik.

Példa – Standard Edition NOEXPAND

Az SQL Server 2012 Standard Edition és az Adventure Works mintaadatbázis felhasználásával először létrehozunk egy nézetet, amely összekapcsol két értékesítési táblát, és kiszámítja a teljes rendelési mennyiséget vásárlónként és termékenként:

CREATE VIEW dbo.CustomerOrdersWITH SCHEMABINDING ASSELECT SOH.CustomerID, SOD.ProductID, OrderQty = SUM(SOD.OrderQty), NumRows = COUNT_BIG(*)FROM Sales.SalesOrderDetail AS SODJOIN Sales.SalesOrderHeader AS SOH ON SOH.SalesOrderID = SOD.SalesOrderIDGROUP BY SOH.CustomerID, SOD.ProductID;

Hogy ez a nézet támogassa a statisztikákat, egy egyedi fürtözött index hozzáadásával kell materializálnunk. Az Ügyfél és a Termék azonosító kombinációja garantáltan egyedi lesz a nézetben (definíció szerint), így ezt fogjuk használni kulcsként. A két oszlopot bármelyik irányban megadhatnánk az indexben, de feltételezve, hogy több lekérdezést várunk a termék szerinti szűrésre, a termékazonosítót tesszük meg vezető oszlopnak. Ez a művelet indexstatisztikát is készít, a termékazonosító értékeiből felépített hisztogrammal.

CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.CustomerOrders (ProductID, CustomerID);

Most egy olyan lekérdezést kell írnunk, amely egy adott termékkörre vonatkozóan mutatja a megrendelések teljes mennyiségét ügyfelenként. Arra számítunk, hogy az indexelt nézetet használó végrehajtási terv hatékony stratégia lesz, mivel elkerüli a join-t, és olyan adatokkal operál, amelyek már részben aggregáltak. Mivel az SQL Server Standard Editiont használjuk, a nézetet explicit módon kell megadnunk, és egy NOEXPAND súgót kell használnunk az indexelt nézethez hozzáférő lekérdezési terv előállításához:

SELECT CO.CustomerID, SUM(CO.OrderQty)FROM dbo.CustomerOrders AS CO WITH (NOEXPAND)WHERE CO.ProductID BETWEEN 711 AND 718GROUP BY CO.CustomerID;

A létrehozott végrehajtási terv az indexelt nézeten való keresést mutatja az érdeklődő termékek sorainak kereséséhez, majd az összesített mennyiség kiszámításához az ügyfelenkénti teljes mennyiség kiszámításához szükséges aggregációt:

Az SQL Sentry Plan Explorer Plan Tree nézete azt mutatja, hogy a kardinalitásbecslés pontosan helyes az indexelt nézet keresésére, és nagyon jó az összesítés eredményére:

A lekérdezés összeállításának és optimalizálásának részeként az SQL Server létrehozott egy további statisztikai objektumot az indexelt nézet Customer ID oszlopán. Ez a statisztika azért készült, mert az Ügyfél-azonosítók várható száma és eloszlása fontos lehet, például az aggregációs stratégia kiválasztásakor. Az új statisztikát a Management Studio Object Explorer segítségével láthatjuk:

A statisztikaobjektumra való dupla kattintás megerősíti, hogy a nézet (és nem egy bázistábla) Customer ID oszlopából épült fel:

Az indexelt nézetek javíthatják a kardinalitásbecslést

A Standard Editiont továbbra is használva most eldobjuk és újra létrehozzuk az indexelt nézetet (ami a nézeti statisztikákat is eldobja), és újra végrehajtjuk a lekérdezést, ezúttal a NOEXPAND súgót kikommentálva:

SELECT CO.CustomerID, SUM(CO.OrderQty)FROM dbo.CustomerOrders AS CO --WITH (NOEXPAND)WHERE CO.ProductID BETWEEN 711 AND 718GROUP BY CO.CustomerID;

Amint az várható volt a Standard Edition NOEXPAND nélküli használatakor, a kapott lekérdezési terv nem közvetlenül a nézeten, hanem az alaptáblákon dolgozik:

A fenti tervben a gyökéroperátoron lévő figyelmeztető háromszög a Sales Order Detail táblán található potenciálisan hasznos indexre figyelmeztet, amely a jelenlegi céljaink szempontjából nem fontos. Ez az összeállítás nem készít statisztikát az indexelt nézetről. A lekérdezés összeállítása után az egyetlen statisztika a nézeten a fürtözött indexhez kapcsolódó statisztika:

A lekérdezés Plan Tree nézete azt mutatja, hogy a kardinalitásbecslés helyes a két táblaszkennelés és a join esetében, de meglehetősen rosszabb a többi tervoperátor esetében:

Az indexelt nézet NOEXPAND súgóval történő használata pontosabb becsléseket eredményezett a tesztlekérdésünk esetében, mivel a nézet statisztikáiból – különösen a nézetindexhez kapcsolódó statisztikákból – jobb minőségű információk álltak rendelkezésre.

Általánosságban elmondható, hogy a statisztikai információk pontossága meglehetősen gyorsan romlik, ahogy a lekérdezési terv operátorai áthaladnak rajtuk és módosítják őket. Az egyszerű egyesítések gyakran nem túl rosszak ebből a szempontból, de az aggregálás eredményéről szóló információ gyakran nem jobb, mint egy megalapozott találgatás. A lekérdezésoptimalizáló pontosabb információkkal való ellátása az indexelt nézetek statisztikáival hasznos technika lehet a terv minőségének és robusztusságának növelésére.

A NOEXPAND nélküli nézet rosszabb tervet eredményezhet

A fent látható lekérdezési terv (Standard Edition, NOEXPAND nélkül) valójában kevésbé optimális, mintha mi magunk írtuk volna meg a lekérdezést az alaptáblákhoz, ahelyett, hogy a lekérdezésoptimalizálónak engedtük volna a nézet bővítését. Az alábbi lekérdezés ugyanazt a logikai követelményt fejezi ki, de nem hivatkozik a nézetre:

SELECT SOH.CustomerID, SUM(OrderQty)FROM Sales.SalesOrderHeader AS SOHJOIN Sales.SalesOrderDetail AS SOD ON SOD.SalesOrderID = SOH.SalesOrderIDWHERE SOD.ProductID BETWEEN 711 AND 718GROUP BY SOH.CustomerID;

Ez a lekérdezés a következő végrehajtási tervet eredményezi:

Ez a terv eggyel kevesebb összesítő műveletet tartalmaz, mint korábban. A nézetbővítés használatakor a lekérdezésoptimalizáló sajnos nem tudott eltávolítani egy felesleges aggregációs műveletet, ami kevésbé hatékony végrehajtási tervet eredményezett. Az új lekérdezés végső kardinalitásbecslése is valamivel jobb, mint amikor az indexelt nézetre NOEXPAND nélkül hivatkoztunk:

A legjobb becslések azonban még mindig azok, amelyeket az indexelt nézet NOEXPAND-vel történő hivatkozásakor kaptunk (az egyszerűség kedvéért alább megismételjük):

Enterprise Edition és a nézetillesztés

Egy Enterprise Edition példányon a lekérdezésoptimalizáló akkor is képes lehet egy indexelt nézetet használni, ha a lekérdezés nem említi kifejezetten a nézetet. Ha a lekérdezésoptimalizáló képes a lekérdezési fa egy részét egy indexelt nézethez illeszteni, akkor a nézet használatának vagy nem használatának költségeire vonatkozó becslése alapján dönthet úgy, hogy ezt teszi. A nézet-illesztési logika meglehetősen okos, de vannak korlátai, amelyeket a gyakorlatban elég könnyű elérni. Még ott is, ahol a nézetillesztés sikeres, az optimalizálót félrevezethetik a pontatlan költségbecslések.

Az EXPAND VIEWS lekérdezési súgó

A lehetőségek közül a ritkábbal kezdve, előfordulhat, hogy egy lekérdezés hivatkozik egy indexelt nézetre, de jobb tervet kapnánk, ha helyette az alaptáblákhoz férnénk hozzá. Ilyen esetekben a EXPAND VIEWS lekérdezési súgó használható:

SELECT CO.CustomerID, SUM(CO.OrderQty)FROM dbo.CustomerOrders AS COWHERE CO.ProductID BETWEEN 711 AND 718GROUP BY CO.CustomerIDOPTION (EXPAND VIEWS);

Az Enterprise Edition kiadáson ez a lekérdezés ugyanazt a tervet eredményezi, mint amit a Standard kiadáson látunk, amikor a NOEXPAND súgó elhagyásra került (beleértve a felesleges összesítő műveletet):

Mellékesen megjegyzem, hogy a EXPAND VIEWS hint véleményem szerint rosszul van elnevezve. Az SQL Server mindig kibontja a nézetdefiníciókat egy lekérdezésben, hacsak nincs megadva a NOEXPAND hint. A EXPAND VIEWS hint kikapcsolja az optimalizálóban azokat a szabályokat, amelyek a kiterjesztett fa egyes részeit visszavezethetik az indexelt nézetekhez. Egyik súgó hiányában az SQL Server először kibővíti a nézetet az alaptábla-definícióra, majd később fontolóra veszi az indexelt nézetekre való visszaillesztést. A EXPAND VIEWS súgó jobb neve lehetett volna a DISABLE INDEXED VIEW MATCHING, mert ezt teszi.

A EXPAND VIEWS súgót valószínűleg leggyakrabban arra használják, hogy megakadályozzák, hogy az alaptáblák elleni lekérdezést indexelt nézetre illesszék:

SELECT SOH.CustomerID, SUM(OrderQty)FROM Sales.SalesOrderHeader AS SOHJOIN Sales.SalesOrderDetail AS SOD ON SOD.SalesOrderID = SOH.SalesOrderIDWHERE SOD.ProductID BETWEEN 711 AND 718GROUP BY SOH.CustomerIDOPTION (EXPAND VIEWS);

A lekérdezési súgó ugyanolyan végrehajtási tervet és becsléseket eredményez, mint amit a Standard Edition és ugyanannak a csak alaptáblákra vonatkozó lekérdezésnek a használatakor láttunk:

Enterprise View Matching and Statistics

Az Enterprise Editionben is csak a NOEXPAND súgó használata esetén jön létre nem indexelt nézeti statisztika. Hogy teljesen egyértelmű legyen, a kizárólag Enterprise nézet-illesztés funkció soha nem eredményez nézetstatisztikák létrehozását vagy frissítését. Ezt a nem intuitív viselkedést érdemes egy kicsit megvizsgálni, mivel meglepő mellékhatásai lehetnek.

Futtassuk most az alap lekérdezésünket a nézet ellen egy Enterprise Edition példányon, mindenféle utalás nélkül:

SELECT CO.CustomerID, SUM(CO.OrderQty)FROM dbo.CustomerOrders AS COWHERE CO.ProductID BETWEEN 711 AND 718GROUP BY CO.CustomerID;

Az újdonság ott a figyelmeztető háromszög a View Clustered Index Seek-en. A tooltip a részleteket mutatja:

Nem használtunk NOEXPAND súgót, így az indexelt nézet ügyfélazonosító oszlopának statisztikája nem készült el automatikusan. Az ügyfélazonosítóra vonatkozó statisztikák valójában nem túl fontosak ebben az egyszerűsített példában, de ez nem mindig lesz így.

Furcsa kardinalitásbecslések

A második érdekes dolog az, hogy a kardinalitásbecslések rosszabbnak tűnnek, mint bármely eddig tapasztalt esetben, beleértve a Standard Edition példákat is.

Először nehéz meglátni, honnan származik a View Clustered Index Seek (11,267) kardinalitásbecslése. Azt várnánk, hogy a becslés a nézetfürtözött indexhez tartozó statisztikákból származó termékazonosító-hisztogram információkon alapul. Ennek a hisztogramnak a vonatkozó része az alábbiakban látható:

DBCC SHOW_STATISTICS ('dbo.CustomerOrders', 'cuq') WITH HISTOGRAM;

Mivel a táblát nem módosították a statisztikák létrehozása óta, azt várnánk, hogy a becslés a 711 és 718 közötti termékazonosító értékekre vonatkozó RANGE_ROWS és EQ_ROWS egyszerű összege legyen (vegye figyelembe, hogy a becslésből ki kell zárni a 711-es bejegyzéssel szemben feltüntetett 28 RANGE_ROWS-t, mivel ezek a sorok a 711-es kulcsérték alatt vannak). A feltüntetett EQ_ROWS összege 7 301. Ez pontosan a nézet által ténylegesen visszaadott sorok száma – tehát honnan származik a 11.267-es becslés?

A válasz a nézeti megfeleltetés jelenlegi működésében rejlik. A lekérdezésünk nem adta meg a NOEXPAND súgót, így a kezdeti kardinalitásbecslések a nézet kiterjesztett lekérdezési fáján alapulnak. Ez legkönnyebben akkor látható, ha újra megnézzük ugyanannak a lekérdezésnek a becsült tervét EXPAND VIEWS megadásával:

A pirosan árnyékolt terület a fa azon részét jelöli, amelyet a nézetillesztési tevékenység helyettesít. Ennek a területnek a kimeneti kardinalitása 11 267. A 11 220 becsült értékkel rendelkező árnyékolatlan részt nem érinti a nézetillesztés. Pontosan ezeket a becsléseket akartuk megmagyarázni:

A nézetillesztés egyszerűen a pirosan árnyékolt területet egy logikailag egyenértékű kereséssel helyettesíti az indexelt nézeten. Nem használta fel a nézet statisztikai információit a kardinalitásbecslés újraszámításához.

Egy bizonyos mértékig valószínűleg érthető, hogy miért működhet ez így: általában kevés okunk van arra számítani, hogy az egyik statisztikai információkészletből kiszámított becslés jobb, mint a másik. Lehetne érvelni amellett, hogy az indexált nézeti statisztikák nagyobb valószínűséggel pontosabbak itt, mint a pirosan árnyékolt területen lévő, csatlakozás utáni származtatott statisztikák, de ezt nehéz lehet általánosítani, vagy helyesen figyelembe venni, hogy a különböző statisztikai információforrások milyen gyorsan elavulhatnak, ha a mögöttes adatok változnak.

Azzal is lehet érvelni, hogy ha annyira biztosak lennénk abban, hogy az indexált nézeti információ jobb, akkor egy NOEXPAND súgót használtunk volna.

Még furcsább kardinalitásbecslések

Egy még érdekesebb helyzet adódik az Enterprise Edition esetében, ha a lekérdezést az alaptáblák ellen írjuk, és az automatikus nézetillesztésre támaszkodunk:

SELECT SOH.CustomerID, SUM(OrderQty)FROM Sales.SalesOrderHeader AS SOHJOIN Sales.SalesOrderDetail AS SOD ON SOD.SalesOrderID = SOH.SalesOrderIDWHERE SOD.ProductID BETWEEN 711 AND 718GROUP BY SOH.CustomerID;

A hiányzó statisztikákra vonatkozó figyelmeztetés ugyanaz, mint korábban, és ugyanaz a magyarázat. Az érdekesebb tulajdonság az, hogy most alacsonyabb becslést kapunk a View Clustered Index Seek által előállított sorok számára (7,149), és magasabb becslést az aggregálásból visszaadott sorok számára (8,226).

Hogy hangsúlyozzuk a lényeget, úgy tűnik, ez a lekérdezési terv azon az elképzelésen alapul, hogy 7,149 forrássor aggregálásával 8,226 sort lehet létrehozni!

A magyarázat egy része ugyanaz, mint korábban. A EXPAND VIEWS lekérdezési terv, amely a nézetillesztéssel helyettesítendő piros régiót mutatja, az alábbiakban látható:

Ez megmagyarázza, honnan származik a 8,226 soros végső becslés, de mi a helyzet a 7,149 soros becsléssel? A korábban látott logikát követve úgy tűnik, hogy a nézetnek 11 267 soros becslést kellene mutatnia?

A válasz az, hogy a 7 149 soros becslés egy találgatás. Igen, tényleg. Az indexelt nézet összesen 79 433 sort tartalmaz. A termékazonosító BETWEEN predikátum mágikus becslési százaléka 9% – ami 0,09 * 79433 = 7148,97 sor. Az SSMS lekérdezési terv azt mutatja, hogy ez a számítás pontosan helyes, még a kerekítés előtt is:

Ebben a helyzetben az SQL Server optimalizálója úgy tűnik, hogy az indexelt nézet kardinalitásán alapuló becslést preferálta a helyettesített részfából származó post-join kardinalitásbecsléssel szemben. Érdekes.

Összefoglaló

A NOEXPAND súgó használata garantálja, hogy a végleges lekérdezési tervben indexelt nézetet használnak, és lehetővé teszi, hogy a lekérdezésoptimalizáló automatikusan létrehozza, karbantartja és használja a nem indexelt statisztikákat. A NOEXPAND használata azt is biztosítja, hogy a kezdeti kardinalitásbecslések az indexelt nézeti információkon alapulnak, nem pedig az alaptáblákból származnak.

Ha a NOEXPAND nincs megadva, a nézeti hivatkozások mindig a bázistábla-definíciókra cserélődnek a lekérdezés összeállításának megkezdése előtt (és ezért a kezdeti kardinalitásbecslés előtt). Csak az Enterprise SKU-kban az indexelt nézetek az optimalizálási folyamat későbbi szakaszában visszahelyezhetők a lekérdezési fába.

A EXPAND VIEWS lekérdezési utalás megakadályozza, hogy az optimalizáló az Enterprise Edition indexelt nézetillesztést végezzen. Ez attól függetlenül érvényes, hogy a lekérdezés eredetileg indexelt nézetre hivatkozott-e vagy sem. Nézetillesztéskor a meglévő kardinalitásbecslést bizonyos körülmények között egy találgatással lehet helyettesíteni.

Az indexelt nézeten hiányzónak feltüntetett statisztikák manuálisan is létrehozhatók, de az optimalizáló általában nem használja őket olyan lekérdezésekhez, amelyek nem használnak NOEXPAND súgót.

Az indexelt nézetek használata javíthatja a kardinalitásbecslést, különösen, ha a nézet egyesítéseket vagy aggregációkat tartalmaz. A lekérdezéseknek akkor van a legnagyobb esélyük a pontosabb nézeti statisztikákból származó előnyökre, ha a NOEXPAND meg van adva.

Vélemény, hozzászólás?

Az e-mail-címet nem tesszük közzé.