Ladění neklastrových indexů pomocí návrhů na chybějící indexy

Platí pro:SQL Server Azure SQL Database Spravovaná instance Azure SQLDatabáze SQL v Microsoft Fabric

Funkce chybějících indexů je jednoduchý nástroj pro nalezení těchto indexů, které mohou významně zlepšit výkon dotazů. Tento článek popisuje, jak pomocí chybějících návrhů indexů efektivně ladit indexy a zlepšit výkon dotazů.

Omezení chybějící funkce indexu

Když optimalizátor dotazů vygeneruje plán dotazu, analyzuje, jaké jsou nejlepší indexy pro konkrétní podmínku filtru. Pokud nejlepší indexy neexistují, optimalizátor dotazů stále vygeneruje plán dotazu pomocí cenově dostupných metod přístupu, ale také ukládá informace o těchto indexech. Funkce chybějících indexů umožňuje přístup k informacím o nejlepších možných indexech, abyste se mohli rozhodnout, jestli se mají implementovat.

Optimalizace dotazů je časově citlivý proces, takže chybějící funkce indexu má určitá omezení. Mezi omezení patří:

Chybějící návrhy indexu jsou založeny na odhadech provedených během optimalizace jednoho dotazu před spuštěním dotazu. Chybějící návrhy indexu se po spuštění dotazu neotestují ani neaktualizují.
Chybějící funkce indexu navrhuje pouze neclusterované diskové indexy rowstore. Jedinečné a filtrované indexy se nenavrhují.
Klíčové sloupce se navrhují, ale návrh nezadá pořadí pro tyto sloupce. Informace o řazení sloupců najdete v části Použití chybějících návrhů indexu v tomto článku.
Zahrnuté sloupce se navrhují, ale SQL Server neprovádí žádnou analýzu nákladů, pokud jde o velikost výsledného indexu, když se navrhne velký počet zahrnutých sloupců.
Chybějící požadavky indexů můžou nabízet podobné varianty indexů ve stejné tabulce a sloupcích napříč dotazy. Je důležité zkontrolovat návrhy indexů a kombinovat je tam, kde je to možné.
Návrhy nejsou určené pro triviální plány dotazů.
Informace o nákladech jsou méně přesné pro dotazy zahrnující pouze predikáty nerovnosti.
Návrhy se shromažďují pro maximálně 600 chybějících skupin indexů. Jakmile je dosaženo této prahové hodnoty, nebudou shromažďována žádná další data z chybějící skupiny indexů.

Kvůli těmto omezením se chybějící návrhy indexů nejlépe považují za jeden z několika zdrojů informací při provádění analýzy indexů, návrhu, ladění a testování. Chybějící návrhy indexů nejsou předpisy pro vytváření indexů přesně podle návrhu.

Note

Azure SQL Database nabízí automatické ladění indexů. Automatické ladění indexů využívá strojové učení k horizontálnímu učení ze všech databází ve službě Azure SQL Database prostřednictvím AI a dynamicky vylepšuje své akce ladění. Automatické ladění indexů zahrnuje proces ověření, který zajišťuje pozitivní zlepšení výkonu úloh z vytvořených indexů.

Zobrazení chybějících doporučení indexu

Chybějící funkce indexů se skládá ze dvou součástí:

Element MissingIndexes v xml plánu provádění. To vám umožní korelovat indexy, které optimalizátor dotazů považuje za chybějící s dotazy, u kterých chybí.
Sada zobrazení dynamické správy (DMV), která lze dotazovat, aby vrátila informace o chybějících indexech. Díky tomu můžete zobrazit všechna chybějící doporučení indexu pro databázi.

Zobrazení chybějících návrhů indexů v plánech provádění

Přehled plánu provádění lze vygenerovat nebo získat několika způsoby:

Při psaní nebo ladění dotazu můžete pomocí aplikace SQL Server Management Studio (SSMS)zobrazit odhadovaný plán provádění bez spuštění dotazu, nebo spustit dotaz a zobrazit skutečný plán provádění.
Monitorování výkonu pomocí úložiště dotazů, pokud je povoleno, shromažďuje plány provádění.
Plány spouštění uložené v mezipaměti můžete identifikovat dotazováním zobrazení dynamické správy, jako je například sys.dm_exec_text_query_plan.

Pomocí následujícího dotazu můžete například vygenerovat chybějící požadavky indexu pro ukázkové databáze AdventureWorks.

SELECT City, StateProvinceID, PostalCode
FROM Person.Address as a
JOIN Person.BusinessEntityAddress as ba on
    a.AddressID = ba.AddressID
JOIN Person.Person as  p on
    ba.BusinessEntityID = p.BusinessEntityID
WHERE p.FirstName like 'K%' and
    StateProvinceID = 9;
