Sdílet prostřednictvím

Správa úloh pomocí tříd prostředků ve službě Azure Synapse Analytics

  • Článek

Pokyny pro použití tříd prostředků ke správě paměti a souběžnosti pro dotazy fondu Synapse SQL ve službě Azure Synapse

Co jsou třídy prostředků?

Výkonová kapacita dotazu je určena třídou prostředků uživatele. Třídy prostředků jsou předem určené limity prostředků ve fondu Synapse SQL, které řídí výpočetní prostředky a souběžnost spouštění dotazů. Třídy prostředků vám můžou pomoct s konfigurací prostředků pro dotazy nastavením limitů počtu dotazů, které běží souběžně, a výpočetních prostředků přiřazených jednotlivým dotazům. Existuje kompromis mezi pamětí a souběžností.

  • Menší třídy prostředků snižují maximální paměť na dotaz, ale zvyšují souběžnost.
  • Větší třídy prostředků zvyšují maximální paměť na dotaz, ale snižují souběžnost.

Existují dva typy tříd prostředků:

  • Třídy statických prostředků, které jsou vhodné pro zvýšení souběžnosti u pevné velikosti sady dat.
  • Dynamické třídy prostředků, které jsou vhodné pro datové sady, které se zvětšují a vyžadují vyšší výkon při vertikálním navýšení kapacity úrovně služby.

Třídy prostředků používají sloty souběžnosti k měření spotřeby prostředků. Sloty souběžnosti jsou vysvětleny dále v tomto článku.

  • Pokud chcete zobrazit využití prostředků pro třídy prostředků, podívejte se na omezení paměti a souběžnosti.
  • Pokud chcete upravit třídu prostředků, můžete dotaz spustit pod jiným uživatelem nebo změnit členství v třídě prostředků aktuálního uživatele.

Statické třídy prostředků

Statické třídy prostředků přidělují stejné množství paměti bez ohledu na aktuální úroveň výkonu, která se měří v jednotkách datového skladu. Vzhledem k tomu, že dotazy získají stejné přidělení paměti bez ohledu na úroveň výkonu, horizontální navýšení kapacity datového skladu umožňuje spouštění více dotazů v rámci třídy prostředků. Statické třídy prostředků jsou ideální, pokud je datový svazek známý a konstantní.

Statické třídy prostředků se implementují s těmito předdefinovanými databázovými rolemi:

  • staticrc10
  • staticrc20
  • staticrc30
  • staticrc40
  • staticrc50
  • staticrc60
  • staticrc70
  • staticrc80

Dynamické třídy prostředků

Dynamické třídy prostředků přidělují proměnlivou velikost paměti v závislosti na aktuální úrovni služby. I když jsou statické třídy prostředků přínosné pro vyšší souběžnost a statické datové svazky, dynamické třídy prostředků jsou vhodnější pro rostoucí nebo proměnlivé množství dat. Když vertikálně navýšit kapacitu na vyšší úroveň služby, vaše dotazy automaticky získají více paměti.

S výjimkou smallrc se dynamické třídy prostředků implementují s těmito předdefinovanými databázovými rolemi:

  • mediumrc
  • většíc
  • xlargerc

Smallrc se nezobrazuje jako role databáze, ale je výchozí třída prostředků.

Přidělení paměti pro každou třídu prostředků je následující.

Úroveň služeb smallrc mediumrc většíc xlargerc
DW100c 25 % 25 % 25 % 70 %
DW200c 12.5% 12.5% 22 % 70 %
DW300c 8 % 10 % 22 % 70 %
DW400c 6.25% 10 % 22 % 70 %
DW500c. 5 % 10 % 22 % 70 %
DW1000c to
DW30000c		 3 % 10 % 22 % 70 %

Výchozí třída prostředků

Ve výchozím nastavení je každý uživatel členem dynamické třídy prostředků smallrc.

Třída prostředků správce služby je opravena v smallrc a nelze ji změnit. Správce služby je uživatel vytvořený během procesu zřizování. Správce služby v tomto kontextu je přihlášení určené pro přihlášení správce serveru při vytváření nového fondu Synapse SQL s novým serverem.

Poznámka:

Uživatelé nebo skupiny definované jako správce služby Active Directory jsou také správci služeb.

Operace třídy prostředků

