Workloadbeheer met resourceklassen in Azure Synapse Analytics

  • Artikel
  • 16 minuten om te lezen

Richtlijnen voor het gebruik van resourceklassen voor het beheren van geheugen en gelijktijdigheid voor Synapse SQL-poolquery's in Azure Synapse.

Wat zijn resourceklassen?

De prestatiecapaciteit van een query wordt bepaald door de resourceklasse van de gebruiker. Resourceklassen zijn vooraf bepaalde resourcelimieten in Synapse SQL-pool die rekenresources en gelijktijdigheid voor het uitvoeren van query's bepalen. Resourceklassen kunnen u helpen bij het configureren van resources voor uw query's door limieten in te stellen voor het aantal query's dat gelijktijdig wordt uitgevoerd en op de rekenresources die aan elke query zijn toegewezen. Er is een afweging tussen geheugen en gelijktijdigheid.

  • Kleinere resourceklassen verminderen de maximale hoeveelheid geheugen per query, maar vergroten de gelijktijdigheid.
  • Grotere resourceklassen verhogen de maximale hoeveelheid geheugen per query, maar verminderen de gelijktijdigheid.

Er zijn twee typen resourceklassen:

  • Statische-resourcesklassen, die zeer geschikt zijn voor een grotere gelijktijdigheid op een vaste gegevenssetgrootte.
  • Dynamische resourceklassen, die zeer geschikt zijn voor gegevenssets die groter worden en betere prestaties nodig hebben naarmate het serviceniveau omhoog wordt geschaald.

Resourceklassen gebruiken gelijktijdigheidssleuven om het resourceverbruik te meten. Gelijktijdigheidssleuven worden verder in dit artikel uitgelegd.

  • Zie Geheugen- en gelijktijdigheidslimieten om het resourcegebruik voor de resourceklassen weer te geven.
  • Als u de resourceklasse wilt aanpassen, kunt u de query uitvoeren onder een andere gebruiker of het lidmaatschap van de resourceklasse van de huidige gebruiker wijzigen.

Statische resourceklassen

Statische resourceklassen wijzen dezelfde hoeveelheid geheugen toe, ongeacht het huidige prestatieniveau, dat wordt gemeten in datawarehouse-eenheden. Omdat query's dezelfde geheugentoewijzing krijgen, ongeacht het prestatieniveau, kunnen met het uitschalen van het datawarehouse meer query's worden uitgevoerd binnen een resourceklasse. Statische resourceklassen zijn ideaal als het gegevensvolume bekend en constant is.

De statische resourceklassen worden geïmplementeerd met deze vooraf gedefinieerde databaserollen:

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

Dynamische resourceklassen

Dynamische resourceklassen wijzen een variabele hoeveelheid geheugen toe, afhankelijk van het huidige serviceniveau. Hoewel statische resourceklassen nuttig zijn voor hogere gelijktijdigheid en statische gegevensvolumes, zijn dynamische resourceklassen beter geschikt voor een groeiende of variabele hoeveelheid gegevens. Wanneer u omhoog schaalt naar een groter serviceniveau, krijgen uw query's automatisch meer geheugen.

De dynamische resourceklassen worden geïmplementeerd met deze vooraf gedefinieerde databaserollen:

  • smallrc
  • mediumrc
  • largerc
  • xlargerc

De geheugentoewijzing voor elke resourceklasse is als volgt.

Serviceniveau smallrc mediumrc largerc 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 naar
DW30000c		 3% 10% 22% 70%

Standaardresourceklasse

Standaard is elke gebruiker lid van de dynamische resourceklasse smallrc.

De resourceklasse van de servicebeheerder staat vast in smallrc en kan niet worden gewijzigd. De servicebeheerder is de gebruiker die tijdens het inrichtingsproces is gemaakt. De servicebeheerder in deze context is de aanmelding die is opgegeven voor de aanmeldgegevens van de serverbeheerder bij het maken van een nieuwe Synapse SQL-pool met een nieuwe server.

Notitie

Gebruikers of groepen die zijn gedefinieerd als Active Directory-beheerder zijn ook servicebeheerders.

Bewerkingen van resourceklasse

Resourceklassen zijn ontworpen om de prestaties voor gegevensbeheer en manipulatieactiviteiten te verbeteren. Complexe query's kunnen ook profiteren van het uitvoeren onder een grote resourceklasse. Queryprestaties voor grote joins en sorteert kunnen bijvoorbeeld worden verbeterd wanneer de resourceklasse groot genoeg is om de query in het geheugen uit te voeren.

Bewerkingen die worden beheerd door resourceklassen

