Blog

Uge 15: Problemer med Cardinality Estimation

Så blev det fredag igen og de sidste to uger har jeg skrevet om statistics – i uge 13 om indledningen til statistics og i sidste uge 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.

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:

SELECT SalesAmount FROM Country
INNER JOIN Orders ON Country.ID = Orders.ID
WHERE Name = 'UK'

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

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:

CREATE STATISTICS Country_UK ON Country(ID)
WHERE Name = 'UK' 

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:

SELECT * FROM Products
WHERE Company = 'Nokia'
AND Product = 'Tesla Model 3'

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

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.

Uge 14: Statistics Inside

I denne uge tager jeg et spadestik dybere fra sidste uges Statistics indlæg og indledning og dermed åbne for motorhjelmen og se 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?

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.

Statistics

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:

DBCC SHOW_STATISTICS

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:

SELECT 
     1 / CAST(COUNT(DISTINCT ProductID) AS NUMERIC(18, 2))
FROM 
     Sales.SalesOrderDetail

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.

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.

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.

Uge 12: Parallelle Execution Planer

I denne uge stepper jeg lidt op af stigen i sværhedsgrad og vil gerne præsentere Parallelle Execution Planer til dig. En query med en Parallel Execution Plan er en query der bruger flere tråde (threads) på SQL serveren til at foretage de nødvendige operationer i den fysiske execution plan.

I første afsnit gennemgår jeg de mest gænge operatorer i parallelle execution planer og efterfølgende lidt flere detaljer om hvordan SQL serveren beslutter anvendelsen af disse.

Parallelle Operators

Mange gange oplever jeg en misforståelse omkring parallelle execution planer, at de anvender et givent sæt af tråde (f.eks 8 tråde på en 8 kerne server) til hele execution planen. Det er ikke helt sådan virkeligheden på SQL serveren er stykket sammen. SQL serveren tildeler mange såkaldte Worker tråde (worker threads) til alle operators som er bygget til parallel eksekvering. Dette betyder at en stor parallel execution plan kan bruge et meget stort antal tråde. SQL Serveren skelner mellem to typer operators i en parallel plan:

  • Parallelism-aware operators
    • En stor del af de traditionelle operators kan også arbejde med flere tråde, de er parallelism-aware – Index Scan, Index Seek, Nested Loop, Hash Join og Sort – for bare at nævne nogen af dem
  • Exchange operators
    • Disse operators bliver brugt til at fordele og sammensætte rækker mellem de forskellige tråde i en parallel execution plan

SQL Serveren har implementeret 3 Exchange Operators:

  • Distribute Streams: Bliver brugt til at fordele en enkelt tråds rækker ud på flere tråde i en såkalt Multi-threaded region i den parallelle execution plan
  • Repartition Streams: Bliver brugt til at gen-fordele rækkerne mellem trådene (hvis for eks den førliggende operator er Parallel Hash Join)
  • Gather Streams: Bliver brugt til at samle rækker fra flere tråde til en enkelt tråd – fra multi-threaded region til single-threaded region i execution planen

Når man ser en parallel execution plan, vil man altid se en kombination af begge typer operators. Enhver execution plan skal producere et single-threaded output, derfor vil man altid finde en Gather Streams omkring slutningen af en parallel execution plan (altså helt til venstre):

Parallelle Execution Planer

Om Optimizeren genererer en parallel execution plan eller ej, afhænger også af indholdet i den afsendte query – såsom:

  • T-SQL og SQLCLR UDFs (dog med undtagelse af de UDFs som kan compiles til Inline UDFs)
  • Indbyggede funktioner som OBJECT_ID(), ERROR_NUMBER(), @@TRANCOUNT

Der er også flere query elementer som forcerer en single-treaded region i den parallele execution plan_

  • System Table Scans
  • Sequence Funktioner
  • Backward Scans
  • Recursive Queries
  • Table Values Functions (igen med undtagelse af dem der kan inlines)
  • TOP operator

Og jo færre single-threaded regioner i en parallel execution plan, jo hurtigere vil queryen levere data. Prøv at huske på det næste gang du synes din query er for langsom.

Hvornår bliver en query parallel?

