Sdílet prostřednictvím

Správa prostoru souborů pro databáze ve službě Azure SQL Managed Instance

Platí pro:Azure SQL Managed Instance

Tento článek vysvětluje, jak monitorovat a spravovat soubory v databázích ve službě Azure SQL Managed Instance. Popisuje, jak monitorovat velikost souboru databáze, zmenšit transakční protokol, zvětšit soubor transakčního protokolu a řídit růst souboru transakčního protokolu.

Tento článek se týká služby Azure SQL Managed Instance. Informace o správě velikosti souborů transakčních protokolů v SQL Serveru naleznete v tématu Správa velikosti souboru transakčního protokolu.

Pochopit druhy úložného prostoru pro databázi

Pro správu prostoru souborů databáze je důležité porozumět následujícímu množství úložného prostoru.

Množství v databázi Definice Komentáře
Využité datové místo Velikost místa použitého k ukládání databázových dat. Obecně platí, že se při vkládání (odstranění) zvyšuje využité místo (snižuje). V některých případech se využité místo při vkládání nebo odstraňování nemění v závislosti na množství a vzoru dat zahrnutých v operaci a jakékoli fragmentaci. Například odstranění jednoho řádku z každé datové stránky nemusí nutně snížit využité místo.
Přidělený datový prostor Množství formátovaného prostoru pro soubory, které je k dispozici pro ukládání dat databáze. Přidělený prostor se automaticky zvětšuje, ale nikdy se nesníží poté, co jsou data odstraněna. Toto chování zajišťuje, že budoucí vložení budou rychlejší, protože není nutné přeformátovat mezery.
Přidělený datový prostor, ale nevyužitý Rozdíl mezi velikostí přiděleného datového prostoru a využitou velikostí datového prostoru. Tato velikost představuje maximální objem volného prostoru, který je možné získat zpět zmenšením datových souborů databáze.
Maximální velikost dat Maximální množství místa, které lze použít k ukládání databázových dat. Množství přiděleného datového prostoru se nemůže zvětšit nad rámec maximální velikosti dat.

Následující diagram znázorňuje vztah mezi různými typy prostoru úložiště pro databázi.

Diagram znázorňuje velikost konceptů rozdílového prostoru databáze v tabulce množství databáze.

Dotazování na jednoúčelovou databázi s informacemi o prostoru souborů

