Controles voor databaseonderhoud verkennen

De query optimizer maakt gebruik van statistische informatie uit de indexen om te proberen het meest optimale uitvoeringsplan te bouwen.

Binnen Azure SQL worden onderhoudstaken zoals back-ups en integriteitscontroles voor u afgehandeld, en hoewel automatische updates uw statistieken mogelijk up-to-date houden, is dit soms niet voldoende.

Als u goede indexen en statistieken hebt, zorgt u ervoor dat een bepaald plan optimaal presteert. Indexonderhoud moet regelmatig worden uitgevoerd wanneer gegevens in uw databases na verloop van tijd worden gewijzigd. U kunt uw strategie voor indexonderhoud wijzigen op basis van de frequentie van wijzigingen in uw gegevens.

Opnieuw opbouwen en opnieuw indelen

Indexfragmentatie treedt op wanneer logische volgorde binnen indexpagina's niet overeenkomt met de fysieke volgorde. Pagina's kunnen in verkeerde volgorde raken tijdens routinematige gegevenswijzigingsinstructies, zoals UPDATE, DELETE en INSERT. Fragmentatie kan prestatieproblemen veroorzaken vanwege de extra I/O die nodig is om de gegevens te vinden waarnaar wordt verwezen door de aanwijzers binnen de indexpagina's.

Wanneer gegevens worden ingevoegd, bijgewerkt en verwijderd uit indexen, komt de logische volgorde in de index niet meer overeen met de fysieke volgorde binnen de pagina's en tussen de pagina's die de indexen vormen. Na verloop van tijd kunnen de gegevenswijzigingen ertoe leiden dat de gegevens worden verspreid of gefragmenteerd in de database. Fragmentatie kan de queryprestaties verminderen wanneer de database-engine extra pagina's moet lezen om de benodigde gegevens te vinden.

Een reorganisatie van een index is een online bewerking waarmee het bladniveau van zowel geclusterde als niet-geclusterde indexen wordt gedefragmenteerd. Met dit defragmentatieproces worden de pagina's op bladniveau fysiek opnieuw gerangschikt in overeenstemming met de logische volgorde van de knooppunten van links naar rechts. Tijdens dit proces worden de indexpagina's ook gecompacteerd op basis van de geconfigureerde fillfactor-waarde.

Een herbouw kan online of offline zijn, afhankelijk van de opdracht die wordt uitgevoerd of de editie van SQL Server die wordt gebruikt. Bij een offline herbouwproces wordt de index zelf verwijderd en opnieuw gemaakt. Als u dit online kunt doen, wordt een nieuwe index parallel aan de bestaande index gebouwd. Zodra de nieuwe index is gemaakt, wordt de bestaande index verwijderd en wordt de nieuwe naam gewijzigd zodat deze overeenkomt met de oude indexnaam. Houd er rekening mee dat de onlineversie meer ruimte vereist omdat de nieuwe index parallel aan de bestaande index is gebouwd.

De algemene richtlijnen voor indexonderhoud zijn:

• > 5% maar < 30% : de index opnieuw organiseren

• > 30% - De index opnieuw opbouwen

Gebruik deze getallen als algemene aanbevelingen. Afhankelijk van uw workload en gegevens moet u mogelijk assertiever zijn of in sommige gevallen kunt u indexonderhoud uitstellen voor databases die voornamelijk query's uitvoeren die specifieke pagina's opzoeken.

De SQL Server- en Azure SQL-platformen bieden DMV's waarmee u fragmentatie in uw objecten kunt detecteren. De meest gebruikte DMV's voor dit doel zijn sys.dm_db_index_physical_stats voor b-tree-indexen en sys.dm_db_column_store_row_group_physical_stats voor columnstore-indexen.

Een ander punt om op te merken is dat het opnieuw opbouwen van indexen ervoor zorgt dat de indexstatistieken worden bijgewerkt, wat de prestaties verder kan verbeteren. Bij indexreorganisatie worden statistieken niet bijgewerkt.

Microsoft heeft hervatbare indexherbouwbewerkingen geïntroduceerd met SQL Server 2017. De optie voor hervatbare indexherbouwbewerkingen biedt meer flexibiliteit bij het beheren van de tijd die een herbouwbewerking kan belasten voor een bepaald exemplaar. Met SQL Server 2019 werd de mogelijkheid geïntroduceerd om een bijbehorende maximale mate van parallelle uitvoering te beheren, waardoor databasebeheerders meer gedetailleerde controle kregen.

