Osvědčené postupy pro vyhrazené fondy SQL v Azure Synapse Analytics

  • Článek

Tento článek obsahuje kolekci osvědčených postupů, které vám pomůžou dosáhnout optimálního výkonu vyhrazených fondů SQL ve službě Azure Synapse Analytics. Pokud pracujete s bezserverovým fondem SQL, přečtěte si osvědčené postupy pro bezserverové fondy SQL, kde najdete konkrétní pokyny. Níže najdete základní pokyny a důležité oblasti, na které se při vytváření řešení zaměříte. Každá část vás seznámí s konceptem a pak vás nasměří na podrobnější články, které tento koncept podrobněji popisují.

Načítání vyhrazených fondů SQL

Pokyny k načítání vyhrazených fondů SQL najdete v pokynech k načítání dat.

Snižte náklady pomocí pozastavení a škálování

Další informace o snížení nákladů prostřednictvím pozastavení a škálování najdete v tématu Správa výpočetních prostředků.

Udržujte statistiky

Vyhrazený fond SQL je možné nakonfigurovat tak, aby automaticky detekoval a vytvářel statistiky sloupců. Plány dotazů vytvořené optimalizátorem jsou stejně dobré jako dostupné statistiky.

Doporučujeme povolit AUTO_CREATE_STATISTICS pro vaše databáze a udržovat statistiky aktualizované denně nebo po každém načtení, abyste zajistili, že jsou statistiky sloupců používaných v dotazech vždy aktuální.

Pokud chcete zkrátit dobu údržby statistik, vyberte, které sloupce mají statistiky, nebo potřebujete nejčastější aktualizaci. Můžete například chtít aktualizovat sloupce kalendářních dat, ve kterých se můžou přidávat nové hodnoty každý den. Zaměřte se na statistiky pro sloupce zapojené do spojení, sloupce používané v klauzuli WHERE a sloupce nalezené v GROUP BY.

Další informace o statistikách najdete v článcích Spravovat statistiky tabulek, CREATE STATISTICS a UPDATE STATISTICS .

Ladění výkonu dotazů

Seskupujte příkazy INSERT do dávek

Jednorázové načtení do malé tabulky s příkazem INSERT, jako INSERT INTO MyLookup VALUES (1, 'Type 1')je například nejlepší přístup v závislosti na vašich potřebách. Pokud ale potřebujete v průběhu dne načíst tisíce nebo miliony řádků, je pravděpodobné, že funkce INSERTs s jednímtonem není optimální.

Jedním ze způsobů, jak tento problém vyřešit, je vyvinout jeden proces, který zapisuje do souboru, a pak jiný proces, který bude tento soubor pravidelně načítat. Další informace najdete v článku INSERT .

Použijte PolyBase k rychlému načítání a exportu dat

Vyhrazený fond SQL podporuje načítání a export dat prostřednictvím několika nástrojů, mezi které patří Azure Data Factory, PolyBase a BCP. Pro malá množství dat, kde není výkon tak důležitý, by vám měl stačit libovolný nástroj.

Poznámka:

PolyBase je nejlepší volbou při načítání nebo exportu velkých objemů dat nebo potřebujete rychlejší výkon.

Úlohy funkce PolyBase můžete spustit pomocí příkazů CTAS nebo INSERT INTO. CTAS minimalizuje protokolování transakcí a je nejrychlejší způsob, jak načíst data. Azure Data Factory také podporuje načítání PolyBase a dokáže dosáhnout výkonu podobného CTAS. PolyBase podporuje různé formáty souborů, včetně souborů Gzip.

Pokud chcete maximalizovat propustnost při použití textových souborů Gzip, rozdělte soubory na 60 nebo více souborů, abyste maximalizovali paralelismus načítání. Pro rychlejší celkovou propustnost zvažte souběžné načítání dat. Další informace, které jsou relevantní pro tuto část, jsou uvedeny v následujících článcích:

Načtěte a následně dotazujte externí tabulky

