Statistics Inside - Uge 14

02. jul 2021
I denne uge går jeg et spadestik dybere fra sidste uges Statistics indlæg og indledning og åbner for motorhjelmen og ser på, hvordan SQL server håndterer Statistics. Med et tænkt eksempel på en operator i en Execution plan som har en metrik “Estimated Number og Rows” til at være 42 (ja, endnu en henvisning), og du ved at 42 ikke er korrekt. Men hvordan kan man så bruge Statistics til at finde ud af hvor det går galt?
Brian Bønk Rueløkke
PRINCIPAL ARCHITECT
Uge 14

Det er her Histogram og Density Vector kommer ind i billedet.

Histogram

Første skridt på vejen er at kigge lidt på Histogram i Statistics. Ideen med det indbyggede histogram er at holde styr på data distributionen af en kolonne – dette på en meget effektiv og kompakt måde. Hver gang et index bliver oprettet på en tabel (Clustered eller Non-Clustered), så vil SQL serveren under motorhjelmen oprette et statistics objekt. Og dette objekt beskriver data distributionen i en specifik kolonne. Forestil dig at du har en ordre-tabel og en kolonne heri med navnet Land. Der er noget salg i forskellige lande og dermed kan man visualisere salget i disse lande som nedenstående illustration.

Af histogrammet ses en data distribution med nogen søjler og akser. Jo højere søjlen er, jo flere rækker er der med den specifikke værdi – i dette tilfælde “DE”. Det samme koncept og format bliver brugt i SQL serveren. Et lidt mere konkret eksempel:

I demo databasen AdventureWorks fra Microsoft er der en tabel ved navn SalesOrderDetail og i denne tabel en kolonne med navn ProductId. Denne kolonne indeholder ID for alle produkterne som er en del af de specifikke salg.

Der eksisterer også et index på den tabel og kolonne – hvilket betyder at SQL serveren har oprettet et statistics objekt som indeholder et histogram med beskrivelse af data distributionen i den kolonne. Man kan tilgå disse statistics i SQL ved to metoder – ved at højreklikke på en kolonne og vælge properties – eller ved at eksekvere denne kommando:

Ovenstående kommando giver nedenstående resultat:

Der fremkommer 3 resultatsæt:

  • Generel information om statistics objektet
  • Density Vector
  • Histogram

Sidstnævnte er vist som tabel-element, men vil hurtigt, via Excel eller Power BI kunne omsættes til en visualisering ala mit eksempel ovenfor med søjlerne.

Density Vector

Den mystiske Density Vector – hvad er nu det for noget? Jo, når man f.eks kigger på et Non-Clustered Index fra den tidligere SalesOrderDetail tabel IX_SalesOrderDetail_ProductID, kan man se at index kun er defineret på een kolonne – nemlig ProductID. Men alle Non-Clustered Index på SQL serveren kræver også at have Clustered Key med sig – som minimum som en logisk “pegepind” til leaf level af index (Data Pagen med data). Når der er defineret et Non-Unique Non-Clustered Index, så bliver Clustered Key en del af strukturen i indexet. Clustered Key på SalesOrderDetail tabelle er en composit version og består af kolonnerne SalesOrderID og SalesOrderDetailID.

Dette betyder at vores Non-Unique Non-Clustered Index i virkeligheden består af kolonnerne ProductID, SalesOrderID og SalesOrderDetailID. Så under motorhjelmen på SQL serveren er der nu en composit index key. Dette betyder også at SQL serveren har oprettet en Density Vector for de andre kolonner, fordi det er kun den første kolonne (ProductID) som er en del af Histogrammet.

Density Vector er 2. resultatsæt i DBCC kommandoen fra tidligere.

SQL serveren gemmer heri en såkaldt Selectevity (eller densitet) af de forskellige kolonne-kombinationer. For eksempel er der en All density værdi for ProductID på 0,003759399. Denne værdi kommer ved at dele 1 med antallet af unikke værdier i kolonnen ProductID – T-SQL eksempel:

Værdien for All density for kombinationen af ProductID, SalesOrderID og ProductID, SalesOrderID, SalesOrderDetailID er det samme og har værdien:

8,242867858585359018109580685312e-6

Dette kan igen verificeres ved en T-SQL kommando – måske du selv skal forsøge dig med denne kommando.

Opsummering – Statistics Inside

Med denne indlæg har jeg forsøgt at løfte sløret lidt for hvordan SQL serveren håndterer og arbejder med Statistics under motorhjelmen. En kort gennemgang af Histogram og Density Vector som bliver brugt til SQL serverens Cardinality Estimation.

I næste uge vil jeg dykke ned i netop Cardinality Estimation og skrive lidt om nogen af de udfordringer det giver.

Har du misset nogle af de tidligere indlæg, kan du finde dem herunder:

Data & AI

Fra enkle datavisualiseringer til mere komplekse Business Intelligence og Data Warehouse løsninger.

Data & AI
Læs mere om vores Data & AI løsninger
Data & AI