http://www.wikihow.com/Make-a-Collapsible-List-in-HTML-Without-Java aanbevelingen voor een goede SQL Server performance

drs Paul van Oordt MCITP‑dba

Freelance SQL Server specialist en troubleshooter gevestigd te Utrecht

paul@vanoordt.nl - 0627400408

LinkedIn - curriculum vitae

vanoordt.nl - English
 

Aanbevelingen voor een goede SQL Server performance

Er zijn grofweg twee situaties waarin je met SQL Server performance aan de slag kunt gaan:

  • performance troubleshooting
  • proactieve performance-tuning

In het eerste geval is er sprake van een (soms) slecht presterende applicatie, trage of geen respons (time-outs), ontevreden gebruikers en oplopende hardware-, licentie- of cloud-kosten. Deze pagina geeft je aanbevelingen voor performance troubleshooting. De lijst is lang niet volledig. Het aantal factoren dat op de performance van invloed kan zijn is enorm. De onderliggende mechanismen zijn vaak complex. Je vindt hier een aantal tests die snel en eenvoudig uit te voeren zijn. En ook een aantal eenvoudige verbeteringen die kunnen bijdragen aan een sneller en soepel draaiend systeem.

Voor proactieve performance-tuning, dus het verbeteren van de performance zonder dat er een direct probleem is, vind je hier ook veel zinvolle informatie. Kijk daarvoor ook op mijn pagina aanbevelingen voor SQL Server ontwikkeling.

Nog één opmerking vooraf: Weersta de verleiding te snel functionaliteit op te offeren voor performance. Een voorbeeld daarvan dat ik in de praktijk regelmatig tegenkom is het simple recovery model voor een productie-database. Daarmee kunnen vele uren aan werk verloren gaan. Een ander voorbeeld, in de meeste gevallen even fout: Het gebruik van nolock hints ten behoeve van een betere concurrency. Dit kan de integriteit van de gelezen data meer schaden dan de meeste ontwikkelaars zich realiseren. Dus: Niet te snel functionaliteit opofferen voor performance. Zeker niet als het systeem nog niet uitvoerig getuned is.

Mijn ervaring is dat de grootste winst over het algemeen te halen is bij het zorgvuldig optimaliseren van een paar probleem-queries. Er zijn vaak resultaten mee te behalen zoals: Een query die elke minuut wordt uitgevoerd, 30 sec CPU-tijd nam en honderdduizend reads deed, doet het nu in minder dan een seconde met een paar duizend reads. In zeker de helft van alle trajecten die ik heb gedaan, komen dit soort verbeteringen voor. Uiteraard profiteert het hele systeem daarvan. Het zal ook duidelijk zijn dat dit soort problemen niet met wat zwaardere hardware blijvend verholpen zijn.

Queries

De grootste performance-problemen ontstaan meestal door het fout programmeren (query-en) van SQL Server. Er zijn heel wt anieren om het fout te doen, bijvoorbeeld:

  • Het gebruik van while-loops, al of niet met cursor, waar dat niet nodig is.
  • Het onzorgvldig gebruik van datatypes in code.
  • Het verdelen in meerdere queries binnen één transactie, wat ook met een enkele query kan. (select..; insert.. values ..; select..; ipv insert .. select .. output;)
  • Het onnodig gebruik van tijdelijke tabellen.
  • Het overrulen van de optimizer met hints.
  • Een slecht datamodel.
  • Het te lang laten open staan van transacties.

Benchmarking

  • 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.
  • Richt een server side trace en/of prestatielogboek in om de problemen vast te leggen.
  • Leg een benchmark vast voor de performance van het systeem.

Performance trouble shooting

.. snelle checks en acties

  • Check het platform, 64 bit
  • Check de geheugendruk 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. Kijk ook of er bovenmatige wachttijden voor memory grants optreden. Mogelijke acties:
    • Voeg geheugen toe. Vergeet niet de 'max server memory' setting aan te passen als die ingesteld staat. Doe dat anders alsnog.
    • Limiteer het geheugengebruik van SQL Server om te voorkomen dat andere processen te weinig geheugen hebben.
  • 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 ernstig beïnvloed kunnen worden door bijvoorbeeld een checkdb of reindex, zeker indien 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:
  • Check de processorbelasting, in het bijzonder de processor-queues en scheduler yields. Mogelijke acties:
    • Pas de 'max degree of parallelism' (maxdop) server setting aan zo dat deze afgestemd is op 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 omvang en het gebruik van de tempdb en of de tempdb daarvoor goed toegerust. Mogelijke acties:
    • Plaats de datafile(s) op snellere schijven, eventuee op SSD.
    • Gebruik meerdere datafiles eventueel op dezelfde schijf indien er veel tijdelijke objecten worden gecreëerd, gewijzigd of weggegooid.
  • Check actuele en historische blocking. Mogelijke acties:
  • Check of er voldoende lege ruimte in de belangrijke databasefiles is. Check de autogrow-settings van deze files. Zijn er grote files met een kleine of percentuele autogrow waarde? Tot voor SQL Server 2016 waren de defaults 1MB en 10% voor respectievelijk data- en logfiles. Mogelijke acties:
  • Check de de duurste queries in de procedure cache, of -nog beter- de query store. Mogelijke acties:

.. snelle verbeteringen

  • Files
  • Voeg relevante ontbrekende indexes toe. Maar let op:
    • 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.
    • In bepaalde situaties kan een index een select query ook aanzienlijk vertragen. Filtered indexes kunnen dan de oplossing zijn.
  • Indien... Tempdb op ssd
  • Indien... meer geheugen
  • Indien
  • Bekijk of er veel plannen in de query cache zijn die slechts eenmaal zijn gebruikt. Gebruik in dat geval de 'optimize for ad hoc workloads' serveroptie.
  • Analyseer en verbeter de duurste queries
    • Is de code set-based, of gaat het bijvoorbeeld om een cursor fetch, of om een procedureel geschreven scalar functie? Herschrijf procedurele code naar set-based code.
    • Let op constructies als 'where (@name = null or name = @name)'. Dit leidt vaak tot inefficiëe plannen omdat een index seek op name geen geldig plan is in het geval @name is null. Gebruik eventueel meerdere statements binnen een 'if' of dynamic SQL.
    • Zijn de duur, de gebruikte cpu-tijd en de hoeveelheid I/O te rijmen? Waar ligt de bottleneck, cpu, I/O of elders?
    • Check de auto parameterization, in het bijzonder wanneer er geen controle is over de queries die vanuit een applicatie worden gegenereerd. Overweeg de database setting 'parameterization forced'.
    • Bekijk het query plan. Let vooral op:
      • Indicaties van ontbrekende indexes.
      • Warnings betreffende hash en sort spills.
      • Warnings betreffende impliciete type-conversies.
      • Grote verschillen in geschatte en actuele aantallen rijen.
      • Zware nested loops.
      • Unordered index scans of table scans van grote indexen/tabellen.
      • Als er inefficiëe plannen worden gecreëerd als gevolg van zeer ongelijke verdeling van cardinaliteit voor verschillende waardes (parameter sniffing), overweeg de 'optimize for unknown' hint.
      • Overweeg 'plan guides', bijvoorbeeld voor optimize for, parameterization of maxdop hints, of eventueel voor een geheel plan, indien inefficiëe plannen worden gebruikt en er geen controle is over de aangeroepen queries.
    • Bij het 'naspelen' van queries in SSMS, let er op dat de set options gelijk zijn.CODE
  • Ontwerp en programmeer voor performance

    temp table caching gebruik table valued parameters columnstore indexes batch mode execution ensure compatibility level = 130 monitor fragments in gebruik voor full text indexes, en reorganiseer indien nodig draai 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 ook uit van text indexing draai geen applicaties zoals SSMS en Profiler op de server laat geen SSMS met open Activity Monitor draaien T1117 or 'autogrow_all_files' from v13 T1118 disables mixed extents T2371 dynamic stats upfdate treshold T3226 don't write succesful backup info to error log T4119 optimizer hotfixes

Database settings en -files

  • Zijn de database files optimaal geplaatst op adequate fysieke schijven.
  • Zorg dat het groeien van database files tijdens normale werking van het systeem tot een minimum beperkt is. Disable auto-shrink files, check dat geen shrink wordt uitgevoerd in maintenance plans.
  • Is de SAN read-write caching adequaat ingesteld.
  • Leidt de auto-grow setting niet tot te zware file grows. Is eventueel instant file initialization ingesteld.
  • Staat auto create en auto update van statistics aan. Zo niet, worden de statistics dan anderszins bijgewerkt.
  • Kan datacompressie worden gebruikt om tot beter geheugengebruik en I/O te komen.
  • Zijn er geen overbodige database snapshots.

Indexering

  • Voeg indexes toe op basis van missing indexes statistics. Let op: Indexes vertragen modificaties en in bepaalde gevallen kunnen indexes ook select queries aanzienlijk vertragen. In het bijzonder bij grote afwijkingen in de cardinaliteit van waardes in een kolom. Gebruik eventueel filtered indexes in combinatie met recompile.
  • Zijn er dubbele of anderszins redundante indexes.
  • Zijn er ongebruikte indexes, of indexes die weing 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 (en noexpand hints), XML indexes en spatial indexes.
  • Belasten de indexes het systeem niet te veel bij modificaties.

Queries

  • 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.
  • Worden cursors alleen gebruikt indien werkelijk nodig. Wordt het juiste type cursor gebruikt, bijvoorbeeld static.
  • 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 om eventuele deadlocks te registreren. Of zet traceflag 1222 aan waarmee xml deadlock-gegevens naar de SQL Server errolog worden geschreven.

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

Aanbevelingen specifiek voor SQL Azure

Veel van de bovenstaande aanbevelingen gelden ook voor SQL Azure. Verder geldt:

  • (Her)schrijf de code zodat het aantal database round-trips beperkt wordt. Gebruik daarbij eventueel xml of table valued parameters.
  • Waar mogelijk en zinvol cache data in de client.
  • Throttling...

(c) Paul van Oordt, www.vanoordt.nl
 

www.vanoordt.nl Laatst gewijzigd: 11 april 2015