Neem contact op
Bel mij terug

Juist gebruik van datatypes in SQL Server

Door Reitse Eskens, BI Consultant en SQL Specialist

Datatypes. Een onderwerp waar veel mensen wel een mening over hebben en waar veel discussies over gevoerd zijn, worden en blijven. Uiteraard doe ik daar met plezier aan mee. In dit blog laat ik via een simpel voorbeeld zien hoe het gebruik van een onjuist datatype in een tabel tot performance issues kan gaan leiden.

SQL Server kent een vrij lange lijst met datatypes, als u ze allemaal eens wilt bekijken:

select name,
       max_length,
       precision,
       scale,
       collation_name,
       is_nullable
from sys.types
where is_user_defined = 0

Deze query levert een lijst op met alle datatypes die beschikbaar zijn in uw database. Als u het where-filter op 1 zet kunt u zien of iemand een eigen datatype toegevoegd heeft aan de database.

Maar dat is niet waar ik het over wilde hebben.

Vooraf

Mijn demo richt zich straks op een aantal datatypes die ik hier kort weergeef.

  • Char: character. dit datatype heeft een vaste lengte, ongeacht de vulling die meegegeven wordt;
  • Decimal: dit datatype past zich redelijk aan de inhoud aan, maar verwacht grote getallen;
  • Int: afkorting van integer en deze past zich volledig aan de inhoud aan;
  • Varchar: variable character, deze groeit met de inhoud mee en heeft een maximum lengte.

Tabellen en vulling

Voor mijn demo ga ik gebruik maken van twee tabellen:

create table weird
(cola char(2200),
       colb decimal(38,0),
       colc char(200)
)

create table expect
(cola varchar(2200),
       colb int,
       colc int
)

De tabellen bestaan uit drie kolommen waarbij de tabel weird datatypes heeft die totaal niet aansluiten bij de data, tabel expect sluit veel meer aan bij de data. Welke data ga ik laden? Hier een voorbeeld record:

Het is een tekst uit sys.messages tabel met twee kolommen die een numerieke waarde bevatten.

Beide tabellen ga ik met 10.000 records vullen:

insert into weird select top 1 text, message_id, severity  from sys.messages

go 10000

insert into expect select top 1 text, message_id, severity  from sys.messages

go 10000

De query is vrijwel identiek, alleen de doeltabel is anders.

Vullen van de tabellen

Eerlijk gezegd had ik verwacht dat het vullen van de tabellen al een verschil op zou leveren, maar dat maakte alleen in milliseconden uit. Niet significant en daarmee niet interessant. Echter, wat wel interessant is, is hoeveel ruimte er nodig is voor de tabellen. Immers hebben we behoorlijke afwijkingen in de datatypes.

Tabel expect:

Tabel weird:

1,3 MB versus 39 MB, een factor 30 verschil. Dit is de ruimte die de tabellen op disk innemen.

Ophalen van data

Maar data op een disk is niet heel interessant, het wordt pas interessant als we de data op gaan halen. Om dat te doen voeren we de volgende query’s uit, waarbij het actual execution plan (door kenners omgedoopt tot execution plan with runtime statistics) actief is.

set statistics time,io on

select top 1000 * from weird
order by colb desc

select top 1000 * from expect
order by colb desc

Het set statement waar we mee beginnen zorgt ervoor dat ik terug kan zien hoeveel tijd de database bezig geweest is met de processor en hoeveel disk IO nodig geweest is om de data op te halen.

Als eerste kijken we naar de resultaten van dat set statement:

Met rood heb ik de verschillen gemarkeerd. De bovenste query gaat naar de weird table, de onderste naar de expect. En zonder al te diep in te gaan op wat alles betekent is zichtbaar dat de bovenste query veel meer reads genereerd dan de onderste. En minder reads is simpelweg sneller.

Dan kijken we naar het execution plan, want ook daarin valt direct iets op:

De bovenste query (op de weird tabel) heeft een geel uitroepteken, de onderste niet, daarnaast heeft de bovenste query 85% van de totaaltijd nodig gehad, de onderste 15%.

Wat is er aan de hand?

Wat gebeurt er. De eerste hint zit in de gele waarschuwing. Als we daar boven gaan hangen zien we het volgende:

De waarschuwing is dat er gebruik gemaakt moest worden van de TempDB. En dat gebeurt als er onvoldoende geheugen beschikbaar is voor de query. Maar hoeveel geheugen was er dan tekort? Er is 30.600 kb (ongeveer 30 MB) aan geheugen gereserveerd. En dat was te weinig, want er zijn 3334 datapages naar TempDB geschreven en weer teruggelezen. Elke datapage is 8 kb, 8*3334 = 26.672 kb of 26 MB. Bij het laden en sorteren van de data is er een onderschatting gemaakt van de data van bijna 50%. En dit is een kleine tabel, stelt u zich voor dat er een echt grote tabel gebruikt wordt.

Hoe is het verhaal dan aan de ‘goede’ kant? Daar valt het kwartje de andere kant op. Als we kijken naar het gebruik van het geheugen dan zien we het volgende:

Er is 14 MB aan geheugen toegekend maar er was slechts 1,6 MB nodig. Een overschatting met een factor 10. Ook dit is niet heel geweldig, maar omdat alles in het geheugen past is het proces snel klaar en valt de last mee.

Oplossingen

Hoe voorkom je nu onnodig gebruik van geheugen. Als eerste, zorg ervoor dat de datatypes passen bij wat er in de tabel geladen wordt. Bepaal daarbij ook of een datatype een functionele noodzaak heeft, bijvoorbeeld voor een applicatie die de data verder gaat verwerken.

In mijn demo lees ik data uit een heap (een tabel zonder enige vorm van indexen). Wat gebeurt er nu als ik er een geclusterde index toevoeg aan de tabellen en dezelfde query uitvoer? Dan krijg ik bij de weird tabel een fout:

Doordat cola zo enorm groot is past die niet in een geclusterde index.

Daarom voor de weird tabel een alternatief:

create clustered index cix_weird_colb_colc on weird
( colb asc, colc asc)

Voor de expect tabel heb ik dat issue niet:

create clustered index ncix_expect_all_columns on expect
(colb asc, cola asc, colc asc)

Deze index wordt keurig aangemaakt. Voeren we dezelfde select query opnieuw uit, dan zien we het volgende:

Beide query’s geven hetzelfde plan en geen waarschuwingen meer. Maar, hoe zit het met de geheugentoewijzingen? Die zijn weg, de query kan buiten het geheugen afgehandeld worden. En dat is een hele mooie winst! Maar, waarom dan nog die verschillen?

De weird tabel moet bijna 20 keer meer leesacties uitvoeren op de disk om de data bij elkaar te halen. Daar hebben we winst geboekt, maar de finale winst zit echt in het aanpassen van de datatypes.

Tot slot

Dit voorbeeld is jammer genoeg niet een voorbeeld wat in het wild veel voorkomt, er zijn vaak veel meer tabellen bij betrokken en een aanpassing is ook niet zomaar gedaan. Maar wilt u verder advies of meer weten? We helpen u graag verder!

We zijn bereikbaar via info.bi.nl@axians.com en via telefoonnummer 088 – 597 55 00.