Hoe kan Query Store de prestaties van een SQL-database optimaliseren?

Wanneer de Query Store is ingeschakeld voor een SQL-database, worden gedetailleerde gegevens verzameld over de uitgevoerde query’s, de gekozen uitvoeringsplannen en de prestatiecijfers die tijdens de uitvoering worden gegenereerd. Dit biedt beheerders krachtige hulpmiddelen voor het monitoren en optimaliseren van de prestaties van de database. De instellingen van de Query Store kunnen worden geconfigureerd via de Database Properties-pagina in SQL Server Management Studio (SSMS), of via T-SQL-commando’s. De volgende parameters kunnen worden aangepast om de werking van de Query Store te optimaliseren:

De "Query Capture Mode" is een van de belangrijkste instellingen voor het beheren van welke query's worden opgeslagen in de Query Store. Wanneer deze is ingesteld op AUTO, wordt alleen informatie verzameld van de query’s die vaak worden uitgevoerd of veel middelen gebruiken. De instelling "NONE" voorkomt het toevoegen van nieuwe query’s, maar laat wel de statistieken van bestaande query’s behouden. Deze flexibele configuratie helpt om de hoeveelheid verzamelde gegevens in de hand te houden, wat vooral belangrijk is bij het werken met grote of complexe databases.

Daarnaast kunnen beheerders de maximale opslagcapaciteit voor de Query Store configureren. De parameter "Max Size (MB)" bepaalt hoeveel schijfruimte er maximaal aan de Query Store wordt toegewezen. Het standaard maximum is 1000 MB voor SQL Server 2019 en later, en kan variëren afhankelijk van de service tier in Azure SQL Database. Het is belangrijk om te begrijpen dat zodra de Query Store deze limiet bereikt, deze automatisch overschakelt naar de "read-only" modus. Dit betekent dat de prestatieanalyse kan verouderen, aangezien geen nieuwe gegevens kunnen worden toegevoegd totdat de opgeslagen gegevens zijn opgeschoond.

Een andere belangrijke configuratie-optie is de "Size Based Cleanup Mode", die bepaalt of er automatisch een opruimactie moet worden uitgevoerd wanneer de Query Store 90% van zijn opslagcapaciteit heeft bereikt. In dit geval worden de oudste en minst kostbare query’s uit de opslag verwijderd om ruimte te maken voor nieuwe gegevens. Dit helpt de opslagcapaciteit te optimaliseren en zorgt ervoor dat de Query Store efficiënt blijft werken.

Wanneer de Query Store eenmaal is geconfigureerd, kunnen beheerders de opgeslagen gegevens in SSMS raadplegen via verschillende rapporten en weergaven. Zo kan men regressies in query-prestaties analyseren, het totale gebruik van systeembronnen volgen, de query’s met de hoogste resourceverbruik identificeren en inzicht krijgen in de query’s die door een beheerder gedwongen uitvoeringsplannen hebben gekregen.

Naast de Query Store zijn er echter andere belangrijke overwegingen die bijdragen aan het efficiënt beheren van SQL-databaseprestaties. Bijvoorbeeld, blokkeringen zijn een veelvoorkomend probleem in SQL-databases. Dit gebeurt wanneer een transactie probeert gegevens te benaderen die door een andere transactie zijn vergrendeld. Hoewel kortdurende blokkeringen meestal geen problemen veroorzaken, kunnen langdurige blokkeringen ernstige prestatieschommelingen veroorzaken. De twee voornaamste oorzaken van langdurige blokkeringen zijn onvoltooide transacties en slecht ontworpen SQL-transacties.

Bij het werken met de Query Store is het daarnaast belangrijk om te weten dat in een Azure SQL-database de Query Store niet kan worden gedeactiveerd, zelfs niet door het uitvoeren van een ALTER DATABASE-commando. Dit komt doordat de Query Store standaard is ingeschakeld voor Azure SQL-databases en geen optie biedt om het uit te schakelen.

Hoe Resource Governor en Database-specifieke Configuraties de prestaties van SQL Server en Azure SQL Verbeteren

Resource Governor wordt standaard uitgeschakeld in SQL Server en Azure SQL Managed Instance. Om het in te schakelen, kunnen beheerders via SSMS naar de pagina "Beheer" in Object Explorer navigeren, met de rechtermuisknop op Resource Governor klikken en "Eigenschappen" selecteren in het contextmenu. Vervolgens kunnen ze op de Resource Governor-eigenschappenpagina het selectievakje "Resource Governor inschakelen" aanvinken. Dit stelt beheerders in staat om werkbelastinggroepen te maken om de resourceallocatie voor verschillende werkbelastingen op dezelfde server te beheren.

