Indeksoidut näkymät ja tilastot

Indeksoituja näkymiä voidaan luoda missä tahansa SQL Serverin versiossa, mutta on olemassa useita käyttäytymistapoja, joista on oltava tietoinen, jos haluat hyödyntää niitä parhaalla mahdollisella tavalla.

Automaattiset tilastot vaativat NOEXPAND-vihjeen

SQL Server voi luoda tilastoja automaattisesti, jotta se voi avustaa kardinaliteettien arvioinnissa ja kustannuslähtöisessä päätöksenteossa tietopyyntöjen optimoinnin aikana. Tämä ominaisuus toimii sekä indeksoitujen näkymien että perustaulujen kanssa, mutta vain jos näkymä on nimenomaisesti nimetty kyselyssä ja NOEXPAND-vihje on määritetty. (Näkymän kuhunkin indeksiin liittyy aina tilasto-objekti, tässä puhutaan sellaisten tilastojen automaattisesta luomisesta ja ylläpidosta, jotka eivät liity indeksiin.)

Jos olet tottunut työskentelemään SQL Serverin muiden kuin Enterprise-versioiden kanssa, et ehkä ole huomannut tätä käyttäytymistä aiemmin. Alemmat SQL Serverin versiot vaativat NOEXPAND-vihjeen tuottaakseen kyselysuunnitelman, joka käyttää indeksoitua näkymää. Kun NOEXPAND on määritetty, indeksoiduille näkymille luodaan automaattiset tilastot aivan kuten tavallisille taulukoille.

Esimerkki – Standard Edition ja NOEXPAND

Käyttäen SQL Server 2012 Standard Editionia ja Adventure Works -esimerkkitietokantaa luodaan ensin näkymä, joka yhdistää kaksi myyntitaulukkoa ja laskee tilausten kokonaismäärän asiakkaittain ja tuotteittain:

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;

Jotta tämä näkymä tukisi tilastoja, se on materialisoitava lisäämällä siihen yksilöllinen klusteroitu indeksi. Asiakas- ja tuotetunnuksen yhdistelmä on taatusti uniikki näkymässä (määritelmän mukaan), joten käytämme sitä avaimena. Voisimme määrittää nämä kaksi saraketta indeksiin kummin päin tahansa, mutta koska oletamme, että useammat kyselyt suodattavat tuotteen mukaan, teemme tuotetunnuksen johtavaksi sarakkeeksi. Odotamme, että indeksoitua näkymää käyttävä suoritussuunnitelma on tehokas strategia, koska siinä vältetään liitos ja operoidaan tiedoilla, jotka on jo osittain aggregoitu. Koska käytämme SQL Server Standard Editionia, meidän on määritettävä näkymä nimenomaisesti ja käytettävä NOEXPAND-vihjettä tuottaaksemme kyselysuunnitelman, joka käyttää indeksoitua näkymää:

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

Tuotettu suoritussuunnitelma osoittaa, että indeksoidusta näkymästä etsitään rivejä kiinnostaville tuotteille, minkä jälkeen suoritetaan yhteenlasku, jonka tarkoituksena on laskennallisesti laskea kokonaismäärät asiakasta kohden:

SQL Sentry Plan Explorerin Plan Tree -näkymästä käy ilmi, että kardinaalisuuden arviointi on täsmälleen oikea indeksoidun näkymän haun osalta ja erittäin hyvä aggregaatin tuloksen osalta:

Osana tämän kyselyn kääntämis- ja optimointiprosessia SQL Server loi ylimääräisen tilasto-objektin indeksoidun näkymän Asiakastunnus-sarakkeelle. Tämä tilasto on luotu, koska Asiakastunnusten odotettu määrä ja jakauma voivat olla tärkeitä esimerkiksi aggregointistrategian valinnassa. Voimme nähdä uuden tilaston Management Studion Object Explorerin avulla:

Tilasto-objektin kaksoisnapsauttaminen vahvistaa, että se on rakennettu näkymän Customer ID -sarakkeesta (ei perustaulusta):

Indexoidut näkymät voivat parantaa kardinaalisuuden arviointia

Käytämme edelleen Standard Editionia, pudotamme nyt indeksoidun näkymän ja luomme sen uudelleen (mikä pudottaa myös näkymän tilastot) ja suoritamme kyselyn uudelleen, tällä kertaa NOEXPAND-vihjeen kommentoituna pois:

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

Kuten on odotettavissa, kun käytetään Standard Editionia ilman NOEXPAND, tuloksena oleva kyselysuunnitelma operoi perustaulukoihin eikä suoraan näkymään:

