Sdílet prostřednictvím

Optimalizace údržby indexů za účelem zlepšení výkonu dotazů a snížení spotřeby prostředků

  • Článek

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)databáze SQL v Microsoft Fabric

Tento článek vám pomůže rozhodnout, kdy a jak provádět údržbu indexů. Popisuje koncepty, jako je fragmentace indexu a hustota stránek a jejich dopad na výkon dotazů a spotřebu prostředků. Popisuje metody údržby indexů, přeuspořádání indexu a opětovné sestavení indexu, a navrhuje strategii údržby indexu, která vyrovnává potenciální zlepšení výkonu oproti spotřebě prostředků vyžadované pro údržbu.

Poznámka

Tento článek se nevztahuje na vyhrazený fond SQL ve službě Azure Synapse Analytics. Informace o údržbě indexů pro vyhrazený fond SQL ve službě Azure Synapse Analytics najdete v tématu Indexování vyhrazených tabulek fondu SQL ve službě Azure Synapse Analytics.

Koncepty: fragmentace indexu a hustota stránek

Co je fragmentace indexů a jak ovlivňuje výkon:

  • V indexech B-tree (rowstore) existuje fragmentace, pokud indexy mají stránky, ve kterých logické řazení v indexu na základě klíčových hodnot indexu neodpovídá fyzickému pořadí stránek indexu.

    Poznámka

    Dokumentace používá termín B-tree obecně v odkazu na indexy. V indexech rowstore databázový stroj implementuje strom B+. To neplatí pro indexy columnstore ani indexy v tabulkách optimalizovaných pro paměť. Další informace najdete v SQL Serveru a architektuře indexu Azure SQL a průvodci návrhem.

  • Databázový stroj automaticky upraví indexy při každé operaci vložení, aktualizace nebo odstranění v podkladových datech. Například přidání řádků v tabulce může způsobit, že existující stránky v indexech rowstore rozdělit, což usnadňuje vkládání nových řádků. Tyto úpravy můžou v průběhu času způsobit, že se data v indexu v databázi rozsadí (fragmentují).

  • U dotazů, které čtou mnoho stránek pomocí prohledávání indexů úplného rozsahu, můžou výrazně fragmentované indexy snížit výkon dotazů v případě, že ke čtení dat je potřeba další vstupně-výstupní operace. Místo malého počtu velkých vstupně-výstupních požadavků by dotaz vyžadoval větší počet malých vstupně-výstupních požadavků ke čtení stejného množství dat.

  • Pokud subsystém úložiště poskytuje lepší sekvenční vstupně-výstupní výkon než výkon náhodných vstupně-výstupních operací, může fragmentace indexu snížit výkon, protože ke čtení fragmentovaných indexů se vyžaduje více náhodných vstupně-výstupních operací.

Co je hustota stránky (označuje se také jako plná stránka) a jak ovlivňuje výkon:

  • Každá stránka v databázi může obsahovat proměnlivý počet řádků. Pokud řádky zabírají všechny mezery na stránce, hustota stránky je 100%. Pokud je stránka prázdná, hustota stránky je 0%. Pokud je stránka s hustotou 100% rozdělena na dvě stránky, aby se přizpůsobila novému řádku, hustota dvou nových stránek je přibližně 50%.
  • Pokud je hustota stránky nízká, k uložení stejného množství dat se vyžaduje více stránek. To znamená, že ke čtení a zápisu těchto dat je potřeba více vstupně-výstupních operací a k ukládání těchto dat do mezipaměti je potřeba více paměti. Pokud je paměť omezená, méně stránek vyžadovaných dotazem se ukládá do mezipaměti, což způsobuje ještě více vstupně-výstupních operací disku. V důsledku toho nízká hustota stránky negativně ovlivňuje výkon.
  • Pokud databázový stroj přidá řádky na stránku během vytváření, opětovného sestavení nebo přeuspořádání indexu, nenaplní stránku úplně, pokud je faktoru vyplnění pro index nastaven na jinou hodnotu než 100 (nebo 0, což je v tomto kontextu ekvivalentní). To způsobuje nižší hustotu stránky a podobně přidává režijní náklady na vstupně-výstupní operace a negativně ovlivňuje výkon.
  • Nízká hustota stránky může zvýšit počet mezilehlých úrovní B-stromu. Tím se mírně zvýší náklady na CPU a I/O při vyhledávání stránek na úrovni listu během indexových skenů a hledání.
  • Když Optimalizátor dotazů zkompiluje plán dotazu, bere v úvahu náklady na vstupně-výstupní operace potřebné ke čtení dat požadovaných dotazem. S nízkou hustotou stránky je k dispozici více stránek ke čtení, takže náklady na vstupně-výstupní operace jsou vyšší. To může mít vliv na volbu plánu dotazu. Například při poklesu hustoty stránky v průběhu času kvůli rozdělení stránek může optimalizátor zkompilovat jiný plán pro stejný dotaz s jiným profilem výkonu a spotřeby prostředků.

Spropitné

V mnoha úlohách má zvýšení hustoty stránek větší pozitivní dopad na výkon než snížení fragmentace.