Beheerders kunnen met behulp van Resource Governor werkbelastinggroepen creëren die specifieke CPU-, geheugen- en opslagbronnen van de server bevatten. Deze pools overlappen deels met de bronnen van andere pools om maximale benutting van de resources te bieden, terwijl andere delen exclusief aan een pool worden toegewezen. Dit biedt een flexibele manier om serverbronnen te verdelen zonder dat één toepassing onevenredig veel middelen gebruikt, wat de prestaties van andere werkbelastingen kan beïnvloeden.

Een belangrijke overweging is de configuratie op database-niveau, die in de loop der jaren steeds meer in SQL Server is geïntegreerd. Naast serverconfiguraties zijn er nu database-specifieke instellingen die beheerders in staat stellen om de configuratie voor individuele databases aan te passen. Dit gebeurt bijvoorbeeld via de T-SQL-commando’s zoals de ALTER DATABASE-opdracht, waarmee beheerders instellingen kunnen configureren zoals het herstelmodel van de database, automatische tuningopties, en de configuratie van de Query Store voor individuele databases.

Naast de configuraties die via ALTER DATABASE beschikbaar zijn, kunnen sommige instellingen, zoals de maximale graad van parallelisme (MaxDOP) of de configuratie voor Legacy Cardinality Estimation, nu ook op database-niveau worden ingesteld. Dit biedt meer granulariteit in prestatiemanagement, waardoor beheerders de configuraties per database kunnen afstemmen op de specifieke behoeften en werkbelastingen.

Daarnaast is er een toenemende focus op de schaalbaarheid van compute- en opslagbronnen, vooral in cloudomgevingen zoals Azure. Azure biedt verschillende service-tiers die geschikt zijn voor uiteenlopende werkbelastingen en prijsmodellen, zoals de General Purpose-, Business Critical- en Hyperscale-tiers. Elke tier heeft zijn eigen prestatiespecificaties, van standaardwerkbelastingondersteuning tot krachtige I/O-prestaties en schaalbare opslagcapaciteiten.

De keuze tussen provisioned en serverless compute-opties is cruciaal bij het schalen van een database. Het provisioned model biedt een doorlopende toewijzing van resources, terwijl serverless installaties de compute-resources dynamisch toewijzen op basis van de werkbelasting. Serverless databases kunnen zelfs inactief worden gepauzeerd wanneer er geen activiteit is, wat leidt tot besparingen op compute-kosten. Het serverless model is echter minder geschikt voor zware productieomgevingen waar de prestaties cruciaal zijn. In dergelijke gevallen biedt het Business Critical- of Hyperscale-model de benodigde prestaties en schaalbaarheid.

In de Azure-omgeving kunnen beheerders ook kiezen voor specifieke opslagconfiguraties, waarbij Premium SSD's de voorkeur genieten voor grotere SQL-installaties. Goedkopere opslagopties, zoals standaard harde schijven, kunnen echter voldoende zijn voor ontwikkel- en testomgevingen, waar de prestaties minder kritisch zijn. Voor productieveomgevingen met grote werkbelastingen is een investering in premium opslag essentieel voor een betere databaseprestaties.

Naast de configuratie van de database is het ook belangrijk dat beheerders zich bewust zijn van de implicaties van hun keuzes in de Azure-omgeving. De schaalbaarheid van de infrastructuur in de cloud maakt het mogelijk om snel aan te passen aan veranderingen in werkbelasting, zoals bijvoorbeeld seizoensgebonden pieken. Dit betekent dat beheerders geen definitieve beslissing hoeven te nemen bij het opzetten van de database-infrastructuur, maar flexibiliteit kunnen behouden om resources in de toekomst eenvoudig te schalen, zowel wat betreft compute als opslag.

In het algemeen kunnen beheerders door gebruik te maken van zowel server- als database-specifieke configuraties de prestaties van SQL Server en Azure SQL drastisch verbeteren. Het vermogen om resourceallocatie te beheren, de juiste cloud-tier te kiezen, en intelligent queryverwerking toe te passen, stelt hen in staat om efficiënter om te gaan met de groeiende eisen van moderne werkbelastingen.