Enhver execution plan får en såkald Cost Factor tildelt fra SQL serveren. Denne Cost Factor er en værdi i form af et tal, der fortæller SQL serveren hvor “dyr” en execution plan er. Jo højere værdi, jo dyrere (i ressourcer) er det at ekekvere den specifikke execution plan.

På SQL serveren er der en konfiguration kaldet Cost-Threshold for Parallelism, som definerer græsen i cost factoren hvorved Optimizeren begynder at overveje parallelle execution planer. Default er 5 (hvilket er meget lavt) som betyder at en Cost Factor højere end 5 vil give optimizeren mulighed for at lave en parallel execution plan, så længe parallellisering er muligt.

Når en parallel execution plan bliver compilet af Query Optimizeren vil konfigurationen af Max Degree of Parallelism (MAXDOP) definere hvor mange tråde, der kan blive anvendt i hver enkelt parallelle operator i execution planen.

Som nævnt tidligere vil enhver operator i den parallelle execution plan køre med flere tråde, og ikke kun i den samlede plan. Tråde kan blive delt imellem operators når de ikke længere bruges, og låses derfor ikke til en enkelt operator i execution planen.

Som standard er MAXDOP sat til 0 ved installation. Derfor vil SQL serveren per default parallellisere en query på alle kerner som er assignet til SQL serveren. Dette kan give performance problemer hvis man arbejder i et NUMA system (Non Uniform Memory Accesss) – typisk eksempel er en viruel server. Derfor er det god skik at sætte MAXDOP til antallet af NUMA noder i systemet (inkl. kerner/NUMA noder fra Hyperthreading).

Opsummering – Parallelle Execution Planer

Dette indlæg har været et af de mere tunge af slagsen, men også meget interessant (synes jeg selv…). Jeg er kommet omkring de forskelle operatorer i en parallel execution plan og skrevet lidt om hvordan SQL serveren behandler disse.

Har du fået blod på tanden og vil læse mere om parallelle execution planer og deres arbejde på SQL serveren er herunder lidt links til nogen gode indlæg:

Og en note – vi er allerede 3 måneder inden i denne serie af blogindlæg – og stadig kun halvvejs. Jeg håber I er med på resten af serien.

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.

Uge 11: Recompilations

I denne uge tager jeg fat på endnu en del af området omkring Execution Plans – nemlig Recompilations.

En Recompilation sker på SQL serveren, når man eksekverer en query (SQL serveren er i gang med at trække data) og en anden aktivitet på serveren har gjort resten af Execution Planen ikke kan forløbe. I disse tilfælde vil SQL serveren stoppe og lave en fuld ny Execution Plan og dermed trække en del ekstra CPU kraft, som så bliver taget fra andre processer på SQL serveren.

Hvad er en Recompilation så?

Først skal jeg lige være helt klar i sproget her – der er forskel på en Recompilation og en Compilation. I uge 9 skrev jeg om compilations af Execution Plans på SQL serveren. En compilation sker altid på SQL serveren når Query Optimizeren oversætter en query til en fysisk Execution Plan. Dette er altså en compilation FØR query eksekveringen starter.

En Recompilation er en proces, der sker UNDER eksekveringen af en query. Derfor laver SQL serveren en delvis Recompile for at tilsikre at det rigtige data bliver behandlet. Et eksempel kan være at et index, som bliver anvendt i execution planen, er blevet slettet. Dette vil føre til forkerte resultater på det behandlede data og SQL serveren trigger derfor en Recompilation af den del af execution planen som indeholder dette index.

SQL serveren trigger en Recompilation baseret på to dele:

  • Correctness-based Recompilations
  • Optimality-based Recompilations

I den første del – Correctness-based Recompilations – sker når en execution plan ikke længere er korrekt. Et schema er måske ændret (nyt index, drop af statistics) eller når der er sket en ændring i en SET operation. Her bliver der triggeret en Recompilation for at tilsikre at execution planen forbliver korrekt.

Recompilations
Eksempel på visning af Recompilation fra SQL Trace

Den anden del – Optimality-based Recompilations – sker det fordi statistics (kommer der mere som senere) er blevet ændret. SQL serveren har selv opdateret statistics, eller en slutbruger har startet processen for at opdatere dem. I dette scenarie kunne det være at Bookmark Lookup nu er over Tipping Point og SQL serveren skal derfor bruge en ny execution plan for at anvende en fuld table scan eller et Clustered Index Scan.