Aby se zabránilo zbytečnému snížení hustoty stránek, Microsoft nedoporučuje nastavit faktor výplně na hodnoty jiné než 100 nebo 0, s výjimkou některých případů, kdy indexy mají vysoký počet rozdělení stránek, například často upravené indexy s počátečními sloupci obsahujícími nesekvenční hodnoty GUID.

Měření fragmentace indexu a hustoty stránek

Fragmentace i hustota stránek patří mezi faktory, které je potřeba vzít v úvahu při rozhodování, zda provést údržbu indexu a jakou metodu údržby použít.

Fragmentace se definuje jinak pro rowstore indexy a columnstore indexy. U indexů rowstore sys.dm_db_index_physical_stats() umožňuje určit fragmentaci a hustotu stránek v určitém indexu, všechny indexy v tabulce nebo indexované zobrazení, všechny indexy v databázi nebo všechny indexy ve všech databázích. Pro dělené indexy sys.dm_db_index_physical_stats() tyto informace poskytuje pro každý oddíl.

Sada výsledků vrácená sys.dm_db_index_physical_stats obsahuje následující sloupce:

Sloupec Popis
avg_fragmentation_in_percent Logická fragmentace (stránky mimo pořadí v indexu).
avg_page_space_used_in_percent Průměrná hustota stránky

U komprimovaných skupin řádků v indexech columnstore se fragmentace definuje jako poměr odstraněných řádků k celkovým řádkům vyjádřeným v procentech. sys.dm_db_column_store_row_group_physical_stats umožňuje určit celkový a odstraněný počet řádků na skupinu řádků v určitém indexu, všechny indexy v tabulce nebo všechny indexy v databázi.

Sada výsledků vrácená sys.dm_db_column_store_row_group_physical_stats obsahuje následující sloupce:

Sloupec Popis
total_rows Počet řádků fyzicky uložených ve skupině řádků U komprimovaných skupin řádků to zahrnuje řádky, které jsou označené jako odstraněné.
deleted_rows Počet řádků fyzicky uložených ve komprimované skupině řádků, které jsou označené k odstranění. 0 pro skupiny řádků, které jsou v delta úložišti.

Fragmentaci komprimované skupiny řádků v indexu columnstore je možné vypočítat pomocí tohoto vzorce:

100.0*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

Spropitné

U indexů rowstore i columnstore zkontrolujte fragmentaci indexu nebo haldy a hustotu stránky po odstranění nebo aktualizaci velkého počtu řádků. U hald, pokud dochází k častým aktualizacím, pravidelně kontrolujte fragmentaci, abyste se vyhnuli proliferaci záznamů o přesměrování. Další informace o haldách najdete v haldy (tabulky bez clusterovaných indexů).

Podívejte se na Příklady, kde najdete ukázkové dotazy k určení fragmentace a hustoty stránek.

Metody údržby indexu: změna uspořádání a opětovného sestavení

Fragmentaci indexů můžete snížit a zvýšit hustotu stránky pomocí jedné z následujících metod:

  • Změna uspořádání indexu
  • Opětovné sestavení indexu

Poznámka

Pro dělené indexy můžete pro všechny oddíly nebo jeden oddíl indexu použít některou z následujících metod.

Změna uspořádání indexu

Změna uspořádání indexu je méně náročná na prostředky než opětovné sestavení indexu. Z tohoto důvodu by měla být upřednostňovanou metodou údržby indexů, pokud neexistuje konkrétní důvod k použití opětovného sestavení indexu. Změna uspořádání je vždy online operace. To znamená, že dlouhodobé zámky na úrovni objektů se neuchovávají a dotazy nebo aktualizace podkladové tabulky mohou během operace ALTER INDEX ... REORGANIZE pokračovat.

  • Pro indexy rowstoredatabázový stroj defragmentuje pouze listovou úroveň klastrovaných a neklastrovaných indexů v tabulkách a zobrazeních tím, že fyzicky změní pořadí stránek na úrovni listu, aby odpovídalo logickému pořadí uzlů listů (zleva doprava). Změna uspořádání také zkomprimuje indexové stránky, aby se hustota stránky rovná faktoru výplně indexu. Chcete-li zobrazit nastavení faktoru vyplnění, použijte sys.indexes. Příklady syntaxe najdete v části Příklady – reorganizace řádkového úložiště.
  • Při použití indexů columnstore může delta úložiště po vložení, aktualizaci a odstranění dat během času mít několik malých skupin řádků. Přeuspořádání indexu columnstore vynutí přesun skupiny řádků z delta úložiště do komprimovaných skupin ve columnstore a následně kombinuje menší komprimované skupiny do větších. Operace změny uspořádání také fyzicky odebere řádky označené jako odstraněné v úložišti sloupců. Změna uspořádání indexu columnstore může vyžadovat další prostředky procesoru ke komprimaci dat. V době, kdy je operace spuštěná, může být výkon pomalý. Po komprimaci dat se ale výkon dotazů zlepší. Příklady syntaxe najdete v tématu Příklady – reorganizace columnstore.

Poznámka

