Checklist SQL Server performance tuning

Voor een nieuwer en completer overzicht zie: wat te weten over SQL Server performance.

Hieronder vind je een groot aantal aandachtspunten en aanbevelingen waarmee je zelf aan de slag kunt om de performance van Microsoft SQL Server te verbeteren. Wil je grondige kennis opbouwen en succesvol je performance-problemen aanpakken, kijk eens naar de mogelijkheden die er zijn voor een in-house SQL Server crash course.

Mijn ervaring met performance tuning is dat de grootste winst over het algemeen te halen is bij het identificeren en zorgvuldig optimaliseren van een paar probleem-queries. Daar zijn de verbeteringen van een factor 10, 100 of zelfs meer te behalen, zowel in verbruikte cpu, I/O als in responstijd. In zeker de helft van alle trajecten die ik heb gedaan, komen dit soort verbeteringen voor. Uiteraard profiteert het hele systeem daarvan. Tevens is duidelijk dat dit soort problemen niet met wat zwaardere hardware blijvend verholpen zijn.

Behalve dat het belangrijk is te weten wat kan bijdragen aan performance-problemen, is het ook goed te weten wat dat niet doet. Bijvoorbeeld: een adequate backup strategie is geen performance killer. Opvallend vaak zie ik productie-databases waarvan het recovery model voor betere prestaties op simple is gezet. Daarmee kunnen vele uren aan transacties verloren gaan. Dus: Niet te snel functionaliteit opofferen voor performance. Zeker niet als het systeem nog niet door een goede specialist getuned is.

Algemeen

In welke mate is SQL Server verantwoordelijk voor de waargenomen performance problemen, en in hoeverre gaat het om andere processen?
Zijn de performance-problemen te reproduceren?
Is een server side trace, extended events session en/of prestatielogboek ingericht om de problemen vast te leggen?
Is er een benchmark voor de performance van het systeem?

Server

Is er geheugendruk? Is de page life expectancy acceptabel?
Is er I/O contentie? Welke files laten de meeste wachttijd zien? Gaat het om echte problemen, of treden wachttijden voornamelijk op tijdens bijvoorbeeld een checkdb of reindex?
Worden de processors te zwaar belast? Is er langduring een hoog processorgebruik door het SQL Server proces?
Wordt de tempdb zwaar belast en zo ja wat is de aard van de belasting en is de tempdb daarvoor goed toegerust? Is het zinvol bijvoorbeeld meerdere datafiles te gebruiken, al of niet op afzonderlijke schijven, al of niet op SSD?
Is het geheugengebruik van SQL Server gelimiteerd om te voorkomen dat andere processen te weinig geheugen krijgen?
Is er overmatig parallellisme in een OLTP systeem? Is de maxdop server setting aangepast aan hyperthreading, numa-architectuur en werklast?
Zijn er bovenmatige wachttijden voor memory grants?
Zijn er bovenmatige wachttijden voor de beschikbaarheid van een processor?

Database settings en -files

Zijn de database files optimaal geplaatst op adequate fysieke schijven?
Is het groeien van database files tijdens normale werking van het systeem tot een minimum beperkt? Leidt de auto-grow setting niet tot te zware file grows? Is eventueel instant file initialization ingesteld? Is auto-shrink files disabled?
Is de SAN read-write caching adequaat ingesteld?
Staat auto create en auto update van statistics aan? Worden de statistics (anderszins) adequaat bijgewerkt?
Kan datacompressie worden gebruikt om tot beter geheugengebruik en I/O te komen?
Zijn er geen overbodige database snapshots?

Indexering

