Upgrade databází pomocí Pomocníka pro ladění dotazů

Článek
12/18/2024

platí pro: SQL Server 2016 (13.x) a novější verze Azure SQL Database Azure Synapse Analytics Analytics Platform System (PDW)

Při migraci ze starší verze SQL Serveru na SQL Server 2014 (12.x) nebo novější a upgrade úrovně kompatibility databáze na nejnovější dostupnou verzi, může být úloha vystavena riziku regrese výkonu. Při upgradu mezi SQL Serverem 2014 (12.x) a jakoukoli novější verzí je to možné i méně.

Počínaje SQL Serverem 2014 (12.x) a s každou novou verzí jsou všechny změny optimalizátoru dotazů omezeny na nejnovější úroveň kompatibility databáze, takže plány provádění se nezmění okamžitě při upgradu, ale až když uživatel změní možnost COMPATIBILITY_LEVEL databáze na nejnovější dostupnou. Další informace o změnách optimalizátoru dotazů zavedených v SQL Serveru 2014 (12.x) najdete v tématu nástroje pro posouzení kardinality . Pro více informací o úrovních kompatibility a o tom, jak mohou ovlivnit modernizace, si přečtěte Úrovně kompatibility a Modernizace databázového enginu.

Tato regulační schopnost poskytovaná úrovní kompatibility databáze v kombinaci s Query Store vám poskytuje skvělou úroveň kontroly nad výkonem dotazů v procesu upgradu, pokud upgrade probíhá podle doporučeného postupu uvedeného níže. Další informace o doporučeném pracovním postupu pro upgrade úrovně kompatibility najdete v tématu Změna režimu kompatibility databáze a použití úložiště dotazů.

doporučený pracovní postup upgradu databáze pomocí úložiště dotazů

Tato kontrola nad upgrady byla dále vylepšena pomocí SQL Serveru 2017 (14.x), kde bylo zavedeno automatické ladění a umožňuje automatizaci posledního kroku v doporučeném pracovním postupu výše.

Počínaje SQL Server Management Studio v18 vás nová funkce Query Tuning Assistant (QTA) provede doporučeným pracovním postupem, který zajistí stabilitu výkonu během upgradů na novější verze SQL Serveru, jak je popsáno v části Zachování stability výkonu během upgradu na novější SQL Server z Scénáře používání úložiště dotazů. QTA se ale nevrátí zpět k dříve známému dobrému plánu, jak je patrné z předchozího kroku doporučeného pracovního postupu. Místo toho bude QTA sledovat všechny regrese nalezené v Úložišti dotazů Regressované dotazy zobrazení a procházet možné permutace použitelných variant modelu optimalizátoru, k vytvoření nového a lepšího plánu.

Důležitý

QTA negeneruje uživatelské úlohy. Pokud používáte QTA v prostředí, které vaše aplikace nepoužívají, ujistěte se, že můžete na cílovém databázovém stroji SQL Serveru spustit reprezentativní testovací úlohu jiným způsobem.

Pracovní postup Pomocníka pro ladění dotazů

Výchozí bod QTA předpokládá, že se přesune databáze z předchozí verze SQL Serveru (prostřednictvím CREATE DATABASE ... PRO PŘIPOJENÍ nebo RESTORE) k novější verzi databázového stroje SQL Serveru a úroveň kompatibility databáze před upgradem se okamžitě nezmění. QTA vás provede následujícími kroky:

Nakonfigurujte úložiště dotazů podle doporučených nastavení pro dobu trvání úlohy (ve dnech) nastavených uživatelem. Zamyslete se nad dobou trvání úloh, která odpovídá vašemu typickému obchodnímu cyklu.
Požádejte o spuštění požadované úlohy, aby úložiště dotazů mohl shromáždit směrný plán dat úloh (pokud ještě není k dispozici).
Upgradujte na úroveň kompatibility cílové databáze zvolenou uživatelem.
Požádejte o shromáždění dat o zatížení pro druhý průchod za účelem porovnání a detekce regrese.
Iterujte přes jakékoli regrese nalezené na základě zobrazení úložiště dotazů dotazů s regresí, experimentujte sběrem runtime statistik pro možné permutace použitelných variant modelu optimalizátoru a změřte výsledek.
Podávání zpráv o měřených vylepšeních a volitelné umožnění zachování těchto změn pomocí plánovacích průvodců .

Další informace o připojení databáze naleznete v tématu Odpojení databáze a připojení.

