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

Uge 6: Clustered Indexes

I sidste uge skrev jeg og introducerede Heap Tables, og her nævnte jeg at tabeller på en SQL server kan være to typer, Heap Tables og Clustered Table – sidstnævnte defineret ved at have et clustered index tilknyttet. Og i dag dykker jeg lidt mere ned i Clustered Indexes og skriver lidt om hvordan man bl.a. kan vælge den rigtige Clustered Key.

Under normale omstændigheder kan man meget ofte gå ud fra at en tabel allerede har et Clustered Index defineret, for hver gang der tilføjes en Primary Key Contraint på SQL server, så er denne contraint, som udgangspunkt opretholdt af et Unique Clustered Index. Det betyder at man i denne kolonne, eller flere kolonner hvis det er en sammensat nøgle, skal have unikke værdier for alle rækkerne. Hvis der ikke allerede eksisterer en Primary Key på tabellen, så kan man selv oprette et Clustered Index via CREATE CLUSTERED INDEX statement og her specificere sin(e) kolonne(r).

Ckustered Indexes

Så snart en tabel har et Clustered Index defineret, så er data fysisk sorteret ud fra de(n) definerede kolonne(r). Herunder kommer lidt fordele og ulemper ved at arbejde med Clustered Indexes på SQL serveren.

Fordele ved Clustered Indexes

En af de største fordele ved at anvende Clustered Indexes er at data er fysisk sorteret på det underliggende storage (disk) ud fra kolonnerne som defineret i indexet. Lidt sammenligneligt med en gammeldags telefonbog (den vi rev i stykker i sidste uge). Her er navnene sorteret efter efternavn. Clustered Tables afviger derfor betydeligt fra Heap Tables som ikke har nogen fysisk sortering på disk.

Det er en virkelig stor fordel at få fra Clustered Tables. Forestil dig at du skal søge efter en specifik række i tabellen, og ved at finde den række kan du søge (WHERE) direkte på den kolonne som er angivet i Clustered Index. Her vil SQL Serveren direkte anvende en execution plan med Clustered Index Seek operator. Seek operationer er ultra hurtige og effektive, fordi SQL serveren anvender B-tree stukturer til at fremfinde de relevante rækker.

I sidste uge skrev jeg lidt om kompleksisteten af Heap Table queries, og I skal ikke snydes denne gang – kompleksisteten af en Seek operation er defineret ved O(log N) – igen en referance til The Big O notation.

Det er det samme som når man søger i en telefonbog, du er garanteret at alle efternavne, som starter med A står først, og at du dermed kun behøver at kigge der. Du er fri for at scanne hele bogen for at finde alle med A – det er det samme SQL serveren kan med et Clustered Index og undgår dermed Data Page scanninger helt ud til leaf levels.

Så længe det definerede Clustered Index ikke har nogen index fragmentation, så vil man også opleve at få sequential I/O – dvs. at diskene kun flytter læsehovedet fremad under læsning. Det er langt hurtigere kun at læse fremad, i stedet for at læse begge veje – ligesom hvis vi selv skal læse en bog.

Indexfragmentation betyder at den logiske og fysiske rækkefølge på Data Pages på Leaf level ikke er den samme. Det kan løses ved at eksekvere Index Rebuild og/eller Index Reorganize. Jeg kommer tilbage til forskellen mellem disse to metoder i en senere gennemgang.

Om man er udsat for indexfragmentation kommer an på den/de valgte kolonner der er indeholdt i Clustered Key. Så længe man anvender en altid stigende værdi (såsom INT eller OrderDate), så vil nye rækker altid blive indsat sidst i Indexet. Vælger man en anden tilgang der ikke altid har stigende værdier ift seneste værdi, så bliver SQL serveren nødt til at sætte data ind midt i tabellen, muligvis lave et Page Break og tilmed tilføje en Forwarding Record for at ramme den næste Data Page korrekt.

Det kommer lidt mere i detaljer nedenfor.

Ulemper ved Clustered Index

Hvis data kun bliver indsat sidst i et Clustered Index, så kan det fremkalde et såkaldt Last Page Insert Latch Contention, fordi man har et enkelt hotspot i slutningen af Clustered Index, hvor risikoen for at flere simultane queries forsøger at skrive til det samme sted, som forsøgt illustreret nedenfor.

For at komme ud over denne fænomen, kan man vælge at anvende en randomiseret Clustered Key i Clustered Index, for så bliver rækkerne fra de forskellige INSERT statements distribureret over flere forskellige steder i det definerede Clustered Index. Men desværre introducerer det så en del Hard Page Splits, fordi SQL serveren bliver nødt til at allokere nye Data Pages midt i det samlede datasæt.

