Sdílet prostřednictvím

Automatické ladění

Platí pro: SQL Server 2017 (14.x) a novější verze Azure SQL DatabaseAzure SQL Managed InstanceSQL databáze v Microsoft Fabric

Automatické ladění je funkce databáze, která poskytuje přehled o potenciálních problémech s výkonem dotazů, doporučení řešení a automatické opravě zjištěných problémů.

Automatické ladění zavedené v SQL Serveru 2017 (14.x), vás upozorní vždy, když se zjistí potenciální problém s výkonem, a umožní vám použít opravné akce nebo umožní databázovému stroji automaticky opravit problémy s výkonem. Automatické ladění SQL Serveru identifikuje a opravuje problémy s výkonem způsobené regresemi plánu spouštění dotazů. Automatické ladění ve službě Azure SQL Database a databázi SQL v Microsoft Fabric také vytvoří potřebné indexy a zahodí nepoužívané indexy. Další informace o plánech provádění dotazů najdete v tématu Plány provádění.

Databázový stroj SQL Serveru monitoruje dotazy spuštěné v databázi a automaticky zvyšuje výkon úlohy. Databázový stroj má integrovaný mechanismus inteligentních funkcí, který dokáže automaticky ladit a zlepšit výkon dotazů dynamickým přizpůsobením databáze vaší úloze. K dispozici jsou dvě funkce automatického ladění:

  • Automatická oprava plánu identifikuje problematické plány provádění dotazů, jako jsou problémy s citlivostí parametrů nebo šifrováním parametrů , a opravuje problémy s výkonem související s plánem provádění dotazů vynucením posledního známého dobrého plánu před regresí. Platí pro: SQL Server (počínaje SQL Serverem 2017 (14.x)), Azure SQL Database a databází SQL v Microsoft Fabric a Azure SQL Managed Instance

  • Automatická správa indexů identifikuje indexy, které by se měly přidat do databáze, a indexy, které by se měly odebrat. Platí pro: Azure SQL Database a databáze SQL v Microsoft Fabric

Note

V tomto článku se funkce a chování služby Azure SQL Database vztahují také na databázi SQL v Microsoft Fabric.

Proč automatické ladění?

Tři z hlavních úloh v klasické správě databází monitorují úlohu, identifikují kritické Transact-SQL dotazy a identifikují indexy, které by se měly přidat za účelem zvýšení výkonu, nebo indexů, které se používají zřídka, a je možné je odebrat, aby se zlepšil výkon. Databázový stroj SQL Serveru poskytuje podrobný přehled o dotazech a indexech, které potřebujete monitorovat. Neustálé monitorování databáze je ale těžko a zdlouhavý úkol, zejména při práci s mnoha databázemi. Správa velkého počtu databází může být nemožné efektivně provádět. Místo ručního monitorování a ladění databáze můžete zvážit delegování některých akcí monitorování a ladění do databázového stroje pomocí funkce automatického ladění.

Jak funguje automatické ladění?

Automatické ladění je proces průběžného monitorování a analýzy, který se neustále učí o vlastnostech vaší úlohy a identifikuje potenciální problémy a vylepšení.

Proces automatického ladění

Tento proces umožňuje databázi dynamicky přizpůsobovat své úloze tím, že zjistí, jaké indexy a plány můžou zlepšit výkon vašich úloh a jaké indexy ovlivňují vaše úlohy. Na základě těchto zjištění používá automatické ladění akce ladění, které zlepšují výkon vaší úlohy. Kromě toho automatické ladění nepřetržitě monitoruje výkon databáze po implementaci jakýchkoli změn, aby se zajistilo, že zlepší výkon vaší úlohy. Všechny akce, které nezlepšovaly výkon, se automaticky vrátí. Tento proces ověření je klíčovou funkcí, která zajišťuje, že jakákoli změna provedená automatickým laděním nezmenší celkový výkon vaší úlohy.

Automatická oprava plánu

Automatická oprava plánu je funkce automatického ladění, která identifikuje regresi výběru plánu provádění a automaticky opravuje problém vynucením posledního známého dobrého plánu. Další informace o plánech provádění dotazů a optimalizátoru dotazů najdete v průvodci architekturou zpracování dotazů.

Important

Automatická oprava plánu závisí na povolení úložiště dotazů v databázi pro sledování úloh.

Co je regrese výběru plánu provádění?

Databázový stroj SQL Serveru může k provádění Transact-SQL dotazů použít různé plány provádění. Plány dotazů závisí na statistikách, indexech a dalších faktorech. Optimální plán, který by se měl použít k provedení dotazu Transact-SQL, se může v průběhu času v závislosti na změnách těchto faktorů změnit. V některých případech nemusí být nový plán lepší než předchozí plán a nový plán může způsobit regresi výkonu, například problém související s citlivostí parametrů nebo šifrováním parametrů .

