Indexed Views and Statistics

Indexed views kunnen in elke editie van SQL Server worden aangemaakt, maar er zijn een aantal gedragingen waarvan u zich bewust moet zijn als u er het meeste uit wilt halen.

Automatische statistieken vereisen een NOEXPAND hint

SQL Server kan automatisch statistieken aanmaken om te helpen met cardinaliteit schatting en kosten-gebaseerde besluitvorming tijdens het optimaliseren van query’s. Deze functie werkt zowel met geïndexeerde views als met basistabellen, maar alleen als de view expliciet in de query wordt genoemd en de NOEXPAND hint is gespecificeerd. (Er is altijd een statistiek object geassocieerd met elke index op een view, het is het automatisch genereren en onderhouden van statistieken die niet geassocieerd zijn met een index waar we het hier over hebben.)

Als u gewend bent te werken met niet-Enterprise edities van SQL Server, is dit gedrag u wellicht nog nooit opgevallen. Lagere edities van SQL Server vereisen de NOEXPAND hint om een query plan te produceren dat een geïndexeerde view benadert. Wanneer NOEXPAND is gespecificeerd, worden automatische statistieken aangemaakt op geïndexeerde views, precies zoals gebeurt met gewone tabellen.

Voorbeeld – Standard Edition met NOEXPAND

Met SQL Server 2012 Standard Edition en de voorbeelddatabase Adventure Works maken we eerst een view die twee verkooptabellen koppelt en de totale bestelhoeveelheid per klant en product berekent:

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;

Om ervoor te zorgen dat deze weergave statistieken ondersteunt, moeten we deze materialiseren door een unieke geclusterde index toe te voegen. De combinatie van Klant- en Product-ID is gegarandeerd uniek in de view (per definitie), dus gebruiken we die als de sleutel. We zouden de twee kolommen om en om kunnen specificeren in de index, maar ervan uitgaande dat we verwachten dat meer queries filteren op product, maken we Product ID de leidende kolom. Deze actie creëert ook indexstatistieken, met een histogram dat is opgebouwd uit Product ID-waarden.

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

We worden nu gevraagd om een query te schrijven die de totale hoeveelheid bestellingen per klant laat zien, voor een bepaalde reeks producten. We verwachten dat een uitvoeringsplan dat gebruik maakt van de geïndexeerde view een effectieve strategie zal zijn, omdat het een join vermijdt en werkt op gegevens die al gedeeltelijk zijn geaggregeerd. Aangezien we SQL Server Standard Edition gebruiken, moeten we de view expliciet specificeren en een NOEXPAND hint gebruiken om een query plan te produceren dat de geïndexeerde view benadert:

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

Het geproduceerde uitvoeringsplan toont een zoekactie op de geïndexeerde view om rijen te vinden voor de producten die van belang zijn, gevolgd door een aggregatie om de totale hoeveelheid per klant te berekenen:

De weergave Plan Tree van SQL Sentry Plan Explorer laat zien dat de schatting van de cardinaliteit exact correct is voor het zoeken op de geïndexeerde weergave, en zeer goed voor het resultaat van de aggregatie:

Als onderdeel van het compilatie- en optimalisatieproces voor deze query heeft SQL Server een extra statistisch object aangemaakt voor de kolom Customer ID van de geïndexeerde weergave. Deze statistiek is aangemaakt omdat het verwachte aantal en de verdeling van de Customer IDs van belang kunnen zijn, bijvoorbeeld bij het kiezen van een aggregatiestrategie. We kunnen de nieuwe statistiek bekijken met Management Studio Object Explorer:

Door te dubbelklikken op het statistiekobject wordt bevestigd dat het is gebouwd op basis van de Customer ID-kolom van de view (niet een basistabel):

Geïndexeerde weergaven kunnen de schatting van de kardinaliteit verbeteren

Wij maken nog steeds gebruik van Standard Edition, maar nu verwijderen we de geïndexeerde weergave en maken deze opnieuw aan (waardoor ook de statistieken van de weergave worden verwijderd) en voeren de query opnieuw uit, dit keer met de NOEXPAND-hint als commentaar weggelaten:

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

