Problemer med Cardinality Estimation - Uge 15

16. jul 2021
Så blev det fredag igen og de sidste to uger har jeg skrevet om statistics –først om indledningen til statistics og sidste gang lidt mere om indholdet i statistics.

Jeg har været omkring vigtigheden af statistics og hvordan de reelt “ser ud” internt i SQL serveren. I denne uge kommer så en gennemgang af nogen af begræsningerne som Cardinality Estimation har, og hvordan man kan komme omkring nogen af dem.
Brian Bønk Rueløkke
PRINCIPAL ARCHITECT
uge 15

Cardinality Estimation Errors

Fra sidste uge. husker du måske at SQL serveren anvender Histogram og Density Vector til at lave Cardinality Estimation når en execution plan bliver oprettet. Modellem som SQL serveren anvender her, er den samme som den har været i rigtig mange år frem til SQL server 2014 og som kommer med en del ulemper og faldgruber. Fra 2014 og frem har Microsoft gjort rigtig meget ud af at tage fat i problemerne. Disse afdækker jeg lidt af i næste uge.

Så resten af dette indlæg, tager sit udgangspunkt i versioner før SQL server 2014 – de er ikke af den grund mindre nødvendige at kende og vide noget om, når man skal optimere på SQL performance.

Et konkret eksempel for Cardinality Estimation har problemer – forestil dig to tabeller: Ordre og Land. Hver række i Ordre-tabellen repræsenterer en ordre afgivet af en kunde (som en fact-tabel i et data warehouse) og denne tabel har en relation gennem en fremmednøgle (foreign key contraint) til tabellen Land (som kunne være en dimensionstabel i data warehouse).

Med nedenstående query mod de to tabeller for at få alle salg fra England:

Ved et kig på Execution Planen for denne query, så fremgår det ret tydeligt at SQL Server har et problem med cardinality estimation:

SQL Server estimterer 501 rækker, mens Clustered Index Seek operator returnerer 1000 rækker. SQL Server anvender her Density Vector fra statistics objeket idx_ID_SalesAmout (fra AdventureWorks databasen fra sidste uge) til at lave estimatet. Density Vector er 0,5 (der er kun 2 unikke værdier i kolonnen), og derfor er estimatet 501 (1001 x 0,5).

Dette problem kan løses ved at tilføje Filtered Statistics til tabellen. Dette vil give SQL serveren mere information om data distributionen og også hjælpe med Cardinality Estimation. Dette kunne se sådan ud:

Ved at køre ovenstående query og genkøre query’en omkring England, så kan du nu se at både de estimerede og de reelle antal rækker er de samme. Det giver SQL serveren en meget bedre mulighed for allokere ressourcer til queryen og dermed give et hurtigere resultat.

Korrelerede kolonner

Et andet problem som kan opstå med Cardinality Estimation i SQL serveren er at såkaldte search predicate som korrelerer hinanden. En imaginær query kunne se ud som denne:

Som menneske er det ret simpelt at Nokia ikke sælger Tesla-biler og dermed er resultatet af denne query 0 rækker. Men når man eksekverer ovenstående query mod en SQL server, så til Query Optimizeren kigge på hvert search predicate individuelt:

  • Step 1: Cardinality Estimation vil ske for Company = ‘Nokia’
  • Step 2: Cardinality Estimation vil ske for Product = ‘Tesla Model 3’

Og slutteligt vil begge estimater blive ganget med hinanden for at give det endelige resultat. Hvis, f.eks. step 1 giver en cardinality estimation på 0,3 og step 2 giver 0,4, så bliver resultatet 0,12 (0,4 x 0,3). Query Optimizeren håndterer hver search predicate som sit eget uden at lave nogen korrelation imellem dem.

Opsummering – Problemer med Cardinality Estimation

Statistics og Cardinality Estimation er en af de vigtigste elementer for en korrekt execution plan og dermed en god performance i SQL Serveren. Uheldigvis er deres brug også begrænset – især i disse grænsetilfælde som jeg har listet ovenfor i dag.

Jeg har forsøgt at give nogen af måderne for at komme omkring nogen af problemerne på – og har du fået blod på tanden for at lære mere om Cardinality Estimation så er der her lidt links:

I næste uge vil jeg tage fat på hvad der sker fra SQL server 2014 og frem (inkl Azure).

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