Pomocí následujícího dotazu na sys.database_files vrátíte přidělený prostor databázového souboru a přidělené množství nevyužitého místa. Výsledek dotazu je v megabajtech (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;

Sledovat využití prostoru pro záznamy

Monitorujte využití prostoru protokolu pomocí sys.dm_db_log_space_usage. Tato dynamická správa vrací informace o aktuálně použitém místě v protokolu a označuje, kdy je třeba zkrátit transakční protokol.

Informace o aktuální velikosti souboru protokolu, jeho maximální velikosti a možnosti automatického zvětšování souboru použijte sizemax_sizev sys.database_files , a growth sloupce pro tento soubor protokolu.

Metriky prostoru úložiště zobrazené v rozhraních API metrik založených na Azure Resource Manageru měří jenom velikost použitých datových stránek. Příklady najdete v tématu PowerShell Get-AZMetric.

Zmenšení velikosti souboru protokolu

Pokud chcete zmenšit fyzickou velikost souboru fyzického protokolu odebráním nepoužívaného místa, zmenšete soubor protokolu. Zmenšení má význam pouze v případě, že soubor transakčního protokolu obsahuje nevyužité místo. Pokud je soubor protokolu plný, pravděpodobně kvůli otevřeným transakcím, prozkoumejte , co brání zkrácení transakčního protokolu.

Upozornění

Operace zmenšení by se neměly považovat za běžnou operaci údržby. Data a soubory protokolů, které se zvětšují kvůli pravidelným opakovaným obchodním operacím, nevyžadují operace zmenšení. Příkazy, které zmenšují databázi, ovlivňují výkon spuštěné databáze. Proto pokud je to možné, spouštějte je v době jejího nízkého využití. Zmenšení datových souborů se nedoporučuje, pokud běžná úloha aplikace způsobí, že se soubory znovu zvětší na stejnou přidělenou velikost.

Mějte na paměti potenciální negativní dopad na výkon zmenšení databázových souborů. Další informace najdete v tématu Údržba indexů po zmenšení. Ve výjimečných případech můžou automatizované zálohy databáze ovlivnit operace zmenšení. V případě potřeby zkuste operaci zmenšení zopakovat.

Před zmenšením transakčního protokolu mějte na paměti faktory, které můžou zpozdit zkrácení protokolu. Pokud se po zmenšení protokolu znovu vyžaduje prostor úložiště, transakční protokol se znovu zvětší a tím se při operacích růstu protokolů zavádějí režijní náklady na výkon. Další informace najdete v části s doporučeními .

Soubor protokolu můžete zmenšit jenom v případě, že je databáze online a alespoň jeden soubor virtuálního protokolu (VLF) je zdarma. V některých případech nemusí být zmenšení protokolu možné až po dalším zkrácení protokolu.

Faktory, jako je dlouhotrvající transakce, mohou uchovávat VLF aktivní po delší dobu, mohou omezit zmenšení protokolu nebo dokonce zabránit zmenšení protokolu vůbec. Informace naleznete v tématu Faktory, které mohou zpozdit zkrácení logu.

Zmenšení souboru protokolu odebere jeden nebo více souborů VLF , které neobsahují žádnou část logického protokolu (to znamená neaktivní soubory VLF). Když zmenšíte soubor transakčního protokolu, neaktivní soubory VLF se odeberou z konce souboru protokolu, aby se protokol snížil na přibližně cílovou velikost.

Další informace o operacích zmenšení najdete v následující dokumentaci:

Zmenšení souboru protokolu (bez zmenšení databázových souborů)

Monitorování událostí zmenšení souboru protokolu

Monitorování logovacího prostoru

Údržba indexu po zmenšení

Po dokončení operace zmenšení datových souborů se indexy můžou fragmentovat. Fragmentace snižuje efektivitu optimalizace výkonu indexu pro určité úlohy, jako jsou dotazy využívající rozsáhlé kontroly. Pokud po dokončení operace zmenšení dojde ke snížení výkonu, zvažte údržbu indexů při opětovném sestavení indexů. Mějte na paměti, že opětovné sestavení indexu vyžaduje volné místo v databázi, a proto může způsobit zvětšení přiděleného prostoru, což snižuje účinek zmenšení.

Další informace o údržbě indexů naleznete v tématu Optimalizace údržby indexů za účelem zlepšení výkonu dotazů a snížení spotřeby prostředků.

Vyhodnocení hustoty indexové stránky

Pokud zkrácení datových souborů nezpůsobí dostatečné snížení přiděleného prostoru, můžete se rozhodnout zmenšit datové soubory databáze a uvolnit tak nevyužité místo z těchto souborů. Jako volitelný, ale doporučený krok byste ale měli nejprve určit průměrnou hustotu stránky pro indexy v databázi. U stejného množství dat se zmenší rychleji, pokud je hustota stránky vysoká, protože přesune méně stránek. Pokud je hustota stránky u některých indexů nízká, zvažte údržbu těchto indexů, abyste před zmenšením datových souborů zvýšili hustotu stránky. Tento krok umožňuje zmenšit větší snížení přiděleného prostoru úložiště.

Pokud chcete určit hustotu stránky pro všechny indexy v databázi, použijte následující dotaz. Hustota stránky se vyhlásí ve sloupci 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;

Pokud existují indexy s vysokým počtem stránek, které mají hustotu stránky nižší než 60–70 %, zvažte před zmenšením datových souborů opětovné sestavení nebo změna uspořádání těchto indexů.

Poznámka:

U větších databází může dokončení dotazu na určení hustoty stránky trvat dlouhou dobu (hodiny). Opětovné sestavení nebo změna uspořádání velkých indexů navíc vyžaduje značné množství času a využití prostředků. Existuje kompromis mezi tím, že strávíte delší dobu na zvýšení hustoty stránky na jedné straně a snížíte dobu trvání zmenšení a dosáhnete vyšších úspor místa na druhé.

Pokud existuje více indexů s nízkou hustotou stránky, můžete je paralelně znovu sestavit v několika databázových relacích, aby se proces urychlil. Ujistěte se však, že se tím nedotáčíte limitů prostředků databáze, a ponechte dostatečný prostor pro prostředky pro úlohy aplikací. Monitorujte spotřebu prostředků (procesor, vstupně-výstupní operace dat, vstupně-výstupní operace protokolu) na webu Azure Portal nebo pomocí zobrazení sys.dm_db_resource_stats . Spusťte další paralelní opětovné sestavení pouze v případě, že využití prostředků v každé z těchto dimenzí zůstává podstatně nižší než 100%. Pokud je využití procesoru, vstupně-výstupních operací nebo vstupně-výstupních operací protokolů 100%, můžete vertikálně navýšit kapacitu databáze, aby měla více jader procesoru a zvýšila propustnost vstupně-výstupních operací, což umožňuje rychlejší paralelní opětovné sestavení.

Příklad příkazu pro obnovu indexu

Následuje ukázkový příkaz k opětovnému sestavení indexu a zvýšení hustoty stránky pomocí příkazu 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);

