Prozkoumání kontrol údržby databáze
Optimalizátor dotazů využívá statistické informace z indexů k pokusu o vytvoření optimálního plánu provádění.
V rámci úloh údržby Azure SQL, jako jsou zálohy a kontroly integrity, se za vás zpracovávají a i když možná budete moct s automatickými aktualizacemi udržovat statistiky aktuální, někdy to nestačí.
Správné indexy a statistiky zajistí optimální efektivitu každého daného plánu. Údržba indexů by se měla provádět pravidelně, protože se data v databázích mění v průběhu času. Strategii údržby indexů můžete změnit na základě četnosti úprav dat.
Opětovné sestavení a změna uspořádání
Fragmentace indexu nastane, když logické řazení na stránkách indexu neodpovídá fyzickému řazení. Stránky mohou být mimo pořadí během rutinních příkazů úprav dat, jako UPDATEjsou , DELETEa INSERT. Fragmentace může představovat problémy s výkonem kvůli nadbytečným vstupně-výstupním operacím potřebným k vyhledání dat, na která odkazují ukazatele na stránkách indexu.
Při vkládání, aktualizaci a odstranění dat z indexů nebude logické řazení v indexu odpovídat fyzickému pořadí uvnitř stránek a mezi stránkami, které tvoří indexy. V průběhu času můžou změny dat způsobit, že se data v databázi můžou bodovat nebo fragmentovat. Fragmentace může snížit výkon dotazů, když databázový stroj potřebuje číst další stránky, aby bylo možné vyhledat potřebná data.
Změna uspořádání indexu je online operace, která defraguje úroveň listu indexu (clusterované i neclusterované). Tento proces defragmentace fyzicky změní pořadí stránek na úrovni listu tak, aby odpovídalo logickému pořadí uzlů zleva doprava. Během tohoto procesu jsou indexové stránky také komprimovány na základě nakonfigurované hodnoty fillfactor.
Opětovné sestavení může být buď online, nebo offline v závislosti na použitém příkazu nebo edici SQL Serveru. Offline proces opětovného sestavení zahodí a znovu vytvoří samotný index. Pokud to můžete udělat online, vytvoří se nový index paralelně s existujícím indexem. Po vytvoření nového indexu se stávající index zahodí a nový index se přejmenuje tak, aby odpovídal původnímu názvu indexu. Mějte na paměti, že online verze bude vyžadovat více místa, protože nový index je integrovaný paralelně s existujícím indexem.
Běžné pokyny pro údržbu indexů:
> 5 % ale < 30 % – Změna uspořádání indexu
>30 % – Opětovné sestavení indexu
Tato čísla použijte jako obecná doporučení. V závislosti na vašich úlohách a datech možná budete muset být assertivnější nebo v některých případech budete moct odložit údržbu indexů pro databáze, které většinou provádějí dotazy, které hledají konkrétní stránky.
Platformy SQL Server a Azure SQL nabízejí zobrazení dynamické správy, které umožňují detekovat fragmentaci objektů. Nejčastěji používané zobrazení dynamické správy pro tento účel jsou sys.dm_db_index_physical_stats pro indexy b-tree a sys.dm_db_column_store_row_group_physical_stats pro indexy columnstore.
Další věcí, kterou je třeba poznamenat, je, že opětovné sestavení indexu způsobí aktualizaci statistik indexu, což může dále pomoct výkonu. Změna uspořádání indexu neaktualizuje statistiky.
Společnost Microsoft zavedla operace opětovného sestavení indexu s SQL Serverem 2017. Možnost obnovitelného opětovného sestavení indexu poskytuje větší flexibilitu při řízení, kolik času může operace opětovného sestavení pro danou instanci zavést. S SQL Serverem 2019 se dále zavedla možnost řídit přidružený maximální stupeň paralelismu, který správcům databází poskytuje podrobnější kontrolu.
Statistika
Při ladění výkonu v Azure SQL je důležité porozumět důležitosti statistik.
Statistiky se ukládají v uživatelské databázi jako binární velké objekty (objekty blob). Tyto objekty blob obsahují statistické informace o rozdělení hodnot dat v jednom nebo více sloupcích tabulky nebo indexovaného zobrazení.
Statistika obsahuje informace o distribuci datových hodnot v rámci sloupce. Optimalizátor dotazů používá statistiky sloupců a indexů k určení kardinality, což je počet řádků, které má dotaz vrátit.
Odhady kardinality pak používá optimalizátor dotazů k vygenerování plánu provádění. Odhady kardinality také pomáhají optimalizátoru určit, jaký typ operace (například hledání indexu nebo prohledávání) se má použít k načtení požadovaných dat.
Pokud chcete zobrazit seznam uživatelem definovaných statistik s datem poslední aktualizace, spusťte následující dotaz:
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
Vytvoření statistiky
Pokud máte AUTO_CREATE_STATISTICS možnost ON, optimalizátor dotazů ve výchozím nastavení vytvoří statistiky indexovaného sloupce. Optimalizátor dotazů také vytvoří statistiky pro jednotlivé sloupce v predikátech dotazů.
Tyto metody poskytují vysoce kvalitní plány dotazů pro většinu dotazů. Někdy možná budete muset vytvořit další statistiky pomocí CREATE STATISTICS příkazu, abyste zlepšili konkrétní plány dotazů.
Doporučujeme ponechat možnost povolenou AUTO_CREATE_STATISTICS , protože optimalizátor dotazů automaticky vytvoří statistiky pro sloupce predikátu dotazu.
Kdykoli narazíte na následující situace, zvažte vytvoření statistiky:
- Poradce pro optimalizaci databázového stroje navrhuje vytváření statistik.
- Predikát dotazu obsahuje více sloupců, které ještě nejsou ve stejném indexu.
- Dotaz vybere z podmnožina dat.
- Dotaz neobsahuje statistiky.
Automatizace úloh údržby
Azure SQL poskytuje nativní nástroje pro provádění úloh údržby databáze pro účely automatizace. V závislosti na platformě, ve které je databáze spuštěná, jsou k dispozici různé nástroje.
SQL Server na virtuálním počítači Azure
Máte přístup k plánovacím službám, jako je agent SQL nebo plánovač úloh systému Windows. Tyto automatizační nástroje můžou pomoct udržet minimální míru fragmentace v indexech. U větších databází je potřeba najít rovnováhu mezi opětovným sestavením a reorganizací indexů, aby byl zajištěn optimální výkon. Flexibilita poskytovaná agentem SQL nebo plánovačem úloh umožňuje spouštět vlastní úlohy.
Azure SQL Database
Vzhledem k povaze služby Azure SQL Database nemáte přístup k agentovi SQL Serveru ani plánovači úloh Windows. Bez těchto služeb je nutné vytvořit údržbu indexů pomocí jiných metod. Operace údržby pro službu SQL Database můžete spravovat třemi způsoby:
Runbooky služby Azure Automation
Úloha agenta SQL z SQL Serveru na virtuálním počítači Azure (vzdálené volání)
Elastické úlohy Azure SQL
Azure SQL Managed Instance
Stejně jako u SQL Serveru na virtuálním počítači Azure můžete plánovat úlohy ve spravované instanci SQL prostřednictvím agenta SQL Serveru. Použití agenta SQL Serveru poskytuje flexibilitu při spouštění kódu navrženého tak, aby se snížila fragmentace v indexech v databázi.