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.

de wijze van gebruik van SQL Server, het datamodel en de code
de indexering van tabellen
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:

✗ procedureel denken en programmeren in plaats van set-based, meest zichtbaar door het gebruik van while loops, al of niet met cursor
✗ uitvoeren van werk in meerdere queries dat ook in een enkele query kan, bijvoorbeeld selecteren van data in variabelen, bewerken en dan inserten, terwijl het ook in een enkele insert .. select kan
✗ overmatig gebruik van tijdelijke tabellen om tussenresultaten op te slaan
✗ gebruik van scalar functies waar eenvoudige expressies van built-in functies volstaan, danwel een inline table valued functie kan worden gebruikt
✗ gebruik van scalar functies waar een persisted computed column mogelijk is
✗ gebruik van een slecht datamodel dat onvoldoende de regelmatigheden van het domein weerspiegelt
✗ ophalen van te veel data naar de client
✗ taken uitvoeren op de client die beter door SQL Server kunnen worden gedaan, of vice versa
✗ gebruik van ongeparametriseerde code, vanuit client of als dynamic sql, waardoor overmatig gecompileerd moet worden
✗ te lang openlaten van transacties
✗ gebruik van te hoog transaction isolation level, bijvoorbeeld serializable waar read committed volstaat
✗ onhandig gebruik van expressies bij geïndexeerde kolommen, waardoor de index niet gebruikt kan worden
✗ onzorgvuldig gebruik van datatypes waardoor statistics niet gebruikt kunnen worden
✗ het overrulen van de optimizer met hints
✗ vanuit de client strings van komma-gescheiden waarden sturen die SQL Server zelf tot een tabel moet verwerken, in plaats van het gebruik van table valued parameters

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:

Indicaties van ontbrekende indexes of het gebruik van spool operators. Je zou de ontbrekende index kunnen aanmaken, maar wees je van het volgende bewust:
- Indexes versnellen sommige queries, maar moeten ook bijgewerkt worden en vertragen daarmee modificaties.
- Het zo maar toevoegen van missing indexes zal leiden tot een situatie met redundante indexes of indexes die makkelijk kunnen worden samengevoegd en daarmee tot onnodige overhead.
- In bepaalde situaties kan een index een select query ook aanzienlijk vertragen. Filtered indexes kunnen dan de oplossing zijn. Soms moet hiervoor ook de code worden aangepast.
Warnings betreffende hash en sort spills of grote verschillen in geschatte en actuele aantallen rijen.
- Het regelmatig bijwerken van statistics kan dit in sommige situaties voorkomen.
- Meestal zit het probleem er echter in dat de code zo geschreven is dat de optimizer geen houvast heeft aan de bestaande statistics. Herschrijven van de code is dan dus nodig.
Warnings betreffende impliciete type-conversies. Dit vereist meestal eenvoudige herschrijving van de code. Een variable is bijvoorbeeld als nvarchar gedefinieerd, terwijl de kolom waarmee wordt vergeleken van type varchar is. Het aanpassen van de variabele kan een groot effect op de performance hebben.
Zware nested loops. Dit is vaak het gevolg van een door de optimizer verkeerd geschatte cardinaliteit, maar kan ook het gevolg zijn van de wijze waarop de join is geschreven.
Unordered scans van grote indexen of tabellen. Dit is wellicht met een index op te lossen, maar meestal ligt het aan de query. Soms is dat gewoon vanwege de gewenste functionaliteit, bijvoorbeeld als er kolom like '%zoekterm%' staat.
Is het plan serieel en gebruikt het veel CPU, kijk dan wat voorkomt dat een parallel plan gekozen wordt. Dat kan bijvoorbeeld zijn het gebruik van scalar functies, of van settings of query-hints, maar het kan ook zijn dat de optimizer geen winst ziet. En wellicht heeft de optimizer het daar fout.
Als er inefficiënte plannen worden gecreëerd als gevolg van zeer ongelijke verdeling van cardinaliteit voor verschillende waardes (parameter sniffing), overweeg de optimize for unknown of recompile hints.
Overweeg 'plan guides' (of Query Store hints in SQL Server 2022), bijvoorbeeld voor optimize for, parameterization of maxdop hints, of eventueel voor een geheel plan, indien inefficiënte plannen worden gebruikt en er geen controle is over de aangeroepen queries.

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.