Regrese výběru plánu provádění dotazů.

Kdykoli si všimnete, že došlo k regresi výběru plánu, měli byste najít předchozí dobrý plán a vynutit jeho použití místo aktuálního plánu. To lze provést pomocí sp_query_store_force_plan postupu. Databázový stroj v SQL Serveru 2017 (14.x) poskytuje informace o regresovaných plánech a doporučených nápravných akcích. Kromě toho databázový stroj umožňuje plně automatizovat tento proces a nechat databázový stroj opravit všechny zjištěné problémy související se změnou plánu.

Important

Automatická oprava plánu by se měla používat v rámci upgradu úrovně kompatibility databáze po zachycení referenčního bodu k automatickému zmírnění rizik upgradu pracovních úloh. Další informace o tomto případu použití naleznete v tématu Zachování stability výkonu během upgradu na novější SQL Server.

Automatická oprava výběru plánu

Databázový stroj může automaticky přepnout na poslední známý dobrý plán při zjištění regrese výběru plánu.

Oprava volby plánu provádění dotazů

Databázový stroj automaticky zjistí jakoukoli potenciální regresi výběru plánu, včetně plánu, který by se měl použít místo nesprávného plánu. Výsledný plán provádění vynucený automatickou opravou plánu bude stejný nebo podobný poslednímu známému dobrému plánu. Vzhledem k tomu, že výsledný plán nemusí být shodný s posledním dobrým plánem, může se výkon vynuceného plánu lišit. Ve výjimečných případech může být rozdíl v výkonu významný a negativní; v tomto případě se automatická oprava plánu automaticky přestane pokoušet vynutit náhradní plán.

Když databázový stroj použije poslední známý dobrý plán před regresí, automaticky monitoruje výkon vynuceného plánu. Pokud vynucený plán není lepší než regresní plán, nově vybraný plán nebude vynucen a databázový stroj zkompiluje nový plán. Pokud výpočetní jednotka databáze ověří, že je vynucený plán lepší než regresovaný plán, bude vynucený plán zachován. Zůstane zachována, dokud nedojde k opětovnému kompilaci (například při další aktualizaci statistiky nebo změně schématu). Další informace o vynucení plánu a typech plánů, které je možné vynutit, najdete v tématu Omezení vynucení plánu.

Note

Pokud se instance SQL Serveru restartuje před ověřením akce vynucení plánu, plán se automaticky nevynutí. Jinak se vynucení plánu zachová při restartování SQL Serveru.

Zapnout automatické opravy výběru plánu

Můžete povolit automatické ladění pro každou databázi a určit, že při zjištění regrese změny plánu by se měl vynutit poslední dobrý plán. Automatické ladění je povolené pomocí následujícího příkazu:

ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Jakmile tuto možnost povolíte, databázový stroj automaticky vynutí jakékoli doporučení, ve kterém je odhadovaný zisk CPU vyšší než 10 sekund, nebo je počet chyb nového plánu vyšší než doporučeného, tak aby ověřil, že je vynucený plán lepší než aktuální plán.

Pokud chcete povolit automatické ladění ve službě Azure SQL Database a azure SQL Managed Instance, přečtěte si téma Povolení automatického ladění ve službě Azure SQL Database pomocí webu Azure Portal.

Alternativa – opravit volbu manuálního plánu

Bez automatického ladění musí uživatelé pravidelně monitorovat systém a hledat dotazy, u kterých došlo k nižšímu výkonu. Pokud se některý plán zhoršil, měl by uživatel najít předchozí dobrý plán a použít ho místo aktuálního plánu pomocí sp_query_store_force_plan postupu. Osvědčeným postupem je vynutit poslední známý dobrý plán, protože starší plány můžou být kvůli změnám statistiky nebo indexu neplatné. Uživatel, který vynutí poslední známý dobrý plán, by měl monitorovat výkon dotazu spuštěného pomocí vynuceného plánu a ověřit, že vynucený plán funguje podle očekávání. V závislosti na výsledcích monitorování a analýzy by měl být plán vynucený nebo by měl uživatel najít jiný způsob optimalizace dotazu, například jeho přepsání. Ručně vynucené plány by neměly být vynuceny navždy, protože databázový stroj by měl být schopen použít optimální plány. Uživatel nebo DBA by nakonec měl plán vynutit pomocí sp_query_store_unforce_plan postupu a nechat databázový stroj najít optimální plán.