Třídy prostředků jsou navržené tak, aby zlepšily výkon při správě dat a manipulaci s nimi. Složité dotazy můžou také těžit z provozu ve velké třídě prostředků. Například výkon dotazů pro velká spojení a řazení se může zlepšit, když je třída prostředků dostatečně velká, aby se dotaz mohl spouštět v paměti.

Operace řízené třídami prostředků

Tyto operace se řídí třídami prostředků:

  • INSERT-SELECT, , UPDATEDELETE
  • SELECT (při dotazování uživatelských tabulek)
  • ALTER INDEX - REBUILD nebo REORGANIZE
  • ALTER TABLE REBUILD
  • CREATE INDEX
  • CREATE CLUSTERED COLUMNSTORE INDEX
  • CREATE TABLE AS SELECT (CTAS)
  • Načítání dat
  • Operace přesunu dat prováděné službou pro přesun dat (DMS)

Poznámka:

Příkazy SELECT v zobrazeních dynamické správy (DMV) nebo jiných systémových zobrazeních se neřídí žádným omezením souběžnosti. Systém můžete monitorovat bez ohledu na počet spuštěných dotazů.

Operace, které nejsou řízeny třídami prostředků

Některé dotazy se vždy spouštějí v malé třídě prostředkůrc, i když je uživatel členem větší třídy prostředků. Tyto vyloučené dotazy se nezapočítávají do limitu souběžnosti. Pokud je například limit souběžnosti 16, může mnoho uživatelů vybírat ze systémových zobrazení, aniž by to ovlivnilo dostupné sloty souběžnosti.

Následující příkazy jsou vyloučené z tříd prostředků a vždy běží v smallrc:

  • CREATE nebo DROP TABLE
  • ALTER TABLE ... SWITCH, SPLIT nebo MERGE PARTITION
  • ALTER INDEX DISABLE
  • DROP INDEX
  • CREATE, UPDATE nebo DROP STATISTICS
  • TRUNCATE TABLE
  • ALTER AUTHORIZATION
  • CREATE LOGIN
  • CREATE, ALTER nebo DROP USER
  • CREATE, ALTER nebo DROP PROCEDURE
  • CREATE nebo DROP VIEW
  • INSERT VALUES
  • SELECT ze systémových zobrazení a zobrazení dynamické správy
  • EXPLAIN
  • DBCC

Sloty souběžnosti

Sloty souběžnosti představují pohodlný způsob, jak sledovat prostředky, které jsou k dispozici pro provádění dotazů. Jsou jako vstupenky, které si koupíte na rezervaci míst na koncertu, protože sezení je omezené. Celkový počet slotů souběžnosti na datový sklad je určen úrovní služby. Před spuštěním dotazu musí být schopný rezervovat dostatek slotů souběžnosti. Po dokončení dotazu uvolní sloty souběžnosti.

  • Dotaz spuštěný s 10 sloty souběžnosti má přístup k 5krát více výpočetním prostředkům než dotaz spuštěný se 2 sloty souběžnosti.
  • Pokud každý dotaz vyžaduje 10 slotů souběžnosti a existuje 40 slotů souběžnosti, můžou se souběžně spouštět jenom 4 dotazy.

Sloty souběžnosti využívají pouze dotazy řízené prostředky. Systémové dotazy a některé triviální dotazy nevyužívají žádné sloty. Přesný počet využitých slotů souběžnosti určuje třída prostředků dotazu.

Zobrazení tříd prostředků

Třídy prostředků se implementují jako předdefinované databázové role. Existují dva typy tříd prostředků: dynamické a statické. Pokud chcete zobrazit seznam tříd prostředků, použijte následující dotaz:

SELECT name
FROM   sys.database_principals
WHERE  name LIKE '%rc%' AND type_desc = 'DATABASE_ROLE';

Změna třídy prostředků uživatele

Třídy prostředků se implementují přiřazením uživatelů k databázovým rolím. Když uživatel spustí dotaz, spustí se dotaz s třídou prostředků uživatele. Pokud je například uživatel členem databázové role staticrc10, spustí se jejich dotazy s malým množstvím paměti. Pokud je uživatel databáze členem databázových rolí xlargerc nebo staticrc80, spustí se jejich dotazy s velkým množstvím paměti.

Pokud chcete zvýšit třídu prostředků uživatele, použijte sp_addrolemember k přidání uživatele do databázové role velké třídy prostředků. Následující kód přidá uživatele do role větší databáze. Každý požadavek získá 22 % systémové paměti.

EXEC sp_addrolemember 'largerc', 'loaduser';