PolyBase není optimální pro dotazy. Tabulky PolyBase pro vyhrazené fondy SQL aktuálně podporují pouze soubory objektů blob Azure a Azure Data Lake Storage. Tyto soubory nemají žádné výpočetní prostředky, které je zálohují. V důsledku toho vyhrazené fondy SQL nemohou tuto práci přesměrovat a musí číst celý soubor načtením, aby tempdb mohly číst data.

Pokud máte několik dotazů na dotazování na tato data, je lepší tato data jednou načíst a použít místní tabulku. Další pokyny k PolyBase najdete v příručce k používání článku PolyBase .

Distribuujte velké tabulky pomocí hodnot hash

Ve výchozím nastavení jsou tabulky distribuované metodou kruhového dotazování. Tato výchozí možnost usnadňuje uživatelům vytváření tabulek, aniž by se museli rozhodnout, jak se mají jejich tabulky distribuovat. Tabulky kruhového dotazování můžou pro některé úlohy provádět dostatečně. Ve většině případů ale distribuční sloupec poskytuje lepší výkon.

Nejběžnějším příkladem tabulky distribuované sloupcem, který provádí tabulku kruhového dotazování, je, když jsou dvě velké tabulky faktů spojené.

Pokud máte například tabulku objednávek distribuovanou order_id a tabulku transakcí distribuovanou také order_id, při spojení tabulky objednávek s tabulkou transakcí na order_id se tento dotaz stane předávacím dotazem. Operace přesunu dat se pak eliminují. Méně kroků znamená rychlejší dotaz. Méně přesunů dat také přispívá ke zrychlení dotazů.

Tip

Při načítání distribuované tabulky by se příchozí data neměla řadit podle distribučního klíče. Tím se zpomalí zatížení.

Odkazy na článek uvedené níže vám poskytnou další podrobnosti o zlepšení výkonu výběrem distribučního sloupce. V klauzuli WITH příkazu CREATE TABLE najdete také informace o tom, jak definovat distribuovanou tabulku:

Nevytvářejte zbytečně moc oddílů

I když dělení dat může být efektivní pro údržbu dat prostřednictvím přepínání oddílů nebo optimalizace kontrol odstraněním oddílů, příliš mnoho oddílů může zpomalit vaše dotazy. Strategie dělení s vysokou členitostí, která může dobře fungovat na SQL Serveru, nemusí dobře fungovat ve vyhrazeném fondu SQL.

Příliš mnoho oddílů může snížit efektivitu clusterovaných indexů columnstore, pokud má každý oddíl méně než 1 milion řádků. Vyhrazené fondy SQL automaticky rozdělují data do 60 databází. Pokud tedy vytvoříte tabulku s 100 oddíly, bude výsledkem 6000 oddílů. Každá úloha se liší, takže nejlepší doporučení je experimentovat s dělením, abyste zjistili, co je pro vaši úlohu nejvhodnější.

Jednou z možností, jak zvážit, je použití členitosti, která je nižší než to, co jste implementovali pomocí SQL Serveru. Zvažte například použití týdenních nebo měsíčních oddílů místo denních oddílů.

Další informace o dělení najdete v článku o dělení tabulek.

Minimalizujte velikosti transakcí

Příkazy INSERT, UPDATE a DELETE se spouští v transakci. Když selžou, musí se vrátit zpět. Pokud chcete snížit potenciál dlouhého vrácení zpět, minimalizujte velikosti transakcí, kdykoli je to možné. Minimalizace velikostí transakcí je možné provést rozdělením příkazů INSERT, UPDATE a DELETE na části. Pokud máte například insert, který očekáváte, že bude trvat 1 hodinu, můžete rozdělit INSERT do čtyř částí. Každé spuštění se pak zkrátí na 15 minut.

Tip

Abyste snížili riziko odvolání transakce, využijte příkazy s minimálním protokolováním, například CTAS, TRUNCATE, DROP TABLE nebo INSERT k vyprázdnění tabulek.