Zoals verwacht bij gebruik van Standard Edition zonder NOEXPAND, werkt het resulterende query plan op de basistabellen in plaats van de view direct:

De waarschuwingsdriehoek op de root operator in het bovenstaande plan waarschuwt ons voor een potentieel nuttige index op de tabel Verkooporderdetails, die niet belangrijk is voor onze huidige doeleinden. Deze compilatie creëert geen statistieken op de geïndexeerde view. De enige statistiek van de view na het compileren van de query is die van de geclusterde index:

De Plan Tree view voor de query laat zien dat de schatting van de cardinaliteit correct is voor de twee table scans en de join, maar een stuk slechter voor de andere plan operatoren:

Het gebruik van de geïndexeerde view met een NOEXPAND-hint resulteerde in nauwkeurigere schattingen voor onze testquery, omdat informatie van betere kwaliteit beschikbaar was uit statistieken over de view – in het bijzonder de statistieken die zijn gekoppeld aan de view-index.

In het algemeen neemt de nauwkeurigheid van statistische informatie vrij snel af naarmate deze door query plan operatoren wordt doorlopen en gewijzigd. Eenvoudige joins zijn in dit opzicht vaak niet al te slecht, maar informatie over het resultaat van een aggregatie is vaak niet beter dan een beredeneerde gok. De query optimizer voorzien van nauwkeurigere informatie met behulp van statistieken op geïndexeerde views kan een nuttige techniek zijn om de kwaliteit en robuustheid van het plan te verhogen.

Een view zonder NOEXPAND kan een inferieur plan opleveren

Het query plan hierboven (Standard Edition, zonder NOEXPAND) is feitelijk minder optimaal dan wanneer we de query zelf tegen de basistabellen hadden geschreven, in plaats van de query optimizer de view te laten uitbreiden. De onderstaande query geeft dezelfde logische eis weer, maar verwijst niet naar de weergave:

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;

Deze query levert het volgende uitvoeringsplan op:

Dit plan bevat één aggregatiebewerking minder dan voorheen. Wanneer view expansion werd gebruikt, was de query optimizer helaas niet in staat om een overbodige aggregatiebewerking te verwijderen, wat resulteerde in een minder efficiënt uitvoeringsplan. De uiteindelijke cardinaliteitsschatting voor de nieuwe query is ook iets beter dan wanneer de geïndexeerde view werd geraadpleegd zonder NOEXPAND:

Niettemin zijn de beste schattingen nog steeds die welke worden geproduceerd wanneer de geïndexeerde view wordt geraadpleegd met NOEXPAND (hieronder herhaald voor het gemak):

Enterprise Edition en viewmatching

Op een Enterprise Edition-instantie kan de query-optimalisator mogelijk een geïndexeerde view gebruiken, zelfs als de query de view niet expliciet vermeldt. Als de optimizer in staat is om een deel van de query tree te matchen met een geïndexeerde view, dan kan hij kiezen om dat te doen, gebaseerd op zijn inschatting van de kosten van het wel of niet gebruiken van de view. De view-matching logica is redelijk slim, maar het heeft grenzen die in de praktijk vrij gemakkelijk te raken zijn. Zelfs wanneer het matchen van views succesvol is, kan de optimizer nog steeds misleid worden door onnauwkeurige kosteninschattingen.

De EXPAND VIEWS query hint

Beginnend met de zeldzaamste van de mogelijkheden, kunnen er gelegenheden zijn waarbij een query verwijst naar een geïndexeerd view, maar een beter plan zou worden verkregen door in plaats daarvan de basistabellen te benaderen. In deze omstandigheden kan de query hint EXPAND VIEWS worden gebruikt:

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

Op Enterprise Edition levert deze query hetzelfde plan op als op Standard Edition wanneer de NOEXPAND hint was weggelaten (inclusief de overbodige aggregatie operatie):

Terzijde: de naam van de EXPAND VIEWS-hint is naar mijn mening niet goed. SQL Server breidt view-definities in een query altijd uit, tenzij de NOEXPAND-hint is gespecificeerd. De EXPAND VIEWS hint schakelt regels in de optimizer uit die delen van de uitgebreide boom kunnen terugleiden naar geïndexeerde views. In de afwezigheid van een van beide hints, breidt SQL Server eerst een view uit naar de basis tabel definitie, en overweegt dan later terug te koppelen naar geïndexeerde views. Een betere naam voor de EXPAND VIEWS hint zou zijn geweest DISABLE INDEXED VIEW MATCHING, want dat is wat het doet.