Hoe evalueer je en kies je de juiste HA/DR-oplossingen voor je organisatie?

De Recovery Time Objective (RTO) specificeert de maximale tijd die een systeem of resource uitvalt voordat de bedrijfsvoering ernstig wordt beïnvloed. Bij een SQL-database die offline gaat, is de RTO de maximale hoeveelheid downtime die het systeem kan verdragen voordat dit ernstige gevolgen heeft voor de bedrijfsactiviteiten. Bijvoorbeeld, wanneer een database uitvalt tijdens een orderinvoerproces, kan dit het opnemen van nieuwe bestellingen verhinderen, wat op zijn beurt de verkoopcijfers beïnvloedt en mogelijk een domino-effect veroorzaakt in de productieketen van de organisatie. Beheerders moeten zichzelf de vraag stellen hoeveel downtime hun bedrijf kan verdragen voordat de zakelijke en financiële gevolgen onaanvaardbaar worden. Dit is de RTO, die gemeten kan worden in minuten, uren of zelfs dagen. Hoe korter de RTO, des te efficiënter (en duurder) de HA/DR-oplossingen moeten zijn om aan deze waarde te voldoen.

Sommige organisaties kunnen denken dat geen enkele downtime of dataverlies acceptabel is, terwijl andere de waarde van HA/DR-technologie in twijfel trekken, vooral wanneer de kans op een ramp miniem lijkt. Het vinden van een realistische balans tussen de noodzakelijke data-integriteit en de economische haalbaarheid van HA/DR-oplossingen is daarom essentieel. Als een organisatie bijvoorbeeld besluit dat geen enkele database-downtime van meer dan 10 minuten acceptabel is, moeten er robuuste HA/DR-mechanismen worden geïmplementeerd, zoals redundante servers die snel de belasting overnemen in geval van een storing. Simpelweg terugsteken van databases vanaf een backup zal waarschijnlijk langer duren dan 10 minuten.

Het kiezen van HA/DR-oplossingen is geen exacte wetenschap. Weinig bedrijven kunnen zich het veroorloven om bescherming te bieden tegen elke mogelijke soort storing. Dit betekent dat de organisatie een compromis moet vinden tussen de data-eisen en de economische haalbaarheid van HA/DR-bescherming. In geval van een natuurramp, zoals een aardbeving, kunnen zelfs redundante servers onvoldoende bescherming bieden. In dergelijke gevallen moeten veel extremere en duurdere HA/DR-oplossingen worden overwogen, zoals redundante datacenters in verschillende steden.

De verschillende HA/DR-oplossingen die beschikbaar zijn voor SQL-implementaties variëren afhankelijk van het platform. In een Infrastructure as a Service (IaaS) SQL-implementatie, bijvoorbeeld met SQL Server op een Azure Virtual Machine (VM), krijgen beheerders volledige controle over het besturingssysteem van de VM en de SQL Server-software. Azure biedt echter ook zijn eigen HA/DR-mogelijkheden voor virtuele machines. De belangrijkste HA/DR-functies die beschikbaar zijn in SQL Server omvatten:

• Always On Failover Cluster Instance (FCI): Deze oplossing maakt gebruik van een onderliggend Windows Server Failover Cluster (WSFC) om meerdere knooppunten met redundante SQL Server-instanties te creëren. Bij een knooppuntstoring wordt de SQL-server op een ander knooppunt opnieuw opgestart.

• Always On Availability Groups (AG): Dit betreft een database-niveau redundantie waarbij een primaire database met lees- en schrijfrechten en een of meer secundaire databases worden gebruikt. Als de primaire database uitvalt, wordt een van de secundaire databases gepromoveerd tot primair.

• Log Shipping: Dit is een manier om SQL-databaseback-ups te gebruiken en bij te werken, waarbij een primaire database op een server wordt geback-upt en hersteld op een secundaire server. Dit biedt redundante databases die kunnen worden gebruikt in geval van een storing.

Naast deze SQL Server-specifieke HA/DR-functies biedt Azure zelf mechanismen die hoge beschikbaarheid en rampenherstel bieden voor IaaS-installaties, wat verder besproken wordt in de sectie "Evaluate Azure-specific HA/DR solutions."

