Paul van Oordt  SQL Services

Freelance SQL Server specialist en troubleshooter, Utrecht / Antwerpen

0627400408 - WhatsApp - info@vanoordt.nl
LinkedIn - curriculum vitae
vanoordt.nl - English
nieuwsbrief

Wat moet je weten over SQL Server performance?

Performance-problemen zijn veel voorkomend in databases. Het is heel gebruikelijk dat een applicatie die in eerste instantie goed werkt na verloop van tijd traag wordt, soms stroperig, soms niet vooruit te branden of met veel time-outs of deadlocks. Het resultaat is ontevreden gebruikers en oplopende hardware-, licentie- of cloud-kosten. Of het nu al zo ver is, of dat je het probeert te vermijden, de informatie op deze pagina kan je helpen SQL Server goed en soepel te laten werken.

Wat betreft mijn diensten, ik heb honderden systemen ge-tuned en getroubleshoot. Ik werk altijd nauw samen met medewerkers van de klant en ben er niet op uit om lang bij een klant te blijven rondlopen. Kennisoverdracht staat centraal. De typische duur van een opdracht is één dag, een enkele keer meer. Ik lever een rapport met bevindingen en aanbevelingen waarmee je zelf verder kan.

Ik geef hieronder wat algemene bevindingen over SQL Server performance, en daarna een aantal mogelijke acties om de performance te verbeteren. Maar eerst een opmerking over ..

SQL Server performance fysiek, virtueel en in de cloud (IaaS, PaaS)

Vrijwel alles in het vervolg geldt voor SQL Server op een fysieke of virtuele machine, on premise of bij een externe partij, of als infrastructure as a service in de cloud. Het meeste geldt ook voor platform as a service Azure SQL Database. Een groot verschil tussen fysieke en virtuele systemen is natuurlijk het gemak van opschalen. Daarom dient een performance-probleem zich vaak anders aan. Bij fysieke systemen treedt er soms een interruptie van de service op, bij virtuele servers, en zeker in de cloud, is dit vaak te voorkomen door de resources aan te passen en de bijbehorende rekening te betalen. Cloud computing biedt veel voordelen, maar is niet goedkoop. Kijkend naar mijn ervaring met SQL Server workloads en resources kunnen de meeste systemen met een paar dagen tunen aanzienlijk down scalen. Een goede performance scan is de eerste stap en een uitstekende investering.

Algemene bevindingen over SQL Server performance

Slechte performance kan allerlei oorzaken hebben. Ik verdeel ze in drie categorieŽn, zeer globaal naar afnemende impact.

  1. de wijze van gebruik van SQL Server, het datamodel en de code
  2. de indexering van tabellen
  3. het platform

Met deze indeling bedoel ik dat je door extra geheugen of cpu's toe te voegen (platform) het systeem misschien enkele keren zo snel krijgt. Een probleemquery die goed geïndexeerd wordt, levert vaak een winst op van 10 tot 100 keer zo snel. En het is geen uitzondering dat het herschrijven van slechte code leidt tot een factor 1000 snellere code. Dit is allemaal heel globaal natuurlijk, maar wel het beeld dat oprijst uit de honderden systemen die ik gezien heb. En ja, er spelen altijd meerdere factoren een rol, die bovendien op allerlei manieren met elkaar interacteren.

Het gebruik van SQL Server

De grootste performance-problemen worden doorgaans dus veroorzaakt door het niet goed programmeren (querieŽn) van SQL Server. Er zijn vele manieren om het fout te doen. Zie mijn aanbevelingen voor SQL Server ontwikkeling voor het goed programmeren van SQL Server. Als je die aanbevelingen volgt krijg je code die begrijpelijk en goed onderhoudbaar is én die in het algemeen ook goed presteert en dat blijft doen. Goede code is schaalbare code.

Enkele van de manieren om het fout te doen zijn:

Nogmaals, om te zien hoe het wel moet: ✓ aanbevelingen voor SQL Server ontwikkeling.

De indexering van tabellen

Individuele queries die goed geschreven zijn, kunnen nog steeds traag zijn, in het bijzonder als grote tabellen worden bevraagd. Vaak zijn deze queries goed te tunen door het creëren van de juiste indexes. Bij goede indexering maakt eigenlijk de grootte van de tabel niet meer uit, en andersom, hoe groter de tabel, hoe belangrijker goede indexering is en hoe erger het fout gaat als die er niet is.