Et lidt dybere dyk ned i scenarier der trigger Recompilations under en query og dennes eksekvering – Temp tables…

Temp Tables

Ja, når du arbejder med temp tabeller på SQL serveren så forcerer du en Recompilation. Et eksempel på en stored procedure:

create procedure DoWork as
begin
   create table #TempTable
   (
      id int identity(1, 1) primary key,
      Fornavn char(4000),
      Efternavn char(4000)
   )
   insert into #TempTable (Fornavn, Efternavn)
   select top 1000 name, name from master.dbo.syscolumns
 
   select * from #TempTable
end
go

Den ovenstående stored procedure opretter en temp tabel, insætter nogen rækker i den og slutteligt trækker alle rækker ud fra tabellel. En forholdsvis simpel øvelse – men den trigger to Recompilations:

  • Den første Recompliation sker på baggrund af den oprettede temp tabel. Ved at oprette en ny tabel (om den er temp eller ej) ændres database schema. Dette trigger en Correctness-based Recompilation
  • Den anden Recompilation sker på baggrund af det sidste select statement. Der er netop blevet indsat rækker i tabellen og statistics er dermed netop også blevet ajourført/opdateret. Dette trigger en Optimality-based Recompilation.

Men hvordan kommer man så uden om disse Recompilations? Man kan bruge Table variabler i stedet for (dem med ‘@’ foran). Med en tabel variabel ændrer man ikke ved database schema. Det er nemlig kun en variabel som ikke har statistics påhæftet. Dermed vil begge ovenstående Recompilations være fjernet. Det kan naturligvis introducere en række andre performance problemer med tabel variabler: Der er ikke nogen statistics og SQL serveren estimerer dermed ALTID kun 1 række, så Cardinality Estimation kan blive helt forskruet og forkert.

Af denne årsag er tabel variabler brugbare i specifikke scenarier på SQL serveren. Når der er små mængder af data. Hvis det er større datamængder, bør man stadig anvende en temp tabel da den så har de nødvendige statistics og man kan lave index på dem. Bagsiden her er de Recompilations som det giver.

Vil du læse mere om Recompilations, kan du se nogen gode links nedenfor:

Opsummering – Recompilations

Så i dette indlæg har jeg forsøgt at hjælpe dig godt i gang med at forstå Recompilations på SQL serveren og hvorfor de kan være farlige. Man kan ikke komme uden om dem, da de skal til for at SQL serveren kan arbejde korrekt med data.

En tilgang ved at være meget opmærksom på at vælge mellem # og @ tabeller (temp og variabler) kan gøre det store udslag i den oplevede performance.

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.

Uge 10: Plan Caching

I denne uge vil jeg, som lovet i sidste uge, forsøge at hjælpe dig godt i gang med at forstå Plan Caching og de afledte effekter den mekanisme har på SQL serverens performance. Fra sidste uge kan du måske huske at enhver logisk query mod SQL serveren kræver en fysisk Execution Plan. Denne Execution Plan bliver gemt i Plan Cache for fremtidigt brug.

Ad-hoc SQL queries

Hver gang man eksekverer en ad-hoc SQL query mod SQL serveren, vil SQL serveren lave en unik Execution Plan for hver unik query. Men hvad er en unik query?

SQL serveren beregner en hash værdi af hele SQL querien (inkl. evt. hard-kodede parametre og disses værdier), og bruger denne hash værdi til at finde eksisterende Execution Plans i Plan Cache. Hvis en eksisterende plan eksisterer, vil denne blive brugt, ellers vil en ny plan blive udarbejdet og denne bliver så gemt i Plan Cache. Et eksempel:

select * from Sales.SalesOrderHeader
where CustomerID = 11000

GO

select * from Sales.SalesOrderHeader
where CustomerID = 30052

GO

select * from Sales.SalesOrderHeader
where CustomerID = 11223

GO

De 3 ovenstående queries vil hver generere en ny Execution Plan, fordi der er hard-kodede parametre. Og heraf bliver hash værdien for de 3 queries unik. Den afledte effekt er at der nu eksisterer 3 execution planer til næsten identiske queries. Dette problem bliver kaldt Plan Cache Pollution.

