Statistics - Uge 13

25. jun 2021
Denne gang tager jeg hul på Statistics på SQL Serveren og hvordan de hjælper Query Optimizeren med at producere en Execution Plan som er “god nok” (som gennemgået i uge 9). Statistics er hovedsageligt brugt af Query Optimizeren til at estimere, hvor mange rækker der vil blive returneret fra en query. Kun et estimat – ikke andet.
Brian Bønk Rueløkke
PRINCIPAL ARCHITECT
Uge 13

Statistics overblik

SQL Server anvender et objekt fra Statistics kaldet et Histogram, som beskriver, via et maksimum på 200 steps, data distributionen for en given kolonne. En af de største begrænsninger på Statistics på SQL serveren er de 200 steps (som man kan komme omkring ved at lave filtrerede statistics, som blev introduceret med SQL Server 2008).

En anden begrænsning er Auto Update mekanismen ved Statistics – ved en tabel med mere end 500 rækker, bliver Statistics kun opdateret hvis 20% og 500 værdier i kolonner bliver ændret. Dette betyder at Statistics bliver opdateret mindre end hvad tabellen vokser i størrelse.

En tabel med 100.000 rækker vil Statistics dermed kun blive opdateret hvis der sker ændringer i 20.500 (20% + 500) værdier i kolonner i den underliggende tabel. Ligedan med en tabel på 1.000.000 rækker, så skal der 200.500 ændringer til, før Statistics bliver opdateret. Denne algoritme er baseret på eksponentiel fremskrivning og ikke liniært.

Fra SQL Server version 2016 er TraceFlag 2173 sat som default. Dette traceflag betyder at SQL serveren nu laver en dynamisk begrænsning baseret på det reelle antal rækker i tabellen og opdaterer Statistics. Denne indstilling giver visse I/O problemer, men fordelene opvejer meget tydeligt ulemperne.

Forestil dig at du har en Execution Plan som indeholder en Bookmark Lookup. Bookmark Lookup operatoren bliver kun valgt af Optimizeren hvis querien returnerer et udvalgt resultat – baseret på de nuværende Statistics. Hvis Statistics er “out of date” og Execution Planen stadig er valid, så vil SQL serveren blindt stole på Statistics og genbruge den cachede plan og data reads vil eksplodere.

Forældede Statistics

For at vise med et eksempel hvordan Statistics opfører sig, har jeg lavet nedenstående gennemgang.

Hvis du bruger SQL server 2016 eller senere, skal du, for at nedenstående virker, “slukke” for traceflad 2173 med nedenstående:

Nedenstående script opretter en simpel tabel med 1.500 rækker, og hvor data distibutionen er lige i Column2 set over histogrammet. Der bliver også oprettet et Non-Clucstered Index på den kolonne.

Når du efterfølgende udfører en simpel SELECT * FROM Table1 WHERE Column2 = 2 mod tabellen ved at filtrere på Column2, vl du få en Execution Plan der ligner den nedenfor:

Der sker en Index Seek (Non-Clustered) operator og SQL serveren estimerer 1 række. I realiteten er det også det der sker, da der er filtrering på en enkelt værdi fra WHERE elementet. Dette er et eksempel på Statistics som er opdateret og hjælper Optimizeren med en god performance.

Tabellen er nu 1.500 rækker lang så SQL serveren vil automatisk opdatere Statistics hvis det underliggende data (i dette tilfælde er det faktisk indexet) bliver ændret med 20% + 500 rækker. Matematikken giver os dermed at der skal ske 800 dataændringer, før statistics bliver opdateret.

Det næste skridt gør at vi arbejder lidt imod SQL serveren (for eksemplets skyld), for vi indsætter kun 799 rækker i tabellen. Men værdien i Column2 er nu altid “2”. Dette betyder en helt skævvridning af Histogrammet for data distributionen i Statistics. Idet der ikke sker en opdatering af Statistics vil SQL serveren stadig tro at der kun er 1 række i vores query fra før.

Hvis vi eksekverer den helt samme query fra før, vil SQL serveren genanvende den cachede execution plan og lave et Bookmark Lookup. Dette betyder at Bookmark Lookup nu bliver eksekveret 2.299 gange – een gang for hver række i tabellen. Det er mange Logical Reads! SQL serveren returnerer da også med 806 page reads – prøv at se nedenstående billede.

Med SQL server 2016 og frem (inkl naturligvis Azure) er det ikke længere noget stort problem med outdatede Statistics. Men hvis du oplever problemer med en underligt opstået Bookmark Lookup, så prøv alligevel at se om det skulle være statistics som er for “gamle”.

Hvis du har leget med traceflag fra ovenstående, så husk at rydde op igen med nedenstående:

Opsummering – Statistics

Med dette indlæg har jeg forsøgt at give dig en indtroduktion til Statistics på SQL serveren. Som du har set kan Statistics som ikke afspejler virkeligheden være fatale for en SQL serverens leverede performance.

I næste uge graver jeg med lidt længere ned i detaljerne om Statistics, så “stay tuned”.

Kan du ikke vente med at læse mere til næste uge, så er der herunder lidt links at hygge med:

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