Počínaje verzí SQL Server 2019 (15.x), Azure SQL Database a Azure SQL Managed Instance pomáhá pohyb tuplů úloha sloučení na pozadí, která automaticky kompresuje menší otevřené delta řádkové skupiny, které existovaly po určitou dobu podle interní prahové hodnoty, nebo sloučí komprimované řádkové skupiny, ze kterých byl odstraněn velký počet řádků. To v průběhu času zlepšuje kvalitu indexu columnstore. Ve většině případů to eliminuje potřebu vydávat příkazy ALTER INDEX ... REORGANIZE.

Spropitné

Pokud operaci reorganizace zrušíte nebo pokud je přerušena jiným způsobem, dosavadní progres se zachová v databázi. Pokud chcete přeuspořádat velké indexy, můžete operaci spustit a zastavit několikrát, dokud se neskončí.

Opětovné sestavení indexu

Opětovné sestavení indexu odstraní a znovu vytvoří index. V závislosti na typu indexu a verzi databázového stroje je možné operaci opětovného sestavení provést offline nebo online. Opětovné sestavení offline indexu obvykle trvá méně času než online opětovné sestavení, ale uchovává zámky na úrovni objektu po dobu trvání operace opětovného sestavení, blokuje dotazy v přístupu k tabulce nebo pohledu.

Opětovné sestavení online indexu nevyžaduje zámky na úrovni objektu až do konce operace, kdy je nutné po krátkou dobu uchovávat zámek k dokončení opětovného sestavení. V závislosti na verzi databázového stroje je možné spustit online opětovné sestavení indexu jako obnovitelnou operaci. Opětovné sestavení indexu lze pozastavit a zachovat dosavadní průběh. Operaci opětovného sestavení lze obnovit po pozastavení nebo přerušení, případně ji lze ukončit, pokud dokončení opětovného sestavení již není nutné.

Pro syntax Transact-SQL se podívejte na téma ALTER INDEX REBUILD. Další informace o opětovném sestavení indexu online naleznete v tématu Provádění operací indexu online.

Poznámka

Během online vytváření indexu musí každá úprava dat v indexovaných sloupcích aktualizovat další kopii indexu. To může způsobit menší snížení výkonu příkazů pro úpravu dat během online opětovného sestavení.

Pokud je pozastavena operace indexu s možností obnovení online, tento dopad na výkon přetrvává, dokud se operace obnovitelná buď dokončí, nebo se přeruší. Pokud nemáte v úmyslu dokončit operaci obnovení indexu, přerušte ji místo pozastavení.

Rada

V závislosti na dostupných prostředcích a vzorech úloh můžete zadáním hodnoty vyšší než výchozí MAXDOP v příkazu ALTER INDEX REBUILD zkrátit dobu opětovného sestavení za cenu vyššího využití procesoru.

  • U indexů rowstore opětovné sestavení eliminuje fragmentaci na všech úrovních indexu a komprimuje stránky na základě zadaného nebo aktuálního faktoru zaplnění. Při zadání ALL se všechny indexy v tabulce zahodí a znovu sestaví v jedné operaci. Když se indexy s 128 nebo více rozsahy znovu sestavují, databázový stroj odloží uvolnění stránek a získání přidružených zámků až po dokončení opětovného sestavení. Příklady syntaxe najdete v tématu Příklady – opětovné sestavení úložiště řádků.

  • Pro columnstore indexy, opětovné sestavení odstraní fragmentaci, přesune všechny řádky delta úložiště do columnstore a fyzicky odstraní řádky označené k odstranění. Příklady syntaxe najdete v tématu Příklady – Opětovné sestavení sloupcového úložiště.

    Spropitné

    Počínaje SQL Serverem 2016 (13.x) není opětovné sestavení indexu columnstore obvykle potřeba, protože REORGANIZE provádí základy opětovného sestavení jako online operace.

Obnovení z poškození dat pomocí opětovného sestavení indexu

Před SQL Serverem 2008 (10.0.x) můžete někdy znovu sestavit neclusterovaný index úložiště řádků, abyste opravili nekonzistence kvůli poškození dat v indexu.

Tyto nekonzistence v neclusterovaného indexu můžete přesto opravit opětovným sestavením neclusterovaného indexu offline. Nelze však opravit neclusterované nekonzistence indexu opětovným sestavením indexu online, protože mechanismus online opětovného sestavení používá existující neclusterovaný index jako základ pro opětovné sestavení a tím nese nekonzistence. Opětovné sestavení indexu offline může někdy vynutit prohledání clusterovaného indexu (nebo haldy) a tím nahradit nekonzistentní data v neclusterovaném indexu daty z clusterovaného indexu nebo haldy.

Pokud chcete zajistit, aby se clusterovaný index nebo halda používaly jako zdroj dat, odstraňte a znovu vytvořte neclusterovaný index místo jeho opětovného sestavení. Stejně jako v předchozích verzích můžete obnovit nekonzistence obnovením ovlivněných dat ze zálohy. Možná ale budete moct opravit neclusterované nekonzistence indexu tak, že ho znovu sestavíte offline nebo znovu vytvoříte. Další informace naleznete v části DBCC CHECKDB (Transact-SQL).