Plan Cachen bliver forurenet med nye Execution Plans som er svære at genbruge og der går værdifuld hukommelse til spilde som ellers kunne bruges på andre processser i SQL serveren. Målet er at have en så høj re-use count på Execution Plans fra Plan Cache.

Plan Stability

En måde at optimere genbrugen af Execution Plans fra Plan Cache, er at lave parameter værdier i SQL queries (med @parameternavn) eller, og endnu bedre, lave stored procedures som skal returnere data. På den måde kan SQL serveren genbruge Execution Plans fra Plan Cache meget effektivt.

Lad mig tage samme eksempel query som fra uge 8 som var den der udførte et Bookmark Lookup:

Som kendt fra uge 8, så giver Bookmark Lookup kun mening hvis man skal bruge få rækker fra sin tabel. Hvis man kommer over Tipping Point, så er det mere cost effektivt at lave hele tabel- eller clustered index scan. Men hvis SQL serveren genbruger en execution plan fra Plan Cache, så bliver denne beslutning slet ikke overvejet længere – SQL serveren genbruger blindt de gemte execution plans, også selv om performance bliver utrolig langsom.

Plan caching

Fra ovenstående eksempel, kan vi se at SQL serveren blindt genbruger en cached execution plan med Bookmark Lookup. Prøv at se forskellen på Estimated Number of Rows og Actual Number of Rows. SQL serveren genbruger en execution plan hvor der var 1 række i resultatsættet, mens der reelt i denne query er 1.499 rækker.

Årsan til dette er at der ikke er Plan Stability. Baseret på det estimerede antal rækker, anvender SQL serveren Bookmark Lookup i stedet for det mere effektive Table/Clustered Index Scan (hvis Tipping Point er nået). Dette er et ret udbredt og generelt problem for mange installationer af SQL serveren og disses performance problemer.

Men hvordan kan man så undgå disse Bookmark Lookups og genbrug af execution plans fra Plan Cache? Det kan du måske allerede gætte: Ved at lave et covering Non-Clustered Index som passer med den query der eksekveres. På den måde får du altid den bedste execution plan for din query og uanset hvordan du baserer dine parametre, vil SQL serveren altid svare hurtigt og effektivt. Men pas på med at lave for mange indexes for at tilgodese alle queries. Det er ret så vigtigt!

Opsummering – Plan Caching

En kort introduktion til Plan Cache og hvilke fordele og ulemper dette kan medføre på SQL serveren. Det er et tve-ægget sværd at arbejde med – på den ene side er Plan Cache et meget effektivt værktøj, da man kan genbruge allerede eksisterende execution plans, på den anden side et meget farligt værktøj, da man kan komme til at genbruge allerede eksisterende execution plans…

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.

Uge 9: Execution Plans

Denne uge er også starten på 3. måned i denne følgeton. Og i dag skriver jeg om Exectution Plans i SQL Server. Execution Plans er det vigtigste værktøj for at forstå og arbejde med Performange Tuning på SQL Serveren, og for at kunne lave effektive ændringer i queries. Så i dag starter jeg ud med en introduktion til Execution Plans og hvordan man kan fortolke og læse dem.

Hvorfor Execution Plans?

Mange har spurgt mig hvorfor der overhovedet er Execution Plans i SQL serveren. Eks. “Vi har en query og hvorfor eksekverer SQL serveren ikke selv denne?”

For at svare på de spørgsmål, bliver jeg nødt til at starte med en lidt tætte gennemgang af SQL som kodesprog (og ja, nogen vil mene det ikke er et kodesprog, men den snak kan vi tage en anden dag 😀). SQL sproget (og også T-SQL på SQL serveren) er et declarative language (deklarativt sprog). Man beskriver logisk hvilken data man skal bruge fra databasen (SELECT) eller hvilken data der skal ændres (UPDATE, INSERT, DELETE). Et eksempel på nedenstående query:

select a.*, b.* from a
inner join b on a.id = b.id
where a.field = 'EnVærdi'

Med denne query fortæller man databasen og den underliggende engine (se uge 1) at:

  • Jeg vil gerne have al data fra tabel a og b
  • Begge tabeller skal sættes sammen via id-kolonnen
  • Rækkerne fra tabel a skal filtreres på ‘field’ ud fra ‘EnVærdi’