Pokud chcete snížit třídu prostředků, použijte sp_droprolemember. Pokud loaduser není členem nebo žádnou jinou třídou prostředků, přejdou do výchozí třídy prostředků smallrc s udělením 3% paměti.

EXEC sp_droprolemember 'largerc', 'loaduser';

Priorita třídy prostředků

Uživatelé mohou být členy více tříd prostředků. Když uživatel patří do více než jedné třídy prostředků:

  • Dynamické třídy prostředků mají přednost před statickými třídami prostředků. Pokud je například uživatel členem třídy mediumrc (dynamic) i staticrc80 (statická), dotazy se spouštějí pomocí mediumrc.
  • Větší třídy prostředků mají přednost před menšími třídami prostředků. Pokud je například uživatel členem mediumrc a largerc, spustí se dotazy s většíc. Podobně platí, že pokud je uživatel členem staticrc20 i statirc80, dotazy se spouštějí s přidělením prostředků staticrc80.

Doporučení

Poznámka:

Zvažte využití možností správy úloh (izolace úloh, klasifikace a důležitosti) pro větší kontrolu nad vašimi úlohami a předvídatelným výkonem.

Doporučujeme vytvořit uživatele, který je vyhrazený pro spuštění konkrétního typu dotazu nebo operace načítání. Dejte uživateli trvalou třídu prostředků místo časté změny třídy prostředků. Statické třídy prostředků nabízejí větší celkovou kontrolu nad úlohou, takže doporučujeme před zvážením dynamických tříd prostředků použít statické třídy prostředků.

Třídy prostředků pro uživatele načítání

CREATE TABLE ve výchozím nastavení používá clusterované indexy columnstore. Komprese dat do indexu columnstore je operace náročná na paměť a zatížení paměti může snížit kvalitu indexu. Zatížení paměti může vést k tomu, že při načítání dat potřebuje vyšší třídu prostředků. Abyste zajistili, že zatížení bude mít dostatek paměti, můžete vytvořit uživatele, který je určený pro spouštění zatížení, a přiřadit ho vyšší třídě prostředků.

Paměť potřebná k efektivnímu zpracování zatížení závisí na povaze načtené tabulky a velikosti dat. Další informace o požadavcíchnach

Jakmile určíte požadavek na paměť, zvolte, jestli se má uživatel zatížení přiřadit statické nebo dynamické třídě prostředků.

  • Pokud požadavky na paměť tabulky spadají do určitého rozsahu, použijte statickou třídu prostředků. Načte spuštění s příslušnou pamětí. Když škálujete datový sklad, zatížení nepotřebuje více paměti. Při použití statické třídy prostředků zůstanou přidělení paměti konstantní. Tato konzistence šetří paměť a umožňuje souběžné spouštění více dotazů. Doporučujeme, aby nová řešení jako první používala statické třídy prostředků, protože poskytují větší kontrolu.
  • Dynamická třída prostředků použijte, když se požadavky na paměť tabulky značně liší. Načtení může vyžadovat více paměti, než poskytuje aktuální úroveň DWU nebo cDWU. Škálování datového skladu přidává více paměti k operacím načítání, což umožňuje rychlejší načítání.

Třídy prostředků pro dotazy

Některé dotazy jsou náročné na výpočetní výkon a některé ne.

  • Pokud jsou dotazy složité, zvolte dynamickou třídu prostředků, ale nepotřebujete vysokou souběžnost. Například generování denních nebo týdenních sestav je příležitostnou potřebou zdrojů. Pokud sestavy zpracovávají velké objemy dat, škálování datového skladu poskytuje více paměti stávající třídě prostředků uživatele.
  • Zvolte statickou třídu prostředků, když se očekávání prostředků v průběhu dne liší. Třída statických prostředků například funguje dobře, když je datový sklad dotazován mnoha lidmi. Při škálování datového skladu se množství paměti přidělené uživateli nezmění. V důsledku toho se v systému dá paralelně spustit více dotazů.

Správné přidělení paměti závisí na mnoha faktorech, jako je množství dat dotazovaných, povaha schémat tabulek a různá spojení, výběr a predikáty skupin. Obecně platí, že přidělení větší paměti umožňuje rychlejší dokončení dotazů, ale snižuje celkovou souběžnost. Pokud souběžnost není problém, nadměrné přidělování paměti nezpůsobí propustnost.

K ladění výkonu použijte různé třídy prostředků. V další části najdete uloženou proceduru, která vám pomůže zjistit nejlepší třídu prostředků.