De EXPAND VIEWS hint wordt waarschijnlijk het meest gebruikt om te voorkomen dat een query tegen basistabellen wordt gematcht met een geïndexeerde view:

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

De query hint resulteert in hetzelfde uitvoeringsplan en dezelfde schattingen die we zagen toen we Standard Edition gebruikten en dezelfde query voor alleen de basistabellen:

Enterprise View Matching and Statistics

Zelfs in Enterprise Edition, worden non-index view statistieken nog steeds alleen gemaakt als de NOEXPAND hint wordt gebruikt. Voor alle duidelijkheid, de Enterprise-only view-matching functie leidt er nooit toe dat view-statistieken worden aangemaakt of bijgewerkt. Dit onintuïtieve gedrag is het waard om een beetje te onderzoeken, omdat het verrassende neveneffecten kan hebben.

We voeren nu onze basis query uit tegen de view op een Enterprise Edition instance, zonder enige hints:

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

Een nieuw ding daar is de waarschuwingsdriehoek op de View Clustered Index Seek. De tooltip toont de details:

We hebben geen NOEXPAND-hint gebruikt, dus de statistieken voor de kolom Klant-ID van de geïndexeerde weergave zijn niet automatisch aangemaakt. De statistieken over Customer ID zijn in dit vereenvoudigde voorbeeld eigenlijk niet zo belangrijk, maar dat zal niet altijd het geval zijn.

Verrassende Cardinaliteitsschattingen

Het tweede interessante punt is dat de cardinaliteitsschattingen slechter lijken te zijn dan alle gevallen die we tot nu toe zijn tegengekomen, inclusief de Standard Edition-voorbeelden.

Het is in eerste instantie moeilijk te zien waar de cardinaliteitsschatting voor de View Clustered Index Seek (11.267) vandaan komt. We zouden verwachten dat de schatting is gebaseerd op Product ID histogram informatie uit de statistieken die zijn gekoppeld aan de view clustered index. Het relevante deel van dit histogram wordt hieronder weergegeven:

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

Gezien het feit dat de tabel niet is gewijzigd sinds de statistieken zijn gemaakt, zouden we verwachten dat de schatting een eenvoudige som is van RANGE_ROWS en EQ_ROWS voor Product ID-waarden tussen 711 en 718 (merk op dat de schatting de 28 RANGE_ROWS uitsluit die bij invoer 711 worden getoond, aangezien die rijen onder de 711-sleutelwaarde staan). De som van de getoonde EQ_ROWS is 7.301. Dit is precies het aantal rijen dat werkelijk door de view wordt geretourneerd – dus waar komt de schatting van 11.267 vandaan?

Het antwoord ligt in de manier waarop view matching momenteel werkt. Onze query heeft de NOEXPAND hint niet gespecificeerd, dus initiële cardinaliteit schattingen zijn gebaseerd op de view-uitgebreide query boom. Dit is het gemakkelijkst te zien door nogmaals te kijken naar het geschatte plan voor dezelfde query met EXPAND VIEWS gespecificeerd:

Het rood gearceerde gebied geeft het deel van de boom weer dat wordt vervangen door view matching activiteit. De output cardinaliteit van dit gebied is 11.267. Het niet gearceerde deel met de 11.220 schatting wordt niet beïnvloed door view matching. Dit zijn precies de schattingen die we wilden verklaren:

View matching verving eenvoudigweg het rood gearceerde gebied door een logisch-equivalente zoekactie op het geïndexeerde beeld. Er werd geen gebruik gemaakt van statistische informatie uit het overzicht om de schatting van de kardinaliteit opnieuw te berekenen.

