Technologie
Optimale SQL databases in Azure: kosten, snelheid, succes
Bij Garansys streven we naar het opzetten van soepele processen. Data en cloud solutions spelen hierbij en belangrijke rol. Een database in de cloud draaien brengt voordelen met zich mee. Kostentechnisch kan het goedkoper zijn dan een on-premise database draaien. Verder wordt het updaten/patchen/veilig houden van het geheel uit handen genomen en heb je er minder zorgen aan. Het kostenplaatje werkt echter alleen als je queries optimaal draait: een krachtige database is duur!
De twee facetten van database-optimalisatie
Het optimaliseren van een database zou dus op twee manieren kunnen: oneindig blijven opschalen tot je bottleneck is opgelost (heel duur, wel makkelijk!), of onderzoeken waar de performance uitdagingen vandaan komen en deze op lossen (waar mogelijk).
Wat biedt Azure: Query Performance Insights
Binnen de Azure SQL database service bevindt zich de functionaliteit om relatief eenvoudig te achterhalen welke queries problemen opleveren tegenover de beschikbare resources. Binnen dit overzicht “Query Performance insights” kan onderzocht worden welke queries veel CPU, Memory of Log IO gebruiken binnen een bepaald tijdsbestek. Er wordt bijgehouden wat de totale doorlooptijd van deze queries is binnen deze tijd en hoe vaak een query wordt uitgevoerd. Hieruit kun je afleiden waar je de meeste winst kan halen in de optimalisatie.
Optimalisatiestrategieën voor trage en zelden gebruikte queries
De service toont de queries echter zoals ze zijn uitgevoerd tegen de database. Dus maak je in je code gebruik van Entity Framework, dan zou je queries kunnen taggen in de code zodat eenvoudig te achterhalen valt welke compiled query bij welke EF-query hoort. Alternatief is in je hoofd te ‘reverse engineeren’ van een SQL query naar LINQ syntax.
Optimalisatie van de gevonden queries kan op meerdere manieren worden aangevlogen. Als het een trage query betreft die weinig wordt uitgevoerd, zijn er een aantal mogelijkheden:
- Herschrijf de query tot deze een optimaler pad bewandelt (lees meer hierover in ons blog: https://garansys.nl/blogs-klantcases/entity-framework)
- Upgrade de Entity Framework versie (hij wordt steeds beter in optimale queries schrijven, als je weet wat je doet)
- Zet de query in een geoptimaliseerde (eventueel indexed) view of stored procedure, óf:
- Bereken een complex veld middels tabel triggers (doe dit alleen als er weinig schrijfacties en veel leesacties zijn)
- If above fails, voer de query eens op een (test)database uit en analyseer het query plan
- Herbouw de indices. Deze indices kunnen gefragmenteerd raken, waardoor zij bij lange na niet meer functioneren. De fragmentatie van indices kan uit de database via queries worden opgevraagd. Wanneer deze bij een (belangrijke) index boven de 30% ligt, is het tijd om te herindexeren.
Automatic Tuning: een intelligente benadering
Een deel van bovenstaande kan worden opgepakt door Automatic Tuning, ook aanwezig binnen Azure SQL Database. Automatic tuning gaat verder dan dit: het kan indices toevoegen, indices verwijderen en identificeren wanneer een nieuw runplan van een query minder presteert dan het vorige runplan. Automatic tuning onthoudt welk runplan het snelst is voor een specifieke query, en forceert de database dit runplan te gebruiken.
Ook kan het voorkomen dat een bepaalde query extreem vaak wordt aangeroepen. Dan kun je deze eens opzoeken in je code om te kijken of het wellicht wat makkelijker kan (een gefocuste SELECT met een JOIN is mooier dan een WHERE IN, maar beide opties zijn (bijna) altijd beter dan duizenden keren een losse query draaien – Denk aan opgetelde query tijd voor iedere losse query en de vele roundtriptimes tussen applicatie en database.
Actieve monitoring en signalering
Dit alles vereist wel dat je periodiek actief je databases langs gaat om te kijken of het nog wel goed draait. Is het niet mogelijk signalering in te bouwen hierin? Hiervoor wenden we ons tot andere, ondersteunende Azure resources. Azure Monitor een alert systeem op te tuigen, die je tijdig zou kunnen informeren over queries welke niet goed / traag / vaak draaien. Dat houdt jou en de database scherp.
Schaalvergroting als ultieme oplossing
If all of the above fails, wordt het wellicht toch tijd om op te gaan schalen. Dat kan ook op meerdere manieren, maar daar kom ik in een volgende post op terug.
Meer weten? Neem contact op!
Marjon van Schaik
Business Development
06-10922020
m.schaik@garansys.nl