Příklad kódu pro vyhledání nejlepší třídy prostředků

Uložená procedura prc_workload_management_by_DWU slouží k:

  • Podívejte se na souběžnost a udělení paměti na třídu prostředků v daném cíli úrovně služeb.
    • Zadejte NULL název schématu i tabulky.
  • Podívejte se na nejlepší třídu prostředků pro operace CCI náročné na paměť (načítání, kopírování tabulky, index opětovného sestavení atd.) v tabulce CCI, která není rozdělena do oddílů v dané třídě prostředků.
    • Uložený proc používá schéma tabulky ke zjištění požadovaného přidělení paměti.

Příklady najdete v příkladu použití.

Závislosti a omezení

  • Tato uložená procedura není určená k výpočtu požadavku na paměť pro dělenou tabulku cci.
  • Tato uložená procedura nebere v úvahu požadavky na paměť pro část CTAS/INSERT-SELECT a předpokládá, že se jedná o SELECT.
  • Tato uložená procedura používá dočasnou tabulku, která je k dispozici v relaci, ve které byla vytvořena tato uložená procedura.
  • Tato uložená procedura závisí na aktuálních nabídkách (například na konfiguraci hardwaru, konfiguraci DMS) a pokud se některá z těchto změn změní, nebude tento uložený proc fungovat správně.
  • Tato uložená procedura závisí na existujících nabídkách omezení souběžnosti a pokud se tato změna změní, tato uložená procedura nebude správně fungovat.
  • Tato uložená procedura závisí na existujících nabídkách tříd prostředků a pokud se tato změna změní, nebude tato uložená procedura fungovat správně.

Poznámka:

Pokud po provedení uložené procedury s zadanými parametry nedostáváte výstup, může existovat dva případy.

  • Parametr DW obsahuje neplatnou hodnotu SLO.
  • Nebo neexistuje žádná odpovídající třída prostředků pro operaci CCI v tabulce.

Například v DW100c je nejvyšší dostupné přidělení paměti 1 GB a pokud je schéma tabulky dostatečně široké, aby překročilo požadavek 1 GB.

Příklad využití

Syntaxe:
EXEC dbo.prc_workload_management_by_DWU @DWU VARCHAR(7), @SCHEMA_NAME VARCHAR(128), @TABLE_NAME VARCHAR(128)

  • @DWU: Zadejte NULL parametr, který extrahuje aktuální DWU z databáze DW, nebo zadejte jakoukoli podporovanou datovou sadu ve formě DW100c.
  • @SCHEMA_NAME: Zadejte název schématu tabulky.
  • @TABLE_NAME: Zadejte název tabulky, která má zájem.

Příklady spuštění tohoto uloženého proc:

EXEC dbo.prc_workload_management_by_DWU 'DW2000c', 'dbo', 'Table1';  
EXEC dbo.prc_workload_management_by_DWU NULL, 'dbo', 'Table1';  
EXEC dbo.prc_workload_management_by_DWU 'DW6000c', NULL, NULL;  
EXEC dbo.prc_workload_management_by_DWU NULL, NULL, NULL;

Následující příkaz vytvoří Table1 , který se použije v předchozích příkladech. CREATE TABLE Table1 (a int, b varchar(50), c decimal (18,10), d char(10), e varbinary(15), f float, g datetime, h date);

Definice uložené procedury

-------------------------------------------------------------------------------
-- Dropping prc_workload_management_by_DWU procedure if it exists.
-------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'prc_workload_management_by_DWU')
DROP PROCEDURE dbo.prc_workload_management_by_DWU
GO

-------------------------------------------------------------------------------
-- Creating prc_workload_management_by_DWU.
-------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_workload_management_by_DWU
(@DWU VARCHAR(8),
 @SCHEMA_NAME VARCHAR(128),
 @TABLE_NAME VARCHAR(128)
)
AS

IF @DWU IS NULL
BEGIN
-- Selecting proper DWU for the current DB if not specified.

SELECT @DWU = 'DW'+ CAST(CASE WHEN Mem> 4 THEN Nodes*500
  ELSE Mem*100
  END AS VARCHAR(10)) +'c'
    FROM (
      SELECT Nodes=count(distinct n.pdw_node_id), Mem=max(i.committed_target_kb/1000/1000/60)
        FROM sys.dm_pdw_nodes n
        CROSS APPLY sys.dm_pdw_nodes_os_sys_info i
        WHERE type = 'COMPUTE')A