Tot op zekere hoogte kunt u waarschijnlijk wel begrijpen waarom het zo zou kunnen werken: in het algemeen is er weinig reden om te verwachten dat een schatting die is berekend op basis van de ene reeks statistische gegevens, beter is dan een andere. Men zou kunnen stellen dat de geïndexeerde view statistieken hier waarschijnlijk nauwkeuriger zijn, vergeleken met de post-join afgeleide statistieken in het rood gearceerde gebied, maar het zou lastig kunnen zijn om dat te veralgemenen, of om correct rekening te houden met hoe snel verschillende bronnen van statistische informatie verouderd kunnen raken als de onderliggende gegevens veranderen.

Een ander argument zou kunnen zijn dat als we er zo zeker van waren dat de geïndexeerde view informatie beter was, we wel een NOEXPAND hint zouden hebben gebruikt.

Even meer curieuze schattingen van de kardinaliteit

Een nog interessantere situatie doet zich voor met Enterprise Edition als we de query tegen de basistabellen schrijven en vertrouwen op geautomatiseerde view matching:

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;

De waarschuwing voor ontbrekende statistieken is dezelfde als voorheen, en heeft dezelfde verklaring. Interessanter is dat we nu een lagere schatting hebben voor het aantal rijen dat de View Clustered Index Seek oplevert (7.149) en een hogere schatting voor het aantal rijen dat de aggregatie oplevert (8.226).

Om het punt te benadrukken, dit queryplan lijkt te zijn gebaseerd op het idee dat 7.149 bronrijen kunnen worden geaggregeerd om 8.226 rijen op te leveren!

Een deel van de verklaring is dezelfde als voorheen. Het EXPAND VIEWS queryplan, met het rode gebied dat zal worden vervangen door view matching, staat hieronder:

Dit verklaart waar de uiteindelijke schatting van 8.226 vandaan komt, maar hoe zit het met de schatting van 7.149 rijen? Volgens de logica die we eerder hebben gezien, zou het overzicht een schatting van 11.267 rijen moeten laten zien?

Het antwoord is dat de schatting van 7.149 een gok is. Ja, echt waar. De geïndexeerde view bevat 79.433 rijen in totaal. Het magische gok percentage voor de Product ID BETWEEN predicaat is 9% – wat 0.09 * 79433 = 7148.97 rijen geeft. Het SSMS query plan laat zien dat deze berekening exact klopt, zelfs voor afronding:

In deze situatie lijkt de SQL Server optimizer de voorkeur te hebben gegeven aan een gok gebaseerd op de cardinaliteit van de geïndexeerde view boven de post-join cardinaliteitsschatting van de vervangen subtree. Merkwaardig.

Samenvatting

Het gebruik van de NOEXPAND hint garandeert dat een geïndexeerde view zal worden gebruikt in het uiteindelijke query plan, en maakt het mogelijk dat non-index statistieken automatisch worden aangemaakt, onderhouden, en gebruikt door de query optimizer. Het gebruik van NOEXPAND zorgt er ook voor dat de initiële cardinaliteitsschattingen gebaseerd zijn op geïndexeerde view informatie in plaats van te worden afgeleid van basistabellen.

Als NOEXPAND niet is gespecificeerd, worden view referenties altijd vervangen door hun basistabel definities voordat de query compilatie begint (en dus vóór de initiële cardinaliteitsschatting). Alleen in Enterprise SKU’s kunnen geïndexeerde views later in het optimalisatieproces weer in de queryboom worden gesubstitueerd.

De EXPAND VIEWS query hint voorkomt dat de optimizer Enterprise Edition geïndexeerde view matching uitvoert. Dit geldt ongeacht of de query oorspronkelijk refereerde aan een geïndexeerde view of niet. Wanneer view matching wordt uitgevoerd, kan een bestaande cardinaliteitsschatting in sommige omstandigheden worden vervangen door een gok.

Statistieken die worden weergegeven als ontbrekend op een geïndexeerde view kunnen handmatig worden gemaakt, maar de optimizer zal ze over het algemeen niet gebruiken voor queries die geen NOEXPAND hint gebruiken.

Het gebruik van geïndexeerde views kan de cardinaliteitsschatting verbeteren, vooral als de view joins of aggregaties bevat. Queries hebben de meeste kans om te profiteren van meer accurate view statistieken als NOEXPAND is gespecificeerd.

Geef een antwoord

Het e-mailadres wordt niet gepubliceerd.