Yllä olevassa suunnitelmassa oleva varoituskolmio root-operaattorin kohdalla varoittaa meitä mahdollisesti hyödyllisestä indeksistä Myyntitilausten yksityiskohdat -taulussa, joka ei ole tärkeä tämänhetkisten tarkoitustemme kannalta. Tämä koostaminen ei luo mitään tilastoja indeksoidusta näkymästä. Ainoa näkymän tilasto kyselyn kääntämisen jälkeen on klusteroituun indeksiin liittyvä tilasto:

Kyselyn Plan Tree -näkymästä nähdään, että kardinaalisuuden arviointi on oikein kahdelle taulun skannaukselle ja yhdistämiselle, mutta melko paljon huonompi muille suunnitelmaoperaattoreille:

Indeksoidun näkymän käyttäminen NOEXPAND-vihjeen kanssa johti tarkempiin arvioihin testikyselyssämme, koska näkymän tilastoista – erityisesti näkymän indeksiin liittyvistä tilastoista – oli saatavissa parempaa laatutietoa.

Yleissääntönä on, että tilastotiedon tarkkuus heikkenee melko nopeasti, kun se kulkee kyselysuunnitelman operaattoreiden läpi ja niitä muutetaan. Yksinkertaiset yhdistämiset eivät useinkaan ole tässä suhteessa kovin huonoja, mutta tieto aggregoinnin tuloksesta ei useinkaan ole parempi kuin valistunut arvaus. Tarkemman tiedon antaminen kyselyoptimoijalle indeksoitujen näkymien tilastojen avulla voi olla hyödyllinen tekniikka suunnitelman laadun ja kestävyyden parantamiseksi.

Näkymä, jossa ei ole NOEXPANDia, voi tuottaa huonomman suunnitelman

Yllä oleva kyselysuunnitelma (Standard Edition, ilman NOEXPAND) on itse asiassa vähemmän optimaalinen kuin jos olisimme kirjoittaneet kyselyn perustaulukoihin itse sen sijaan, että olisimme antaneet kyselyoptimoijan laajentaa näkymää. Alla oleva kysely ilmaisee saman loogisen vaatimuksen, mutta siinä ei viitata näkymään:

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;

Tämä kysely tuottaa seuraavan suorituskaavion:

Tämässä kaaviossa on yksi aggregointitoimenpide aiempaa vähemmän. Kun käytettiin näkymän laajennusta, kyselyn optimoija ei valitettavasti pystynyt poistamaan turhaa aggregointioperaatiota, mikä johti vähemmän tehokkaaseen suoritussuunnitelmaan. Uuden kyselyn lopullinen kardinaliteettiestimaatti on myös hieman parempi kuin silloin, kun indeksoituun näkymään viitattiin ilman NOEXPAND:

Parhaat estimaatit ovat kuitenkin edelleen ne, jotka saatiin, kun indeksoituun näkymään viitattiin NOEXPAND:llä (toistetaan alla kätevyyden vuoksi):

Enterprise Edition ja näkymien täsmäytys

Enterprise Edition -instanssissa kyselyn optimoija voi käyttää indeksoitua näkymää, vaikka kyselyssä ei mainittaisi näkymää nimenomaisesti. Jos optimoija pystyy sovittamaan osan kyselypuusta indeksoituun näkymään, se voi valita, tekeekö se niin, perustuen arvioonsa näkymän käyttämisestä tai käyttämättä jättämisestä aiheutuvista kustannuksista. Näkymien täsmäytyslogiikka on kohtuullisen nokkela, mutta sillä on rajansa, joihin on melko helppo törmätä käytännössä. Jopa silloin, kun näkymien täsmäytys onnistuu, optimoijaa voi silti johtaa harhaan epätarkat kustannusarviot.

Kyselyvihje EXPAND VIEWS

Alkaen harvinaisemmasta mahdollisuudesta, voi olla tilanteita, joissa kysely viittaa indeksoituun näkymään, mutta parempi suunnitelma saataisiin käyttämällä sen sijaan perustauluja. Näissä tilanteissa voidaan käyttää kyselyvihjettä EXPAND VIEWS:

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

Enterprise Edition -versiossa tämä kysely tuottaa saman suunnitelman kuin Standard Edition -versiossa nähtiin, kun vihjeen NOEXPAND käyttäminen jätettiin väliin (mukaan luettuna turha aggregointioperaatio):

Sivuhuomautuksena mainittakoon, että vihje EXPAND VIEWS on mielestäni huonosti nimetty. SQL Server laajentaa näkymämääritykset kyselyssä aina, ellei NOEXPAND-vihjettä ole määritetty. Vihje EXPAND VIEWS poistaa käytöstä optimointisäännöt, jotka voivat sovittaa laajennetun puun osia takaisin indeksoituihin näkymiin. Jos kumpaakaan vihjettä ei ole annettu, SQL Server laajentaa näkymän ensin sen perustaulukkomäärittelyyn ja harkitsee sitten myöhemmin sovittamista takaisin indeksoituihin näkymiin. Parempi nimi vihjeelle EXPAND VIEWS olisi voinut olla DISABLE INDEXED VIEW MATCHING, koska se tekee juuri niin.