In Platform as a Service (PaaS) SQL-implementaties, zoals Azure SQL Database en Azure SQL Managed Instance, zijn de HA/DR-mogelijkheden ingebouwd en vereisen ze meestal geen administratieve tussenkomst. Deze oplossingen bieden onder andere een service level agreement (SLA) van 99,99% beschikbaarheid van de database en automatische failover wanneer een fout optreedt.

PaaS-implementaties zoals Azure SQL Database kunnen ook leesreplica’s van databases in verschillende geografische regio’s creëren, wat bescherming biedt zelfs in het geval van een grootschalige regionale storing.

Wat betreft hybride implementaties, waarin on-premises SQL Server-installaties worden gemengd met Azure-VM’s die SQL Server draaien, biedt Azure mogelijkheden om redundante servers te creëren in verschillende regio's, wat zorgt voor een robuuste HA/DR-architectuur. In hybride omgevingen kunnen on-premises servers worden gerepliceerd naar een Azure Managed Instance, maar de replicatie kan alleen in één richting plaatsvinden, van on-premises naar de cloud.

Endtext

Hoe beheer je Azure SQL Managed Instance en wat moet je weten over de operationele prestaties en beveiliging?

Bij het gebruik van Azure SQL Managed Instance is het essentieel om een solide basis voor operationele prestaties te begrijpen. Het stellen van een baseline voor de prestaties van een SQL Server-omgeving is een cruciale stap in het beheersen van de serverbelasting. Dit helpt bij het vaststellen van een referentiepunt voor hoe goed de server presteert onder normale omstandigheden. Het interpreteren van de juiste meetwaarden is hierbij van groot belang: door deze goed te analyseren, kunnen prestaties tijdig worden geoptimaliseerd. Azure SQL biedt verschillende manieren om prestaties te monitoren, waaronder het gebruik van Query Store, SQL Insights, en uitgebreide gebeurtenissen. Deze tools stellen beheerders in staat om trends in servergebruik te identificeren en problemen zoals trage query's of overbelaste indices snel te verhelpen.

Daarnaast is het belangrijk om na te denken over beveiligingsmaatregelen. Het gebruik van encryptie, zowel op object- als rijniveau, biedt een extra laag bescherming tegen onbevoegde toegang. Azure SQL Managed Instance ondersteunt verschillende encryptiemethoden, zoals Always Encrypted, wat zorgt voor versleuteling van gevoelige gegevens, zelfs wanneer ze in de database zelf worden bewerkt. Dit zorgt ervoor dat gegevens veilig blijven, zelfs als er een beveiligingsinbreuk plaatsvindt op het serverniveau.

De combinatie van geautomatiseerde patches, encryptie en een proactieve benadering van prestatiemonitoring maakt het mogelijk om een stabiele en veilige omgeving te onderhouden. Het is essentieel om altijd de principes van minimale privileges toe te passen bij het instellen van toegangspunten en gebruikersrechten binnen de database. Dit voorkomt dat onbevoegde gebruikers toegang krijgen tot kritieke systemen en gegevens.

Naast de beveiliging en prestaties is het ook van groot belang om de kosten en licentiemodellen goed te begrijpen bij het plannen van een implementatie in Azure. Het gebruik van vCore-gebaseerde tarieven in plaats van DTU's (Database Transaction Units) biedt bedrijven meer flexibiliteit en nauwkeurigheid in hun kostenbeheer. VCore-modellen maken het mogelijk om resources zoals CPU, geheugen en opslag afzonderlijk af te stemmen op de werkelijke behoeften van de applicaties, wat leidt tot een kostenoptimalisatie.

Als het gaat om data migratie, biedt Azure verschillende hulpmiddelen voor een soepele overgang naar de cloud, zoals de Azure Data Migration Service (DMS). Dit helpt bij het minimaliseren van downtime en maakt het mogelijk om de migratie online of offline uit te voeren, afhankelijk van de specifieke eisen van de organisatie. Het is van cruciaal belang om de compatibiliteit van de databronnen grondig te beoordelen en de migratie goed voor te bereiden door een gedetailleerd validatieplan op te stellen.

Tot slot is het gebruik van Microsoft Defender for SQL een essentieel onderdeel van de beveiligingsstrategie voor SQL-databases in Azure. Het biedt bescherming tegen bekende kwetsbaarheden, SQL-injecties en ongeautoriseerde toegangspogingen. Door de juiste configuraties en beveiligingsinstellingen te implementeren, kan men ervoor zorgen dat de databaseomgeving goed beschermd is tegen potentiële aanvallen.