Automatická správa indexů a statistik

Pomocí řešení, jako je Adaptive Index Defrag, automaticky spravujte fragmentaci indexu a aktualizace statistik pro jednu nebo více databází. Tento postup automaticky zvolí, zda se má index znovu sestavit nebo znovu uspořádat podle úrovně fragmentace, mimo jiné parametry, a aktualizovat statistiky lineární prahovou hodnotou.

Důležité informace týkající se opětovného sestavení a reorganizace indexů rowstore

Následující scénáře způsobí, že se všechny neklastrované indexy řádkového úložiště v tabulce automaticky obnoví:

  • Vytvoření clusterovaného indexu v tabulce, včetně opětovného vytvoření clusterovaného indexu s jiným klíčem pomocí CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON)
  • Vyřazení clusterovaného indexu, což způsobí uložení tabulky jako haldy

Následující scénáře automaticky nevytvářejí všechny řádkové neklastrové indexy ve stejné tabulce:

  • Opětovné sestavení clusterovaného indexu
  • Změna úložiště clusterovaného indexu, například použití schématu dělení nebo přesunutí clusterovaného indexu do jiné skupiny souborů

Důležitý

Index nelze změnit uspořádání ani znovu vytvořit, pokud je skupina souborů, ve které je umístěna, offline nebo jen pro čtení. Pokud je zadané klíčové slovo ALL a jeden nebo více indexů jsou v offline nebo jen pro čtení, příkaz selže.

Když dojde k opětovnému sestavení indexu, fyzické médium musí mít dostatek místa pro uložení dvou kopií indexu. Po dokončení opětovného sestavení databázový stroj odstraní původní index.

Když je zadán příkaz ALTER INDEX ... REORGANIZE s parametrem ALL, jsou indexy typu clustered, nonclustered a XML v tabulce reorganizovány.

Opětovné sestavení nebo změna uspořádání malých indexů rowstore obvykle nezmenšuje fragmentaci. Včetně SQL Serveru 2014 (12.x) databázový stroj SQL Serveru přiděluje místo pomocí smíšených rozsahů. Proto jsou stránky malých indexů někdy uloženy ve smíšených rozsahech, což implicitně vytváří takové indexy fragmentované. Smíšené rozsahy sdílí až osm objektů, takže fragmentace malého indexu se nemusí po opětovném uspořádání ani opětovném sestavení snížit.

Důležité informace týkající se opětovného sestavení indexu columnstore

Při opětovném sestavení indexu columnstore načte databázový stroj všechna data z původního indexu columnstore, včetně rozdílového úložiště. Kombinuje data do nových skupin řádků a komprimuje všechny skupiny řádků do columnstore. Databázový stroj defragmentuje columnstore fyzicky odstraněním řádků, které byly označeny jako odstraněné.

Poznámka

Od versi SQL Server 2019 (15.x) je přesun řádkových skupin usnadněn pozadím úlohy slučování, která automaticky komprimuje menší otevřené řádkové skupiny delta store, jež překročily interní prahovou hodnotu, nebo sloučí komprimované řádkové skupiny, kde byl odstraněn velký počet řádků. To v průběhu času zlepšuje kvalitu indexu columnstore. Další informace o termínech a konceptech technologie Columnstore najdete v tématu Indexy Columnstore: Přehled.

Opětovné sestavení oddílu místo celé tabulky

Opětovné sestavení celé tabulky trvá dlouho, pokud je index velký a vyžaduje dostatek místa na disku pro uložení další kopie celého indexu během opětovného sestavení.

U dělených tabulek nemusíte znovu sestavit celý index columnstore, pokud fragmentace existuje pouze v některých oddílech, například v oddílech, kde UPDATE, DELETEnebo MERGE příkazy ovlivnily velký počet řádků.

Opětovné sestavení oddílu po načtení nebo úpravě dat zajišťuje, že všechna data jsou uložená v komprimovaných skupinách řádků ve columnstore. Když proces načítání dat vloží data do oddílu pomocí dávek menších než 102 400 řádků, může oddíl mít několik otevřených skupin řádků v rozdílovém úložišti. Opětovné sestavení přesune všechny řádky delta úložiště do komprimovaných skupin řádků ve columnstore.

Důležité informace týkající se změny uspořádání indexu columnstore

