Aandachtspunten en aanbevelingen

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.

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 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 of andere loops 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 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?

Bron: Paul van Oordt