Waarom is mijn SQL Server traag...?

Iedere SQL Server specialist krijgt wel eens de vraag: “Mijn SQL Server is traag. Hoe kan ik er achter komen waar dat aan ligt?”. Een makkelijke vraag, maar het antwoord is niet zo eenvoudig. Zeker wanneer je meer een ontwikkelaar bent en geen beheerder en niet dagelijks met performance problemen bezig bent. Stel je de vraag aan een handvol database beheerders, krijg je een handvol uiteenlopende antwoorden. Het lijkt wel alsof het analyseren en oplossen van performance problemen in een database een soort zwarte magie is waarvan geen toverboek bestaat.

Resources

De performance van SQL Server wordt door vier resources beïnvloedt:

De analyse van een performance probleem zal zich dan ook altijd moeten richten op het gebruik van deze resources. Meestal worden de problemen niet veroorzaakt door verkeerde hardware of de configuratie van hard- en software, maar door het inefficiënte gebruik van één of meerdere van de vier genoemde resources. Queries die te veel leesacties veroorzaken (en dus een te grote aanslag doen op de opslag), of queries die steeds opnieuw uitgevoerd worden (met onnodig gebruik van alle genoemde resources als gevolg). Soms kan er een aanpassing gedaan worden in de logica van de query, maar ook indexen kunnen een oplossing bieden. Pas als je het gevoel hebt dat de queries niet veel efficiënter kunnen, ga je naar de hardware kijken; je kunt vaak vrij eenvoudig een bepaalde query 10 tot 100 keer sneller maken. Dat soort snelheidswinst kun je met hardware vaak niet eens halen, hoeveel geld je er ook tegenaan gooit.

Profiler en Plan Cache

Een makkelijke manier om snel resultaat te halen is simpelweg de SQL profiler te starten en elke query die langer dan bijv. 3 seconden duurt te filteren en deze queries te analyseren. Veel mensen halen hun neus op voor deze inmiddels deprecated manier, maar hij werkt nog steeds en is relatief makkelijk. Ook kun je in de SQL Server plan cache informatie vinden over alle queries die door SQL Server zijn uitgevoerd en met welk plan dit is gedaan. Een waardevol ‘laatje’ met veel informatie, maar je weg er in vinden, kan lastig zijn. Gelukkig heeft Brent Ozar het snuffelen in dit laatje makkelijk gemaakt met de sp_BlitzCache stored procedure, onderdeel van zijn First Responder Kit.

Performance Counters

Een andere manier om performance problemen te onderzoeken, is gebruik maken van de performance counters in Windows. Naast de standaard counters voor CPU, geheugen, opslag en netwerk gebruik (die dus héél belangrijk zijn!), zijn er ook heel veel counters voor SQL Server. Te veel, want zelfs wanneer je weet welke counter je wilt monitoren, valt het nog niet mee die te vinden tussen de enorme hoeveelheid counters… Mocht in je data centrum al System Center gebruikt worden, weet dan dat er ook een management pack voor SQL Server is met heel veel performance counters.

Naar welke counters zou je kunnen kijken? Hier heeft Brent Ozar al in 2006 een goed artikel over geschreven. Het is al 11 jaar oud, maar nog steeds van toepassing. Een recenter en eveneens goed artikel over performance counters, is het artikel 15 SQL Server Performance Counters to Monitor van Allen White (SentryOne).

Bij het gebruik van performance counters is het vooral van belang een goede baseline te hebben. Meten wanneer er nog niets aan de hand is en de performance van het systeem aan de verwachtingen voldoet, zodat je wanneer er wel iets aan de hand is de cijfers beter kunt interpreteren en kunt zien waar er afwijkingen zijn. Ook moet je vaak naar meerdere counters tegelijk kijken omdat een individuele counter vaak niet zoveel zegt. Zo zegt de Page Life Expectancy counter in combinatie met Buffer Cache Hit Ratio (beide in het SQLServer:Buffer Manager object) iets over geheugendruk. De laatste moet idealiter 100% zijn: SQL Server kan alle query resultaten uit het geheugen halen in plaats van de veel tragere disk te moeten benaderen. Dat kan natuurlijk nooit, maar een zo hoog mogelijke waarde, is prettig. De Page Life Expectancy geeft aan hoe lang een page in de buffer cache blijft staan. Hoe langer, hoe beter. Een te lage waarde (< 300 sec.) kan wijzen op te weinig geheugen. Maar eigenlijk is het het belangrijkst dit soort counters over de tijd te monitoren en alert te zijn op plotselinge wijzigingen.

Disk performance kun je monitoren met Disk I/O: PhysicalDiskAvg. Disk sec/Read en PhysiclDiskAvg. Disk sec/Write. Hiermee meet je de vertraging (latency) die een lees resp. schrijfactie op een geselecteerde disk heeft (het moment tussen een lees/schrijfactie en het daadwerkelijk lezen/schrijven van bytes). Deze moet zo laag mogelijk zijn. Waarden hoger dan 25 ms, zijn indicaties voor disk i/o problemen (dmv benchmarking moet je de normale waarden in jouw omgeving bepalen, de genoemde 25 ms is indicatief en sterk afhankelijk van het soort opslag in jouw omgeving). Zie ook https://blogs.technet.microsoft.com/askcore/2012/02/07/measuring-disk-latency-with-windows-performance-monitor-perfmon/. Een andere belangrijk gegeven van een disk is het aantal I/O operaties dat een disk per seconde kan verwerken (IOPS). Deze kun je meten met de performance counters PhysicalDiskDisk Reads/sec resp. PhysicalDiskDisk Writes/sec.

Om de grootte van je totale database files te monitoren, kun je gebruik maken van de counter SQLServer:Databases, Data File(s) Size (KB). Wil je monitoren hoeveel vrije ruimte er in tempdb beschikbaar is, kijk je naar de counter SQL Server:TransactionsFree Space in tempdb (KB).

Andere standaard beschikbare hulpmiddelen

Naast SQL Server Profiler, de plan cache en performance counters, biedt SQL Server een scala aan hulpmiddelen die je in kunt zetten bij het analyseren van performance problemen. Zo zijn wait statistics een belangrijke bron van informatie, omdat performance problemen vaak veroorzaakt worden door resources waarop SQL Server moet wachten. Ik heb daar al een keer over geschreven en in dat artikel al verwezen naar een artikel van Paul Randal. Ook groeit met iedere versie van SQL Server het aantal beschikbare dynamic management views (DMV’s) waarmee informatie uit de motor van SQL Server beschikbaar komt. Tenslotte zijn extended events en de performance tuning wizard nog het vermelden waard.

Tools

Naast het gebruik van standaard beschikbare hulpmiddelen waarmee je zelf je eigen monitoring omgeving op kunt zetten, zijn er ook tools op de markt die het monitoren van SQL Server makkelijker kunnen maken. Denk hierbij aan Microsoft System Center Operations Manager (SCOM) of de tools van SentryOne (voorheen SQL Sentry). Of gratis tools, zoals de First Responder Kit van Brent Ozar. En als je al gebruik maakt van de laatste versie van SQL Server Management Studio, vergeet dan niet de Performance Dashboard reports die nu standaard onderdeel zijn van SSMS 17.2.

DBA

A fool with a tool stays a fool… Met andere woorden, ook al gebruik je het meest fancy tool, ook al gebruik je alle in dit artikel genoemde performance counters, je moet wel kennis van zaken hebben wat al die counters nu betekenen en combinaties van counters kunnen interpreteren. Kortom, voor het échte werk, heb je toch echt een DBA nodig!