Tento příkaz zahájí online a obnovitelné opětovné sestavení indexu. Tento typ opětovného sestavení umožňuje souběžným úlohám pokračovat v používání tabulky, zatímco probíhá opětovné sestavení, a umožňuje obnovení opětovného sestavení, pokud se z nějakého důvodu přeruší. Tento typ opětovného sestavení je však pomalejší než offline opětovné sestavení, které blokuje přístup k tabulce. Pokud během opětovného sestavení nemusí k tabulce přistupovat žádné jiné úlohy, nastavte možnosti ONLINE a RESUMABLE na OFF a odeberte klauzuli WAIT_AT_LOW_PRIORITY.

Další informace o údržbě indexů najdete v tématu Optimalizace údržby indexů za účelem zlepšení výkonu dotazů a snížení spotřeby prostředků.

Zmenšení více datových souborů

Jak už jsme uvedli dříve, zmenšení s přesunem dat je dlouhotrvající proces. Pokud databáze obsahuje více datových souborů, můžete proces urychlit zmenšením několika datových souborů paralelně. Tuto operaci provedete otevřením více databázových relací a použitím DBCC SHRINKFILE v každé relaci s jinou file_id hodnotou. Podobně jako při opětovném sestavení indexů se ujistěte, že máte před spuštěním každého nového příkazu paralelního zmenšení dostatek prostředků (CPU, datových I/O operací, protokolových I/O operací).

Následující ukázkový příkaz zmenší datový soubor o file_id 4 a pokusí se zmenšit přidělenou velikost na 52 000 MB přesunutím stránek v souboru:

DBCC SHRINKFILE (4, 52000);

Pokud chcete zmenšit přidělené místo pro soubor na minimum, spusťte příkaz bez zadání cílové velikosti:

DBCC SHRINKFILE (4);

Pokud úloha běží souběžně se zmenšením, může začít využívat prostor úložiště uvolněný zmenšením před dokončením zmenšení a zkrátí soubor. V takovém případě zmenšení nemůže snížit přidělený prostor určenému cíli.

Tento problém můžete zmírnit zmenšením jednotlivých souborů v menších krocích. To znamená, že v DBCC SHRINKFILE příkazu nastavíte cíl, který je o něco menší než aktuální přidělený prostor pro soubor. Pokud je například přidělený prostor pro soubor s file_id 4 je 200 000 MB a chcete ho zmenšit na 100 000 MB, můžete nejprve nastavit cíl na 170 000 MB:

DBCC SHRINKFILE (4, 170000);

Po dokončení tohoto příkazu zkrátí soubor a zmenšuje jeho přidělenou velikost na 170 000 MB. Potom můžete tento příkaz zopakovat, nastavit nejprve cíl na 140 000 MB, pak na 110 000 MB a tak dále, dokud se soubor nesmaže na požadovanou velikost. Pokud se příkaz dokončí, ale soubor není zkrácený, použijte menší kroky, například 15 000 MB místo 30 000 MB.

Pokud chcete monitorovat průběh zmenšení pro všechny souběžně spuštěné relace zmenšení, můžete použít následující dotaz:

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');

Poznámka:

Průběh zmenšení může být nelineární a hodnota ve percent_complete sloupci může zůstat po dlouhou dobu beze změny, i když stále probíhá zmenšení.

Jakmile se zmenší pro všechny datové soubory, použijte dotaz využití místa k určení výsledné redukce přidělené velikosti úložiště. Pokud je stále velký rozdíl mezi využitým prostorem a přiděleným prostorem, můžete znovu sestavit indexy. Opětovné sestavení může dočasně zvětšit přidělený prostor, ale zmenšení datových souborů po opětovném sestavení indexů by mělo vést k hlubšímu snížení přiděleného prostoru.

Zvětšete soubor protokolu

Ve službě Azure SQL Managed Instance můžete do souboru protokolu přidat místo tak, že zvětšujete existující soubor protokolu, pokud je místo na disku povolené. Přidání souboru protokolu do databáze se nepodporuje. Jeden soubor transakčního protokolu stačí, pokud není nedostatek místa na protokolu a na svazku, který obsahuje soubor protokolu, dochází také místo na disku.

