Bestandsruimte voor databases beheren in Azure SQL Managed Instance

van toepassing op:Azure SQL Managed Instance

In dit artikel wordt uitgelegd hoe u bestanden in databases in Azure SQL Managed Instance bewaakt en beheert. Hierin wordt beschreven hoe u de grootte van het databasebestand bewaakt, het transactielogboek verkleint, een transactielogboekbestand vergroot en de groei van een transactielogboekbestand bepaalt.

Dit artikel is van toepassing op Azure SQL Managed Instance. Zie De grootte van het transactielogboekbestand beheren in SQL Server voor meer informatie over het beheren van de grootte van transactielogboekbestanden.

Inzicht in typen opslagruimte voor een database

Inzicht in de volgende opslagruimten is belangrijk voor het beheren van de bestandsruimte van een database.

In het volgende diagram ziet u de relatie tussen de verschillende typen opslagruimte voor een database.

Een query uitvoeren op één database voor informatie over de bestandsruimte

Gebruik de volgende query op sys.database_files om de hoeveelheid toegewezen databasebestandsruimte en de toegewezen hoeveelheid ongebruikte ruimte te retourneren. Eenheden van het queryresultaat zijn in MB.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

Gebruik van logboekruimte bewaken

Bewaak het gebruik van logboekruimte met behulp van sys.dm_db_log_space_usage. Deze DMV retourneert informatie over de hoeveelheid gebruikte logboekruimte en geeft aan wanneer het transactielogboek moet worden afgekapt.

Voor informatie over de huidige logboekbestandsgrootte, de maximale grootte en de optie voor automatisch vergroten van het bestand, gebruikt u het size, max_sizeen growth de kolommen voor dat logboekbestand in sys.database_files.

Metrische gegevens over opslagruimte die worden weergegeven in de op Azure Resource Manager gebaseerde API's voor metrische gegevens meten alleen de grootte van gebruikte gegevenspagina's. Zie PowerShell Get-AZMetric voor voorbeelden.

Grootte van logboekbestand verkleinen

Als u de fysieke grootte van een fysiek logboekbestand wilt verkleinen door ongebruikte ruimte te verwijderen, verkleint u het logboekbestand. Een verkleining maakt alleen een verschil wanneer een transactielogboekbestand ongebruikte ruimte bevat. Als het logboekbestand vol is, waarschijnlijk vanwege geopende transacties, onderzoekt u wat verhindert dat het transactielogboek wordt afgekapt.

Houd rekening met de mogelijke negatieve invloed op de prestaties van het verkleinen van databasebestanden. Zie indexonderhoud na verkleinen voor meer informatie. In zeldzame gevallen kunnen geautomatiseerde databaseback-ups van invloed zijn op verkleiningsbewerkingen. Voer indien nodig de verkleiningsbewerking opnieuw uit.

Voordat u het transactielogboek verkleint, moet u rekening houden met factoren die het afkappen van logboeken kunnen vertragen. Als de opslagruimte opnieuw nodig is nadat een logboek is verkleind, neemt het transactielogboek weer toe. Hierdoor ontstaat er prestatieoverhead tijdens logboekgroeibewerkingen. Zie de sectie aanbevelingen voor meer informatie.

U kunt een logboekbestand alleen verkleinen terwijl de database online is en ten minste één VLF- (virtual log file) gratis is. In sommige gevallen kan het verkleinen van het logboek pas na de volgende logboektruncatie plaatsvinden.

Factoren, zoals een langlopende transactie, kunnen VLF's gedurende een langere periode actief houden, de krimp van het logboek beperken of zelfs voorkomen dat het logboek helemaal wordt verkleind. Zie Factoren die het afkappen van logboeken kunnen vertragenvoor meer informatie.

Als u een logboekbestand verkleint, worden een of meer VLF's verwijderd die geen deel uitmaken van het logische logboek (dat wil gezegd inactieve VLF's). Wanneer u een transactielogboekbestand verkleint, worden inactieve VLF's verwijderd aan het einde van het logboekbestand om het logboek te verminderen tot ongeveer de doelgrootte.

Raadpleeg de volgende documentatie voor meer informatie over verkleiningsbewerkingen:

een logboekbestand verkleinen (zonder databasebestanden te verkleinen)

• DBCC SHRINKFILE (Transact-SQL)

• een bestand verkleinen

Gebeurtenissen voor het verkleinen van logboekbestanden controleren

• gebeurtenisklasse voor automatisch verkleinen van logboekbestand.

Logboekruimte bewaken

• sys.dm_db_log_space_usage (Transact-SQL)

