Performance Tuning

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…

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