Tip

Alternativně můžete použít pohled úložiště dotazů Dotazy s vynucenými plány k vyhledání a zrušení vynucování plánů.

SQL Server poskytuje všechna potřebná zobrazení a postupy potřebné k monitorování výkonu a řešení problémů v úložišti dotazů.

V SQL Serveru 2016 (13.x) můžete najít regrese výběru plánu pomocí systémových zobrazení úložiště dotazu. Počínaje SQL Serverem 2017 (14.x) databázový stroj zjistí a zobrazí potenciální regrese výběru plánu a doporučené akce, které by se měly použít v zobrazení dynamické správy sys.dm_db_tuning_recommendations (Transact-SQL). Dynamická správa zobrazuje informace o problému, jeho důležitost a podrobnosti, jako je identifikovaný dotaz, ID plánu, který byl regresován, ID plánu, který byl použit jako referenční plán pro porovnání, a příkaz Transact-SQL, který je možné spustit k vyřešení problému.

typ description datetime skóre details ...
FORCE_LAST_GOOD_PLAN Čas procesoru se změnil z 4 ms na 14 ms 3/17/2017 83 queryId recommendedPlanId regressedPlanId T-SQL
FORCE_LAST_GOOD_PLAN Čas procesoru se změnil z 37 ms na 84 ms 3/16/2017 26 queryId recommendedPlanId regressedPlanId T-SQL

Některé sloupce z tohoto zobrazení jsou popsány v následujícím seznamu:

  • Typ doporučené akce FORCE_LAST_GOOD_PLAN.
  • Popis, který obsahuje informace, proč databázový stroj považuje tuto změnu plánu za potenciální regresi výkonu.
  • Datum a čas, kdy se zjistí potenciální regrese.
  • Skóre tohoto doporučení
  • Podrobnosti o problémech a údajích zahrnují například ID zjištěného plánu, ID regrese plánu, ID plánu, který by měl být vynucen k vyřešení problému, a Transact-SQL skript, který by mohl být použit k jeho řešení. Podrobnosti jsou ukládány ve formátu JSON.

Pomocí následujícího dotazu získejte skript, který opraví problém a další informace o odhadovaném získání:

SELECT reason, score,
      script = JSON_VALUE(details, '$.implementationDetails.script'),
      planForceDetails.*,
      estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
                  * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
      error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            regressedPlanId int '$.regressedPlanId',
            recommendedPlanId int '$.recommendedPlanId',
            regressedPlanErrorCount int,
            recommendedPlanErrorCount int,
            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float
          ) AS planForceDetails;

Tady je soubor výsledků.

reason skóre skript query_id aktuální plan_id doporučený plan_id odhadovaný zisk náchylný k chybám
Čas procesoru se změnil z 3 ms na 46 ms 36 EXEC sp_query_store_force_plan 12, 17; 12 28 17 11.59 0

Sloupec estimated_gain představuje odhadovaný počet sekund, které by se uložily, pokud by se doporučený plán použil k provádění dotazů místo aktuálního plánu. Doporučený plán by měl být použit místo aktuálního plánu, pokud je získaný čas větší než 10 sekund. Pokud v aktuálním plánu dojde k více chybám (například vypršení časového limitu nebo přerušení provádění) než v doporučeném plánu, nastaví se sloupec error_prone na hodnotu YES. Dalším důvodem, proč by se doporučený plán měl upřednostnit před současným, je to, že současný plán je náchylný k chybám.

Přestože databázový stroj poskytuje všechny informace potřebné k identifikaci regresí voleb plánu, může se stát zdlouhavým procesem průběžného monitorování a řešení problémů s výkonem. Díky automatickému ladění je tento proces mnohem jednodušší.

Note

Data v sys.dm_db_tuning_recommendations zobrazení dynamické správy se po restartování databázového stroje neuchovávají. Pomocí sloupce sqlserver_start_time v sys.dm_os_sys_info vyhledejte čas posledního spuštění databázového stroje.

Automatická správa indexů

Ve službě Azure SQL Database je správa indexů snadná, protože Azure SQL Database se seznámí s vaší úlohou a zajistí optimální indexování dat. Správný návrh indexu je zásadní pro optimální výkon úloh a automatická správa indexů vám může pomoct optimalizovat indexy. Automatická správa indexů může buď opravit problémy s výkonem nesprávně indexovaných databází, nebo udržovat a zlepšovat indexy u stávajícího schématu databáze. Automatické ladění ve službě Azure SQL Database provádí následující akce:

  • Identifikuje indexy, které by mohly zlepšit výkon vašich Transact-SQL dotazů, které čtou data z tabulek.
  • Identifikuje redundantní indexy nebo indexy, které nebyly použity v delším časovém období, které by bylo možné odebrat. Odebrání nepotřebných indexů zlepšuje výkon dotazů, které aktualizují data v tabulkách.