En Hard Page Split betyder at en eksisterende Data Page bliver delt i to for at gøre plads til det nye data midt i datasættet – det er utrolig meget tungere end det modsatte Soft Page Spilts, hvor en nye Data Page blot bliver allokeret i slutningen af tabellen og data bliver gemt herpå.

Som sideeffekt heraf, vil man også opleve en del index fragmentation hvis man anvender en randomiseret nøgle for sit Clustered Index. Igen vil det føre til Random I/O, som vil knække performanec på enhver traditionel rotations-harddisk ifm scan operationer, da læsehovedet skal skifte spor hele tiden rundt omkring på disk for at finde det samlede datasæt.

Opsummering – Clustered Index

Clustered Indexes skallerer utrolig godt, så længe man tænker sig om omkring den anvende nøgle for index. Clustered Index anvender B-tree strukturer til at søge og fremfinde det forespurgte data, og SQL serveren kan meget effektivt anvende data. Men husk at vælge den rigtige nøgle for dit Clustered Index, ellers så vil du opleve en kraftig nedgang i performance for din tabel. Det er nogen gange tidskrævende at finde den eller de rigtige kolonner til at være en del af det Clustered Index. Og når det valg er korrekt, vil man opleve en meget hurtig performance.

Næste uge skriver jeg videre på Indexes og tager fat i Non-Clustered Index – så hyg dig 7 dage endnu og så vender jeg tilbage med mere.

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 5: Heap Tables

Så ruller anden måned af SQL Performance Tuning, og denne måned bliver der skruet op for sværhedsgraden. Jeg kommer til at skrive rigtig meget om index, index og så lidt mere index på SQL Server. Her i første indlæg denne måned bliver om Heap Tables.

Selv om det kan være tungt og trættende, så er det det hele værd. Tro mig 🙂

Som skrevet, bliver der denne gang fokuseret på Heap Tables og hen over de næste 3 uger kommer jeg til at skrive om Clustered Index, Non-Clustered Index og generelle index-strategier på SQL Server databaser.

Men vi starter ved Heap Tables som er tabeller uden et Clustered Index. Hvis en tabel på SQL serveren har et Clustered Index så kaldes det for en Clustered Table og uden et sådan index, så er det en Heap Table.

heap tables

Det er også sådan Microsoft helt tilbage i starten af Azure’s spæde opstart, ville have os alle til at oprette tabeller på en Azure SQL Database, der skulle et Clustered Index på tabellen.

I en Heap Table er data ikke sorteret på nogen som helst måde. Al data ligger som en rodet masse – i uorden og som udstrukturerede rækker (nej, ikke som big-data terminologien). Bare i uorden og vilkårlige bunker. Når man tilgår data fra en Heap Table via et almindeligt SELECT statement, så vil SQL Serveren anvende en Table Scan operator i Execution Planen (mere om operators i Execution plans senere), når der heller ikke er et passende Non-Clustered Index tilstede. Der findes ikke en Table Seek operator. Det er vigtigt at huske.

Som jeg kommer til at skrive om i næste uge omkring Clustered Indexes, så bliver et sådan index tilgået via en Clustered Index Scan og via en Clustered Index Seek operator. På en Heap Table er der kun mulighed for at benytte sig af Table Scan. Og en Table Scan betyder at HELE tabellen gennemsøges for data og dermed bliver al data fra tabellen også læst. Så jo mere data der er i tabellen, jo længere tid vil en forespørgsel tage.

En Table Scan vil dermed altid være en O(n) operation (The Big O notation) – det kan ikke skallere efterhånden som tabellen bliver større og større.

Herunder gennemgår jeg fordele og ulemper ved at anvende Heap Tables på en SQL server.

Fordele ved Heap Tables

Heap Tables er meget meget hurtige – til at lave INSERT statements imod. Som skrevet ovenfor, så er data gemt som en stor bunke uden orden eller struktur. Ligesom hvis du tager en gammeldags telefonbog og river alle siderne ud af bogen og ligger dem foran dig – det er en Heap Table.

Det at tilføje nye rækker til denne bunke af data er meget hurtig – der skal blot findes en ny side frem (Data Page på 8kb), skrive de nye rækker på denne side og gemme den i bunken med alle de andre sider. Færdig – ingen sortering eller specifik placering er nødvendig.