GO

Generování a zobrazení chybějících požadavků indexu:

Otevřete SSMS a připojte relaci ke své kopii ukázkových databází AdventureWorks.
Vložte dotaz do relace a vygenerujte odhadovaný plán provádění v aplikaci SSMS pro dotaz výběrem tlačítka Zobrazit odhadovaný plán provádění . Plán provádění se zobrazí v podokně v aktuální relaci. V horní části grafického plánu se zobrazí zelený příkaz Chybějící index .

Jeden plán provádění může obsahovat několik chybějících požadavků indexu, ale v grafickém plánu provádění lze zobrazit pouze jeden chybějící požadavek indexu. Jednou z možností zobrazení úplného seznamu chybějících indexů pro plán provádění je zobrazení xml plánu provádění.
Klikněte pravým tlačítkem myši na plán provádění a v nabídce vyberte Zobrazit XML plánu provádění.

XML dokument plánu provádění se otevře jako nová karta v rámci SSMS.

Note

V nabídce Podrobnosti chybějícího indexu... se zobrazí pouze jeden návrh chybějícího indexu, i když v XML plánu provádění je více takových návrhů. Chybějící zobrazený návrh indexu nemusí být ten, který má nejvyšší odhadované zlepšení dotazu.
Pomocí klávesové zkratky CTRL+f zobrazte dialogové okno Najít.
Vyhledejte MissingIndex.

V tomto příkladu jsou dva MissingIndex prvky.
- První chybějící index naznačuje, že dotaz může použít index v Person.Address tabulce, která podporuje hledání rovnosti ve StateProvinceID sloupci, který obsahuje dva další sloupce City a PostalCode". V době optimalizace optimalizátor dotazů věřil, že tento index může snížit odhadované náklady na dotaz o 34,2737%.
- Druhý chybějící index naznačuje, že dotaz může použít index v Person.Person tabulce, která podporuje vyhledávání nerovnosti ve sloupci FirstName. V době optimalizace optimalizátor dotazů věřil, že tento index může snížit odhadované náklady na dotaz o 18,1102%.

Každý neclusterovaný index založený na disku v databázi zabírá místo, zvyšuje režii při vkládání, aktualizacích a odstraňování a může vyžadovat údržbu. Z těchto důvodů je osvědčeným postupem zkontrolovat všechny chybějící požadavky indexu pro tabulku a existující indexy v tabulce před přidáním indexu na základě plánu provádění dotazu.

Zobrazení chybějících návrhů indexu v DMV

Informace o chybějících indexech můžete načíst dotazem na objekty dynamické správy uvedené v následující tabulce.

Zobrazení dynamické správy	Vrácené informace
sys.dm_db_missing_index_group_stats	Vrátí souhrnné informace o chybějících skupinách indexů, například vylepšení výkonu, která by mohla být získána implementací konkrétní skupiny chybějících indexů.
sys.dm_db_missing_index_groups	Vrátí informace o konkrétní skupině chybějících indexů, jako je identifikátor skupiny a identifikátory všech chybějících indexů, které jsou obsaženy v dané skupině.
sys.dm_db_missing_index_details	Vrátí podrobné informace o chybějícím indexu; Vrátí například název a identifikátor tabulky, ve které chybí index, a sloupce a typy sloupců, které by se měly shodovat s chybějícím indexem.
sys.dm_db_missing_index_columns	Vrátí informace o sloupcích tabulky databáze, u kterých chybí index.

Následující dotaz používá k vygenerování CREATE INDEX příkazů chybějící zobrazení dynamické správy indexu. Příkazy pro vytvoření indexu vám po prozkoumání všech požadavků na tabulku spolu s existujícími indexy v tabulce pomohou při vytváření vlastního DDL.

SELECT TOP 20
    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1),
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' +
        CONVERT (varchar, mig.index_group_handle) + '_' +
        CONVERT (varchar, mid.index_handle) + ' ON ' +
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') +
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' +
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;
GO

Tento dotaz objedná návrhy podle sloupce s názvem estimated_improvement. Odhadované zlepšení vychází z kombinace:

Odhadované náklady na dotazy spojené s chybějícím požadavkem indexu
Odhadovaný dopad přidání indexu Jedná se o odhad toho, o kolik by neklastrovaný index snížil náklady dotazu.
Součet spuštění operátorů dotazu (hledání a prohledávání), které byly spuštěny pro dotazy přidružené k chybějícímu požadavku indexu. Jak se budeme zabývat zachováním chybějících indexů v úložišti dotazů, tyto informace se pravidelně vymažou.

Note