Při přeuspořádání indexu columnstore zkomprimuje databázový engine každou uzavřenou skupinu řádků v delta úložišti do columnstore jako komprimovanou skupinu řádků. Počínaje SQL Serverem 2016 (13.x) a v Azure SQL Database provede příkaz REORGANIZE následující další optimalizace defragmentace online:

  • Fyzicky odebere řádky ze skupiny řádků, když bylo 10% nebo více řádků logicky odstraněno. Pokud má například komprimovaná skupina řádků 1 milion řádků odstraněných 100 000 řádků, databázový stroj odebere odstraněné řádky a rekomprimuje skupinu řádků s 900 000 řádky, což snižuje nároky na úložiště.
  • Kombinuje jednu nebo více komprimovaných skupin řádků, aby se zvýšil počet řádků na skupinu řádků až do maximálního počtu 1 048 576 řádků. Pokud například hromadně vložíte pět dávek, každá o 102 400 řádcích, získáte pět komprimovaných skupin řádků. Pokud spustíte REORGANIZE, tyto skupiny řádků se sloučí do jedné komprimované skupiny řádků s 512 000 řádky. Předpokládá se, že nedošlo k žádným omezením velikosti slovníku ani paměti.
  • Databázový stroj se pokusí zkombinovat skupiny řádků, ve kterých bylo 10% nebo více řádků označeno jako odstraněné s jinými skupinami řádků. Například skupina řádků 1 je komprimovaná a má 500 000 řádků, zatímco skupina řádků 21 je komprimovaná a má 1 048 576 řádků. Skupina řádků 21 má 60% řádků označených jako odstraněných, což ponechá 409 830 řádků. Databázový stroj upřednostňuje kombinování těchto dvou skupin řádků za účelem komprese nové skupiny řádků, která má 909 830 řádků.

Po načtení dat můžete mít v rozdílovém úložišti několik malých skupin řádků. Pomocí ALTER INDEX REORGANIZE můžete tyto skupiny řádků vynutit do columnstore a pak zkombinovat menší komprimované skupiny řádků do větších komprimovaných skupin řádků. Operace změny uspořádání odebere také řádky označené jako odstraněné ze columnstore.

Poznámka

Změna uspořádání indexu columnstore pomocí sady Management Studio kombinuje komprimované skupiny řádků dohromady, ale nevynucuje komprimaci všech skupin řádků do columnstore. Uzavřené skupiny řádků budou komprimovány, ale otevřené skupiny řádků nebudou komprimovány do columnstore. Chcete-li vynutit komprimaci všech skupin řádků, použijte Transact-SQL příklad, který obsahuje COMPRESS_ALL_ROW_GROUPS = ON.

Co vzít v úvahu před provedením údržby indexů

Údržba indexů, kterou provádí změna uspořádání nebo opětovné sestavení indexu, je náročná na prostředky. Způsobuje významné zvýšení využití procesoru, využité paměti a vstupně-výstupních operací úložiště. V závislosti na úloze databáze a dalších faktorech ale výhody, které poskytuje, jsou od zásadního významu až po minuscule.

Abyste se vyhnuli zbytečnému využití prostředků, vyhněte se provádění údržby indexů nerozlišeně. Místo toho by měly být výhody výkonu z údržby indexů určovány empiricky pro každou úlohu pomocí doporučené strategie a zvážit náklady na prostředky a dopad úloh potřebný k dosažení těchto výhod.

Pravděpodobnost, že při velké fragmentování indexu nebo nízké hustotě stránky uvidíte výhody z hlediska výkonu při přeuspořádání nebo opětovném sestavení indexu, je vyšší. Nejedná se ale o jediné věci, které je potřeba vzít v úvahu. Faktory, jako jsou vzory dotazů (zpracování transakcí vs. analýzy a reporting), chování subsystému úložiště, dostupná paměť a vylepšení databázového enginu v průběhu času, hrají roli.

Důležitý

Rozhodnutí o údržbě indexu by se měla provést po zvážení několika faktorů v konkrétním kontextu jednotlivých úloh, včetně nákladů na prostředky údržby. Neměly by být založeny na pevné fragmentaci nebo prahových hodnotách hustoty stránek.

Pozitivní vedlejší účinek opětovného sestavení indexu

Zákazníci často sledují vylepšení výkonu po opětovném sestavení indexů. V mnoha případech ale tato vylepšení nesouvisejí se snížením fragmentace nebo zvýšením hustoty stránky.

Opětovné sestavení indexu má důležitou výhodu: aktualizuje statistiky u klíčových sloupců indexu procházením všech řádků v indexu. Jedná se o ekvivalent provádění UPDATE STATISTICS ... WITH FULLSCAN, což činí statistiky aktuální a někdy zlepšuje jejich kvalitu v porovnání s výchozí aktualizací statistiky vzorku. Při aktualizaci statistik se plány dotazů, které na ně odkazují, překompilují. Pokud předchozí plán dotazu nebyl optimální z důvodu zastaralých statistik, nedostatečného poměru vzorkování statistik nebo z jiných důvodů, bude rekompilovaný plán často fungovat lépe.

Zákazníci často nesprávně přisuzují toto zlepšení samotnému opětovnému sestavení indexu, mylně se domnívají, že je výsledkem snížení fragmentace a zvýšení hustoty stránek. Ve skutečnosti lze stejnou výhodu často dosáhnout mnohem levnějšími náklady na zdroje aktualizací statistik namísto opětovného sestavení indexů.

Spropitné

Náklady na aktualizaci statistik jsou oproti opětovnému sestavení indexu nízké a operace se často dokončí během minut. Opětovné sestavení indexu může trvat hodiny.

Strategie údržby indexů