Det er det samme for en SQL Server. En ny Data Page på de velkendte 8kb er allokeret og de nye rækker bliver skrevet til denne Data Page og denne er så assignet til den specifikke Heap Table. Done deal – det er en meget hurtig måde at gemme ny data på, for SQL serveren skal ikke garantere eller på anden vis tilsikre at data er sorteret eller struktureret. Det er det man kalder “ad letteste vej” i forsvaret.

Af denne årsag, kan det nogen gange være fordelagtigt at have nogen tabeller på SQL serveren som Heap Tables. Tabeller hvor der kan ske meget store og meget hurtige INSERT statements i. Et eks kunne være staging data til et Data Warehouse eller Log-tabeller til en OLTP løsning. Og de skal ikke bruges alle steder – det er kun i særlige tilfælde at de kan give mening at anvende.

Ulemper ved Heap Tables

Ud over fordelene ved at Heap Tables er super hurtige til at modtage data, så er der også en række ulemper ved dem. Disse ulemper bør du have med i din overvejelse, når du designer din database, og hver gang du opretter en Heap Table.

En af de første, som nogen af jer måske allerede har regnet ud, så har en Heap Table en negativ indvirkning på Random I/O på det underliggende storage system. Prøv at forestille dig en simpel forespørgsel (SELECT) mod en Heap Table. Hvis data ikke allerede er gemt i Buffer Pool’en bliver SQL serveren nødt til at starte fysiske indlæsninger af alle Data Pages fra disk. Disse indlæsninger er Random I/O da alle Data Pages er gemt “et eller andet sted” på disken, og ikke ved siden af hinanden.

Hvis man (stadig) bruger diske med skriver der roterer, så vil man opleve negativ performance på disk niveau’et, da Random I/O er langsomt, meget langsomt. Læsehovederne fra disken skal flytte sig rundt på skiverne hele tiden. SSD diske er meget bedre til at håndtere Random I/O, for der er det efterhånden ret ligegyldigt hvor data ligger på disken. SSD diske udjævner mere og mere den hastighedsnedgang der er ved Random I/O – og ja, der er stadig en forskel – selv på det hurtigste SSD diske i dag.

Et andet stort problem som man med sikkerhed vil have med store Heap Tables i en database, er mange Forwarding Records i Data Pages. Rækker som gemmes på en Heap Table kan i nogen tilfælde rykke sig til en anden Data Page – og i disse tilfælde så gemmer SQL serveren en henvisning til den nye Data Page på den originale Data Page, så data kan findes igen.

Når man så tilgår sine data fra en Heap Table, så læser SQL serveren stadig den originale Data Page og bliver herfra henvist til den nye ekstra Data Page som den skal finde frem og læse. Dette påvirker læsehastigheden gevaldigt.

Opsummering – Heap Tables

Heap Tables har deres berettigelse på en SQL server – i visse tilfælde. Anbefalingen er næsten altid at lave et Clustered Index på tabellen og dermed få en Clustered Table. Der hvor det giver mening at have en Heap Table, så skal alle fordele og ulemper overvejes.

Hvis du har fået blod på tanden til at læse mere om Heap Tables og Clustered Tables, så er Thomas Kejsers blogindlæg om det samme rigtig godt:

Næste uge kommer til at handle om Clustered Index på SQL serveren. Om hvordan man vælger det rigtige og om hvad man skal passe på omkring disse index.

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 4: Data Page begrænsninger

De første 4 uger er allerede gået og vi tager nu fat på nogen af de lidt tungere emner. Alt herfra bygger videre på de tidligere indlæg.

Har du misset nogen af dem – så kan du så dem herunder:

I dag bliver emnet omkring de begrænsninger der er implementeret omkring data pages. Mange har delte meninger om disse begræsninger, og måske du også har din. Skriv den gerne som kommentar nedenfor, når du har læst indlægget.

Data Pages er, som kendt fra uge 2, altid 8kb data og man kan gemme 8060 bytes data på dem. Og størrelsen på den enkelte række i datasættet afgør hvor mange rækker, der kan være på en enkelt data page. Når man arbejder med faste datalængder (Eks. CHAR, INT, DATETIME osv) så er der en fast begræsning på størrelsen af en række, som ikke kan overstige 8060 bytes (inkl. den interne overhead fra SQL serveren).

begræsninger

Begrænsninger – de gode af dem

Lad os tage et eksempel med en tabel med mindre end 8 kolonner. Her skal man lægge 7 bytes til for den interne overhead for SQL serveren. Og for hver 8 kolonner herefter skal man lægge 1 byte. Så ved 17 kolonner skal man have 9 bytes som overhead.