Check het platform:
- Draai geen interactieve applicaties op de server, laat zeker geen Activity Monitor in SQL Server Management Studio open staan.
- Kijk ook naar de andere services en of het nodig is dat ze op dit systeem draaien.
- Limiteer het geheugengebruik van SQL Server om te voorkomen dat andere processen te weinig geheugen hebben, wat uiteindelijk ook SQL Server niet ten goede komt.
- Gebruik geen anti-virus software op de server (beveilig de server op een andere manier), of anders sluit de datafiles van SQL Server uit.
- Sluit de datafiles van SQL Server uit van text indexing.
- Check dat de processor in high performance mode werkt.
- Check dat je recente versies van Windows en SQL Server gebruikt en een bijbehorend database compatibility level.
Check de geheugendruk (van de buffercache) middels de Page Life Expectancy. Dit is een waarde die snel kan dalen, maar niet snel kan stijgen. Een waarde onder de 300 wordt meestal als problematisch gezien, maar eigenlijk wil je dat de waarde consequent veel hoger is. Je kunt deze grootheid monitoren met de Windows Performance Monitor of via de dynamic management view sys.dm_os_performance_counters. Als je geheugen toevoegt, vergeet dan niet de max server memory setting aan te passen.
Check I/O contentie per databasefile. De norm is maximaal 20 ms per write/read. Files met veel I/O zouden aan deze norm moeten voldoen. Let wel dat deze gemiddelden sterk beïnvloed kunnen worden door bijvoorbeeld een checkdb of reindex, zeker als er verder weinig I/O is. Gebruik bij twijfel een script dat niet alleen een overall gemiddelde bekijkt, maar de resultaten geeft bijvoorbeeld per uur. Mogelijke acties:
- Als het gaat om slechte leestijden en de Page Life Expectancy niet bijzonder hoog is (bijvoorbeeld regelmatig onder de 1000), voeg meer geheugen toe, of overweeg datacompressie te gebruiken om tot beter geheugengebruik en I/O te komen.
- Plaats de belangrijkste database-files op snellere schijven, bijvoorbeeld SSD.
- Verdeel de I/O last over meer spindles.
- Check het I/O subsysteem (of neem contact op met de SAN-beheerder).
- Check of de mogelijkheden van write caching optimaal worden gebruikt.
Check de processorbelasting, in het bijzonder de processor-queues en scheduler yields. Actuele CPU queues kun je zien als runnable tasks via sys.dm_os_schedulers. Dit is een getal dat snel kan veranderen. Mogelijke acties:
- Pas de max degree of parallelism (maxdop) server setting aan zo dat deze afgestemd is op het aantal cores, hyperthreading, numa-architectuur en werklast. Bijvoorbeeld voor een transactieverwerkend systeem is het gebruik van parallellisme minder gepast dan voor een decision support system. Pas om dezelfde reden de cost treshold for parallelism aan.
- Check de tijd die besteed wordt aan compilatie, en of dit wellicht verminderd kan worden door meer geheugen toe te voegen. Geheugen voor de procedure cache is weliswaar niet direct in te stellen, maar een geheugendruk zoals hierboven beschreven heeft hierop een nadelig effect. Indien er veel plannen in de query cache zijn die slechts eenmaal zijn gebruikt, werkt wellicht ook de optimize for ad hoc workloads serveroptie of de database-setting parameterization forced. Het is ook mogelijk dat de compilatie-werklast alleen via het herschrijven van code is aan te pakken.
- Processor cores toevoegen is mogelijk, maar vraagt in het algemeen ook extra licenties en is dus duur. Zeker in een virtuele omgeving kan je de cores en de licenties wellicht tijdelijk afnemen, totdat je werklast goed geschreven en getuned is. De mogelijkheid op te schalen en neer te schalen zijn belangrijke overwegingen bij de keuze van een platform. Zo kan je de resultaten van performance tuning meteen omzetten in lagere platformkosten.
Check de omvang en het gebruik van tempdb en of deze daarvoor goed is toegerust. Mogelijke acties:
- Check waarvoor tempdb vooral gebruikt wordt, user objects, internal objects of version store. Kijk naar de mogelijkheden deze last te verlichten, bijvoorbeeld door het gebruik van non-durable memory-optimized tables.
- Zorg dat de files zo groot zijn dat ze niet op runtime hoeven te groeien.
- Plaats de datafile(s) op snellere schijven, bijvoorbeeld SSD.
- Gebruik meerdere datafiles van gelijke grootte eventueel op dezelfde schijf indien er veel tijdelijke objecten (user of internal) worden gecreëerd, gewijzigd of weggegooid.
Check de databasefiles, met name de vrije ruimte en de autogrow-settings. Dit zijn de aanbevelingen voor zowel data als logfiles:
- Zorg dat de files groot genoeg zijn om de groei van bijvoorbeeld het komende jaar op te vangen.
- Zet het groei-increment op 64MB. Tot en met SQL Server 2014 waren de defaults 1MB en 10% voor respectievelijk data- en logfiles.
- Controleer zo nu en dan of de files nog aan de eerste regel voldoen en vergroot ze anders op een rustig moment.
- Doe een shrink alleen in uitzonderlijke gevallen, en zorg dus dat de auto_shrink database-setting uit staat.
- Gebruik Instant File Initialization, wat overigens niet werkt voor de logfiles.
Verbeter de concurrency:
- Als je in de wait statistics hoge wachttijden ziet voor shared locks (LCK_M_S of LCK_M_IS) en wellicht ook voor exclusive locks (LCK_M_X en vooral LCK_M_IX), dan heb je mogelijk baat bij de database setting read committed snapshot isolation. Deze setting zorgt er voor dat data modificeren en read committed data lezen elkaar niet meer blokkeren. De tempdb wordt gebruikt om versies van gemodificeerde data op te slaan, los van of deze nu gelezen wordt of niet. Dit kost uiteraard ook resources, dus overweeg en test het goed.
- Staat deze setting al aan en heb je nog steeds wachttijden voor shared locks, check dan de gebruikte isolation levels, en of deze ook daadwerkelijk nodig zijn.
- Deadlocks zijn in de meeste gevallen moeilijk direct aan te pakken. De beste manier om deze te verminderen is de blocking te verminderen, en dat doe je door te zorgen voor goed gebruik van transacties en goede query performance.
Check de indexering. Indexes kunnen query-performance zeer sterk verbeteren, maar ze vertragen datamodificaties.
- Verwijder dubbele of anderszins redundante indexes.
- Verwijder ongebruikte indexes, of indexes die weing toevoegen aan andere indexes.
- Check de fill-factor. Om historische redenen staat deze nog wel eens op 80%, waarmee geheugengebruik en I/O 20% minder efficiënt worden. Voor de meeste indexes is 100% prima.

Laatst gewijzigd: 28 december 2022