Skript pro tvorbu indexů v Nástrojovém setu Tiger od společnosti Microsoft prozkoumá chybějící zobrazení dynamické správy indexů, automaticky odebere všechny redundantní navrhované indexy, odstraní indexy s nízkým dopadem a generuje skripty pro vytváření indexů pro vaši kontrolu. Stejně jako v dotazu výše provádí NOT příkazy pro vytvoření indexu. Skript pro vytvoření indexu je vhodný pro SQL Server a spravovanou instanci Azure SQL. Pro Azure SQL Database zvažte implementaci automatického ladění indexu.

Přečtěte si omezení chybějící funkce indexu a způsob použití chybějících návrhů indexů před vytvořením indexů a upravte název indexu tak, aby odpovídal konvenci vytváření názvů pro vaši databázi.

Zachování chybějících indexů v úložišti dotazů

Chybějící návrhy indexů ve zobrazení dynamické správy se vymažou událostmi, jako jsou restartování instance, převzetí služeb při selhání a přechod databáze do offline režimu. Kromě toho když se metadata tabulky změní, odstraní se z těchto objektů dynamické správy všechny chybějící informace o indexu této tabulky. Ke změnám metadat tabulky může dojít při přidání nebo vyřazení sloupců z tabulky, například při vytvoření indexu ve sloupci tabulky. Provedení operace ALTER INDEX u indexu v tabulce také vymaže chybějící požadavky indexu pro danou tabulku.

Podobně jsou prováděcí plány uložené v mezipaměti plánu vymazány událostmi, jako je restartování instance, převzetí služeb při selhání a přepnutí databáze do režimu offline. Plány spouštění se můžou z mezipaměti odebrat kvůli zatížení paměti a rekompilace.

Chybějící návrhy indexů v plánech provádění je možné v rámci těchto událostí zachovat povolením výkonu monitorování pomocí úložiště dotazů.

Následující dotaz načte prvních 20 plánů dotazů obsahujících chybějící požadavky indexu z úložiště dotazů na základě přibližného odhadu celkového logického čtení dotazu. Data jsou omezená na spouštění dotazů během posledních 48 hodin.

SELECT TOP 20
    qsq.query_id,
    SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
    SUM(qrs.count_executions) AS sum_executions,
    AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads,
    SUM(qsq.count_compiles) AS sum_compiles,
    (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
        WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,
    TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
        WHERE qsp2.query_id=qsq.query_id
        ORDER BY qsp2.plan_id DESC)) AS query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on
    qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi on
    qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE
    qsp.query_plan like N'%<MissingIndexes>%'
    and qsrsi.start_time >= DATEADD(HH, -48, SYSDATETIME())
GROUP BY qsq.query_id, qsq.query_hash
ORDER BY est_logical_reads DESC;
GO

Aplikovat návrhy k vytvoření chybějícího indexu

Pokud chcete efektivně používat chybějící návrhy indexu, postupujte podle neclusterovaných pokynů k návrhu indexu. Při ladění neclusterovaných indexů s chybějícími návrhy indexů zkontrolujte strukturu základní tabulky, pečlivě zkombinujte indexy, zvažte pořadí klíčových sloupců a zkontrolujte zahrnuté návrhy sloupců.

Kontrola struktury základní tabulky

Před vytvořením neclusterovaných indexů v tabulce na základě chybějících návrhů indexu zkontrolujte clusterovaný index tabulky.

Jedním ze způsobů, jak zkontrolovat clusterovaný index, je použití sp_helpindex systémové uložené procedury. Můžeme například zobrazit souhrn indexů v Person.Address tabulce spuštěním následujícího příkazu:

exec sp_helpindex 'Person.Address';
GO

Zkontrolujte sloupec index_description. Tabulka může mít pouze jeden clusterovaný index. Pokud je pro tabulku implementovaný clusterovaný index, index_description bude obsahovat slovo "clustered".

Pokud není k dispozici žádný clusterovaný index, tabulka je heapem. V tomto případě zkontrolujte, jestli byla tabulka záměrně vytvořená jako hromada k vyřešení konkrétního problému s výkonem. Většina tabulek těží z clusterovaných indexů: často se tabulky implementují jako haldy omylem. Zvažte implementaci clusterovaného indexu na základě pokynů pro návrh clusterovaného indexu.

Kontrola chybějících indexů a existujících indexů pro překrývání

Chybějící indexy můžou nabízet podobné varianty neclusterovaných indexů ve stejné tabulce a sloupcích napříč dotazy. Chybějící indexy se můžou podobat i existujícím indexům v tabulce. Pro zajištění optimálního výkonu je nejlepší prozkoumat chybějící indexy a existující indexy, aby se překrývaly, a vyhnout se vytváření duplicitních indexů.

Skriptování existujících indexů v tabulce

Jedním ze způsobů, jak prozkoumat definici existujících indexů v tabulce, je skriptovat indexy pomocí podrobností Průzkumníka objektů:

Připojte Průzkumníka objektů k instanci nebo databázi.
Rozbalte uzel pro danou databázi v Průzkumníku objektů.
Rozbalte složku Tabulky.
Rozbalte tabulku, pro kterou chcete indexy skriptovat.
Vyberte složku Indexy .
Pokud podokno Podrobnosti Průzkumníka objektů ještě není otevřené, vyberte v nabídce Zobrazitpodrobnosti Průzkumníka objektů nebo stiskněte klávesu F7.
Vyberte všechny indexy uvedené v podokně Podrobnosti Průzkumníka objektů pomocí klávesové zkratky CTRL+a.
Klikněte pravým tlačítkem na libovolné místo ve vybrané oblasti a vyberte možnost Script index as, to CREATE ** a New Query Editor Window.

Kontrola indexů a kombinování tam, kde je to možné

Projděte si chybějící doporučení indexu pro tabulku jako skupinu spolu s definicemi existujících indexů v tabulce. Mějte na paměti, že při definování indexů by obecně měly být sloupce rovnosti vloženy před sloupce nerovnosti a společně by měly tvořit klíč indexu. Pokud chcete určit efektivní pořadí sloupců rovnosti, seřadte je na základě jejich selektivity: nejprve vypište nejlektivnější sloupce (úplně vlevo v seznamu sloupců). Jedinečné sloupce jsou nejčastěji selektivní, zatímco sloupce s mnoha opakujícími se hodnotami jsou méně selektivní.

Zahrnuté sloupce by měly být přidány do CREATE INDEX příkazu pomocí INCLUDE klauzule. Pořadí zahrnutých sloupců nemá vliv na výkon dotazů. Proto se při kombinování indexů můžou zahrnuté sloupce kombinovat, aniž byste se museli starat o pořadí. Další informace najdete v pokynech pro zahrnuté sloupce.

Můžete mít například tabulku Person.Address s existujícím indexem na klíčovém sloupci StateProvinceID. V tabulce se můžou zobrazit chybějící doporučení indexu Person.Address pro následující sloupce:

Filtry ROVNOSTI pro StateProvinceID a City
Filtry ROVNOSTI pro StateProvinceID a City, INCLUDEPostalCode

Úprava existujícího indexu tak, aby odpovídala druhému doporučení, tedy indexu s klíči na StateProvinceID a City včetně PostalCode, by pravděpodobně splňovala dotazy, které vygenerovaly oba návrhy indexu.

Kompromisy jsou běžné při ladění indexů. U mnoha datových City sad je pravděpodobně sloupec selektivnější než StateProvinceID sloupec. Pokud je ale náš stávající index StateProvinceID silně využívaný a další požadavky se do značné míry prohledávají v obou StateProvinceID případech, Cityje to pro databázi obecně nižší režijní náklady, aby měla jeden index s oběma sloupci v klíči, což je počáteční , StateProvinceIDi když se nejedná o nejlektivnější sloupec.

Indexy se můžou upravovat několika způsoby:

Příkaz CREATE INDEX můžete použít s klauzulí DROP_EXISTING. Indexy, které následují po úpravě, můžete přejmenovat tak, aby název stále přesně popisoval definici indexu v závislosti na vaší konvenci vytváření názvů.
Můžete použít příkaz DROP INDEX (Transact-SQL) následovaný příkazem CREATE INDEX.

Pořadí klíčů indexu záleží při kombinování návrhů indexu: City jako počáteční sloupec se liší od StateProvinceID počátečního sloupce. Další informace najdete v pokynech k návrhu neclusterovaného indexu.

Při vytváření indexů zvažte použití online operací indexu , pokud jsou k dispozici.

Indexy sice můžou v některých případech výrazně zlepšit výkon dotazů, ale indexy mají také režijní náklady a náklady na správu. Před vytvořením indexů si projděte obecné pokyny k návrhu indexů , které vám pomůžou posoudit výhody indexů.

Ověřte, jestli je změna indexu úspěšná.

Je důležité ověřit, jestli byly změny indexu úspěšné: je optimalizátor dotazů, který používá vaše indexy?

Jedním ze způsobů, jak ověřit změny indexu, je použití úložiště dotazů k identifikaci dotazů s chybějícími požadavky indexu. Poznamenejte si query_id pro dotazy. Pomocí zobrazení Sledované dotazy v úložišti dotazů můžete zkontrolovat, jestli se plány provádění pro dotaz změnily a jestli optimalizátor používá nový nebo upravený index. Zjistěte více o sledovaných dotazech v úvodní části řešení problémů s výkonem dotazů.

Váš názor

Byla tato stránka užitečná?

Last updated on 2025-11-18