Performance Tuning

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…

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