|
drs Paul van Oordt MCITP-dba MCT
Freelance SQL Server specialist gevestigd te Utrecht
info@vanoordt.nl
06 27 400 408
Checklist SQL Server performance tuning
Hier volgt een zeer beknopte opsomming van mogelijke oorzaken van performance-problemen in Microsoft SQL Server. De lijst is dus lang niet volledig. Veel tests zijn snel en eenvoudig uit te voeren. Eenvoudige verbeteringen kunnen zo bijdragen aan een sneller en soepel draaiend systeem. Zie ook mijn home page, curriculum vitae of LinkedIn profile.
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.
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 die in staat stelt de data te restoren tot vlak voor een crash of ander probleem, is geen performance killer. Opvallend vaak zie ik productie-databases waarvan het recovery model, kennelijk voor betere prestaties, op simple is gezet. Daarmee kunnen vele uren aan transacties verloren gaan.
Voorbereidingen
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? Zo niet is er een server side trace en/of prestatielogboek ingericht om deze problemen vast te leggen? Leg de performance van het systeem vast in een document, ook voordat en nadat er problemen zijn.
Server
Is er geheugendruk? Is de page life expectancy acceptabel? Is het geheugengebruik van SQL Server gelimiteerd om te voorkomen dat andere processen het moeilijk krijgen? Is er I/O contentie? Welke files laten de meeste wachttijd zien? Zijn de uitgevoerde reads efficiënt? Is dat te verbeteren door de extends per object meer aansluitend te laten zijn? Worden de processors te zwaar belast? Wordt de tempdb zwaar belast en zo ja, is deze daarvoor goed toegerust? Bijvoorbeeld meerdere datafiles op afzonderlijke schijven.
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?
Wordt er dus nergens een auto-shrink van files uitgevoerd? Is de SAN read-write caching adequaat ingesteld? Leidt de auto-grow setting niet tot te zware file grows? 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
Zijn er dubbele of anderszins redundante indexen? Worden de bestaande indexen gebruikt? 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. Worden indexen afdoende gedefragmenteerd? Staat de fill-factor voor alle dan wel specifieke indexen op een goede waarde? Kunnen de meest belastende queries met een indexering versneld worden? Zijn covering indexes gecreëerd waar dat aan de orde is? Dat is in het bijzonder waar queries kunnen profiteren van een ordered scan
over de leaf-level pagina's van de index, zoals queries met group by of order by clause. Zijn indexed views gecreëerd waar dat aan de orde is? Belasten ze het systeem niet te veel bij modificaties? Worden XML indexen gebruikt voor querying van XML data?
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? Worden cursors alleen gebruikt indien werkelijk nodig? Wordt het juiste type cursor gebruikt, bijvoorbeeld static. Wordt union en union all adequaat gebruikt? Maken expressies het gebruik van indexen niet onmogelijk?
Vaak kan er herschreven worden zodat het gebruik van een index wel mogelijk is.
Bijvoorbeeld:
col > dateadd(dd, -1, getdate()) i.p.v. getdate() < dateadd(dd, 1, col) Als er een clustered index op een identity kolom staat, worden dan de ranges gezocht op die kolom, en niet bijvoorbeeld op de insert-date kolom?
Dat is functioneel gelijk, maar de clustered index kan niet gebruikt worden voor de ordered scan. Zijn er (te) veel hercompilaties, resulterend in een hoge belasting van de CPU? Zo ja, kunnen queries herschreven worden zodat
ze gebruik maken van de opgeslagen compilaties in de procedure cache?
Concurrency
Is in iedere connectie het juiste transactie isolation level gebruikt? Maakt de code op een acceptabele manier gebruik van transacties? Draait een server side trace om eventuele deadlocks te registreren?
Database design
Is het database design werkelijk schoon en logisch? Een mooie database is een efficiënte database, en in ieder geval een goed te tunen database. Is er efficiënt gebruik van datatypes? Dus bijvoorbeeld niet een 72-bytes nvarchar(36) om een 16-bytes uniqueidentifier op te slaan. Dit komt voor,
en kan een enorme hoeveelheid ruimte en dus efficiënt gebruikt geheugen schelen. Is uniqueness middels constraints of indexen gedefinieerd waar mogelijk? Zijn er dubbele of anderszins redundante constraints?
Data transfer
Scaling out, denormalisatie, etc
Is een scheiding van transactieverwerking en reporting wenselijk? Is gecontroleerde denormalisatie wenselijk? Kunnen databases, partities of filegroups read-only worden gezet, wat het beheer van locks overbodig maakt? Is het een optie de query governor te gebruiken om resources voor connecties of queries te beperken?
(c) Paul van Oordt, www.vanoordt.nl
|
