Arbetsbelastningshantering med resursklasser i Azure Synapse Analytics

  • Artikel

Vägledning för hur du använder resursklasser för att hantera minne och samtidighet för Synapse SQL-poolfrågor i Azure Synapse.

Vad är resursklasser?

Prestandakapaciteten för en fråga bestäms av användarens resursklass. Resursklasser är förutbestämda resursgränser i Synapse SQL-pool som styr beräkningsresurser och samtidighet för frågekörning. Resursklasser kan hjälpa dig att konfigurera resurser för dina frågor genom att ange gränser för antalet frågor som körs samtidigt och på de beräkningsresurser som tilldelats varje fråga. Det finns en kompromiss mellan minne och samtidighet.

  • Mindre resursklasser minskar det maximala minnet per fråga, men ökar samtidigheten.
  • Större resursklasser ökar maximalt minne per fråga, men minskar samtidigheten.

Det finns två typer av resursklasser:

  • Statiska resursklasser, som passar bra för ökad samtidighet för en fast datauppsättningsstorlek.
  • Dynamiska resursklasser, som passar bra för datauppsättningar som växer i storlek och behöver ökad prestanda när tjänstnivån skalas upp.

Resursklasser använder samtidighetsfack för att mäta resursförbrukning. Samtidighetsfack beskrivs senare i den här artikeln.

Statiska resursklasser

Statiska resursklasser allokerar samma mängd minne oavsett den aktuella prestandanivån, som mäts i informationslagerenheter. Eftersom frågor får samma minnesallokering oavsett prestandanivå kan fler frågor köras i en resursklass genom att skala ut informationslagret . Statiska resursklasser är idealiska om datavolymen är känd och konstant.

De statiska resursklasserna implementeras med dessa fördefinierade databasroller:

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

Dynamiska resursklasser

Dynamiska resursklasser allokerar en variabel mängd minne beroende på den aktuella tjänstnivån. Statiska resursklasser är bra för högre samtidighet och statiska datavolymer, men dynamiska resursklasser passar bättre för en växande eller variabel mängd data. När du skalar upp till en större tjänstnivå får dina frågor automatiskt mer minne.

De dynamiska resursklasserna implementeras med dessa fördefinierade databasroller:

  • smallrc
  • mediumrc
  • större
  • xlargerc

Minnesallokeringen för varje resursklass är följande.

Servicenivå smallrc mediumrc större 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 till
DW30000c		 %3 10 % 22 % 70%

Standardresursklass

Som standard är varje användare medlem i den dynamiska resursklassen smallrc.

Tjänstadministratörens resursklass är fast i smallrc och kan inte ändras. Tjänstadministratören är den användare som skapades under etableringsprocessen. Tjänstadministratören i den här kontexten är inloggningen som anges för "Serveradministratörsinloggning" när du skapar en ny Synapse SQL-pool med en ny server.

Kommentar

Användare eller grupper som definieras som Active Directory-administratör är också tjänstadministratörer.

Resursklassåtgärder

Resursklasser är utformade för att förbättra prestanda för datahantering och manipuleringsaktiviteter. Komplexa frågor kan också dra nytta av att köras under en stor resursklass. Frågeprestanda för stora kopplingar och sorteringar kan till exempel förbättras när resursklassen är tillräckligt stor för att frågan ska kunna köras i minnet.

Åtgärder som styrs av resursklasser

Dessa åtgärder styrs av resursklasser:

  • INSERT-SELECT, UPDATE, DELETE
  • SELECT (när du kör frågor mot användartabeller)
  • ALTER INDEX – ÅTERSKAPA ELLER OMORGANISERA
  • ÄNDRA TABELLFÖRBYGGNING
  • CREATE INDEX
  • SKAPA GRUPPERAT COLUMNSTORE-INDEX
  • SKAPA TABELL SOM SELECT (CTAS)
  • Läsa in data
  • Dataförflyttningsåtgärder som utförs av Data Movement Service (DMS)

Kommentar

SELECT-instruktioner för dynamiska hanteringsvyer (DMV:er) eller andra systemvyer styrs inte av någon av samtidighetsgränserna. Du kan övervaka systemet oavsett hur många frågor som körs på det.

Åtgärder som inte styrs av resursklasser

Vissa frågor körs alltid i resursklassen smallrc även om användaren är medlem i en större resursklass. Dessa undantagsfrågor räknas inte mot samtidighetsgränsen. Om samtidighetsgränsen till exempel är 16 kan många användare välja från systemvyer utan att påverka tillgängliga samtidighetsfack.