Vihjettä EXPAND VIEWS käytetään luultavasti useimmiten estämään kantataulukoihin kohdistuvan kyselyn sovittaminen indeksoituun näkymään:

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);

Kyselyvihjeen tuloksena syntyy sama suorituskaavio ja samat estimaatit, jotka on nähty silloin, kun käytimme Standard Edition -versiota ja samaa, pelkkien kantataulukoiden perusteella tehtävää kyselyä:

Enterprise-näkymien täsmäytys ja tilastot

Jopa Enterprise Edition -versiossa ei-indeksinäkymätilastot luodaan edelleen vain, jos käytetään vihjettä NOEXPAND. Jotta asia olisi täysin selvä, vain Enterprise-version näkymien täsmäytysominaisuus ei koskaan johda siihen, että näkymätilastoja luodaan tai päivitetään. Tätä epäintuitiivista käyttäytymistä kannattaa tutkia hieman, koska sillä voi olla yllättäviä sivuvaikutuksia.

Suoritamme nyt peruskyselymme näkymälle Enterprise Edition -instanssissa ilman vihjeitä:

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

Uutena siellä on varoituskolmio Näkymän klusteroidun indeksin etsimisessä. Työkaluvihjeessä näkyy yksityiskohtia:

Emme käyttäneet NOEXPAND-vihjettä, joten indeksoidun näkymän Asiakastunnus-sarakkeen tilastoja ei luotu automaattisesti. Asiakastunnusta koskevat tilastot eivät oikeastaan ole kauhean tärkeitä tässä yksinkertaistetussa esimerkissä, mutta näin ei ole aina.

Yllättävät kardinaalisuusarviot

Toinen kiinnostava asia on se, että kardinaalisuusarviot näyttävät olevan huonompia kuin missään muussa tapauksessa, johon olemme tähän mennessä törmänneet, mukaan lukien Standard Edition -esimerkkien kohdalla.

Aluksi on vaikea hahmottaa, mistä Näkymän klusteroidun indeksin haku (View Clustered Index Seek) kardinaalisuusarvio (11 267) on peräisin. Odottaisimme arvion perustuvan tuotetunnuksen histogrammitietoihin näkymän klusteroituun indeksiin liittyvistä tilastoista. Asianomainen osa tästä histogrammista on esitetty alla:

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

Edellytyksenä on, että taulukkoa ei ole muutettu sen jälkeen, kun tilastotiedot luotiin, odottaisimme estimaatin olevan pelkkä RANGE_ROWS- ja EQ_ROWS-arvojen summa, kun tuotetunnuksen arvot ovat välillä 711-718 (huomaa, että estimaatin ei pitäisi sisältää 28 RANGE_ROWS-arvoa, jotka näkyvät 711-merkinnän kohdalla, koska nämä rivit ovat 711-avainarvon alapuolella). EQ_ROWS-arvojen summa on 7 301. Tämä on täsmälleen näkymän todellisuudessa palauttamien rivien määrä – mistä siis johtuu 11 267 rivin arvio?

Vastaus piilee tavassa, jolla näkymien täsmäytys tällä hetkellä toimii. Kyselyssämme ei määritetty NOEXPAND-vihjettä, joten alkuperäiset kardinaalisuusarviot perustuvat näkymän laajennettuun kyselypuuhun. Tämä on helpointa havaita tarkastelemalla uudelleen saman kyselyn estimoitua suunnitelmaa, jossa on määritetty EXPAND VIEWS:

Punaisella tummennettu alue edustaa puun osaa, joka korvataan näkymän täsmäytystoiminnolla. Tämän alueen tuloskardinaalisuus on 11 267. Tummennettuun osaan, jossa on 11 220 arviota, näkymien täsmäytys ei vaikuta. Nämä ovat juuri niitä arvioita, joita halusimme selittää:

Näkymien täsmäytys yksinkertaisesti korvaa punaisella tummennetun alueen loogisesti vastaavalla hakutoiminnolla indeksoidussa näkymässä. Se ei käyttänyt näkymästä saatua tilastotietoa kardinaalisuusestimaatin uudelleenlaskemiseen.