Hvis man forsøger at lave en tabel, som indeholder rækker med mere data end der kan være på en database (inkl. overhead), så returner SQL serveren en besked til dig – eks:

CREATE TABLE LargeTable
(
   Kolonne1 CHAR(5000),
   Kolonne2 CHAR(3000),
   Kolonne3 CHAR(54)
)

Som du kan se nedenfor, så kræver denne tabel 8061 bytes for hver række (5000+3000+54+7). Det er mere end der kan være på en Data Page.

Msg 1701, Level 16, State 1, Line 1
Creating or altering table ‘LargeTable’ failed because the minimum row size would be 8061, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

Hvis du laver en tabel med mere end 8 kolonner, så skal man huske at tillægge de ekstra bytes for hver 8 kolonner. Eks:

CREATE TABLE LargeTable
(
   Kolonne1 CHAR(1000) NOT NULL,
   Kolonne2 CHAR(1000) NOT NULL,
   Kolonne3 CHAR(1000) NOT NULL,
   Kolonne4 CHAR(1000) NOT NULL,
   Kolonne5 CHAR(1000) NOT NULL,
   Kolonne6 CHAR(1000) NOT NULL,
   Kolonne7 CHAR(1000) NOT NULL,
   Kolonne8 CHAR(1000) NOT NULL,
   Kolonne9 CHAR(53) NOT NULL
)

Igen giver dette mere end de 8060 bytes der er tilladt på en data page og samme fejl som tidligere kommer fra SQL serveren:

Msg 1701, Level 16, State 1, Line 1
Creating or altering table ‘LargeTable’ failed because the minimum row size would be 8061, including 8 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

Begræsninger – de knapt så gode

I det ovenstående afsnit, var de gode begrænsninger. De er gode, for SQL serveren viser og fortæller dig allerede ved oprettelse af tabellen, at der er noget galt.

Men der er også nogen som du vil komme til at synes er knapt så gode. For her vil SQL serveren fint tillade dig at oprette tabellen, men idet du begyndet at indsætte data i den, vil fejlene komme væltende. Nogen gange kan du være glad for at det lykkes, andre gange fejler det.

Problemet er datatyper med variabel længde, som VARCHAR. Når disse felter ikke passer på en enkelt data page alene, så forsøger SQL serveren at offloade dem til en såkaldt off-row location på andre data pages. Dette kales også for Row-Overflow Page (en ny page type til listen…). På den originale data page sættes nu en markør på 24 bytes som peger på den nye row-overflow page. I nogen tilfælde kan denne markør sammen med længden på de andre kolonner godt overskride de 8060 tilladte bytes.

Et eksempel på en sådan tabeldefinition kunne være:

CREATE TABLE LargeTable
(
   Kolonne1 CHAR(5000),
   Kolonne2 CHAR(3000),
   Kolonne3 CHAR(30),
   Kolonne4 VARCHAR(3000)
)

Bemærk den sidste kolonne er VARCHAR(3000). Her giver SQL serveren dig en advarsel om at tabellen godt kan oprettes, men at INSERT/UPDATE handlinger måske kan fejle.

Warning: The table “LargeTable” has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

Den følgende insert statement vil kunne lade sig gøre:

INSERT INTO LargeTable VALUES
(
   REPLICATE('x', 5000),
   REPLICATE('x', 3000),
   REPLICATE('x', 30),
   REPLICATE('x', 19)
)

Her er en enkelt række 8056 bytes lang. Og her vil SQL serveren fortsat gemme al data på den samme data page. Men prøv at se nedenstående eksempel:

INSERT INTO LargeTable VALUES
(
   REPLICATE('x', 5000),
   REPLICATE('x', 3000),
   REPLICATE('x', 30),
   REPLICATE('x', 3000)
)

Her skal SQL Serveren flytte data fra den 4 kolonne til en row-overflow data page, fordi de 3000 bytes, sammen med de første kolonner, ikke kan være på den samme data page. Så her efterlader SQL serveren en markør på data pagen på 245 bytes som peger på den ekstra data page. Rækken er derfor nu 8061 bytes lang (5000+3000+30+24+7 bytes).

Så nu fylder datarækken mere end 8060 bytes og INSERT statement fejler.

Dette er den dårlige begræsning på data pages, som først rammer dig når de endelige transaktioner foregår på databasen.

Det er en god ide at huske på denne begrænsning når du opretter tabeller på din database.

Opsummering – Data Page begræsninger