Vi beskriver kun, gennem SQL kode, hvordan resultatet af det data som querien henter skal se ud. Med en SQL query, kan du kun specificere resultatet – ikke andet. Man kan ikke fortælle SQL serveren hvordan den skal tilgå data fysisk/logisk og hvordan den ellers skal eksekvere og behandle det data, der skal arbejdes med.

Man tilgår med andre ord altid data på SQL serveren via et logisk lag, beskrivende hvilke elementer af data, der skal anvendes eller ændres. Men SQL serveren skal bruge en fysisk execution plan som i steps beskriver hvordan data skal hentes eller ændres. Execution Plan er en form for strategi udvalgt og planlagt af Query Optimizeren til at hente og behandle data.

En analogi: Forestil dig at fuldføre en rejse mellem to byer – fra Kolding til Odense, så har du bestemt dig for det logiske udtryk. Dette logiske udtryk, kan have mange forskellige fysiske fortolkninger:

  • Man kan gå
  • Køre på cykel
  • Tage toget/bilen/rulleskøjter

Det er underordnet hvilken fortolkning man anvender her, uanset hvilken der vælges er der igen flere forskellige fortolktninger af den del. Mulighederne eksploderer. Opgaven er at finde den mulighed som koster mindst og samtidig kræver mindst energi.

Vi vil som mennesker nok vælge bilen, men den koster jo mere end at gå.

Det samme problem har Query Optimizeren i SQL serveren. Query Optimizeren skal vælge den Execution Plan som kræver mindst arbejde, men som stadig tilgodeser kravene fra querien. Udfordringen for Query Optimizeren er at finde en Execution Plan der er god nok fra det overordnede Search Space – og dette Search Space kan være ret så stort hvis der er mange tabeller og indexes i den specifikke query.

Hvordan man læser en Execution Plan

Første gang man overhovedet kigger på en Execution Plan kan man godt blive blæst bagover og synes de er underlige. Det kan være svært ved første øjekast at forstå og fortolke dem korrekt. Et eks:

Execution Plans

Som du kan se af ovenstående, så indeholder alle Execution Plans flere trin, også kaldet Operators på SQL serveren. Disse operators er row-by-row fra SQL serveren, hvilket betyder at rækker fra data flyder fra højre mod venstre i Execution Plan.

I ovenstående tilfælde starter SQL serveren med at ekekvere det første trin Index Seek (NonClustered) operator på tabellen CustomerAddress. Hver enkelt række fra dette skan løber videre til Nested Loop operator, som kommer efter et scan. Og for hver række heri, foretager SQL serveren en Key Lookup (Clustered) operator (Bookmark Lookup, måske du kan huske den) på den samme tabel. Hvis der er en række der matcher, bliver denne række sendt videre til SELECT operator, som er den der endeligt returnerer resultatet tilbage til brugeren.

Som du måske allerede nu kan se, er det lettere at læse en Execution Plan fra højre mod venstre, fordi det er også denne vej rækker med data flyder. En Execution Plan er stadig fysisk eksekveret fra venstre mod højre. Vi læser den blot fra højre mod venstre for at følge data.

Jeg håber denne tilgang giver dig lige mere blod på tanden til at forsøge at forstå og læse Execution Plans. Hvis du vil mere i dybden med operators kan du læse mere om dem her:

Opsummering – Execution Plans

Jeg har skrevet lidt om og introduceret Execution Plans til dig i denne udgave af SQL Performance Tuning, og hvordan de kan blive læst og fortolktet. Vi kommukerer altid med SQL serveren via et logisk lag; vi beskriver det data vil skal bruge eller vil ændre.

Query Optimizeren har til opgave at bygge en god nok Execution Plan til disse queries. Og Execution Plan er den der slutteligt beskriver de fysiske trin der skal udføres på SQL serveren for at tilgodese queries. I næste uge kommer der mere om Execution Plans og Plan Caching – og hvorfor det kan være både godt og skidt.

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.

Uge 8: Covering Indexes og Tipping Point

