drs Paul van Oordt MCITP‑dba

Freelance SQL Server specialist en troubleshooter

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

Aanbevelingen voor 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.

Wat betreft mijn diensten, ik doe geen lange ontwikkeltrajecten, maar ik kan wel heel goed een ontwikkelteam bijscholen of adviseren bij het modelleren en de bijbehorende algoritmiek en code. Ook kan ik samen met de ontwikkelaars bestaande code evalueren en laten zien waar het beter kan. De typische duur van een opdracht is één of enkele dagen. Kennisoverdracht staat altijd centraal.

Zie ook de mogelijkheid van een in-house SQL Server crash course.

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 incomplete 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.

  • de system supplied data types en de mogelijkheid zelf in .NET types te definiëren
  • nullability en defaults, computed columns, sequences en de identity property
  • primary keys en unique constraints, foreign keys en check constraints
  • stored procedures, scalar en table valued functions, DML triggers, CLR integration, views
  • transactions en isolation levels, row versioning
  • server en database triggers
  • (read only) filegroups, table partitioning en het weggooien of verplaatsen van partities
  • file tables, temporal tables, external tables, synoniemen
  • logins, database-users, server- en database rollen, applicatie rollen, schema's
  • row level security, encryption, dynamic data masking
  • full text search
  • in memory OLTP
  • service broker

Modellering en database design

Bij het modelleren en creëren van databases en 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.
  • Denormalisatie of het dupliceren van data op een andere server zijn laatste redmiddellen. Doe dit niet voordat je de andere mogelijkheden voor verbetering van performance of schaalbaarheid hebt uitgeput. De complexiteit die je introduceert zal je de rest van de levensduur van de applicatie achtervolgen.
  • Gebruik in principe voor een applicatie één database, dat is het eenvoudigst voor databasebeheer, versiebeheer van code, high availability en programmering. Gebruik eventueel schema's, row level security, (read only) filegroups, table partitioning, etc.
  • 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 smallint voor een jaartal en tinyint voor een kwartaal, alhoewel dit meestal afgeleide (computed) waardes zullen zijn
    • 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 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 bytes in plaats van één per teken.
  • Vermijd het gebruik van (n)text en image, gebruik in plaats daar van (n)varchar(max) en varbinary(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 sleutel krijgen en behoeven niet zelf nog een extra betekenisloze sleutel. Voor begrijpelijkheid en performance is dit het beste. De volgorde van de kolommen in de sleutel kan wel van belang zijn.
    • 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:
      select object_name(parent_object_id) tab, name, is_not_trusted
        from sys.foreign_keys;
      select object_name(parent_object_id) tab, name, is_not_trusted
        from sys.check_constraints;
  • 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, hoofdletters, 'CamelCase'
    • namen van key-kolommen, refererende kolommen, constraints
    • 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 eenvoudig.
    • 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. Ook table valued parameters kunnen soms worden gebruikt om loops te voorkomen. Geef vanuit de client-applicatie een lijst van waardes niet door als een string; gebruik een table valued parameter.
  • 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 (cte's) en is efficiënter dan het gebruik van tijdelijke tabellen om tussenresultaten op te slaan, omdat een cte samen met de rest van het statement wordt geoptimaliseerd en uitgevoerd.
  • 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, in het bijzonder tussen varchar en 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 voor dergelijke taken het gebruik van .NET assemblies binnen SQL Server, of voer de taak uit in de client.
  • 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, bijvoorbeeld door een deadlock, 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 deze 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. In plaats daarvan moet worden gebruikt: repeatable read als het saldo is opgeslagen in een enkel updatetable veld, of serializable als het saldo wordt berekend uit een set mutaties.
  • 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. Vanaf SQL Server 2019 is dit beter geregeld.
  • 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:
    • Geef bij elk object ook het schema, dit vermijdt misverstanden, onvoorziene effecten en levert een (beperkt) performancevoordeel op.
    • Gebruik expliciete kolomlijsten bij inserts en updates, zodat het toevoegen van een kolom aan een tabel nooit leidt tot falende code.
    • Kwalificeer alle kolommen met de schema- en tabelnaam dan wel de table alias (tenzij triviaal).
    • Gebruik de join syntax met het 'join' keyword, in plaats van de verouderde ANSI syntax.
    • Gebruik optionele keywords zoals 'inner' join consequent.
    • Gebruik haakjes in de where clause als and en or door elkaar worden gebruikt, gewoon om elke twijfel weg te nemen.
    • Let op een consequente indentatie en wijze waarop je statements verdeelt over regels.
    • Zorg voor consequent hoofdlettergebruik.
    • Sluit statements af met ";".
  • Voeg op beslissende punten commentaar toe, niet te weinig en ook niet te veel:
    • 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

De meeste van de bovenstaande aanbevelingen zijn niet alleen goed voor datakwaliteit en correcte en onderhoudbare code, maar ook voor performance. Meestal is het afwijken van deze principes ten behoeve van performane een slecht idee. Specifiek op het gebied van performance adviseer ik voor de ontwikkelaar nog het volgende:

  • Indexeer tabellen bij het schrijven van queries (en niet pas als er in productie performance-problemen optreden).
    • Maak nooit zomaar een index 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 behandel eventuele warnings en missing indexes.
  • Zie ook: Wat moet je weten over SQL Server performance-problemen?

 

(c) Paul van Oordt, vanoordt.nl
 

vanoordt.nl Laatst gewijzigd: 15 april 2019