Når man designer tabeller på SQL serveren, bliver man virkelig nødt til at tænke over hvad man laver. Som gennemgået ovenfor, så er der nogen begræsninger som gør at man kan ramme en masse fejl når først dataflow og det hele ruller.

Nogen gange er man heldig at det kan køre igennem, og andre gange hjælper SQL serveren med en fejlmeddelelse.

Selv om man bliver advaret, har jeg set installationer, som stadig har disse fejl. Så husk det nu 🙂

Næste gang vender jeg HEAP tables og hvad det er for en størrelse på en SQL database.

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 3: Extent Management på SQL Server

Med uge 3 allerede godt i gang og sidste uges gennemgang af Data Pages, kommer her denne uges indlæg. Denne gang om Extent Management i SQL Server.

Har du misset det sidste indlæg, så kommer det herunder

Uge 2: Data Pages – grundlaget i SQL serveren

På et overordnet plan er Extents en samling af 8 pages på hver 8kb. Dermed er de dataelementer på 64kb. SQL server har to forskellige typer Extents.

  • Mixed Extents
  • Uniform Extents

Mixed og Uniform Extents

I en Mixed Extent er de 8 pages fra forskellige databaseobjekter, såsom tabeller og indexes. Det betyder også at en Mixed Extent kan pege på 8 forskellige objekter. Modsat en Uniform Extent, som kun indeholder pages fra et og samme databaseobjekt.

Det spændende er nu, hvordan SQL Serveren arbejder med denne forskel og hvorfor det er sådan.

Før årtusindeskiftet var diskkapacitet dyrt, meget dyrt. Målet var den gang at optimere så meget som muligt på anvendelsen af denne kapacitet. Derfor bliver de første 8 pages af nye tabeller eller indexes altid allokeret til Mixed Extents. Det betyder også at tabeller og indexes vokser i størrelse med 8kb ad gangen. Resultatet bliver at små tabeller forbliver meget små – forbruget af kapaciteten er så effektiv som muligt.

Så snart et databaseobjekt overstiger 8 pages og den 9. page skal allokeres, bliver der også allokeret en hel Extent til det objekt. Størrelsen stiger derfor fra 8kb til 72kb (8kb + 64kb). Med den 17 page stiger den til 136kb (8kb + 64kb + 64kb) osv.

Med de nuværende priser på diskkapacitet, så er det lidt hovedrystende at have dette designvalg, men set historisk har det været et vigtigt valg for at spare diskplads.

Extent Management

Extent Management

Men hvordan håndterer SQL Serveren så alle disse Extents? Forestil dig en database på 1 TB – det er en kæmpe bunke Extents og endnu flere pages. SQL Serveren anvender her 2 særlige pages, som igen (og som altid) er 8kb:

  • Global Allocation Map Pages (GAM)
  • Shared Global Allocation Map Pages (SGAM)

Uniform Extents er altid håndteret af GAM pages. SQL serveren bruger GAM pages 8.000 bytes (64.000 bits) til at danne et bitmap som repræsenterer en Uniform Extent. Hvis en bit er tændt bliver den specifikke Extent ikke brugt (den er dermed ledig), hvis den er slukket bliver den brugt. Det betyder at en GAM page kan håndtere 4 GB data (64.000 x 64 / 1024 / 1024). Derfor ses det også at GAM pages ligger vel spredt ud over datafilen til databasen – for hver 4 GB. Det samme er gældende for SGAM pages. Den håndterer også 4 GB data, men for Mixed Extents.

Når man indsætter en række i en tabel, finder SQL serveren, via SGAM pages, de Mixed Extents som har mindst en ledig data page. Hvis en tabel eller index er større end 64kb, så finder SQL Serveren den næste ledige Uniform Extent via gennemsøgning af GAM pages.

Når jeg senere kommer til at tale om TempDb, så kommer jeg også til at tale om seriøse performanceproblemer, når det workload man arbejder med opretter store mængder data i TempDb. Jeg kommer også med tips og tricks til hvordan man kan komme omkring det med konfiguration af TempDb (on-premise og managed instance i Azure).

Opsummering – Extent Management

Med denne gennemgang af Extent Management og de tidligere indlæg om Pages og motorrummet, begynder grundlaget for performancetuning at være på plads.

Har du fået blod på tanden til at vide mere om Extents på SQL Serveren, så er der lidt links nedenfor.

Næste gang skriver jeg lidt om Data Page Restrictions.

Husk at skrive dig på maillisten nedenfor, så du ikke misser næste udgave af denne blogserie.

en_USEnglish