Dalším způsobem, jak eliminovat odvolávání transakcí, je použít ke správě dat operace pouze nad metadaty, jako třeba přepínání oddílů. Například místo provedení příkazu DELETE odstraňte všechny řádky v tabulce, ve které byl order_date v říjnu 2001, můžete data rozdělit měsíčně. Potom můžete oddíl s daty pro prázdný oddíl přepnout z jiné tabulky (viz příklady ALTER TABLE).

U tabulek, které nejsou rozdělené na oddíly, zvažte použití CTAS k zápisu dat, která chcete zachovat v tabulce, místo použití delete. Pokud CTAS trvá stejnou dobu, je mnohem bezpečnější spustit, protože má minimální protokolování transakcí a v případě potřeby ho můžete rychle zrušit.

Další informace o obsahu souvisejícím s touto částí najdete v následujících článcích:

Zmenšení velikostí výsledků dotazu

Omezení velikosti výsledků dotazů pomáhá vyhnout se problémům na straně klienta způsobeným velkými výsledky dotazů. Dotaz můžete upravit tak, aby se snížil počet vrácených řádků. Některé nástroje pro generování dotazů umožňují přidat do každého dotazu syntaxi "top N". Můžete také CETAS výsledek dotazu do dočasné tabulky a pak použít Export PolyBase pro zpracování nižší úrovně.

Použijte co nejmenší velikost sloupce

Při definování DDL použijte nejmenší datový typ, který bude podporovat vaše data. Tím se zlepší výkon dotazů. Toto doporučení je zvlášť důležité pro sloupce CHAR a VARCHAR. Pokud má nejdelší hodnota v sloupci 25 znaků, nadefinujte typ sloupce jako VARCHAR(25). Vyhněte se definování všech sloupců se znaky na výchozí délku. Kromě toho definujte sloupce jako VARCHAR, pokud je to vše potřebné místo použití NVARCHAR.

Podrobnější přehled základních konceptů relevantních pro výše uvedené informace najdete v přehledu tabulek, datových typech tabulek a článcích CREATE TABLE .

Použijte dočasné tabulky hald pro přechodná data

Když dočasně cílíte data do vyhrazených fondů SQL, tabulky haldy obecně zrychlová celkový proces. Pokud načítáte data pouze pro fázi před spuštěním dalších transformací, načtení tabulky do tabulky haldy bude rychlejší než načtení dat do clusterované tabulky columnstore.

Načítání dat do dočasné tabulky se také načte mnohem rychleji než načtení tabulky do trvalého úložiště. Dočasné tabulky začínají na "#" a jsou přístupné pouze relací, která ji vytvořila. V důsledku toho můžou fungovat pouze v omezených scénářích. Tabulky hald se definují v klauzuli WITH příkazu CREATE TABLE. Pokud používáte dočasnou tabulku, nezapomeňte nad ní také vytvořit statistiky.

Další informace najdete v článcích Dočasné tabulky, CREATE TABLE a CREATE TABLE AS SELECT .

Optimalizujte clusterované tabulky columnstore

Clusterované indexy columnstore jsou jedním z nejúčinnějších způsobů, jak ukládat data ve vyhrazeném fondu SQL. Ve výchozím nastavení se tabulky ve vyhrazeném fondu SQL vytvářejí jako Clustered ColumnStore. Pokud chcete dosáhnout co nejlepšího výkonu dotazů na tabulky columnstore, je důležité mít kvalitní segmenty. Když se řádky zapisují do tabulek columnstore při zatížení paměti, může tím utrpět kvalita segmentů columnstore.

Kvalitu segmentu lze měřit počtem řádků v komprimované skupině řádků. Podrobné pokyny k detekci a zlepšení kvality segmentů pro clusterované tabulky columnstore tabulek najdete v článku o příčinách špatné kvality indexu columnstore.

Vzhledem k tomu, že segmenty columnstore s vysokou kvalitou jsou důležité, je vhodné použít ID uživatelů, kteří jsou ve střední nebo velké třídě prostředků pro načítání dat. Použití nižších jednotek datového skladu znamená, že chcete uživateli načítání přiřadit větší třídu prostředků.

