Performance Tuning

Uge 13: Statistics

Allerede uge 13 og dermed starten på den fjerde måned i denne blogserie. 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.

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:

DBCC TRACEOFF (2371,-1)

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.

CREATE TABLE Table1
(
   Column1 INT IDENTITY,
   Column2 INT
)
GO
-- Insert 1500 records into Table1
SELECT TOP 1500 IDENTITY(INT, 1, 1) AS n INTO #Nums
FROM
master.dbo.syscolumns sc1
INSERT INTO Table1 (Column2)
SELECT n FROM #nums
DROP TABLE #nums
GO 
CREATE NONCLUSTERED INDEX idx_Table1_Colum2 ON Table1(Column2)
GO

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:

Statistics

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.

SELECT TOP 799 IDENTITY(INT, 1, 1) AS n INTO #Nums
FROM
master.dbo.syscolumns sc1
INSERT INTO Table1 (Column2)
SELECT 2 FROM #nums
DROP TABLE #nums
GO

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:

DBCC TRACEON (2371,-1) 

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:

Husk at skrive dig på maillisten nedenfor, så du ikke misser næste udgave af denne blogserie. Jeg lover dig kun at sende en mail, når der er nyt i denne serie.

Få besked om næste indlæg

Skriv dig gerne op til at modtage en mail, ved næste indlæg. Det kan du gøre nedenfor.



Marketing stuff

Our emails contain marketing stuff, so we need to give you some fine quality fine print: brianbonk will use the information you provide on this form to email you with updates and marketing. You can change your mind at any time by clicking the unsubscribe link in the footer of any email you receive from us, or by contacting us at help@brianbonk.dk. We use Mailchimp as our marketing platform. By checking the box to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing, and that we may process your information in accordance with these terms.

Følg mig på Instagram

Leave a Reply

Your email address will not be published. Required fields are marked *

en_USEnglish