Sdílet prostřednictvím

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

  • Článek

Platí pro: Azure SQL Managed Instance

Tento článek popisuje, jak monitorovat a spravovat soubory v databázích ve službě Azure SQL Managed Instance. Projdeme si, 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. I když je to velmi podobné, 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.

Principy typů prostoru úložiště 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 zapojených do operace a jakékoli fragmentace. Například odstranění jednoho řádku z každé datové stránky nemusí nutně zmenšit využitý prostor.
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 po odstranění se nikdy nezmenší. Toto chování zajišťuje, aby budoucí vložení bylo 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. Velikost přiděleného datového prostoru se nemůže zvětšovat 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;

Monitorování využití prostoru protokolu

Monitorujte využití prostoru protokolu pomocí sys.dm_db_log_space_usage. Tato zobrazení dynamické správy vrací informace o množství aktuálně používaného prostoru protokolu a označuje, kdy transakční protokol potřebuje zkrácení.

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

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 powershellových metrikách get-metrics.

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í je rozdíl 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 pravidelnou operaci údržby. Data a soubory protokolů, které se zvětšují z důvodu pravidelných opakovaných obchodních operací, 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í. Nedoporučujeme zmenšovat datové soubory, pokud normální provoz aplikace způsobí, že soubory časem opět narostou na stejnou velikost.

Mějte na paměti potenciální negativní dopad na výkon zmenšení databázových souborů, viz Údržba indexu po zmenšení. Ve výjimečných případech můžou být operace zmenšení ovlivněny automatizovanými zálohami databáze. 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ů zvýší režijní náklady na výkon. Další informace najdete v tématu Doporučení.

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ýchpřípadechch

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í protokolu.

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 ooperacích

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

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

Monitorování prostoru protokolu

Údržba indexu po zmenšení

Po dokončení operace zmenšení datových souborů se indexy můžou fragmentovat. Tím se snižuje efektivita optimalizace výkonu u určitých úloh, 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 stránky indexu

Pokud zkrácení datových souborů nezpůsobilo dostatečné snížení přiděleného místa, 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šování dokončí rychleji, pokud je hustota stránky vysoká, protože bude nutné přesunout 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. To také umožní zmenšit dosažení hlubšího 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 strávenou 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 ale, že se tím nedotáčíte limitů prostředků databáze, a ponechte dostatečný prostor pro prostředky pro úlohy aplikací. Monitorování spotřeby prostředků (cpu, vstupně-výstupních operací dat, vstupně-výstupních operací protokolu) na webu Azure Portal nebo pomocí zobrazení sys.dm_db_resource_stats a spuštění dalších paralelních opětovného 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 využití procesoru, vstupně-výstupních operací dat nebo vstupně-výstupních operací protokolů činí 100 %, můžete vertikálně navýšit kapacitu databáze tak, aby měla více jader procesoru a zvýšila propustnost vstupně-výstupních operací, což umožňuje rychlejší další paralelní opětovné sestavení.

Ukázkový příkaz pro opětovné sestavení 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. To umožňuje souběžným úlohám pokračovat v používání tabulky, zatímco probíhá opětovné sestavení, a pokud dojde k přerušení opětovného sestavení z nějakého důvodu, můžete obnovit opětovné sestavení. 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 ONLINE a odeberte klauzuli a RESUMABLE OFF odeberte ji 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ě. Uděláte to tak, že otevřete více databázových relací a použijete DBCC SHRINKFILE je pro každou 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, Vstupně-výstupních operací dat, vstupně-výstupních operací protokolu).

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ě nebude možné zmenšit přidělené místo 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 se soubor zkrátí a zmenší jeho přidělenou velikost na 170 000 MB. Potom můžete tento příkaz zopakovat, nastavit cíl jako první na 140 000 MB, pak na 110 000 MB atd., 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 prakticky beze změny po dlouhou dobu, i když stále probíhá zmenšení.

Po dokončení zmenšení všech datových souborů použijte dotaz na využití místa k určení výsledného snížení 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. To 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šení souboru protokolu

Ve službě Azure SQL Managed Instance přidejte místo do souboru protokolu rozšířením existujícího souboru protokolu (pokud to umožňuje místo na disku). 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 syntaxi.MAXSIZE Další informace naleznete v tématu ALTER DATABASE (Transact-SQL) Soubor a možnosti 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) Filegroup a Filegroup. Je potřeba upozornit na následující:

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

Doporučení

Tady je několik obecných doporučení při práci se soubory transakčních protokolů:

  • Automatický růst (autogrow) inkrementace transakčního protokolu, jak je nastavená možností FILEGROWTH , musí být dostatečně velký, aby zůstal před potřebami transakcí úloh. Růst souboru by měl být dostatečně velký, aby nedocházelo k častému rozšíření. Dobrým ukazatelem na správnou velikost transakčního protokolu je monitorování množství protokolu obsazeného během:

    • Doba potřebná ke spuštění ú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.

  • Při nastavování automatického zvětšování dat a souborů protokolů pomocí FILEGROWTH této možnosti může být vhodnější ji nastavit size místo percentage, aby bylo možné lépe řídit poměr růstu, protože procento je stále rostoucí množství.

    • Ve službě Azure SQL Managed Instance může okamžité inicializace souborů využívat události růstu transakčních protokolů až do 64 MB. Výchozí velikost automatického růstu 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 nelze využít při okamžité inicializaci souborů.
    • Osvědčeným postupem je nenastavovat FILEGROWTH hodnotu možnosti nad 1 024 MB pro protokoly transakcí.

  • Malý automatický nárůst může generovat příliš mnoho malých VLF a může 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.

  • Velký přírůstek automatického růstu může způsobit dva problémy:

    • Velký automatický nárůst může způsobit pozastavení databáze při přidělení nového místa, což může způsobit vypršení časového limitu dotazu.
    • Velký automatický nárůst 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) Soubor a možnosti skupiny souborů.

  • Soubory protokolů je možné nastavit tak, aby se automaticky zmenšují. Toto se však nedoporučuje a vlastnost databáze auto_shrink je ve výchozím nastavení nastavena na HODNOTU FALSE. Pokud je auto_shrink nastavena 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