Podívejte se níže, jak QTA změní poslední kroky doporučeného pracovního postupu pro upgrade úrovně kompatibility pomocí úložiště dotazů, které vidíte výše. Místo toho, abyste si mohli vybrat mezi aktuálně neefektivním plánem provádění a posledním známým dobrým plánem spuštění, QTA nabízí možnosti ladění, které jsou specifické pro vybrané regresní dotazy, a vytvořit tak nový vylepšený stav s vyladěnými plány provádění.

doporučený pracovní postup upgradu databáze pomocí QTA

Ladění interního vyhledávacího prostoru QTA

QTA se zaměřuje pouze na dotazy SELECT, které lze spouštět z úložiště dotazů. Parametrizované dotazy jsou způsobilé, pokud je zkompilovaný parametr známý. Dotazy, které jsou závislé na konstruktorech modulu runtime, jako jsou dočasné tabulky nebo proměnné tabulky, nejsou v tuto chvíli způsobilé.

QTA cílí na známé možné vzory regresí dotazů z důvodu změn ve verzích nástroje pro odhad kardinality (CE). Například při upgradu databáze ze SQL Serveru 2012 (11.x) a úrovně kompatibility databáze 110 na SQL Server 2017 (14.x) a úroveň kompatibility databáze 140 mohou některé dotazy zaznamenat regresi, protože byly navrženy speciálně pro práci s verzí CE, která existovala v SQL Serveru 2012 (11.x) (CE 70). To neznamená, že vrácení z CE 140 na CE 70 je jedinou možností. Pokud regresi zavádí pouze určitá změna v novější verzi, je možné naznačit, že tento dotaz bude používat pouze relevantní část předchozí verze CE, která fungovala lépe pro konkrétní dotaz, a přesto používat všechna další vylepšení novějších verzí CE. A také povolte další dotazy v úloze, které nezhoršily svou výkonnost, aby mohly těžit z novějších vylepšení CE.

Vzory CE prohledané QTA jsou následující:

nezávislost vs. korelace: Pokud předpoklad nezávislosti poskytuje lepší odhady pro konkrétní dotaz, pak USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') nápověda dotazu způsobí, že SQL Server vygeneruje plán provádění pomocí minimální selektivity při odhadu AND predikátů pro filtry, které zohledňují korelaci. Další informace naleznete v tématu TIPY DOTAZŮ a VERZE CE.
cs-CZ: Jednoduché obsazení vs. Základní obsazení: Pokud jiné omezení spojení poskytuje lepší odhady pro konkrétní dotaz, pak nápověda dotazu USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') způsobí, že SQL Server vygeneruje plán provádění pomocí předpokladu Jednoduchého obsazení místo výchozího předpokladu Základního obsazení. Další informace naleznete v tématu nápověda k dotazům USE HINT a verze CE.
Funkce s více příkazy vracející tabulku (MSTVF) s fixním odhadem kardinality 100 řádků vs. 1 řádek: Pokud výchozí fixní odhad pro TVF se 100 řádky nevede k efektivnějšímu plánu než použití fixního odhadu pro TVF s 1 řádkem (což odpovídá výchozímu nastavení v modelu optimalizace dotazů SQL Serveru 2008 R2 (10.50.x) a starších verzí), pak se k vygenerování plánu provádění použije nápověda dotazu QUERYTRACEON 9488. Další informace o MSTVFs naleznete viz Vytvoření uživatelsky definovaných funkcí (databázový stroj).

Poznámka

Pokud úzce vymezené nápovědy nepřinášejí dostatečně dobré výsledky pro vhodné vzory dotazů, je jako poslední možnost považováno plné využití CE 70 pomocí nápovědy dotazu USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') pro vygenerování plánu provádění.

Důležitý

Jakákoli nápověda vynutí určité chování, které může být vyřešeno v budoucích aktualizacích SQL Serveru. Doporučujeme použít pouze tipy, pokud neexistuje žádná jiná možnost, a plánujte znovu zkontrolovat kód s každým novým upgradem. Vynucením chování můžete zabránit své úloze využívat výhod vylepšení zavedených v novějších verzích SQL Serveru.

Spuštění Pomocníka pro ladění dotazů pro upgrady databáze

QTA je funkce založená na relacích, která ukládá stav relace do msqta schématu uživatelské databáze, ve které se relace vytvoří poprvé. V jedné databázi lze v průběhu času vytvořit více relací ladění, ale pro libovolnou databázi může existovat pouze jedna aktivní relace.

Vytvořte relaci upgradu databáze

