drs Paul van Oordt MCITP‑dba

Freelance SQL Server specialist en troubleshooter

paul@vanoordt.nl - 0627400408
LinkedIn - curriculum vitae
vanoordt.nl - English
 

Aanbevelingen voor goede SQL Server-ontwikkeling

Hier volgt een beknopte lijst van aanbevelingen voor het ontwikkelen van een SQL Server-applicatie. Deze lijst kan je gebruiken bij het zelf ontwikkelen van software, en ook bij het beoordelen van software van derden, bijvoorbeeld in het kader van pakket-evaluatie.

Onderstaande regels dragen bij aan een goed onderhoudbare en goed presterende applicatie met een optimale afgedwongen kwaliteit van data. Zo zal een applicatie die geen referentiële constraints op databaseniveau heeft vroeger of later inconsistente data bevatten. In ieder geval zal de data op al die regelmatigheden getest moeten worden indien je die ooit op een andere wijze wilt gebruiken, bijvoorbeeld voor een datawarehouse of bij een migratie naar een ander pakket. Dit maakt het gebruik van de data, jouw data, veel lastiger en kostbaarder.

Planning en ontwerp

Als je een SQL Server-toepassing gaat ontwikkelen, zorg dan dat je goed op de hoogte bent van de mogelijkheden die het product biedt. Onderstaande features moet je in een vroegtijdig stadium overwegen omdat het later alsnog gebruiken grote aanpassingen vereist. Evalueer je een pakket van een derde, dan krijg je een idee van het kennisniveau van de leverancier, en in hoeverre de applicatie aangepast is aan nieuwe features van SQL Server.

Hier volgt een lijst van features die je enigszins moet kennen en begrijpen, respectievelijk waarvan je wilt dat de leverancier van een pakket ze waar toepasselijk gebruikt heeft.

  • datatypes
    • de verschillende datum en tijd datatypes
    • de diverse numerieke datatypes
    • de ASCII en unicode datatypes
    • het bit datatype
    • het hierarchyid datatype
    • de geografische datatypes
    • het sql_variant datatype
    • het timestamp datatype
    • het uniqueidentifier datatype
    • de binary datatypes
    • XML met optionele XSD schemas en JSON ondersteuning
    • .NET user defined types
  • nullability en defaults
  • computed columns
  • sequences en de identity property
  • primary keys en unique constraints
  • foreign keys
  • check constraints
  • schema's
  • procedures, scalar en table valued functions, DML triggers
  • views
  • transactions en isolation levels
  • server en database triggers
  • sparse columns
  • row versioning
  • (read only) filegroups
  • temporal tables
  • table partitioning and dropping or moving partitions
  • synoniemen
  • file tables
  • external tables
  • logins, database-users, server- en database rollen, applicatie rollen
  • row level security
  • encryption
  • dynamic data masking
  • full text search
  • CLR integratie
  • in memory OLTP
  • service broker

Modellering en database design