Microsoft doporučuje, aby zákazníci zvážili a přijali následující strategii údržby indexů:

  • Nepředpokládáme, že údržba indexů vždy výrazně zlepší vaši úlohu.
  • Změřte konkrétní dopad přeuspořádání nebo opětovného sestavení indexů na výkon dotazů ve vaší úloze. Úložiště dotazů je vhodný způsob měření výkonu "před údržbou" a "po údržbě" pomocí techniky testování A/B.
  • Pokud zjistíte, že opětovné sestavení indexů zvyšuje výkon, zkuste ho nahradit aktualizací statistik. To může vést k podobnému zlepšení. V takovém případě možná nebudete muset indexy znovu sestavit tak často nebo vůbec, a místo toho můžete provádět pravidelné aktualizace statistik. U některých statistik možná budete muset zvýšit poměr vzorkování pomocí WITH SAMPLE ... PERCENT nebo WITH FULLSCAN klauzulí (to není běžné).
  • Sledujte fragmentaci indexu a hustotu stránek v průběhu času, abyste zjistili, zda existuje korelace mezi vývojem těchto hodnot a výkonem dotazů. Pokud vyšší fragmentace nebo nižší hustota stránky snižuje nepřijatelný výkon, přeuspořádejte nebo znovu sestavte indexy. Často stačí pouze přeuspořádat nebo znovu sestavit konkrétní indexy používané dotazy se sníženým výkonem. Tím se vyhnete vyšším nákladům na prostředky při údržbě každého indexu v databázi.
  • Navazování korelace mezi fragmentací nebo hustotou stránek a výkonem vám také umožňuje určit frekvenci údržby indexů. Nepředpokládáme, že údržba musí být provedena podle pevného plánu. Lepší strategií je monitorovat fragmentaci a hustotu stránek a podle potřeby spouštět údržbu indexů před nepřijatelným snížením výkonu.
  • Pokud jste zjistili, že je potřeba údržba indexu a její náklady na prostředky jsou přijatelné, proveďte údržbu v době nízkého využití prostředků, pokud je to možné.
  • Pravidelně testujte, protože vzory využití prostředků se můžou v průběhu času měnit.

Údržba indexů ve službě Azure SQL Database a azure SQL Managed Instance

Kromě výše uvedených aspektů a strategie je důležité ve službě Azure SQL Database a Azure SQL Managed Instance zvážit zejména náklady a výhody údržby indexů. Zákazníci by to měli provést pouze tehdy, když je prokázána potřeba, a s ohledem na následující body.

  • Azure SQL Database a Azure SQL Managed Instance implementují zásady správného řízení prostředků k nastavení hranic na využití procesoru, paměti a vstupně-výstupních operací podle zřízené cenové úrovně. Tyto hranice platí pro všechny uživatelské úlohy, včetně údržby indexů. Pokud se kumulativní spotřeba prostředků všemi úlohami blíží hranicím prostředků, může operace opětovného sestavení nebo reorganizace snížit výkon jiných úloh kvůli kolizí prostředků. Například hromadné načítání dat může být pomalejší, protože vstupně-výstupní operace transakčního protokolu je 100% kvůli souběžnému opětovnému sestavení indexu. Ve službě Azure SQL Managed Instance je možné tento dopad snížit spuštěním údržby indexů v samostatné skupině úloh Správce prostředků s omezeným přidělením prostředků za cenu prodloužení doby trvání údržby indexu.
  • Kvůli úsporám nákladů zákazníci často zřizují databáze, elastické fondy a spravované instance s minimální velikostí prostředků. Cenová úroveň je zvolena tak, aby byla dostatečná pro úlohy aplikací. Kvůli významnému zvýšení využití prostředků kvůli údržbě indexů bez snížení výkonu aplikace můžou zákazníci potřebovat zřídit další prostředky a zvýšit náklady, aniž by museli zvýšit výkon aplikace.
  • V elastických fondech se prostředky sdílejí napříč všemi databázemi ve fondu. I když je konkrétní databáze nečinná, může provádění údržby indexů v této databázi ovlivnit úlohy aplikací spuštěné souběžně v jiných databázích ve stejném fondu. Další informace najdete v tématu Správa prostředků v hustých elastických fondech.
  • U většiny typů úložiště používaných ve službě Azure SQL Database a Azure SQL Managed Instance neexistuje žádný rozdíl v výkonu mezi sekvenčními vstupně-výstupními operacemi a náhodnými vstupně-výstupními operacemi. To snižuje dopad fragmentace indexů na výkon dotazů.
  • Při použití čtení při škálování nebo geografické replikace replik se latence dat na replikách často zvyšuje, zatímco údržba indexu probíhá na primární replice. Pokud je geografická replika zřízena s nedostatečnými prostředky k udržení zvýšeného generování transakčních logů způsobeného údržbou indexu, může zaostávat daleko za primární kopií, což způsobí, že ji systém znovu přeosadí. Díky tomu bude replika nedostupná, dokud se obnovení nedokončí. Kromě toho se v úrovních služby Premium a Kritická pro podnikání mohou repliky používané pro vysokou dostupnost při údržbě indexu podobně dostat daleko za hlavní instanci. Pokud se přepnutí při selhání vyžaduje během údržby indexu nebo brzy po ní, může trvat déle, než se očekávalo.
  • Pokud se na primární replice spustí opětovné sestavení indexu a zároveň se na čitelné replice spustí dlouhotrvající dotaz, může být tento dotaz automaticky ukončen, aby se zabránilo blokování vlákna pro opětovné provedení na replice.