Med dette indlæg er vi allerede 2 måneder inde i forløbet og denne gang vil jeg skrive lidt mere om Non-Clustered Index og nogen flere negative følger der kan komme ved at anvende dem. Herunder Covering Indexes og Tipping Point.

I sidste uge skrev jeg lidt om Bookmark Lookup og at de kan være farlige for performance. Det var den mekanisme der skal lave en Table Scan hvis ikke alle kolonnerne fra query er med i index. Så hvis man vil undgå Bookmark Lookups, skal vi til at lave såkaldte Convering Indexes.

Covering Indexes

Et Covering Index er et traditionelt Non-Clustered Index på SQL Serveren. Forskellen ligger i at et Covering Index indholder alle de nødvendige kolonner til en given query. Dette betyder at Bookmark Lookup ikke er nødvendige.

Et eksempel – nedenstående query vil lave et Bookmark Lookup da postnummeret ikke er den af at det definerede Non-Clustered Index.

create nonclustered index nci_dbo_adresse on dbo.adresse (
regionId, adresseId
)
GO

select
   AdresseId
  ,Postnummer
from dbo.Adresse
where regionId = 1

Ovenstående query laver Bookmark Lookup til tabellen dbo.Adresse for at finde data til kolonnen Postnummer.

Det kan vi komme omkring ved, ved at tilpasse det tidligere Non-Clustered Index som nedenfor.

create nonclustered index nci_dbo_adresse on dbo.adresse (
regionId, adresseId
)
include (Postnummer)
GO

Når den samme query eksekveres igen, vil du se at Execution Plan ikke længere har en Bookmark Lookup og at SQL serveren anvender et Index Seek (NonClustered). Logical Reads vil også være kraftigt mindre end ved med det første index. Det er ret vilde performance-optimeringer, der er sket med en lille ændring.

Husk dog på, at det er ikke alle Bookmark Lookups der er dårlige for performance. Målet er ikke at eliminere alle Bookmark Lookups – kun dem der indvirker negativt på performance.

Tipping Point

I nogen tilfælde, når SQL serveren skal lave en Bookmark Lookup for en specifik query, så kan det ske at SQL serveren vælger at en Bookmark Lookup er for dyr (i forhold til Logical Reads). I disse tilfælde vil SQL serveren lave en hel Table Scan og blot udelade alle rækker som ikke passer med query’ens filter. Det punkt hvor det bliver valgt hedder Tipping Point på SQL serveren. Tipping Point definerer om SQL serveren laver en Bookmark Lookup eller en fuld Table Scan.

Covering Indexes

Tipping Point ligger et sted mellem 1/4 og 1/3 af det totale antal Data Pages som query’en skal læse. Det har ikke noget med antallet af rækker der skal læses. Fordi, som du måske husker, så er det størrelsen på hver række, der definerer hvor mange rækker, der kan være på de 8 kb Data Pages.

Hvis jeg for eksempel har en tabel på SQL serveren med hver række fylder 400 bytes. Så kan der være 20 rækker på en Data Page. Hvis der også er et Non-Clustered Index på kundeId kolonnen. Tabellen indeholder måsle 2000 rækker. Så vil nedenstående query lave et Bookmark Lookup:

select * from kunder
where kundeid < 1062

Hvis jeg så retter min query til at have bare en række mere med, så rammer jeg Tipping Point og SQL serveren scanner hele tabellen:

select * from kunder
where kundeid < 1063

To næsten ens queries, men to vidt forskellige Execution Plans. Dette kan lede til store problemer, da man så ikke længere har en stabil Execution Plan for sine queries (også kaldet Plan Stability).

Opsummering – Covering Indexes og Tipping Point

Jeg har forsøgt her at ramme lidt dybere ned i Non-Clustered Index og Tipping Point mekanismerne ved at kigge på Covering Indexes. De næste 4 uger kommer til at være meget mere om Indexes og de “magiske” ting der kan ske med disse.

Og det kan godt være at Indexes kan optimere dine queries til at levere data hurtigt når der skal læses, men det kan så give en negativ indvirkning på performance, når der skal skrives til tabellerne. Et index skal vedligeholdes efter hver endt INSERT, UPDATE og DELETE transaktion. Så husk at balancere indexeringstrategien på både læse og skrive operationer.

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.