Tabulky Columnstore obvykle nebudou odesílat data do komprimovaného segmentu columnstore, dokud nebude více než 1 milion řádků na tabulku. Každá vyhrazená tabulka fondu SQL se distribuuje do 60 různých distribucí. Tabulky columnstore proto nebudou těžit z dotazu, pokud tabulka neobsahuje více než 60 milionů řádků.

Tip

U tabulek s méně než 60 miliony řádků nemusí být optimálním řešením index columnstore.

Pokud rozdělíte data, každý oddíl bude muset mít 1 milion řádků, aby mohl využívat clusterovaný index columnstore. V případě tabulky s 100 oddíly musí mít alespoň 6 miliard řádků, aby bylo možné využít clusterovaného úložiště sloupců (60 distribucí 100 oddílů 1 milion řádků).

Pokud tabulka neobsahuje 6 miliard řádků, máte dvě hlavní možnosti. Buď snižte počet oddílů, nebo zvažte použití tabulky haldy. Také může být vhodné experimentovat, abyste zjistili, jestli je možné dosáhnout lepšího výkonu pomocí tabulky haldy se sekundárními indexy, nikoli tabulkou columnstore.

Při dotazování tabulky columnstore budou příkazy pracovat rychleji, pokud vyberete pouze sloupce, které potřebujete. Další informace o indexech table a columnstore najdete v následujících článcích:

Použijte větší třídu prostředků k vylepšení výkonu dotazu

Fondy SQL používají skupiny prostředků jako způsob přidělení paměti dotazům. Zpočátku jsou všichni uživatelé přiřazeni k malé třídě prostředků, která uděluje 100 MB paměti na distribuci. Vždy existuje 60 distribucí. Každé distribuci je přiděleno minimálně 100 MB. Celkové přidělení paměti pro celý systém je 6 000 MB nebo jen pod 6 GB.

Pro určité dotazy, například velká spojení nebo nahrávání do clusterovaných tabulek columnstore, bude větší přidělení paměti přínosem. Některé dotazy, jako jsou čisté kontroly, neuvidí žádné výhody. Využití větších tříd prostředků má vliv na souběžnost. Před přesunem všech uživatelů do velké třídy prostředků proto budete chtít mít na paměti tato fakta.

Další informace o třídách prostředků najdete v článku o třídách prostředků pro správu úloh.

Použití menší třídy prostředků ke zvýšení souběžnosti

Pokud si všimnete dlouhého zpoždění v dotazech uživatelů, můžou být vaši uživatelé spuštěni ve větších třídách prostředků. Tento scénář podporuje spotřebu slotů souběžnosti, což může způsobit, že se ostatní dotazy zařadí do fronty. Pokud chcete zjistit, jestli jsou dotazy uživatelů zařazené do fronty, spusťte příkaz SELECT * FROM sys.dm_pdw_waits , abyste zjistili, jestli se vrátí nějaké řádky.

Třídy prostředků pro správu úloh a články o sys.dm_pdw_waits vám poskytnou další informace.

Použijte zobrazení dynamických zpráv k monitorování a optimalizaci dotazů

Vyhrazené fondy SQL mají několik zobrazení dynamické správy, které lze použít k monitorování provádění dotazů. Následující článek monitorování vás provede podrobnými pokyny k zobrazení podrobností o prováděcím dotazu. S rychlým vyhledáváním dotazů v těchto zobrazeních dynamických zpráv může pomoci použití možnosti LABEL v dotazech. Další podrobné informace najdete v následujících článcích:

Další kroky

Projděte si také článek Řešení běžných problémů a řešení.

Pokud potřebujete informace, které nejsou uvedené v tomto článku, vyhledejte na stránce pro otázky Microsoft Q&A azure Synapse místo, kde můžete klást otázky jiným uživatelům a produktové skupině Azure Synapse Analytics.

Toto fórum aktivně sledujeme, abychom zajistili, že vaši otázku zodpoví další uživatel nebo někdo z nás. Pokud dáváte přednost kladení otázek na Stack Overflow, máme také fórum Služby Stack Overflow služby Azure Synapse Analytics.