• sys.database_files (Transact-SQL) (zie de kolommen size, max_sizeen growth voor het logboekbestand of de logboekbestanden.)

Indexonderhoud na verkleinen

Nadat een verkleiningsbewerking is voltooid voor gegevensbestanden, kunnen indexen worden gefragmenteerd. Fragmentatie vermindert de effectiviteit van prestatieoptimalisatie van een index voor bepaalde workloads, zoals query's met behulp van grote scans. Als prestatievermindering optreedt nadat de verkleiningsbewerking is voltooid, kunt u indexonderhoud overwegen om indexen opnieuw te bouwen. Houd er rekening mee dat voor het herbouwen van indexen vrije ruimte in de database is vereist, waardoor de toegewezen ruimte kan toenemen, waardoor het effect van de verkleining wordt tegengehouden.

Zie Indexonderhoud optimaliseren om de queryprestaties te verbeteren en het resourceverbruik te verminderenvoor meer informatie over indexonderhoud.

Indexpaginadichtheid evalueren

Als het afkappen van gegevensbestanden niet leidt tot een voldoende vermindering van de toegewezen ruimte, kunt u besluiten om databasegegevensbestanden te verkleinen om ongebruikte ruimte uit die bestanden vrij te maken. Als optionele maar aanbevolen stap moet u echter eerst de gemiddelde paginadichtheid voor indexen in de database bepalen. Voor dezelfde hoeveelheid gegevens, verkleint u sneller als de paginadichtheid hoog is, omdat er minder pagina's worden verplaatst. Als de paginadichtheid laag is voor sommige indexen, kunt u overwegen om onderhoud uit te voeren op deze indexen om de paginadichtheid te verhogen voordat u gegevensbestanden verkleint. Met deze stap kunt u verkleinen tot een diepere vermindering van de toegewezen opslagruimte.

Gebruik de volgende query om de paginadichtheid voor alle indexen in de database te bepalen. Paginadichtheid wordt gerapporteerd in de kolom avg_page_space_used_in_percent.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc,
       ips.ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

Als er indexen zijn met een hoog aantal pagina's met een paginadichtheid die lager is dan 60-70%, kunt u overwegen deze indexen opnieuw te bouwen of te herstructureren voordat u gegevensbestanden verkleint.

Als er meerdere indexen met lage paginadichtheid zijn, kunt u deze mogelijk parallel herbouwen op meerdere databasesessies om het proces te versnellen. Zorg er echter voor dat u de limieten voor databaseresources niet nadert door dit te doen en zorg ervoor dat er voldoende ruimte is voor resources voor toepassingsworkloads. Bewaak het resourceverbruik (CPU, Data IO, Log IO) in Azure Portal of gebruik de sys.dm_db_resource_stats weergave. Start verdere parallelle herbouwen alleen als het resourcegebruik voor elk van deze dimensies aanzienlijk lager blijft dan 100%. Als het CPU-, gegevens-IO- of logboek-IO-gebruik 100%is, kunt u de database omhoog schalen om meer CPU-kernen te hebben en io-doorvoer te verhogen, zodat het proces sneller kan worden voltooid.

Voorbeeld van opdracht voor opnieuw samenstellen van index

Hier volgt een voorbeeldopdracht om een index opnieuw te bouwen en de paginadichtheid ervan te vergroten met behulp van de instructie ALTER INDEX:

ALTER INDEX [index_name] ON [schema_name].[table_name]
REBUILD WITH (FILLFACTOR = 100, MAXDOP = 8,
ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)),
RESUMABLE = ON);

Met deze opdracht wordt een online en hervatbare index opnieuw opgebouwd. Met dit type herbouw kunnen gelijktijdige workloads de tabel blijven gebruiken terwijl de herbouw wordt uitgevoerd en kunt u de herbouw hervatten als deze om welke reden dan ook wordt onderbroken. Dit type herbouw is echter langzamer dan een offline herbouwing, waardoor de toegang tot de tabel wordt geblokkeerd. Als er tijdens het opnieuw opbouwen geen andere workloads toegang nodig hebben tot de tabel, stelt u de ONLINE- en RESUMABLE-opties in op OFF en verwijdert u de WAIT_AT_LOW_PRIORITY-component.

Zie Indexonderhoud optimaliseren om de queryprestaties te verbeteren en het resourceverbruik te verminderenvoor meer informatie over indexonderhoud.

Meerdere gegevensbestanden verkleinen