END

-- Dropping temp table if exists.
IF OBJECT_ID('tempdb..#ref') IS NOT NULL
BEGIN
  DROP TABLE #ref;
END;

-- Creating ref. temp table (CTAS) to hold mapping info.
CREATE TABLE #ref
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
WITH
-- Creating concurrency slots mapping for various DWUs.
alloc
AS
(
SELECT 'DW100c' AS DWU,4 AS max_queries,4 AS max_slots,1 AS slots_used_smallrc,1 AS slots_used_mediumrc,2 AS slots_used_largerc,4 AS slots_used_xlargerc,1 AS slots_used_staticrc10,2 AS slots_used_staticrc20,4 AS slots_used_staticrc30,4 AS slots_used_staticrc40,4 AS slots_used_staticrc50,4 AS slots_used_staticrc60,4 AS slots_used_staticrc70,4 AS slots_used_staticrc80
  UNION ALL
   SELECT 'DW200c',8,8,1,2,4,8,1,2,4,8,8,8,8,8
  UNION ALL
   SELECT 'DW300c',12,12,1,2,4,8,1,2,4,8,8,8,8,8
  UNION ALL
   SELECT 'DW400c',16,16,1,4,8,16,1,2,4,8,16,16,16,16
  UNION ALL
   SELECT 'DW500c',20,20,1,4,8,16,1,2,4,8,16,16,16,16
  UNION ALL
   SELECT 'DW1000c',32,40,1,4,8,28,1,2,4,8,16,32,32,32
  UNION ALL
   SELECT 'DW1500c',32,60,1,6,13,42,1,2,4,8,16,32,32,32
  UNION ALL
   SELECT 'DW2000c',48,80,2,8,17,56,1,2,4,8,16,32,64,64
  UNION ALL
   SELECT 'DW2500c',48,100,3,10,22,70,1,2,4,8,16,32,64,64
  UNION ALL
   SELECT 'DW3000c',64,120,3,12,26,84,1,2,4,8,16,32,64,64
  UNION ALL
   SELECT 'DW5000c',64,200,6,20,44,140,1,2,4,8,16,32,64,128
  UNION ALL
   SELECT 'DW6000c',128,240,7,24,52,168,1,2,4,8,16,32,64,128
  UNION ALL
   SELECT 'DW7500c',128,300,9,30,66,210,1,2,4,8,16,32,64,128
  UNION ALL
   SELECT 'DW10000c',128,400,12,40,88,280,1,2,4,8,16,32,64,128
  UNION ALL
   SELECT 'DW15000c',128,600,18,60,132,420,1,2,4,8,16,32,64,128
  UNION ALL
   SELECT 'DW30000c',128,1200,36,120,264,840,1,2,4,8,16,32,64,128
)
-- Creating workload mapping to their corresponding slot consumption and default memory grant.
,map  
AS
(
  SELECT CONVERT(varchar(20), 'SloDWGroupSmall') AS wg_name, slots_used_smallrc AS slots_used FROM alloc WHERE DWU = @DWU
UNION ALL
  SELECT CONVERT(varchar(20), 'SloDWGroupMedium') AS wg_name, slots_used_mediumrc AS slots_used FROM alloc WHERE DWU = @DWU
UNION ALL
  SELECT CONVERT(varchar(20), 'SloDWGroupLarge') AS wg_name, slots_used_largerc AS slots_used FROM alloc WHERE DWU = @DWU
UNION ALL
  SELECT CONVERT(varchar(20), 'SloDWGroupXLarge') AS wg_name, slots_used_xlargerc AS slots_used FROM alloc WHERE DWU = @DWU
  UNION ALL
  SELECT 'SloDWGroupC00',1
  UNION ALL
    SELECT 'SloDWGroupC01',2
  UNION ALL
    SELECT 'SloDWGroupC02',4
  UNION ALL
    SELECT 'SloDWGroupC03',8
  UNION ALL
    SELECT 'SloDWGroupC04',16
  UNION ALL
    SELECT 'SloDWGroupC05',32
  UNION ALL
    SELECT 'SloDWGroupC06',64
  UNION ALL
    SELECT 'SloDWGroupC07',128
)