Het met de hand goed indexeren van tabellen vraagt een grondig inzicht in de werking van de SQL Server engine. Dit geeft wel het beste resultaat, omdat alle index features kunnen worden meegenomen: clustered en non-clustered, filtered, included kolommen, de fill-factor, columnstore indexes en indexed views. (Er zijn ook nog XML- en spatial indexes.)

Er kan ook gebruik gemaakt worden van de index-suggesties van SQL Server zelf. Die suggesties vind je in het query plan, in de missing index statistics en via de Database Engine Tuning Advisor. Maak nooit zomaar een index zonder te kijken wat er al is. SQL Server weerhoudt je er niet van redundante of zelfs identieke indexes aan te maken. Vaak kan een bestaande index worden aangepast, door het toevoegen van key- of included kolommen. Weet ook dat er wel eens 'aparte' indexes worden gesuggereerd, zoals op nauwelijks selectieve kolommen, dat sommige ontbrekende indexes niet worden gevonden, bijvoorbeeld bij een trivial plan, en dat de verwachte performance-winst soms heel verkeerd wordt geschat.

Het platform

Over het platform wil je weten hoe het staat met elk van de volgende drie resources: CPU, memory en I/O.

Als blijkt dat er een geheugentekort is, kun je natuurlijk extra memory configureren, maar als je niet goed hebt gekeken naar de manier waarop SQL Server wordt gebruikt, en naar de indexering van tabellen, dan behaal je hier maar beperkt resultaten mee. De kans bestaat ook dat je extra core-licenties koopt, of een duurdere cloud-versie gebruikt, terwijl het herschrijven of tunen van enkele queries het probleem ook, en waarschijnlijk beter oplost.

Het kan ook zijn dat een systeem overvloedige resources heeft en toch traag is. Dat kan als gevolg van blocking, maar ook bijvoorbeeld indien parallelisme niet goed wordt gebruikt.

Aanpakken van SQL Server performance-problemen

Performance-problemen kunnen zo gecompliceerd zijn, en de 'toolkit' is zo uitgebreid, dat ik niet probeer een algemeen recept te geven. Ik noem hier wel een aantal dingen die je kunt meten en verbeteren en wat zaken die in een tuning-traject aan de orde kunnen komen.

De werklast

Belangrijk te weten is welke werklast (queries) zorgen voor een traag systeem, en of de traagheid vooral het gevolg is van hoog gebruik van resources zoals CPU, of van blocking.

Een momentopname vind je het meest eenvoudig door het gebruik van de procedure sp_who2. Je ziet daarin per connectie de gebruikte resources zoals cpu en I/O, en ook of er op dit moment blocking is.

Een overall beeld krijg je middels de dynamic management view sys.dm_os_wait_stats. Daar zie je bijvoorbeeld of er CPU-queues zijn (SOS_SCHEDULER_YIELD, en een hoge signal_wait_time), of er veel blocking is (kijk zowel naar het aantal, de duur en de maximale duur van alle waits met type LCK_M_x), of er memory of I/O issues zijn (PAGEIOLATCH_SH, PAGEIOLATCH_EX). Dit geeft al een heel globaal beeld van de toestand van de SQL Server sinds de laatste herstart. Let wel dat deze getallen sterk beÔnvloed kunnen worden door bijvoorbeeld nachtelijke (onderhouds)taken. Gebruik bij twijfel een script dat de waardes geeft voor bijvoorbeeld een uur tijdens kantoortijd, of nog beter, voor een bepaalde query.

Query tuning

Queries die lang duren of veel resources nemen kun je vinden in de Activity Monitor, in de procedure cache of in de Query Store, die je daartoe wel eerst moet inschakelen. Je kunt die queries naspelen in de SQL Server Management Studio, en ze tunen door te herschrijven, indexes toe te voegen etcetera. Het kan van belang zijn dat de connection options gelijk zijn aan die in de productieomgeving.

Analyseer en verbeter de duurste queries. Bekijk of de code geschreven is vogens de regels in aanbevelingen voor SQL Server ontwikkeling. Als dat het geval is, of er is (nu) geen mogelijkheid de code te herschrijven, bekijk dan het query plan. Let daarbij vooral op:

Tuning van het platform

Ook op platformniveau is er wat te doen, al is het resultaat meestal minder schokkend. Ik geef hier wat snelle checks en acties.

 

(c) Paul van Oordt, vanoordt.nl
 

vanoordt.nl Laatst gewijzigd: 28 december 2022