V aplikaci SQL Server Management Studio otevřete Průzkumník objektů a připojte se k databázovému stroji.
Pro databázi, u které chcete aktualizovat úroveň kompatibility, klikněte pravým tlačítkem myši na název databáze, vyberte Úlohy, poté Upgrade databázea nakonec Nová relace upgradu databáze.
V okně Průvodce QTA je třeba ke konfiguraci relace provést dva kroky:
1. V okně nastavení nakonfigurujte úložiště dotazů tak, aby zachytilo ekvivalent jednoho úplného obchodního cyklu dat úloh k analýze a ladění.
  - Zadejte očekávanou dobu trvání úlohy ve dnech (minimálně 1 den). Použije se k návrhu doporučených nastavení Úložiště dotazů, která prozatím umožní shromažďování celé základní linie. Zachycení dobré základní úrovně je důležité k zajištění, aby dotazy s regresí výkonu mohly být analyzovány po změně úrovně kompatibility databáze.
  - Po dokončení pracovního postupu QTA nastavte požadovanou úroveň kompatibility cílové databáze, na které by měla být uživatelská databáze. Po dokončení vyberte Další.
2. V okně Nastavení se zobrazí dva sloupce, které ukazují aktuální stav úložiště dotazů v cílové databázi a také doporučená nastavení.
  - Doporučená nastavení jsou vybrána jako výchozí, ale výběr radiového tlačítka ve sloupci Aktuální nastavení přijímá současná nastavení a také umožňuje dokonaleji nastavit současnou konfiguraci úložiště dotazů.
  - Navrhované nastavení prahové hodnoty dotazu je dvakrát očekávaná doba trvání zátěže, počítáno ve dnech. Důvodem je to, že úložiště dotazů bude muset uchovávat informace o základní úloze a úloze po upgradu databáze. Po dokončení vyberte Další.
  Důležitý
  
  Navrhovaná maximální velikost je libovolná hodnota, která může být vhodná pro krátkou úlohu. Mějte však na paměti, že nemusí stačit k uchování informací o úlohách před a po upgradu databáze, zejména při velmi intenzivních zátěžích, kdy může být vygenerováno mnoho různých plánů. Pokud předpokládáte, že to bude případ, zadejte vyšší hodnotu, která je vhodná.
Okno Ladění uzavírá konfiguraci relace a poskytuje pokyny k dalším krokům pro otevření a pokračování v procesu relace. Až budete hotovi, vyberte Dokončit.

Proveďte pracovní postup upgradu databáze

Pro databázi, která je určena k upgradu úrovně kompatibility databáze, klikněte pravým tlačítkem myši na název databáze, vyberte Úlohy, vyberte Upgrade databázea vyberte Monitorování relací.
Stránka správy relací obsahuje seznam aktuálních a minulých relací pro databázi v oboru. Vyberte požadovanou relaci a vyberte Podrobnosti.

Poznámka

Pokud aktuální relace není k dispozici, vyberte tlačítko Aktualizovat.

Seznam obsahuje následující informace:
- ID relace
- název relace: Systémový vygenerovaný název se skládá z názvu databáze, data a času vytvoření relace.
- Stav: Stav relace (aktivní nebo uzavřená).
- Popis: Systém vygenerovaný se skládá z úrovně kompatibility cílové databáze vybrané uživatelem a počtu dnů pro úlohy obchodního cyklu.
- Čas zahájení: Datum a čas vytvoření relace.
Poznámka

Odstranit relaci odstraní všechna data uložená pro vybranou relaci. Odstraněním uzavřené relace se ale neodstraní žádné dříve nasazené příručky k plánu. Pokud odstraníte relaci, která měla nasazené příručky plánu, nemůžete použít funkci QTA k vrácení zpět. Místo toho pomocí systémové tabulky sys.plan_guides vyhledejte příručky plánu a odstraňte je ručně pomocí sp_control_plan_guide.
Vstupním bodem pro novou relaci je krok shromažďování dat.

Poznámka

Tlačítko Relace se vrátí na stránku správy relací a aktivní relace zůstane as-is.

Tento krok obsahuje tři dílčí kroky:
1. Shromažďování základní linie dat požádá uživatele o spuštění reprezentativního cyklu úloh, aby úložiště dotazů mohlo shromáždit základní linii. Po dokončení úlohy zkontrolujte Hotovo se spuštěním úloh a vyberte Další.
  
  Poznámka
  
  Okno QTA je možné zavřít během spuštění úlohy. Když se později vrátíte do relace, která zůstane v aktivním stavu, obnoví se ze stejného kroku, ve kterém skončila.
2. Upgradovat databázi vyzve k oprávnění k upgradu úrovně kompatibility databáze na požadovaný cíl. Pokud chcete přejít k dalšímu dílčímu kroku, vyberte Ano.
  
  Následující stránka potvrzuje, že úroveň kompatibility databáze byla úspěšně upgradována.
