Performance Tuning

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.

Få besked om næste indlæg

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



Marketing stuff

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

Følg mig på Instagram

Leave a Reply

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

en_USEnglish