Uge 7: Non-Clustered Indexes

I sidste uge var emnet omkring Clustered Indexes på SQL Server. Og her blev der beskrevet at data fysisk sorteres på disk via de definerede kolonner, når man implementerer et Clustered Index. I tillæg hertil kan man tilføje flere Non-Clustered Indexes på SQL server.

Et Non-Clustered Index er et sekundært index som kan defineres på en eller flere kolonner af tabellen. Igen kan et Non-Clustered Index sammenlignes med en bog. Men denne gang er bogen som et reference-index til hele leksikonnet. Lidt ligesom der var reference-index til Lademanns leksikon (til dem af jer der kender det og kan huske det). Indexet findes også for nogen bagerst i bøgerne – sammenligningen er den samme. Når man skal finde et specifikt navn eller ord, slår man op i index og bliver henvist til en eller flere sider.

Indexet fra bogen giver dig en “lookup value”, side-nummeret, hvor du kan finde flere detaljer. Det samme gælder for SQL serverens Non-Clustered Index og dens anvendelse i Execution Plan. Her returnerer SQL Serveren de Data Pages på Leaf niveau, som indeholder det efterspurgte data. SQL Serveren fremfinder så de specifikke Data Pages og returnerer de efterspurgte kolonner og rækker. Hvis kolonnerne ikke er direkte en del af det definerede Non-Clustered Index, så kalder man det for et Bookmark Lookup.

Bookmark Lookups

Hver gang SQL serveren anvender et Non-Clustered Index i Execution Planen og man efterspørger en kolonne som ikke er en del af indexet, så bliver SQL Serveren nødt til at lave et Bookmark Lookup.

Nedenfor vises et eksempel på en sådan Bookmark Lookup.

non-clustered index

Af ovenstående kan læses at SQL serveren laver en Non-Clustered Index seek på Address tabellen. Hertil laver SQL serveren en anden søgning for at finde resten af de adspurgte kolonner via Key Lookup (Clustered) på den underliggende Clustered Table. Umiddelbart er det ret så smart at kunne dette, men det kan også være meget farligt og tungt at arbejde med, hvis man ikke tager sine forholdsregler.

Bookmark Lookups kan lede til Deadlocks og performance vil være tydelig dårlig hvis tabellen har dårlige eller out-of-date statistics eller der er sker Parameter Sniffing (to nye begreber som jeg kommer tilbage til en senere uge).

Bookmark Lookups kan kun ske i samarbejde med Non-Clustered Index, og næste gang graver jeg lidt mere i årsagen og hvordan vi kan komme omkring denne performance nedgang.

Afhængigheder til Clustered Key

Som jeg har skrevet tidligere, så gemmer SQL serveren Leaf Level referencer i Non-Clustered Index som peger på Data Pages i enten Clusted Table eller Heap Table.

Når man definerer et Non-Clustered Index på en Heap Table, så vil lookup værdien til referencen være Row-Identifier. Dette er en 8 bit lang værdi, som gemmer Page nummeret (4 bytes), file id (2 bytes) og slot nummeret (2 bytes) som tilsammen henviser til den fysiske placering på disk hvor data er gemt.

Hvis man definerer et Non-Clustered Index på en Clustered Table, så anvender SQL serveren den definerede Index nøgle (Clustered Key) til at finde lookup værdien. Dette betyder at din nøje udtænkte Clustered Key er en del af alle Non-Clustered Index. Der er dermed en stor afhængighed mellem Clustered og Non-Clustered Index. Og Clustered Key er dermed også det mest redundante element i din tabel. Det er derfor man skal vælge sine kolonner med omhu, og man bør have denne liste med i tankerne når man laver sin Clustered Key – den bør være:

  • Unik
  • Smal
  • Statisk

Opsummering – Non-Clustered Index

Non-Clustered Index er et centralt element i optimering af queries. Og hvis man kommer til at introducere Bookmark Lookups i sit design, kan man også introducere side-effekter ifm. performance på databasen.

I næste uge kommer jeg til at skrive om Covering Non-Clustered Indexes som, ofte, kan eliminere Bookmark Lookups. Og her introducerer jeg også Tipping Point, som definerer som SQL serveren anvender et Non-Clustered Index eller ej.

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.

en_USEnglish