Proč potřebujete správu indexů?

Indexy urychlují některé dotazy, které čtou data z tabulek, ale můžou zpomalit dotazy, které aktualizují data. Je potřeba pečlivě analyzovat, kdy vytvořit index a jaké sloupce potřebujete do indexu zahrnout. Některé indexy nemusí být po nějaké době potřeba. Proto je potřeba pravidelně identifikovat a odstranit tyto indexy, které nepřinesou žádné výhody. Pokud nepoužívané indexy ignorujete, sníží se výkon dotazů, které aktualizují data, bez jakýchkoli výhod dotazů, které čtou data. Nepoužité indexy také ovlivňují celkový výkon systému, protože další aktualizace vyžadují zbytečné protokolování.

Nalezení optimální sady indexů, které zlepšují výkon dotazů, které čtou data z tabulek a mají minimální dopad na aktualizace, můžou vyžadovat průběžnou a složitou analýzu.

Azure SQL Database používá předdefinovaná inteligentní a pokročilá pravidla, která analyzují vaše dotazy, identifikují indexy, které by byly pro vaše aktuální úlohy optimální, a identifikují indexy, které by mohly být potřeba odebrat. Azure SQL Database zajišťuje, že máte minimální potřebnou sadu indexů, které optimalizují dotazy, které čtou data, s minimalizovaným dopadem na ostatní dotazy.

Automatická správa indexů

Kromě detekce může Azure SQL Database automaticky použít identifikovaná doporučení. Pokud zjistíte, že integrovaná pravidla zlepšují výkon vaší databáze, můžete nechat službu Azure SQL Database automaticky spravovat vaše indexy.

Když Azure SQL Database použije doporučení CREATE INDEX nebo DROP INDEX, automaticky monitoruje výkon dotazů ovlivněných indexem. Nový index se zachová jenom v případě, že se zlepší výkon ovlivněných dotazů. Zrušený index bude automaticky znovu vytvořen, pokud některé příkazy běží pomaleji v důsledku absence indexu.

Důležité informace o automatické správě indexů

Akce potřebné k vytvoření nezbytných indexů ve službě Azure SQL Database můžou spotřebovávat prostředky a časově ovlivnit výkon úloh. Aby se minimalizoval dopad vytváření indexů na výkon úloh, Azure SQL Database najde odpovídající časový interval pro jakoukoli operaci správy indexů. Akce ladění se odloží, pokud databáze potřebuje prostředky ke spuštění úlohy a restartuje se, když má databáze dostatek nepoužitých prostředků, které je možné použít pro úlohu údržby. Jednou z důležitých funkcí automatické správy indexů je ověření akcí. Když Azure SQL Database vytvoří nebo zahodí index, proces monitorování analyzuje výkon vaší úlohy a ověří, že akce zlepšila celkový výkon. Pokud nedošlo k významnému zlepšení - akce se okamžitě vrátí. Azure SQL Database tak zajistí, že akce automatického ladění nebudou mít negativní vliv na výkon vaší úlohy. Indexy vytvořené automatickým laděním jsou transparentní pro operaci údržby v podkladovém schématu. Změny schématu, jako je vyřazení nebo přejmenování sloupců, nejsou blokovány přítomností automaticky vytvořených indexů. Indexy automaticky vytvořené službou Azure SQL Database se okamžitě zahodí při vyřazení související tabulky nebo sloupců.

Alternativní řešení – ruční správa indexů

Bez automatické správy indexů by uživatel nebo DBA museli ručně dotazovat zobrazení sys.dm_db_missing_index_details (Transact-SQL) nebo pomocí sestavy řídicího panelu výkonu v sadě Management Studio najít indexy, které by mohly zvýšit výkon, vytvořit indexy pomocí podrobností uvedených v tomto zobrazení a ručně monitorovat výkon dotazu. Aby bylo možné najít indexy, které by se měly vynechat, by uživatelé měli sledovat statistiky provozního využití indexů, aby našli zřídka používané indexy.

Azure SQL Database tento proces zjednodušuje. Azure SQL Database analyzuje vaši úlohu, identifikuje dotazy, které by se mohly spouštět rychleji pomocí nového indexu, a identifikuje nepoužívané nebo duplikované indexy. Další informace o identifikaci indexů, které by se měly změnit, najdete v tématu Najít doporučení indexu na webu Azure Portal.

Další kroky

  • Last updated on