Följande instruktioner är undantagna från resursklasser och körs alltid i smallrc:

  • SKAPA ELLER TA BORT TABELL
  • ALTER TABLE ... VÄXLA, DELA ELLER SAMMANFOGA PARTITION
  • INAKTIVERA ALTER INDEX
  • DROP INDEX
  • SKAPA, UPPDATERA eller TA BORT STATISTIK
  • TRUNCATE TABLE
  • ÄNDRA AUKTORISERING
  • SKAPA INLOGGNING
  • SKAPA, ÄNDRA eller SLÄPP ANVÄNDARE
  • SKAPA, ÄNDRA ELLER SLÄPP-PROCEDUR
  • SKAPA ELLER SLÄPP VY
  • INFOGA VÄRDEN
  • VÄLJ från systemvyer och DMV:er
  • EXPLAIN
  • DBCC

Samtidighetsfack

Samtidighetsfack är ett bekvämt sätt att spåra de resurser som är tillgängliga för frågekörning. De är som biljetter som du köper för att reservera platser på en konsert eftersom sittplatserna är begränsade. Det totala antalet samtidighetsfack per informationslager bestäms av tjänstnivån. Innan en fråga kan börja köras måste den kunna reservera tillräckligt med samtidighetsfack. När en fråga är klar släpper den sina samtidighetsfack.

  • En fråga som körs med 10 samtidighetsfack kan komma åt fem gånger fler beräkningsresurser än en fråga som körs med 2 samtidighetsfack.
  • Om varje fråga kräver 10 samtidighetsplatser och det finns 40 samtidighetsplatser kan endast 4 frågor köras samtidigt.

Endast resursstyrda frågor förbrukar samtidighetsfack. Systemfrågor och vissa triviala frågor förbrukar inga platser. Det exakta antalet samtidighetsfack som förbrukas bestäms av frågans resursklass.

Visa resursklasserna

Resursklasser implementeras som fördefinierade databasroller. Det finns två typer av resursklasser: dynamiska och statiska. Om du vill visa en lista över resursklasserna använder du följande fråga:

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

Ändra en användares resursklass

Resursklasser implementeras genom att tilldela användare till databasroller. När en användare kör en fråga körs frågan med användarens resursklass. Om en användare till exempel är medlem i databasrollen staticrc10 körs deras frågor med små mängder minne. Om en databasanvändare är medlem i databasrollerna xlargerc eller staticrc80 körs deras frågor med stora mängder minne.

Om du vill öka en användares resursklass använder du sp_addrolemember för att lägga till användaren i en databasroll för en stor resursklass. Koden nedan lägger till en användare i den större databasrollen. Varje begäran hämtar 22 % av systemminnet.

EXEC sp_addrolemember 'largerc', 'loaduser';

Om du vill minska resursklassen använder du sp_droprolemember. Om "loaduser" inte är medlem eller andra resursklasser går de till standardresursklassen smallrc med ett minnesbidrag på 3 %.

EXEC sp_droprolemember 'largerc', 'loaduser';

Prioritet för resursklass

Användare kan vara medlemmar i flera resursklasser. När en användare tillhör mer än en resursklass:

  • Dynamiska resursklasser har företräde framför statiska resursklasser. Om en användare till exempel är medlem i både mediumrc(dynamic) och staticrc80 (statisk) körs frågor med mediumrc.
  • Större resursklasser har företräde framför mindre resursklasser. Om en användare till exempel är medlem i mediumrc och largerc körs frågor med större. På samma sätt, om en användare är medlem i både staticrc20 och statirc80, körs frågor med staticrc80-resursallokeringar.

Rekommendationer

Kommentar

Överväg att använda arbetsbelastningshanteringsfunktioner (arbetsbelastningsisolering, klassificering och prioritet) för mer kontroll över din arbetsbelastning och förutsägbara prestanda.

Vi rekommenderar att du skapar en användare som är dedikerad till att köra en viss typ av fråga eller belastningsåtgärd. Ge användaren en permanent resursklass i stället för att ändra resursklassen regelbundet. Statiska resursklasser ger större övergripande kontroll över arbetsbelastningen, så vi föreslår att du använder statiska resursklasser innan du överväger dynamiska resursklasser.

Resursklasser för inläsningsanvändare

CREATE TABLE använder grupperade kolumnlagringsindex som standard. Att komprimera data till ett columnstore-index är en minnesintensiv åtgärd, och minnestryck kan minska indexkvaliteten. Minnestryck kan leda till att du behöver en högre resursklass vid inläsning av data. För att säkerställa att belastningarna har tillräckligt med minne kan du skapa en användare som är avsedd för körning av belastningar och tilldela användaren till en högre resursklass.

Det minne som behövs för att bearbeta belastningar effektivt beror på vilken typ av tabell som läses in och datastorleken. Mer information om minneskrav finns i Maximera radgruppskvalitet.

När du har fastställt minnesbehovet väljer du om du vill tilldela belastningsanvändaren till en statisk eller dynamisk resursklass.

  • Använd en statisk resursklass när kraven på tabellminne ligger inom ett visst intervall. Belastningar körs med lämpligt minne. När du skalar informationslagret behöver belastningarna inte mer minne. Med hjälp av en statisk resursklass förblir minnesallokeringarna konstanta. Den här konsekvensen sparar minne och gör att fler frågor kan köras samtidigt. Vi rekommenderar att nya lösningar använder de statiska resursklasserna först eftersom dessa ger större kontroll.
  • Använd en dynamisk resursklass när kraven på tabellminne varierar kraftigt. Belastningar kan kräva mer minne än den aktuella DWU- eller cDWU-nivån ger. Genom att skala informationslagret läggs mer minne till för att läsa in åtgärder, vilket gör att belastningar kan utföras snabbare.