Zoals eerder vermeld, is daling door gegevensverplaatsing een langlopend proces. Als de database meerdere gegevensbestanden heeft, kunt u het proces versnellen door meerdere gegevensbestanden parallel te verkleinen. U doet deze bewerking door meerdere databasesessies te openen en voor elke sessie met een andere file_id waarde te gebruikenDBCC SHRINKFILE. Net als bij het opnieuw opbouwen van indexen moet u ervoor zorgen dat u voldoende ruimte hebt voor resources (CPU, Data IO, Log IO) voordat u elke nieuwe parallelle verkleiningsopdracht start.

Met de volgende voorbeeldopdracht verkleint u het gegevensbestand met file_id 4 en probeert u de toegewezen grootte te verkleinen tot 52.000 MB door pagina's in het bestand te verplaatsen:

DBCC SHRINKFILE (4, 52000);

Als u de toegewezen ruimte voor het bestand zo min mogelijk wilt beperken, voert u de instructie uit zonder de doelgrootte op te geven:

DBCC SHRINKFILE (4);

Als een workload gelijktijdig met het verkleinproces wordt uitgevoerd, kan deze de opslagruimte gebruiken die door het verkleinen is vrijgemaakt, nog voordat het verkleinproces is voltooid en het bestand wordt ingekort. In dit geval kan verkleinen de toegewezen ruimte niet verminderen voor het opgegeven doel.

U kunt dit probleem oplossen door elk bestand in kleinere stappen te verkleinen. Dit betekent dat u in de opdracht DBCC SHRINKFILE het doel instelt dat iets kleiner is dan de huidige toegewezen ruimte voor het bestand. Als bijvoorbeeld toegewezen ruimte voor bestand met file_id 4 200.000 MB is en u deze wilt verkleinen tot 100.000 MB, kunt u het doel eerst instellen op 170.000 MB:

DBCC SHRINKFILE (4, 170000);

Zodra deze opdracht is voltooid, wordt het bestand afgekapt en wordt de toegewezen grootte verkleind tot 170.000 MB. Vervolgens kunt u deze opdracht herhalen, het doel eerst instellen op 140.000 MB, vervolgens op 110.000 MB, enzovoort, totdat het bestand is verkleind tot de gewenste grootte. Als de opdracht is voltooid, maar het bestand niet is afgekapt, gebruikt u kleinere stappen, bijvoorbeeld 15.000 MB in plaats van 30.000 MB.

Als u de voortgang van de verkleining wilt controleren voor alle gelijktijdig uitgevoerde verkleinde sessies, kunt u de volgende query gebruiken:

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       CAST(((DATEDIFF(s,start_time, GETDATE()))/3600) AS varchar) + ' hour(s), '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%3600)/60 AS varchar) + 'min, '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%60) AS varchar) + ' sec' AS running_time
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.databases AS d
ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact','DBCC');

Zodra het verkleinen is voltooid voor alle gegevensbestanden, gebruikt u de query voor ruimtegebruik om de resulterende vermindering van de toegewezen opslaggrootte te bepalen. Als er nog steeds een groot verschil is tussen gebruikte ruimte en toegewezen ruimte, kunt u indexen opnieuw opbouwen. Herbouwen kan de toegewezen ruimte tijdelijk vergroten, maar het opnieuw verkleinen van gegevensbestanden na het opnieuw opbouwen van indexen moet leiden tot een diepere vermindering van de toegewezen ruimte.

Een logboekbestand vergroten

In Azure SQL Managed Instance kunt u ruimte toevoegen aan een logboekbestand door het bestaande logboekbestand te vergroten als schijfruimte is toe te voegen. Het toevoegen van een logboekbestand aan de database wordt niet ondersteund. Eén transactielogboekbestand is voldoende, tenzij de logboekruimte opraakt en schijfruimte ook op het volume met het logboekbestand opraakt.

Als u het logboekbestand wilt vergroten, gebruikt u de MODIFY FILE component van de ALTER DATABASE instructie en geeft u de SIZE en MAXSIZE syntaxis op. Zie ALTER DATABASE (Transact-SQL) Bestands- en bestandsgroepoptiesvoor meer informatie.

Zie de Aanbevelingenvoor meer informatie.

Groei van transactielogboekbestanden beheren

Als u de groei van een transactielogboekbestand wilt beheren, gebruikt u de instructie ALTER DATABASE (Transact-SQL) Bestands- en bestandsgroepopties . Let op de volgende opties:

• Gebruik de SIZE optie om de huidige bestandsgrootte te wijzigen in kB-, MB-, GB- en TB-eenheden.
• Gebruik de FILEGROWTH optie om de toename van de groei te wijzigen. Een waarde van 0 geeft aan dat automatische groei wordt uitgeschakeld en dat er geen extra ruimte is toegestaan.
• Gebruik de MAXSIZE optie om de maximale grootte van een logboekbestand in kB-, MB-, GB- en TB-eenheden te bepalen of om groei in te stellen op UNLIMITED.

Aanbevelingen

Houd rekening met de volgende aanbevelingen wanneer u met transactielogboekbestanden werkt:

• Stel de automatische groei (automatische groei) van het transactielogboek in, zoals geconfigureerd door de FILEGROWTH optie, om groot genoeg te zijn om te voldoen aan de behoeften van uw workloadtransacties. Maak de bestandsgroei op een logboekbestand voldoende groot om frequente uitbreiding te voorkomen. U kunt een transactielogboek op de juiste manier wijzigen door de hoeveelheid logboeken te bewaken die tijdens het volgende worden gebruikt:

  • De tijd die nodig is om een volledige back-up uit te voeren, omdat er geen logboekback-ups kunnen worden uitgevoerd totdat deze is voltooid.
  • De tijd die nodig is voor de grootste indexonderhoudsbewerkingen.
  • De tijd die nodig is om de grootste batch in een database uit te voeren.

• Stel automatisch groei in voor gegevens- en logboekbestanden met behulp van de FILEGROWTH optie in size plaats van percentage, om een betere controle over de groeiverhouding mogelijk te maken, omdat het percentage een steeds groeiende hoeveelheid is.

  • In Azure SQL Managed Instance kan directe bestandsinitialisatie profiteren van groeigebeurtenissen in het transactielog tot 64 MB. De standaard incrementele grootte voor automatische groei voor nieuwe databases is 64 MB. Automatische groei van transactielogboekbestanden die groter zijn dan 64 MB kan niet profiteren van onmiddellijke bestandsinitialisatie.
  • Stel de optiewaarde boven 1024 MB niet FILEGROWTH in voor transactielogboeken.

• Vermijd het instellen van een kleine toename van automatisch groeien omdat deze te veel kleine VLF's kan genereren en de prestaties kan verminderen. Als u de optimale VLF-distributie wilt bepalen voor de huidige grootte van het transactielogboek van alle databases in een bepaald exemplaar en de vereiste groeiverhogingen om de vereiste grootte te bereiken, raadpleegt u dit script voor het analyseren en herstellen van VLF's, geleverd door het SQL Tiger Team.

• Vermijd het instellen van een grote toename van automatische groei, omdat dit twee problemen kan veroorzaken:

  • De database kan onderbreken terwijl de nieuwe ruimte wordt toegewezen, waardoor er mogelijk time-outs voor query's ontstaan.
  • Het kan te weinig en grote VLF's genereren en kan ook van invloed zijn op de prestaties. Als u de optimale VLF-distributie wilt bepalen voor de huidige grootte van het transactielogboek van alle databases in een bepaald exemplaar en de vereiste groeiverhogingen om de vereiste grootte te bereiken, raadpleegt u dit script voor het analyseren en herstellen van VLF's, geleverd door het SQL Tiger Team.

• Zelfs als automatische groei is ingeschakeld, kunt u een bericht ontvangen dat het transactielogboek vol is als het niet snel genoeg kan groeien om aan de behoeften van uw query te voldoen. Zie ALTER DATABASE (Transact-SQL) Bestands- en bestandsgroepoptiesvoor meer informatie over het wijzigen van de groeitoename.

• U kunt logboekbestanden zo instellen dat ze automatisch worden verkleind. Deze procedure wordt echter niet aanbevolen en de eigenschap auto_shrink database is standaard ingesteld op FALSE. Als u auto_shrink instelt op TRUE, vermindert automatisch verkleinen de grootte van een bestand alleen wanneer meer dan 25 procent van de ruimte niet wordt gebruikt.

  • Het bestand is verkleind tot de grootte waarvan slechts 25 procent van het bestand ongebruikte ruimte is of wordt verkleind tot de oorspronkelijke grootte van het bestand, afhankelijk van wat groter is.
  • Zie voor meer informatie over het wijzigen van de instelling van de eigenschap auto_shrink, De eigenschappen van een database bekijken of wijzigen en ALTER DATABASE SET Options (Transact-SQL).

Verwante inhoud

• Automatische back-ups in Azure SQL Managed Instance
• ALTER DATABASE (Transact-SQL) Bestands- en bestandsgroepopties
• Overzicht van resourcelimieten van Azure SQL Managed Instance
• Problemen met transactielogboeken oplossen met azure SQL Managed Instance