Statistieken

Bij het afstemmen van prestaties in Azure SQL is het belangrijk om inzicht te krijgen in het belang van statistieken.

Statistieken worden opgeslagen in de gebruikersdatabase als binary large objects (BLOBs). Deze blobs bevatten statistische informatie over de verdeling van gegevenswaarden in een of meer kolommen van een tabel of geïndexeerde weergave.

Statistieken bevatten informatie over de verdeling van gegevenswaarden in een kolom. De queryoptimalisatie maakt gebruik van kolom- en indexstatistieken om de kardinaliteit te bepalen. Dit is het aantal rijen dat een query naar verwachting retourneert.

Kardinaliteitschattingen worden vervolgens gebruikt door de queryoptimizer om het uitvoeringsplan te genereren. Kardinaliteitschattingen helpen de optimizer ook te bepalen welk type bewerking (bijvoorbeeld indexzoeken of scannen) moet worden gebruikt om de aangevraagde gegevens op te halen.

Voer de volgende query uit om de lijst met door de gebruiker gedefinieerde statistieken weer te geven met de laatst bijgewerkte datum:

SELECT sp.stats_id, 
       name, 
       last_updated, 
       rows, 
       rows_sampled
FROM sys.stats
     CROSS APPLY sys.dm_db_stats_properties(object_id, stats_id) AS sp
WHERE user_created = 1

Statistieken maken

Wanneer u de optie AUTO_CREATE_STATISTICS instelt op ON, maakt de queryoptimalisator standaard statistieken voor de geïndexeerde kolom. De queryoptimalisator maakt ook statistieken voor afzonderlijke kolommen in querypredicaten.

Deze methoden bieden queryplannen van hoge kwaliteit voor de meeste query's. Soms moet u mogelijk meer statistieken maken met de instructie CREATE STATISTICS om specifieke queryplannen te verbeteren.

Het is raadzaam om de AUTO_CREATE_STATISTICS optie ingeschakeld te houden, omdat de queryoptimalisatie automatisch statistieken voor querypredicaatkolommen kan maken.

Wanneer u de volgende situaties tegenkomt, kunt u overwegen statistieken te maken:

• De Database Engine Tuning Advisor stelt voor om statistieken te maken
• Het querypredicaat bevat meerdere kolommen die zich nog niet in dezelfde index bevinden
• De query selecteert uit een subset met gegevens
• De query bevat ontbrekende statistieken

Automatisering van onderhoudstaken

Azure SQL biedt systeemeigen hulpprogramma's voor het uitvoeren van databaseonderhoudstaken voor automatiseringsdoeleinden. Er zijn verschillende hulpprogramma's beschikbaar, afhankelijk van het platform waarop de database wordt uitgevoerd.

SQL Server op een virtuele Azure-machine

U hebt toegang tot planningsservices, zoals de SQL Agent of de Windows Task Scheduler. Deze automatiseringshulpprogramma's kunnen helpen om de hoeveelheid fragmentatie binnen indexen tot een minimum te beperken. Bij grotere databases moet een balans worden gevonden tussen het opnieuw opbouwen en een reorganisatie van indexen om optimale prestaties te garanderen. Dankzij de flexibiliteit van SQL Agent of Task Scheduler kunt u aangepaste taken uitvoeren.

Azure SQL Database

Vanwege de aard van Azure SQL Database hebt u geen toegang tot SQL Server Agent of Windows Task Scheduler. Zonder deze services moet indexonderhoud met behulp van andere methoden worden uitgevoerd. Er zijn drie manieren om onderhoudsbewerkingen voor SQL Database te beheren:

• Azure Automation runbooks

• SQL Agent-taak van SQL Server in een virtuele Azure-machine (externe aanroep)

• Azure SQL elastic jobs

Azure SQL Managed Instance (een beheerde database-instantie van Azure)

Net als bij SQL Server op een virtuele Azure-machine kunt u taken plannen op een SQL Managed Instance via SQL Server Agent. Het gebruik van SQL Server Agent biedt flexibiliteit om code uit te voeren die is ontworpen om fragmentatie binnen de indexen in de database te verminderen.