Resursklasser för frågor

Vissa frågor är beräkningsintensiva och andra inte.

  • Välj en dynamisk resursklass när frågor är komplexa, men inte behöver hög samtidighet. Att till exempel generera dagliga eller veckovisa rapporter är ett tillfälligt behov av resurser. Om rapporterna bearbetar stora mängder data ger skalning av informationslagret mer minne till användarens befintliga resursklass.
  • Välj en statisk resursklass när resursförväntningarna varierar under dagen. Till exempel fungerar en statisk resursklass bra när informationslagret efterfrågas av många personer. När du skalar informationslagret ändras inte mängden minne som allokeras till användaren. Därför kan fler frågor köras parallellt i systemet.

Rätt minnesbeslag beror på många faktorer, till exempel mängden data som efterfrågas, typen av tabellscheman och olika kopplingar, val och grupppredikat. I allmänhet gör allokering av mer minne att frågor kan slutföras snabbare, men minskar den övergripande samtidigheten. Om samtidighet inte är ett problem skadar inte överallokering av minne dataflödet.

Om du vill justera prestanda använder du olika resursklasser. Nästa avsnitt innehåller en lagrad procedur som hjälper dig att ta reda på den bästa resursklassen.

Exempelkod för att hitta den bästa resursklassen

Du kan använda följande angivna lagrade procedur för att ta reda på samtidighet och minnesbeviljande per resursklass vid en viss SLO och den bästa resursklassen för minnesintensiva CCI-åtgärder i en icke-partitionerad CCI-tabell i en viss resursklass:

Här är syftet med den här lagrade proceduren:

  1. Om du vill se samtidighets- och minnestillviljan per resursklass vid en viss SLO. Användaren måste ange NULL för både schema och tabellnamn enligt det här exemplet.
  2. Om du vill se den bästa resursklassen för minnesintensiva CCI-åtgärder (läsa in, kopiera tabell, återskapa index osv.) på en icke-partitionerad CCI-tabell i en viss resursklass. Den lagrade processen använder tabellschema för att ta reda på det nödvändiga minnesbidraget.

Beroenden och begränsningar

  • Den här lagrade proceduren är inte utformad för att beräkna minnesbehovet för en partitionerad cci-tabell.
  • Den här lagrade proceduren tar inte hänsyn till minneskraven för SELECT-delen av CTAS/INSERT-SELECT och förutsätter att det är en SELECT.
  • Den här lagrade proceduren använder en temporär tabell som är tillgänglig i sessionen där den här lagrade proceduren skapades.
  • Den här lagrade proceduren beror på de aktuella erbjudandena (till exempel maskinvarukonfiguration, DMS-konfiguration) och om någon av dessa ändringar kommer den lagrade processen inte att fungera korrekt.
  • Den här lagrade proceduren beror på befintliga erbjudanden om samtidighetsgräns och om dessa ändras fungerar inte den här lagrade proceduren korrekt.
  • Den här lagrade proceduren beror på befintliga resursklasserbjudanden och om dessa ändras fungerar inte den här lagrade proceduren korrekt.

Kommentar

Om du inte får utdata efter att ha kört den lagrade proceduren med angivna parametrar kan det finnas två fall.

  1. Endera DW-parametern innehåller ett ogiltigt SLO-värde
  2. Eller så finns det ingen matchande resursklass för CCI-åtgärden i tabellen.

Vid DW100c är till exempel det högsta tillgängliga minnestillslaget 1 GB och om tabellschemat är tillräckligt brett för att överskrida kravet på 1 GB.

Användningsexempel

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

  1. @DWU: Ange antingen en NULL-parameter för att extrahera den aktuella DWU:en från DW DB eller ange valfri DWU som stöds i form av "DW100c"
  2. @SCHEMA_NAME: Ange ett schemanamn för tabellen
  3. @TABLE_NAME: Ange ett tabellnamn för räntan

Exempel som kör den här lagrade processen:

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;

Följande instruktion skapar Table1 som används i föregående exempel. CREATE TABLE Table1 (a int, b varchar(50), c decimal (18,10), d char(10), e varbinary(15), f float, g datetime, h date);

Definition av lagrad procedur

-------------------------------------------------------------------------------
-- 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

Nästa steg

Mer information om hur du hanterar databasanvändare och säkerhet finns i Skydda en databas i Synapse SQL. Mer information om hur större resursklasser kan förbättra indexkvaliteten för grupperade kolumnlager finns i Minnesoptimeringar för kolumnlagringskomprimering.