-- Creating ref based on current / asked DWU.
, ref
AS
(
  SELECT  a1.*
  ,       m1.wg_name          AS wg_name_smallrc
  ,       m1.slots_used * 250 AS tgt_mem_grant_MB_smallrc
  ,       m2.wg_name          AS wg_name_mediumrc
  ,       m2.slots_used * 250 AS tgt_mem_grant_MB_mediumrc
  ,       m3.wg_name          AS wg_name_largerc
  ,       m3.slots_used * 250 AS tgt_mem_grant_MB_largerc
  ,       m4.wg_name          AS wg_name_xlargerc
  ,       m4.slots_used * 250 AS tgt_mem_grant_MB_xlargerc
  ,       m5.wg_name          AS wg_name_staticrc10
  ,       m5.slots_used * 250 AS tgt_mem_grant_MB_staticrc10
  ,       m6.wg_name          AS wg_name_staticrc20
  ,       m6.slots_used * 250 AS tgt_mem_grant_MB_staticrc20
  ,       m7.wg_name          AS wg_name_staticrc30
  ,       m7.slots_used * 250 AS tgt_mem_grant_MB_staticrc30
  ,       m8.wg_name          AS wg_name_staticrc40
  ,       m8.slots_used * 250 AS tgt_mem_grant_MB_staticrc40
  ,       m9.wg_name          AS wg_name_staticrc50
  ,       m9.slots_used * 250 AS tgt_mem_grant_MB_staticrc50
  ,       m10.wg_name          AS wg_name_staticrc60
  ,       m10.slots_used * 250 AS tgt_mem_grant_MB_staticrc60
  ,       m11.wg_name          AS wg_name_staticrc70
  ,       m11.slots_used * 250 AS tgt_mem_grant_MB_staticrc70
  ,       m12.wg_name          AS wg_name_staticrc80
  ,       m12.slots_used * 250 AS tgt_mem_grant_MB_staticrc80
  FROM alloc a1
  JOIN map   m1  ON a1.slots_used_smallrc     = m1.slots_used and m1.wg_name = 'SloDWGroupSmall'
  JOIN map   m2  ON a1.slots_used_mediumrc    = m2.slots_used and m2.wg_name = 'SloDWGroupMedium'
  JOIN map   m3  ON a1.slots_used_largerc     = m3.slots_used and m3.wg_name = 'SloDWGroupLarge'
  JOIN map   m4  ON a1.slots_used_xlargerc    = m4.slots_used and m4.wg_name = 'SloDWGroupXLarge'
  JOIN map   m5  ON a1.slots_used_staticrc10    = m5.slots_used and m5.wg_name NOT IN ('SloDWGroupSmall','SloDWGroupMedium','SloDWGroupLarge','SloDWGroupXLarge')
  JOIN map   m6  ON a1.slots_used_staticrc20    = m6.slots_used and m6.wg_name NOT IN ('SloDWGroupSmall','SloDWGroupMedium','SloDWGroupLarge','SloDWGroupXLarge')
  JOIN map   m7  ON a1.slots_used_staticrc30    = m7.slots_used and m7.wg_name NOT IN ('SloDWGroupSmall','SloDWGroupMedium','SloDWGroupLarge','SloDWGroupXLarge')
  JOIN map   m8  ON a1.slots_used_staticrc40    = m8.slots_used and m8.wg_name NOT IN ('SloDWGroupSmall','SloDWGroupMedium','SloDWGroupLarge','SloDWGroupXLarge')
  JOIN map   m9  ON a1.slots_used_staticrc50    = m9.slots_used and m9.wg_name NOT IN ('SloDWGroupSmall','SloDWGroupMedium','SloDWGroupLarge','SloDWGroupXLarge')
  JOIN map   m10  ON a1.slots_used_staticrc60    = m10.slots_used and m10.wg_name NOT IN ('SloDWGroupSmall','SloDWGroupMedium','SloDWGroupLarge','SloDWGroupXLarge')
  JOIN map   m11  ON a1.slots_used_staticrc70    = m11.slots_used and m11.wg_name NOT IN ('SloDWGroupSmall','SloDWGroupMedium','SloDWGroupLarge','SloDWGroupXLarge')
  JOIN map   m12  ON a1.slots_used_staticrc80    = m12.slots_used and m12.wg_name NOT IN ('SloDWGroupSmall','SloDWGroupMedium','SloDWGroupLarge','SloDWGroupXLarge')
  WHERE   a1.DWU = @DWU
)
SELECT  DWU
,       max_queries
,       max_slots
,       slots_used
,       wg_name
,       tgt_mem_grant_MB
,       up1 as rc
,       (ROW_NUMBER() OVER(PARTITION BY DWU ORDER BY DWU)) as rc_id
FROM
(
    SELECT  DWU
    ,       max_queries
    ,       max_slots
    ,       slots_used
    ,       wg_name
    ,       tgt_mem_grant_MB
    ,       REVERSE(SUBSTRING(REVERSE(wg_names),1,CHARINDEX('_',REVERSE(wg_names),1)-1)) as up1
    ,       REVERSE(SUBSTRING(REVERSE(tgt_mem_grant_MBs),1,CHARINDEX('_',REVERSE(tgt_mem_grant_MBs),1)-1)) as up2
    ,       REVERSE(SUBSTRING(REVERSE(slots_used_all),1,CHARINDEX('_',REVERSE(slots_used_all),1)-1)) as up3
    FROM    ref AS r1
    UNPIVOT
    (
        wg_name FOR wg_names IN (wg_name_smallrc,wg_name_mediumrc,wg_name_largerc,wg_name_xlargerc,
        wg_name_staticrc10, wg_name_staticrc20, wg_name_staticrc30, wg_name_staticrc40, wg_name_staticrc50,
        wg_name_staticrc60, wg_name_staticrc70, wg_name_staticrc80)
    ) AS r2
    UNPIVOT
    (
        tgt_mem_grant_MB FOR tgt_mem_grant_MBs IN (tgt_mem_grant_MB_smallrc,tgt_mem_grant_MB_mediumrc,
        tgt_mem_grant_MB_largerc,tgt_mem_grant_MB_xlargerc, tgt_mem_grant_MB_staticrc10, tgt_mem_grant_MB_staticrc20,
        tgt_mem_grant_MB_staticrc30, tgt_mem_grant_MB_staticrc40, tgt_mem_grant_MB_staticrc50,
        tgt_mem_grant_MB_staticrc60, tgt_mem_grant_MB_staticrc70, tgt_mem_grant_MB_staticrc80)
    ) AS r3
    UNPIVOT
    (
        slots_used FOR slots_used_all IN (slots_used_smallrc,slots_used_mediumrc,slots_used_largerc,
        slots_used_xlargerc, slots_used_staticrc10, slots_used_staticrc20, slots_used_staticrc30,
        slots_used_staticrc40, slots_used_staticrc50, slots_used_staticrc60, slots_used_staticrc70,
        slots_used_staticrc80)
    ) AS r4
) a
WHERE   up1 = up2
AND     up1 = up3
;