Bij het modelleren en creëren van tabellen is het volgende aanbevolen.

  • Geef kwaliteit, begrijpelijkheid en onderhoudbaarheid van je datamodel en je data de hoogste prioriteit.
  • Normaliseer; zorg dat ieder atomair feit precies één keer in de database voorkomt. Voorkom redundantie, anders dan redundatie die door SQL Server zelf gemanaged wordt zoals (persisted) computed columns, nonclustered indexes en (indexed) views.
  • Het goed gebruik van datatypes en nullability vereenvoudigt je programmacode aanzienlijk, en maakt het datamodel inzichtelijk voor anderen. Gebruik altijd het minimaal geschikte datatype, bijvoorbeeld:
    • geen int of char ('Y', 'N'), maar bit voor een boolean
    • geen datetime, maar date voor een datum
    • geen varchar, maar date, time of datetime voor een datum en/of tijd
    • geen int maar tinyint voor een kwartaal
    • geen varchar, maar char voor waardes van vaste lengte zoals een landcode
    • geen nvarchar(4000), maar bijvoorbeeld nvarchar(200) voor een persoonsnaam; de opgegeven lengte van een datatype kan consequenties hebben voor het geschat benodigd geheugen van een query
  • Gebruik geen leading zero's zoals in '0001'. Doe dat indien nodig in een computed kolom, en gebruik voor de data gewoon een numeriek datatype:
    n int not null,
    n10 as replicate('0', 10-len(cast(n as varchar(10))))+cast(n as varchar(10))
  • Zet nooit twee attributen in één kolom, bijvoorbeeld bedrijfscode en volgnummer 'CORP-0001'. Gebruik twee kolommen en voeg ze eventueel samen in een computed kolom.
  • Weersta de verleiding om kolommen te misbruiken, zoals code -1 betekent 'niet gebruikt'. Specificeer in plaats daarvan een extra kolom:
    gebruikt bit not null default (1)
  • Overweeg zeer serieus het systematisch gebruik van unicode in plaats van ASCII. Daarmee zijn alle problemen met betrekking tot ASCII code-pages en het vergelijken van data afkomstig uit verschillende landen verleden tijd. Dit ten koste van een dubbele hoeveelheid opslag voor character data, twee byted in plaats van één per teken.
  • Vermijd het gebruik van (n)text en image, gebruik in plaats daar van (n)varchar(max) en binary(max).
  • Gebruik hierarchyid om hiërarchische relaties tussen records in één tabel vast te leggen.
  • Wees strict met nullability, gebruik eventueel defaults. Zorg wel dat je de logica van nulls zeer goed begrijpt.
  • Maak maximaal gebruik van key, referentiële en check constraints. Daarmee garandeer je data-integriteit en breng je data-kwaliteit op een minimum niveau. Programmeren tegen de data wordt daarmee ook makkelijker, immers aannames over regelmatigheden hoef je niet meer eerst te testen, en uitzonderingen (orderregels zonder order, niet-unieke BSN's) hoef je niet te behandelen. Daarnaast kunnen constraints ook goed zijn voor de performance.
    • Iedere tabel heeft een primary key. Gebruik betekenisloze sleutels, waarvoor dus nooit een reden is die te updaten. Incidenteel zijn ook betekenisvolle sleutels acceptabel, bijvoorbeeld een landcode char(2) volgens de ISO standaard.
    • Koppeltabellen met referenties naar betekenisloze sleutels kunnen een samengestelde steutel krijgen en behoeven niet zelf nog een extra betekenisloze sleutel. Voor begrijpelijkheid en performance is dit het beste. Wel van belang is de volgorde van de kolommen in de sleutel.
    • Definieer unique keys waar relevant.
    • Gebruik foreign keys voor referentiële integriteit. Overweeg cascade delete voor FK's waar dat zinvol en veilig is, bijvoorbeeld doorgaans kan een rij uit een koppeltabel wel verwijderd worden als de gerefereerde rij wordt verwijderd.
    • Gebruik check constraints om de waardes binnen kolommen verder te beperken.
    • Let er op dat de referentiële en check constraints trusted zijn en blijven.
  • Hanteer een naamgevingsconventie, let daarbij op
    • gebruik van verklarende namen
    • al of niet prefixen van namen met het objecttype, bijv 'tbl', 'vw', 'usp' etc. ('sp_' is bedoeld voor procedures in de master-database, gebruik deze prefix dus niet, net zo min als 'xp_', 'fn_', 'ms_' of 'if_')
    • een werkwoord in procedurenaam, doe of laat het consequent
    • tabelnamen in meervoud/enkelvoud
    • gebruik van underscores, spaties etc.
    • namen van key-kolommen, refererende kolommen, constraints
    • hoofdlettergebruik, 'CamelCase'
    • Engels of Nederlands. Reserved words zijn altijd te omzeilen met '[ ]':
      create table dbo.[group]

Programmeerconstructies

Voor het schrijven van duidelijke, correcte, onderhoudbare en goed presterende T-SQL code is het volgende aanbevolen.

  • Bepaal een consistente manier om de database te benaderen, bijvoorbeeld via stored procedures, ook voor selects. Dit heeft een aantal voordelen:
    • De T-SQL code staat op één plaats, in de database en niet in de applicatiecode.
    • Hergebruik van code volgens de principes van low coupling, high cohesion is makkelijk.
    • Stored procedures presteren in de meeste gevallen beter dan ad hoc queries.
    • Beveiliging kan goed geregeld worden door alleen rechten te geven op de procedures en niet op de onderliggende tabellen.
  • Gebruik set based routines in plaats van procedurele code. Gebruik loops (inclusief cursors) alleen wanneer echt nodig, bijvoorbeeld bij het verzenden van mails of het schrijven van bestanden. Loops zijn één van de grootste performance-killers in relationele databases. Bovendien maken ze je code onnodig lang en ingewikkeld. Soms kunnen loops vermeden worden door te joinen met een 'nums' tabel.
  • Schrijf in één query wat in één query geschreven kan worden. Maak daartoe bijvoorbeeld gebruik van common table expressions en de output clause. Het conceptueel in stappen verdelen van een query gaat heel goed met common table expressions en is efficiënter dan het gebruik van tijdelijke tabellen om tussenresultaten op te slaan, omdat ze samen met de rest van het statement worden gecompileerd.
  • Als je tijdelijke tabellen gebruikt, geef ze dan een primary key en hanteer verder de eisen die je ook aan gewone tabellen stelt (datatypes, nullability, constraints).
  • Gebruik geen tabelvariabelen voor grote datasets. Voor tabelvariabelen worden geen statistics bijgehouden en de optimizer 'schat' dat er één rij in zit. Een tabelvariabele met veel rijen kan dus leiden tot een slecht query plan.
  • Wees zorgvuldig met datatypes van variabelen en parameters. Gebruik het datatype van de kolom waarmee je vergelijkt. Doe je dit onzorgvuldig, dan kan dat grote gevolgen hebben voor de performance, omdat statistics mogelijk niet kunnen worden gebruikt. Voorkom impliciete datatype conversies, bijvoorbeeld van varchar naar nvarchar.
  • Het gebruik van de output clause kan code versimpelen met minder kans op blocking en deadlocks. Bijvoorbeeld bij een insert .. select waarbij de ge-inserte data in het vervolg van de code nodig is.
  • Gebruik computed columns, eventueel persisted, waar mogelijk om de logica van een afgeleid attribuut in de tabel vast te leggen, bijv.:
    naam as voornaam+' '+isnull(tussenvoegsel+' ', '')+achternaam
  • T-SQL is gemaakt om te werken met relationele data. Het is een minder geschikte taal voor bijvoorbeeld string processing en grote berekeningen. Overweeg middels CLR integratie delen van je code te schrijven in één van de .NET talen en deze direct binnen SQL Server uit te voeren.
  • Vermijd indexhints in operationele code. Daarmee wordt immers de optimizer overruled en is de kans op een beter plan verkeken. Bovendien leidt het tot een fout als ooit de index gedropt wordt.
  • Gebruik de nolock hint of het read uncommitted isolation level alleen als je echt bereid bent ongecommitte en in uitzonderlijke gevallen dubbele data te lezen. Overweeg anders de read committed snapshot optie om te voorkomen dat readers en writers elkaar blokken.
  • Schrijf triggers zo dat ze geldig zijn voor een willekeurig aantal gemodificeerde rijen. Join daartoe met de inserted en deleted tabellen. Gebruik daarvoor geen cursor.
  • Gebruik transacties om statements samen te voegen die als logisch geheel dienen te worden uitgevoerd. Realiseer je dat iedere query om externe redenen kan falen en je code nog steeds geldig moet zijn en de database achterlaat in een duidelijke en consistente toestand. Zet de transactie binnen een 'try .. catch ..' om de foutafhandeling inclusief rollback te regelen.
  • Houdt transacties in alle gevallen zo kort mogelijk. Doe geen werk binnen een transactie dat daarbuiten kan, zoals het checken van de geldigheid van parameters.
  • Gebruik het juiste isolation level voor transacties. Soms is het nodig data gelockt te houden vanaf het moment dat het in een transactie gelezen is, bijvoorbeeld bij het uitvoeren van een overboeking waarbij eerst wordt gecontroleerd op het saldo. Het default isolation level van read committed is dan niet genoeg, en in plaats daarvan moet repeatable read of serializable worden gebruikt.  
  • Check het default isolation level dat je programmatuur gebruikt. Soms is dat nodeloos hoog, leidend tot een beperking in de concurrency. Het isolation level van bestaande connecties kan je als volgt zien (zie SQL Docs voor de decodering van de kolom transaction_isolation_level):
    select session_id, program_name, client_interface_name, transaction_isolation_level
      from sys.dm_exec_sessions
      where session_id > 50
  • Wees voorzichtig met het gebruik van user defined scalar functions. Deze worden per regel aangeroepen en leiden vaak tot een slechte performance. Ook multi-statement table valued functies presteren meestal slecht.
  • Gebruik geen codefragmenten van internet tenzij je precies begrijpt wat er gebeurt of de bron helemaal vertrouwt. Er zijn veel scalar functies in omloop, bijvoorbeeld voor het formatteren van datums, die ware performance-killers zijn. Incorrecte of verouderde code komt ook veel voor.
  • Gebruik 'yyyymmdd' voor invoer van een datum, dit werkt altijd onafhankelijk van de set dateformat optie.
  • In het geval van dynamic SQL, gebruik geen exec, maar sp_executesql met expliciete parameters. Dit zowel ten behoeve van caching als om SQL injection te voorkomen.
  • Gebruik bij voorkeur 'with schemabinding' bij de definitie van views, functies en triggers.
  • Zorg voor een consequente en duidelijke layout van je code. Dit bevordert de leesbaarheid en verkleint de kans op fouten:
    • Let op een consequente indentatie en wijze waarop je statements verdeelt over regels.
    • Gebruik optionele keywords zoals 'inner' join consequent.
    • Zorg voor consequent hoofdlettergebruik.
    • Sluit statements af met ";".
    • Gebruik de join syntax met het 'join' keyword, in plaats van de verouderde ANSI syntax.
    • Kwalificeer alle kolommen met de schema- en tabelnaam dan wel de table alias.
    • Geef bij elk object ook het schema, dit vermijdt misverstanden, onvoorziene effecten en levert een (beperkt) performancevoordeel op.
    • Gebruik haakjes in de where clause als and en or door elkaar worden gebruikt, gewoon om elke twijfel weg te nemen.
    • Gebruik expliciete kolomlijsten bij inserts en updates, zodat het toevoegen van een kolom aan een tabel nooit leidt tot falende code.
  • Voeg op beslissende punten commentaar toe
    • Documenteer het doel van de procedure, functie etc, bijv.:
      -- rapporteert wanbetalers
    • Geef meta-informatie zoals auteurs, datum, status, wijzigingshistorie. Doe dit op een systematische wijze, bijv.:
      -- PvO, 20180806
      -- PvO, 20190220, parameter @drempelbedrag toegevoegd
    • In grotere stukken code kan het zinvol zijn bij 'else' aan te geven welke voorwaarden voldoen:
      if @custid is null and @showall = 1
      ..
      else -- @custid is not null or isnull(@showall, 0) = 0
      ..
    • Voor elke cte (of tijdelijke tabel met tussenresutaten), geef aan waaruit de set bestaat, bijv.:
      with OF -- onbetaalde facturen
      as (select ... )
         , K2 -- klanten met > twee herinneringen in afgelopen jaar
      as (select ... )
         , OFK2 -- onbetaalde facturen van klanten met > twee herinneringen in afgelopen jaar
      as (select ... )
    • Gebruikte veronderstellingen die niet worden gecontroleerd of afgedwongen in het datamodel, bijv.:
      -- de string bevat '<br/>' ten minste elke 200 tekens
      -- de datum is niet kleiner dan 1 minuut voor getdate()
    • Keuzes gemaakt met het oog op performance, bijv.:
      -- de rijen worden ingevoegd met dummywaarde voor kolom C om te voorkomen dat ze groeien na een update
    • Geef bij een query aan als er een covering index voor gemaakt is. Bij het wijzigen van de query is dan duidelijk dat ook de index moet worden aangepast:
      -- voor deze query: covering index Tbl1.Idx1

Performance

Naast alles wat hierboven beschreven is, adviseer ik voor de ontwikkelaar het volgende op het gebied van performance.

  • Idealiter indexeer je tabellen bij het schrijven van queries (en niet pas als er in productie performance-problemen optreden).
    • Maak nooit zomaar een index aan zonder te kijken wat er al is. SQL Server weerhoudt je er niet van twee identieke, of anderszins redundante indexes aan te maken. Vaak kan ook een bestaande index worden aangepast, door het toevoegen van key- of included kolommen.
    • Ga na of een index fragmenteert en of er queries zijn die daar last van hebben. Zo ja, zet eventueel een fill factor en neem de index op in een defragmentatie-routine.
  • Test de queries die je schrijft op performance.
    • Een makkelijke manier om performance-gegevens te zien is met de volgende set options in de Management Studio:
      set statistics io, time on
    • Bekijk het actuele query plan, en let daarbij o.a. op warnings, op operatoren die percentueel de meeste resources nemen, en bijvoorbeeld op grote verschillen tussen de geschatte en actuele cardinaliteit van tussenresultaten.
  • Zie verder mijn checklist SQL Server performance tuning.

 

(c) Paul van Oordt, www.vanoordt.nl
 

www.vanoordt.nl Laatst gewijzigd: 20 februari 2019