Jossain määrin voit luultavasti ymmärtää, miksi se saattaa toimia näin: yleensä ei ole juurikaan syytä olettaa, että yhden tilastotiedon perusteella laskettu estimaatti olisi yhtään parempi kuin toinen. Voidaan väittää, että indeksoidun näkymän tilastot ovat tässä tapauksessa todennäköisemmin tarkkoja verrattuna punaisella tummennetulla alueella oleviin yhdistämisen jälkeen johdettuihin tilastoihin, mutta voi olla hankalaa yleistää tätä tai ottaa oikein huomioon, kuinka nopeasti eri tilastotiedon lähteet saattavat vanhentua, kun taustalla olevat tiedot muuttuvat.

Voidaan myös väittää, että jos olisimme olleet niin varmoja, että indeksoidun näkymän tiedot ovat parempia, olisimme käyttäneet NOEXPAND-vihjettä.

Jopa omituisempia kardinaliteettiarvioita

Vielä mielenkiintoisempi tilanne syntyy Enterprise Editionissa, jos kirjoitamme kyselyn kantatauluja vastaan ja luotamme automaattiseen näkymien täsmäytykseen:

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;

Puuttuvia tilastoja koskeva varoitus on sama kuin aiemminkin, ja sillä on sama selitys. Mielenkiintoisempi piirre on se, että meillä on nyt pienempi arvio View Clustered Index Seek -haun tuottamien rivien lukumäärälle (7 149) ja suurempi arvio aggregoinnin palauttamien rivien lukumäärälle (8 226).

Kohdan korostamiseksi tämä kyselysuunnitelma näyttäisi perustuvan ajatukseen, että 7 149 lähderiviä voidaan aggregoida tuottamaan 8 226 riviä!

Tosin selitys on sama kuin aiemmin. EXPAND VIEWS Kyselysuunnitelma, jossa näkyy punainen alue, joka korvataan näkymien täsmäytyksellä, näkyy alla:

Tämä selittää, mistä lopullinen 8 226 rivin arvio tulee, mutta entä 7 149 rivin arvio? Aiemmin nähdyn logiikan mukaisesti näkymän pitäisi näyttää 11 267 rivin arvio?

Vastaus on, että 7 149 rivin arvio on arvaus. Kyllä, oikeasti. Indeksoitu näkymä sisältää yhteensä 79 433 riviä. Tuotetunnuksen BETWEEN-predikaatin maaginen arvausprosentti on 9 % – mikä antaa 0,09 * 79433 = 7148,97 riviä. SSMS:n kyselysuunnitelma osoittaa, että tämä laskelma on täsmälleen oikea, jopa ennen pyöristämistä:

Tässä tilanteessa SQL Serverin optimoija näyttää suosineen indeksoidun näkymän kardinaalisuuteen perustuvaa arvausta korvatun alipuun post-join-kardinaalisuusarvion sijaan. Outoa.

Yhteenveto

Vihjeen NOEXPAND käyttäminen takaa, että indeksoitua näkymää käytetään lopullisessa kyselysuunnitelmassa, ja mahdollistaa sen, että kyselyn optimoija luo, ylläpitää ja käyttää automaattisesti muita kuin indeksitilastoja. Käyttämällä NOEXPAND varmistetaan myös, että alustavat kardinaliteettiarviot perustuvat indeksoitujen näkymien tietoihin sen sijaan, että ne johdettaisiin kantataulukoista.

Jos NOEXPAND:tä ei ole määritetty, näkymäviittaukset korvataan aina niiden kantataulukon määritelmillä, ennen kuin kyselyn kääntäminen aloitetaan (ja siten ennen alustavaa kardinaliteettiarviota). Vain Enterprise SKU:ssa indeksoidut näkymät voidaan korvata takaisin kyselypuuhun myöhemmin optimointiprosessin aikana.

Kyselyvihje EXPAND VIEWS estää optimoijaa suorittamasta Enterprise Editionin indeksoitujen näkymien täsmäytystä. Tätä sovelletaan riippumatta siitä, viittasiko kysely alun perin indeksoituun näkymään vai ei. Kun näkymien täsmäytys suoritetaan, olemassa oleva kardinaliteettiestimaatti saatetaan joissakin tilanteissa korvata arvauksella.

Puuttuvina näkymässä näkyvät tilastotiedot voidaan luoda manuaalisesti, mutta optimoija ei yleensä käytä niitä kyselyissä, joissa ei käytetä NOEXPAND-vihjettä.

Indeksoitujen näkymien käyttäminen voi parantaa kardinaliteettiestimaattia erityisesti, jos näkymä sisältää yhdistämisiä tai aggregointeja. Kyselyillä on parhaat mahdollisuudet hyötyä tarkemmista näkymätilastoista, jos NOEXPAND on määritetty.

Vastaa

Sähköpostiosoitettasi ei julkaista.