V Azure SQL Database a ve službě Azure SQL Managed Instance mohou nastat specifické, ale neobvyklé scénáře, kdy je potřebná jednorázová nebo pravidelná údržba indexu.

Rada

Pokud jste zjistili, že údržba indexů je nezbytná pro úlohy Azure SQL Database a Azure SQL Managed Instance, měli byste buď změnit uspořádání indexů, nebo použít opětovné sestavení indexu online. To umožňuje dotazovým úlohám přistupovat k tabulkám při obnově indexů.

Pokud operaci učiníte znovu obnovitelnou, můžete se vyhnout nutnosti jejího restartu od začátku, pokud dojde k přerušení plánovaným nebo neplánovaným převzetím služeb při selhání databáze. Použití operací obnovitelných indexů je obzvláště důležité, pokud jsou indexy velké.

Spropitné

Offline operace indexu jsou obvykle dokončeny rychleji než online operace. Měly by se použít, když k tabulkám nebudou během operace přistupovat dotazy, například po načtení dat do pracovních tabulek jako součást sekvenčního procesu ETL.

Omezení a restrikce

Indexy úložiště řádků s více než 128 rozsahy se znovu sestavují ve dvou samostatných fázích: logické a fyzické. V logické fázi se existující alokační jednotky používané indexem označí pro uvolnění, řádky dat se následně zkopírují a seřadí, a pak se přesunou do nových alokačních jednotek vytvořených k ukládání znovu vytvořeného indexu. Ve fyzické fázi jsou dříve přidělené jednotky označené k uvolnění fyzicky vyřazeny v krátkých transakcích, ke kterým dochází na pozadí, a nevyžadují mnoho zámků. Další informace o alokačních jednotkách naleznete v Průvodci architekturou stránek a rozsahů .

Příkaz ALTER INDEX REORGANIZE vyžaduje, aby datový soubor obsahující index měl k dispozici volné místo, protože operace může přidělit dočasné pracovní stránky pouze v tom samém souboru, nikoli v jiném souboru ve stejné skupině souborů. I když má skupina souborů volné místo, může se uživateli stále zobrazit chyba 1105: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup během operace reorganizace, pokud je datový soubor mimo prostor.

Index nelze změnit uspořádání, pokud je ALLOW_PAGE_LOCKS nastavena na VYPNUTO.

Až SQL Server 2017 (14.x), opětovné sestavení clusterovaného indexu columnstore je offline operace. Databázový modul musí během opětovného sestavení získat výhradní zámek na tabulku nebo oddíl. Data jsou během opětovného sestavení offline a nedostupná i při použití NOLOCK, izolace snímků potvrzených čtením (RCSI) nebo izolace snímků. Počínaje SQL Serverem 2019 (15.x) je možné znovu vytvořit clusterovaný index columnstore pomocí možnosti ONLINE = ON.

Varování

Vytvoření a opětovné sestavení nerovnaných indexů v tabulce s více než 1 000 oddíly je možné, ale nepodporuje se. To může způsobit snížení výkonu nebo nadměrné využití paměti během těchto operací. Microsoft doporučuje používat zarovnané indexy pouze tehdy, když počet oddílů překročí 1 000.

Omezení statistiky

  • Pokud je index vytvořen nebo znovu sestaven, statistiky se vytvoří nebo aktualizují skenováním všech řádků v tabulce, které jsou ekvivalentní použití klauzule FULLSCAN v CREATE STATISTICS nebo UPDATE STATISTICS. Počínaje SQL Serverem 2012 (11.x) se při vytvoření nebo přepracování děleného indexu statistiky nevytvářejí ani neaktualizují prohledáváním všech řádků v tabulce. Místo toho se použije výchozí poměr vzorkování. Pokud chcete vytvořit nebo aktualizovat statistiky v dělených indexech prohledáváním všech řádků v tabulce, použijte CREATE STATISTICS nebo UPDATE STATISTICS s klauzulí FULLSCAN.
  • Podobně se při obnovení operace vytvoření nebo opětovného sestavení indexu vytvoří nebo aktualizuje statistika s výchozím poměrem vzorkování. Pokud byly statistiky vytvořeny nebo naposledy aktualizovány s klauzulí PERSIST_SAMPLE_PERCENT nastavené na ON, obnovovatelné indexové operace použijí trvalý poměr vzorkování k jejich vytvoření nebo aktualizaci.
  • Pokud je index přeuspořádaný, statistiky se neaktualizují.

Příklady

Kontrola fragmentace a hustoty stránek indexu rowstore pomocí Transact-SQL

Následující příklad určuje průměrnou fragmentaci a hustotu stránky pro všechny indexy rowstore v aktuální databázi. Používá SAMPLED režim k rychlému vrácení výsledků s možností akce. K přesnějším výsledkům použijte režim DETAILED. To vyžaduje skenování všech indexových stránek a může to trvat dlouho.

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_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
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;

Předchozí příkaz vrátí sadu výsledků podobnou následující:

schema_name  object_name           index_name                               index_type    avg_fragmentation_in_percent avg_page_space_used_in_percent page_count  alloc_unit_type_desc
------------ --------------------- ---------------------------------------- ------------- ---------------------------- ------------------------------ ----------- --------------------
dbo          FactProductInventory  PK_FactProductInventory                  CLUSTERED     0.390015600624025            99.7244625648629               3846        IN_ROW_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            89.6839757845318               497         LOB_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            80.7132814430442               251         IN_ROW_DATA
dbo          FactFinance           NULL                                     HEAP          0                            99.7982456140351               239         IN_ROW_DATA
dbo          ProspectiveBuyer      PK_ProspectiveBuyer_ProspectiveBuyerKey  CLUSTERED     0                            98.1086236718557               79          IN_ROW_DATA
dbo          DimCustomer           IX_DimCustomer_CustomerAlternateKey      NONCLUSTERED  0                            99.5197553743514               78          IN_ROW_DATA

Další informace najdete v tématu sys.dm_db_index_physical_stats.

Kontrola fragmentace indexu columnstore pomocí Transact-SQL

Následující příklad určuje průměrnou fragmentaci pro všechny indexy columnstore s komprimovanými skupinami řádků v aktuální databázi.

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
   AND
   i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, index_type;

Předchozí příkaz vrátí sadu výsledků podobnou následující:

schema_name  object_name            index_name                           index_type                avg_fragmentation_in_percent
------------ ---------------------- ------------------------------------ ------------------------- ----------------------------
Sales        InvoiceLines           NCCX_Sales_InvoiceLines              NONCLUSTERED COLUMNSTORE  0.000000000000000
Sales        OrderLines             NCCX_Sales_OrderLines                NONCLUSTERED COLUMNSTORE  0.000000000000000
Warehouse    StockItemTransactions  CCX_Warehouse_StockItemTransactions  CLUSTERED COLUMNSTORE     4.225346161484279

Údržba indexů pomocí aplikace SQL Server Management Studio

Změna uspořádání nebo opětovného sestavení indexu

  1. V Průzkumník objektůrozbalte databázi obsahující tabulku, na které chcete změnit uspořádání indexu.
  2. Rozbalte složku Tabulky.
  3. Rozbalte tabulku, u které chcete změnit uspořádání indexu.
  4. Rozbalte složku Indexy.
  5. Klikněte pravým tlačítkem myši na index, který chcete změnit uspořádání, a vyberte Změnit uspořádání.
  6. V dialogovém okně Změnit uspořádání indexů ověřte, zda je správný index v Indexy, které mají být reorganizovány mřížky, a vyberte OK.
  7. Zaškrtněte políčko Komprimovat data sloupce velkých objektů a určete, že jsou také komprimovány všechny stránky obsahující velké objekty (LOB).
  8. Vyberte OK.

Změna uspořádání všech indexů v tabulce

  1. V Průzkumník objektůrozbalte databázi obsahující tabulku, na které chcete změnit uspořádání indexů.
  2. Rozbalte složku Tabulky.
  3. Rozbalte tabulku, u které chcete změnit uspořádání indexů.
  4. Klikněte pravým tlačítkem myši na složku Indexes a vyberte Znovu uspořádat vše.
  5. V dialogovém okně Změnit uspořádání indexů ověřte, zda jsou správné indexy v Indexy, které mají být reorganizovány. Chcete-li odebrat index z mřížky s indexy k reorganizaci, vyberte index a stiskněte klávesu Delete.
  6. Zaškrtněte políčko Komprimovat data sloupce velkých objektů a určete, že jsou také komprimovány všechny stránky obsahující velké objekty (LOB).
  7. Vyberte OK.

Údržba indexů pomocí Transact-SQL

Poznámka

Další příklady použití Transact-SQL k opětovnému sestavení nebo změně uspořádání indexů naleznete v tématu ALTER INDEX Examples - Rowstore Indexes a ALTER INDEX Examples - Columnstore Indexes.

Změna uspořádání indexu

Následující příklad přeorganizuje index IX_Employee_OrganizationalLevel_OrganizationalNode v tabulce HumanResources.Employee v databázi AdventureWorks2022.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

Následující příklad přeorganizuje index IndFactResellerSalesXL_CCI columnstore v tabulce dbo.FactResellerSalesXL_CCI v databázi AdventureWorksDW2022. Tento příkaz vynutí všechny zavřené a otevřené skupiny řádků do columnstore.

-- This command forces all closed and open row groups into columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Změna uspořádání všech indexů v tabulce

Následující příklad přeorganizuje všechny indexy v tabulce HumanResources.Employee v databázi AdventureWorks2022.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

Opětovné sestavení indexu

Následující příklad znovu sestaví jeden index tabulky Employee v databázi AdventureWorks2022.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

Opětovné sestavení všech indexů v tabulce

Následující příklad znovu sestaví všechny indexy přidružené k tabulce v AdventureWorks2022 databázi pomocí klíčového slova ALL. Jsou zadány tři možnosti.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)
;

Další informace naleznete v tématu ALTER INDEX.

Související obsah