-- Getting current info about workload groups.
WITH  
dmv  
AS  
(
  SELECT
          rp.name                                           AS rp_name
  ,       rp.max_memory_kb*1.0/1048576                      AS rp_max_mem_GB
  ,       (rp.max_memory_kb*1.0/1024)
          *(request_max_memory_grant_percent/100)           AS max_memory_grant_MB
  ,       (rp.max_memory_kb*1.0/1048576)
          *(request_max_memory_grant_percent/100)           AS max_memory_grant_GB
  ,       wg.name                                           AS wg_name
  ,       wg.importance                                     AS importance
  ,       wg.request_max_memory_grant_percent               AS request_max_memory_grant_percent
  FROM    sys.dm_pdw_nodes_resource_governor_workload_groups wg
  JOIN    sys.dm_pdw_nodes_resource_governor_resource_pools rp    ON  wg.pdw_node_id  = rp.pdw_node_id
                                                                  AND wg.pool_id      = rp.pool_id
  WHERE   rp.name = 'SloDWPool'
  GROUP BY
          rp.name
  ,       rp.max_memory_kb
  ,       wg.name
  ,       wg.importance
  ,       wg.request_max_memory_grant_percent
)
-- Creating resource class name mapping.
,names
AS
(
  SELECT 'smallrc' as resource_class, 1 as rc_id
  UNION ALL
    SELECT 'mediumrc', 2
  UNION ALL
    SELECT 'largerc', 3
  UNION ALL
    SELECT 'xlargerc', 4
  UNION ALL
    SELECT 'staticrc10', 5
  UNION ALL
    SELECT 'staticrc20', 6
  UNION ALL
    SELECT 'staticrc30', 7
  UNION ALL
    SELECT 'staticrc40', 8
  UNION ALL
    SELECT 'staticrc50', 9
  UNION ALL
    SELECT 'staticrc60', 10
  UNION ALL
    SELECT 'staticrc70', 11
  UNION ALL
    SELECT 'staticrc80', 12
)
,base AS
(   SELECT  schema_name
    ,       table_name
    ,       SUM(column_count)                   AS column_count
    ,       ISNULL(SUM(short_string_column_count),0)   AS short_string_column_count
    ,       ISNULL(SUM(long_string_column_count),0)    AS long_string_column_count
    FROM    (   SELECT  sm.name                                             AS schema_name
                ,       tb.name                                             AS table_name
                ,       COUNT(co.column_id)                                 AS column_count
                           ,       CASE    WHEN co.system_type_id IN (36,43,106,108,165,167,173,175,231,239)
                                AND  co.max_length <= 32
                                THEN COUNT(co.column_id)
                        END                                                 AS short_string_column_count
                ,       CASE    WHEN co.system_type_id IN (165,167,173,175,231,239)
                                AND  co.max_length > 32 and co.max_length <=8000
                                THEN COUNT(co.column_id)
                        END                                                 AS long_string_column_count
                FROM    sys.schemas AS sm
                JOIN    sys.tables  AS tb   on sm.[schema_id] = tb.[schema_id]
                JOIN    sys.columns AS co   ON tb.[object_id] = co.[object_id]
                           WHERE tb.name = @TABLE_NAME AND sm.name = @SCHEMA_NAME
                GROUP BY sm.name
                ,        tb.name
                ,        co.system_type_id
                ,        co.max_length            ) a
GROUP BY schema_name
,        table_name
)
, size AS
(
SELECT  schema_name
,       table_name
,       75497472                                            AS table_overhead

,       column_count*1048576*8                              AS column_size
,       short_string_column_count*1048576*32                       AS short_string_size,       (long_string_column_count*16777216) AS long_string_size
FROM    base
UNION
SELECT CASE WHEN COUNT(*) = 0 THEN 'EMPTY' END as schema_name
         ,CASE WHEN COUNT(*) = 0 THEN 'EMPTY' END as table_name
         ,CASE WHEN COUNT(*) = 0 THEN 0 END as table_overhead
         ,CASE WHEN COUNT(*) = 0 THEN 0 END as column_size
         ,CASE WHEN COUNT(*) = 0 THEN 0 END as short_string_size

,CASE WHEN COUNT(*) = 0 THEN 0 END as long_string_size
FROM   base
)
, load_multiplier as
(
SELECT  CASE
          WHEN FLOOR(8 * (CAST (CAST(REPLACE(REPLACE(@DWU,'DW',''),'c','') AS INT) AS FLOAT)/6000)) > 0
            AND CHARINDEX(@DWU,'c')=0
          THEN FLOOR(8 * (CAST (CAST(REPLACE(REPLACE(@DWU,'DW',''),'c','') AS INT) AS FLOAT)/6000))
          ELSE 1
        END AS multiplication_factor
)
       SELECT  r1.DWU
       , schema_name
       , table_name
       , rc.resource_class as closest_rc_in_increasing_order
       , max_queries_at_this_rc = CASE
             WHEN (r1.max_slots / r1.slots_used > r1.max_queries)
                  THEN r1.max_queries
             ELSE r1.max_slots / r1.slots_used
                  END
       , r1.max_slots as max_concurrency_slots
       , r1.slots_used as required_slots_for_the_rc
       , r1.tgt_mem_grant_MB  as rc_mem_grant_MB
       , CAST((table_overhead*1.0+column_size+short_string_size+long_string_size)*multiplication_factor/1048576    AS DECIMAL(18,2)) AS est_mem_grant_required_for_cci_operation_MB
       FROM    size
       , load_multiplier
       , #ref r1, names  rc
       WHERE r1.rc_id=rc.rc_id
                     AND CAST((table_overhead*1.0+column_size+short_string_size+long_string_size)*multiplication_factor/1048576    AS DECIMAL(18,2)) < r1.tgt_mem_grant_MB
       ORDER BY ABS(CAST((table_overhead*1.0+column_size+short_string_size+long_string_size)*multiplication_factor/1048576    AS DECIMAL(18,2)) - r1.tgt_mem_grant_MB)
GO

Související obsah

Další informace o správě uživatelů databáze a zabezpečení najdete v tématu Zabezpečení databáze ve službě Synapse SQL. Další informace o tom, jak větší třídy prostředků mohou zlepšit kvalitu clusterovaného indexu columnstore, naleznete v tématu Optimalizace paměti pro kompresi columnstore.