Pokud chcete zvětšit soubor protokolu, použijte MODIFY FILE klauzuli ALTER DATABASE příkazu a zadejte SIZE a MAXSIZE syntaxi. Další informace naleznete v tématu ALTER DATABASE (Transact-SQL) Možnosti souboru a skupiny souborů.

Další informace najdete v tématu Doporučení.

Řízení růstu souboru transakčního protokolu

Ke správě růstu souboru transakčního protokolu použijte příkaz ALTER DATABASE (Transact-SQL) File and Filegroup options . Všimněte si následujících možností:

  • SIZE Pomocí možnosti můžete změnit aktuální velikost souboru v jednotkách KB, MB, GB a TB.
  • FILEGROWTH Pomocí možnosti můžete změnit přírůstek růstu. Hodnota 0 označuje, že je automatický růst nastavený na vypnutý a není povolen žádný dodatečný prostor.
  • MAXSIZE Pomocí možnosti můžete řídit maximální velikost souboru protokolu v kB, MB, GB a TB jednotky nebo nastavit růst na UNLIMITED.

Doporučení

Při práci se soubory transakčních protokolů zvažte následující doporučení:

  • Nastavte automatický růst (autogrow) inkrementace transakčního protokolu, jak je nakonfigurovaná podle FILEGROWTH možnosti, aby byl dostatečně velký, aby splňoval požadavky vašich transakcí úloh. Zvýšení růstu souboru v souboru protokolu dostatečně velké, aby nedocházelo k častému rozšíření. Transakční protokol můžete správně nastavit tak, že monitorujete množství protokolu, který je obsazen během:

    • Doba potřebná k provedení úplného zálohování, protože zálohy protokolů se nedají provést, dokud se nedokončí.
    • Doba potřebná pro největší operace údržby indexů.
    • Doba potřebná ke spuštění největší dávky v databázi.

  • Nastavte automatické zvětšování dat a souborů protokolů pomocí FILEGROWTH možnosti size místo percentagetoho, abyste umožnili lepší kontrolu nad poměrem růstu, protože procento je stále rostoucí množství.

    • Ve službě Azure SQL Managed Instance může okamžitá inicializace souborů zlepšit události růstu transakčních protokolů do 64 MB. Výchozí velikost automatického zvětšování pro nové databáze je 64 MB. Události automatického zvětšování souboru transakčního protokolu větší než 64 MB nemohou těžit z okamžité inicializace souborů.
    • Osvědčeným postupem je nenastavovat FILEGROWTH hodnotu možnosti nad 1 024 MB pro protokoly transakcí.

  • Vyhněte se nastavení malého automatického přírůstku, protože může generovat příliš mnoho malých VLF a snížit výkon. Chcete-li určit optimální distribuci VLF pro aktuální velikost transakčního protokolu všech databází v dané instanci a požadované zvýšení růstu k dosažení požadované velikosti, přečtěte si tento skript pro analýzu a opravu souborů VLF, které poskytuje tým SQL Tiger.

  • Vyhněte se nastavení velkého automatického přírůstku, protože to může způsobit dva problémy:

    • Databáze se může pozastavit, když je přiděleno nové místo, což může způsobit vypršení časových limitů dotazů.
    • Může generovat příliš málo a velké VLF a může také ovlivnit výkon. Chcete-li určit optimální distribuci VLF pro aktuální velikost transakčního protokolu všech databází v dané instanci a požadované zvýšení růstu k dosažení požadované velikosti, přečtěte si tento skript pro analýzu a opravu souborů VLF, které poskytuje tým SQL Tiger.

  • I s povoleným automatickým zvětšováním můžete obdržet zprávu, že transakční protokol je plný, pokud nemůže dostatečně rychle narůst, aby vyhovoval potřebám vašeho dotazu. Další informace o změně přírůstku růstu najdete v tématu ALTER DATABASE (Transact-SQL) možnosti souborů a skupin souborů.

  • Soubory protokolu můžete nastavit tak, aby se automaticky zmenšují. Tento postup se však nedoporučuje a vlastnost databáze auto_shrink je ve výchozím nastavení nastavena na HODNOTU NEPRAVDA. Pokud nastavíte auto_shrink na hodnotu PRAVDA, automatické zmenšení zmenší velikost souboru pouze v případě, že je nevyužito více než 25 procent místa.

Související obsah

  • Last updated on