Zijn de belangrijkste queries adequaat door indexes ondersteund? In het bijzonder van belang voor updates en deletes, omdat daarbij naast een inefficiënt queryplan ook nog eens grote aantallen locks betrokken kunnen zijn.
Zijn er significante missing indexes?
Zijn er dubbele of anderszins redundante indexes?
Zijn er ongebruikte indexes, of indexes die weinig toevoegen aan andere bestaande indexes?
Is voor (nagenoeg) iedere tabel een clustered index gedefinieerd op de juiste kolommen? Dat is in het bijzonder waar queries kunnen profiteren van een ordered scan over de index.
Staat de fill-factor voor alle dan wel specifieke indexes op een goede waarde en worden indexes tijdig gedefragmenteerd?
Is optimaal gebruik gemaakt van de mogelijkheid covering indexes te creëren met behulp van included columns?
Is optimaal gebruik gemaakt van filtered indexes voor kolommen met een zeer ongelijke verdeling van waarden? Zijn in queries de juiste recompile opties aangegeven waarmee het gebruik van de filtered indexes mogelijk wordt gemaakt?
Is optimaal gebruik gemaakt van columnstore indexes, indexed views, XML indexes en spatial indexes?
Belasten de indexes het systeem niet te veel bij modificaties?

Queries

Is de code set-based? Worden cursors, andere loops en multi statement functions alleen gebruikt indien werkelijk nodig?
Maken de meest belastende queries gebruik van zware nested loops? Kunnen join clauses herschreven worden zodat de meer efficiënte merge en hash join worden gebruikt?
Is het gebruik van tijdelijke tabellen of tabelvariabelen beperkt? Kunnen ze worden vervangen door common table expressions?
Wordt union en union all adequaat gebruikt?
Maken expressies het gebruik van indexes niet onmogelijk? Kan de query herschreven worden zodat het gebruik van de index wel mogelijk is?
Zijn er variabelen met afwijkende datatypes die het gebruik van statistics verhinderen?
Zijn er query plannen die grote verschillen laten zien tussen actual en estimated number of rows?
Zijn er plannen met bovenmatige spilling van hash- of sort-resultaten naar de tempdb?
Zijn er te veel (her)compilaties, resulterend in een hoge belasting van de CPU? Zo ja, kunnen queries herschreven worden zodat ze gebruik maken van de opgeslagen plannen in de procedure cache?

Concurrency

Is er bovenmatige blocking?
Is het gebruik van 'read committed snapshot' een oplossing voor concurrency-problemen?
Wordt voor iedere connectie het juiste transactie isolation level gebruikt? Sommige clients gebruiken standaard het zwaarste level 'serializable'.
Maakt de code op een acceptabele manier gebruik van transacties? Zijn er geen onverwacht lang openstaande transacties?
Kunnen databases, partities of filegroups read-only worden gezet, wat het beheer van locks overbodig maakt?
Draait een server side trace of extended events session, of is traceflag 1222 gebruikt om eventuele deadlocks te registreren?

Database design

Is het database design werkelijk schoon en logisch? Een goed ontworpen database is een efficiënte database, en in ieder geval een goed te tunen database.
Is er efficiënt gebruik van datatypes? Dus, extreem voorbeeld, niet een 72-bytes nchar(36) om een 16-bytes uniqueidentifier op te slaan.
Is uniqueness middels constraints of indexes gedefinieerd waar mogelijk?
Zijn foreign keys gedefinieerd waar mogelijk?
Wordt waar zinvol gebruik gemaakt van computed columns, persisted of juist niet en eventueel geïndexeerd?

Data transfer

Worden triggers, constraints en eventueel indexes op de juiste manier disabled en weer enabled rondom data transfer?
Is er de mogelijkheid data transfer uit te voeren als metadata-only operaties op partities?
Is het verwijderen van grote hoeveelheden data wellicht uit te voeren als metadata-only operaties op partities?

Scaling out, denormalisatie, etc

Is een scheiding van transactieverwerking en reporting wenselijk?
Is gecontroleerde denormalisatie wenselijk?
Is het een optie de query governor te gebruiken om resources voor connecties of queries te beperken?

Laatst gewijzigd: 19 december 2022