3. shromažďování pozorovaných dat požádá uživatele, aby znovu spustil reprezentativní cyklus úloh, aby úložiště dotazů mohl shromáždit srovnávací směrný plán, který se použije k hledání příležitostí optimalizace. Při provádění úloh použijte tlačítko Aktualizovat k průběžné aktualizaci seznamu dotazů s nižším výkonem, pokud nějaké byly nalezeny. Změňte Dotazy tak, aby zobrazovaly hodnotu, aby se omezil počet zobrazených dotazů. Pořadí seznamu je ovlivněno metrikou (doba trvání nebo čas procesoru) a agregace (výchozí hodnota je průměr). Vyberte také, kolik dotazů se má zobrazit. Po dokončení úlohy zkontrolujte Hotovo se spuštěním úloh a vyberte Další.
  
  Seznam obsahuje následující informace:
  - ID dotazu
  - Text dotazu: tvrzení Transact-SQL, které lze rozbalit kliknutím na tlačítko ....
  - Vykonání: Zobrazí počet vykonání tohoto dotazu pro celou sbírku pracovních zatížení.
  - Základní metrika: Vybraná metrika (Doba trvání nebo Čas procesoru) v ms pro shromažďování dat před aktualizací kompatibility databáze.
  - pozorovaná metrika: Vybraná metrika (Doba trvání nebo CPU čas) v milisekundách při shromažďování dat po upgradu kompatibility databáze.
  - % Změnit: Procentuální změna pro vybranou metriku mezi stavem upgradu kompatibility databáze před a po. Záporné číslo představuje množství měřené regrese dotazu.
  - laditelný: Pravda nebo Nepravda podle toho, zda dotaz má nárok na experimentování.
Zobrazit analýzu umožňuje výběr dotazů k experimentování a hledání příležitostí optimalizace. Dotazy, které zobrazí hodnotu, se stanou rozsahem způsobilých dotazů pro experimentování. Jakmile jsou požadované dotazy zaškrtnuté, vyberte Další a spusťte experimentování.

Poznámka

Dotazy s Laditelné = False nelze pro experimentování vybrat.

Důležitý

Upozornění sděluje, že jakmile se QTA přesune do fáze experimentování, nebude možné vrátit se na stránku Zobrazit analýzu.
Pokud před přechodem do fáze experimentování nevyberete všechny oprávněné dotazy, budete muset později vytvořit novou relaci a opakovat pracovní postup. To vyžaduje resetování úrovně kompatibility databáze na předchozí hodnotu.
Zobrazit zjištění umožňuje výběr dotazů, na které se aplikuje navrhovaná optimalizace jako průvodce plánem.

Seznam obsahuje následující informace:
- ID dotazu
- Text dotazu: výrok Transact-SQL, který lze rozbalit kliknutím na tlačítko ....
- Stav: Zobrazí aktuální stav experimentování dotazu.
- Základní metrika: Vybraná metrika (Doba trvání nebo CpuTime) v ms pro dotaz, jak je proveden v kroku 2, dílčí krok 3, což představuje regresní dotaz po upgradu kompatibility databáze.
- pozorovaná metrika: Vybraná metrika (Doba trvání nebo Čas procesoru) v ms pro dotaz po provedení experimentu, pro dostatečně dobrou navrhovanou optimalizaci.
- % Změna: Procentuální změna pro vybranou metriku mezi stavem experimentování a po experimentování představuje množství měřeného zlepšení dotazu s navrženou optimalizací.
- možnost dotazu: Odkaz na navrženou nápovědu, která zlepšuje metriku provádění dotazů.
- Může nasadit: true nebo false v závislosti na tom, jestli je možné navrženou optimalizaci dotazů nasadit jako průvodce plánem.
Ověření zobrazuje stav nasazení dříve vybraných dotazů pro tuto relaci. Seznam na této stránce se liší od předchozí stránky změnou sloupce Může nasadit na Může vrátit zpět. Tento sloupec může být true nebo false v závislosti na tom, jestli se dá nasazená optimalizace dotazů vrátit zpět a průvodce plánem se odebere.

Pokud později je potřeba vrátit navrženou optimalizaci, vyberte příslušný dotaz a vyberte Zrušit. Průvodce plánem dotazu se odebere a seznam bude aktualizován, aby se odstranil vrácený dotaz. Všimněte si na následujícím obrázku, že dotaz 8 byl odebrán.

Poznámka

Odstraněním uzavřené relace se neodstraní žádné dříve nasazené příručky k plánu. Pokud odstraníte relaci, která měla nasazené rady pro plán, nemůžete použít funkci QTA k vrácení změn zpět. Místo toho vyhledejte plánovací průvodce pomocí systémové tabulky sys.plan_guides a poté je ručně odstraňte pomocí sp_control_plan_guide.