Deze bewerkingen worden beheerd door resourceklassen:

  • INSERT-SELECT, UPDATE, DELETE
  • SELECT (bij het uitvoeren van query's op gebruikerstabellen)
  • ALTER INDEX - HERBOUWEN OF OPNIEUW INDELEN
  • ALTER TABLE REBUILD
  • CREATE INDEX
  • GECLUSTERDE COLUMNSTORE-INDEX MAKEN
  • CREATE TABLE AS SELECT (CTAS)
  • Gegevens laden
  • Bewerkingen voor gegevensver verplaatsen die worden uitgevoerd door de Data Movement Service (DMS)

Notitie

SELECT-instructies voor dynamische beheerweergaven (DMV's) of andere systeemweergaven zijn niet onderworpen aan een van de gelijktijdigheidslimieten. U kunt het systeem bewaken, ongeacht het aantal query's dat op het systeem wordt uitgevoerd.

Bewerkingen die niet worden beheerd door resourceklassen

Sommige query's worden altijd uitgevoerd in de resourceklasse smallrc, zelfs als de gebruiker lid is van een grotere resourceklasse. Deze uitgesloten query's tellen niet mee voor de gelijktijdigheidslimiet. Als de gelijktijdigheidslimiet bijvoorbeeld 16 is, kunnen veel gebruikers kiezen uit systeemweergaven zonder dat dit van invloed is op de beschikbare gelijktijdigheidssleuven.

De volgende instructies zijn uitgesloten van resourceklassen en worden altijd uitgevoerd in smallrc:

  • CREATE or DROP TABLE
  • ALTER TABLE ... SWITCH, SPLIT, or MERGE PARTITION
  • ALTER INDEX DISABLE
  • DROP INDEX
  • STATISTIEKEN MAKEN, BIJWERKEN OF NEERZETTEN
  • TRUNCATE TABLE
  • AUTORISATIE WIJZIGEN
  • CREATE LOGIN (AANMELDING MAKEN)
  • GEBRUIKER MAKEN, WIJZIGEN OF NEERZETTEN
  • PROCEDURE MAKEN, WIJZIGEN OF NEERZETTEN
  • WEERGAVE MAKEN OF NEERZETTEN
  • WAARDEN INVOEGEN
  • SELECT in systeemweergaven en DMV's
  • EXPLAIN
  • DBCC

Gelijktijdigheidssleuven

Gelijktijdigheidssleuven zijn een handige manier om de resources bij te houden die beschikbaar zijn voor het uitvoeren van query's. Het zijn net tickets die u koopt om plaatsen tijdens een concert te reserveren, omdat het aantal passagiers beperkt is. Het totale aantal gelijktijdigheidssleuven per datawarehouse wordt bepaald door het serviceniveau. Voordat een query kan worden uitgevoerd, moet deze voldoende gelijktijdigheidssleuven kunnen reserveren. Wanneer een query is voltooid, worden de gelijktijdigheidssleuven ervan uitgebracht.

  • Een query die wordt uitgevoerd met 10 gelijktijdigheidssleuven heeft toegang tot vijf keer meer rekenresources dan een query die wordt uitgevoerd met 2 gelijktijdigheidssleuven.
  • Als voor elke query 10 gelijktijdigheidssleuven zijn vereist en er 40 gelijktijdigheidssleuven zijn, kunnen slechts 4 query's gelijktijdig worden uitgevoerd.

Alleen resourcebeheerde query's gebruiken gelijktijdigheidssleuven. Systeemquery's en enkele triviale query's verbruiken geen sleuven. Het exacte aantal verbruikte gelijktijdigheidssleuven wordt bepaald door de resourceklasse van de query.

De resourceklassen weergeven

Resourceklassen worden geïmplementeerd als vooraf gedefinieerde databaserollen. Er zijn twee typen resourceklassen: dynamisch en statisch. Gebruik de volgende query om een lijst met resourceklassen weer te geven:

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

De resourceklasse van een gebruiker wijzigen

Resourceklassen worden geïmplementeerd door gebruikers toe te wijzen aan databaserollen. Wanneer een gebruiker een query uitvoert, wordt de query uitgevoerd met de resourceklasse van de gebruiker. Als een gebruiker bijvoorbeeld lid is van de databaserol staticrc10, worden de query's uitgevoerd met kleine hoeveelheden geheugen. Als een databasegebruiker lid is van de xlargerc- of staticrc80-databaserollen, worden de query's uitgevoerd met grote hoeveelheden geheugen.

Als u de resourceklasse van een gebruiker wilt vergroten, gebruikt sp_addrolemember om de gebruiker toe te voegen aan een databaserol van een grote resourceklasse. Met de onderstaande code wordt een gebruiker toegevoegd aan de largerc-databaserol. Elke aanvraag krijgt 22% van het systeemgeheugen.

EXEC sp_addrolemember 'largerc', 'loaduser';

Als u de resourceklasse wilt verlagen, gebruikt u sp_droprolemember. Als 'loaduser' geen lid is of geen andere resourceklassen, gaan ze naar de standaardresourceklasse smallrc met een geheugenverschaf van 3%.

EXEC sp_droprolemember 'largerc', 'loaduser';

Prioriteit van resourceklasse

Gebruikers kunnen lid zijn van meerdere resourceklassen. Wanneer een gebruiker tot meer dan één resourceklasse behoort:

  • Dynamische resourceklassen hebben voorrang op statische resourceklassen. Als een gebruiker bijvoorbeeld lid is van mediumrc (dynamic) en staticrc80 (statisch), worden query's uitgevoerd met mediumrc.
  • Grotere resourceklassen hebben voorrang op kleinere resourceklassen. Als een gebruiker bijvoorbeeld lid is van mediumrc en largerc, worden query's uitgevoerd met largerc. En als een gebruiker lid is van zowel staticrc20 als statirc80, worden query's uitgevoerd met staticrc80-resourcetoewijzingen.

Aanbevelingen

Notitie

Overweeg gebruik te maken van de mogelijkheden voor workloadbeheer (isolatie van workloads,classificatie en belang)voor meer controle over uw workload en voorspelbare prestaties.

U wordt aangeraden een gebruiker te maken die is toegewezen aan het uitvoeren van een specifiek type query of laadbewerking. Geef die gebruiker een permanente resourceklasse in plaats van de resourceklasse regelmatig te wijzigen. Statische resourceklassen bieden meer algemene controle over de workload. Daarom raden we u aan om statische resourceklassen te gebruiken voordat u dynamische resourceklassen overweegt.

Resourceklassen voor gebruikers laden

CREATE TABLE gebruikt standaard geclusterde columnstore-indexen. Het comprimeren van gegevens in een columnstore-index is een geheugenintensieve bewerking en geheugendruk kan de kwaliteit van de index verminderen. Geheugendruk kan ertoe leiden dat er een hogere resourceklasse nodig is bij het laden van gegevens. Om ervoor te zorgen dat de belasting voldoende geheugen heeft, kunt u een gebruiker maken die is aangewezen voor het uitvoeren van loads en die gebruiker toewijzen aan een hogere resourceklasse.

Het geheugen dat nodig is om de belasting efficiënt te verwerken, is afhankelijk van de aard van de geladen tabel en de gegevensgrootte. Zie Maximizing rowgroup quality (Kwaliteit van rijgroepen maximaliseren) voor meer informatie over geheugenvereisten.

Nadat u de geheugenvereiste hebt bepaald, kiest u of u de gebruiker van de belasting wilt toewijzen aan een statische of dynamische resourceklasse.

  • Gebruik een statische resourceklasse wanneer de geheugenvereisten voor de tabel binnen een bepaald bereik vallen. De belasting wordt uitgevoerd met het juiste geheugen. Wanneer u het datawarehouse schaalt, heeft de belasting niet meer geheugen nodig. Door een statische resourceklasse te gebruiken, blijven de geheugentoewijzingen constant. Deze consistentie bespaart geheugen en maakt het mogelijk om meer query's gelijktijdig uit te voeren. We raden u aan om voor nieuwe oplossingen eerst de statische resourceklassen te gebruiken, omdat deze meer controle bieden.
  • Gebruik een dynamische resourceklasse wanneer de geheugenvereisten voor de tabel sterk variëren. Voor belasting is mogelijk meer geheugen vereist dan het huidige DWU- of cDWU-niveau biedt. Door het datawarehouse te schalen, wordt er meer geheugen toegevoegd aan laadbewerkingen, waardoor de belasting sneller kan worden uitgevoerd.

Resourceklassen voor query's

Sommige query's zijn rekenintensief en andere niet.

  • Kies een dynamische resourceklasse wanneer query's complex zijn, maar geen hoge gelijktijdigheid nodig hebben. Het genereren van dagelijkse of wekelijkse rapporten is bijvoorbeeld af en toe nodig voor resources. Als de rapporten grote hoeveelheden gegevens verwerken, biedt het schalen van het datawarehouse meer geheugen aan de bestaande resourceklasse van de gebruiker.
  • Kies een statische resourceklasse wanneer de resourceverwachtingen gedurende de dag variëren. Een statische resourceklasse werkt bijvoorbeeld goed wanneer het datawarehouse door veel mensen wordt opgevraagd. Bij het schalen van het datawarehouse verandert de hoeveelheid geheugen die aan de gebruiker wordt toegewezen niet. Daarom kunnen er meer query's parallel op het systeem worden uitgevoerd.

De juiste geheugenverkenningen zijn afhankelijk van veel factoren, zoals de hoeveelheid gegevens die wordt opgevraagd, de aard van de tabelschema's en verschillende joins, select- en group-predicaten. Over het algemeen kunnen query's sneller worden voltooid door meer geheugen toe tewijsen, maar wordt de algehele gelijktijdigheid beperkt. Als gelijktijdigheid geen probleem is, heeft te veel toewijzing van geheugen geen gevolgen voor de doorvoer.

Gebruik verschillende resourceklassen om de prestaties af te stemmen. De volgende sectie bevat een opgeslagen procedure waarmee u de beste resourceklasse kunt vinden.

Voorbeeldcode voor het vinden van de beste resourceklasse

U kunt de volgende opgeslagen procedure gebruiken om te achterhalen van gelijktijdigheid en geheugenverwijdering per resourceklasse op een bepaalde SLO en de beste resourceklasse voor geheugenintensieve CCI-bewerkingen op niet-gepart partitioneerde CCI-tabel op een bepaalde resourceklasse:

Dit is het doel van deze opgeslagen procedure:

  1. Om de gelijktijdigheid en het verlenen van geheugen per resourceklasse op een bepaalde SLO te bekijken. De gebruiker moet NULL voor zowel schema- als tabelnaam verstrekken, zoals in dit voorbeeld wordt weergegeven.
  2. Om de beste resourceklasse te zien voor de geheugenintensieve CCI-bewerkingen (laden, tabel kopiëren, index herbouwen, enzovoort) op een niet-gepart partitioneerde CCI-tabel in een bepaalde resourceklasse. De opgeslagen proc maakt gebruik van een tabelschema om de vereiste geheugen-toekenning te vinden.

Afhankelijkheden & beperkingen

  • Deze opgeslagen procedure is niet ontworpen om de geheugenvereiste voor een gepartitiesteerde cci-tabel te berekenen.
  • Bij deze opgeslagen procedure wordt geen rekening gehouden met de geheugenvereisten voor het SELECT-gedeelte van CTAS/INSERT-SELECT en wordt ervan uitgenomen dat het een SELECT-bestand is.
  • Deze opgeslagen procedure maakt gebruik van een tijdelijke tabel, die beschikbaar is in de sessie waarin deze opgeslagen procedure is gemaakt.
  • Deze opgeslagen procedure is afhankelijk van de huidige aanbiedingen (bijvoorbeeld hardwareconfiguratie, DMS-configuratie), en als een van deze wijzigingen heeft, werkt deze opgeslagen proc niet goed.
  • Deze opgeslagen procedure is afhankelijk van bestaande gelijktijdigheidslimietaanbiedingen. Als deze wijzigen, werkt deze opgeslagen procedure niet goed.
  • Deze opgeslagen procedure is afhankelijk van bestaande resourceklasseaanbiedingen en als deze wijzigen, werkt deze opgeslagen procedure niet goed.

Notitie

Als u geen uitvoer krijgt na het uitvoeren van de opgeslagen procedure met opgegeven parameters, kunnen er twee gevallen zijn.

  1. Dw Parameter bevat een ongeldige SLO-waarde
  2. Of er is geen overeenkomende resourceklasse voor de CCI-bewerking in de tabel.

Bij DW100c is de hoogste beschikbare geheugenverlening bijvoorbeeld 1 GB en als het tabelschema breed genoeg is om de vereiste van 1 GB te doorkruisen.

Gebruiksvoorbeeld

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

  1. @DWU: Geef een NULL-parameter op om de huidige DWU uit de DW DB te extraheren of een ondersteunde DWU in de vorm van DW100c op te geven
  2. @SCHEMA_NAME: Geef een schemanaam van de tabel op
  3. @TABLE_NAME: Geef een tabelnaam op die van belang is

Voorbeelden van het uitvoeren van deze opgeslagen 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;

Met de volgende instructie wordt Table1 gemaakt dat in de voorgaande voorbeelden wordt gebruikt. CREATE TABLE Table1 (a int, b varchar(50), c decimal (18,10), d char(10), e varbinary(15), f float, g datetime, h date);

Definitie van opgeslagen procedure

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

Volgende stappen

Zie Een database beveiligen in SynapseSQL voor meer informatie over het beheren van databasegebruikers SQL. Zie Geheugenoptimalisaties voor columnstore-compressie voor meer informatie over hoe grotere resourceklassen de kwaliteit van de geclusterde columnstore-index kunnen verbeteren.