Share via

Workloadbeheer met resourceklassen in Azure Synapse Analytics

  • Artikel

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 vastgestelde resourcelimieten in Synapse SQL-pool die rekenresources en gelijktijdigheid voor het uitvoeren van query's bepalen. Met resourceklassen kunt u resources voor uw query's configureren 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 het maximale geheugen per query, maar verhogen gelijktijdigheid.
  • Grotere resourceklassen verhogen het maximale geheugen per query, maar verminderen gelijktijdigheid.

Er zijn twee typen resourceklassen:

  • Statische resourceklassen, die geschikt zijn voor een grotere gelijktijdigheid van een gegevensset die is opgelost.
  • Dynamische resourceklassen, die goed geschikt zijn voor gegevenssets die steeds groter worden en betere prestaties nodig hebben wanneer het serviceniveau omhoog wordt geschaald.

Resourceklassen gebruiken gelijktijdigheidssites om het resourceverbruik te meten. Gelijktijdigheidssites worden verderop in dit artikel uitgelegd.

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 meer query's in een resourceklasse worden uitgevoerd door het datawarehouse uit te schalen. 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.

Met uitzondering van smallrc worden de dynamische resourceklassen geïmplementeerd met deze vooraf gedefinieerde databaserollen:

  • mediumrc
  • groterc
  • xlargerc

Smallrc wordt niet weergegeven als databaserol, maar is de standaardresourceklasse.

De geheugentoewijzing voor elke resourceklasse is als volgt.

Serviceniveau smallrc mediumrc groterc 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 is opgelost op smallrc en kan niet worden gewijzigd. De servicebeheerder is de gebruiker die is gemaakt tijdens het inrichtingsproces. De servicebeheerder in deze context is de aanmelding die is opgegeven voor de aanmelding 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.

Resourceklassebewerkingen

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 sorteringen 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, , UPDATEDELETE
  • SELECT (bij het uitvoeren van query's op gebruikerstabellen)
  • ALTER INDEX - REBUILD of REORGANIZE
  • ALTER TABLE REBUILD
  • CREATE INDEX
  • CREATE CLUSTERED COLUMNSTORE INDEX
  • CREATE TABLE AS SELECT (CTAS)
  • Gegevens laden
  • Bewerkingen voor gegevensverplaatsing uitgevoerd door de Data Movement Service (DMS)

Notitie

SELECT-instructies over dynamische beheerweergaven (DMV's) of andere systeemweergaven vallen niet onder een van de gelijktijdigheidslimieten. U kunt het systeem bewaken, ongeacht het aantal query's dat erop wordt uitgevoerd.

Bewerkingen die niet onder resourceklassen vallen

Sommige query's worden altijd uitgevoerd in de smallrc-resourceklasse, ook al is de gebruiker lid van een grotere resourceklasse. Deze vrijgestelde query's tellen niet mee voor de gelijktijdigheidslimiet. Als de limiet voor gelijktijdigheid bijvoorbeeld 16 is, kunnen veel gebruikers kiezen uit systeemweergaven zonder dat dit van invloed is op de beschikbare gelijktijdigheidssites.

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

  • CREATE of DROP TABLE
  • ALTER TABLE ... SWITCH, SPLIT of MERGE PARTITION
  • ALTER INDEX DISABLE
  • DROP INDEX
  • CREATE, UPDATE of DROP STATISTICS
  • TRUNCATE TABLE
  • ALTER AUTHORIZATION
  • CREATE LOGIN
  • CREATE, ALTER of DROP USER
  • CREATE, ALTER of DROP PROCEDURE
  • CREATE of DROP VIEW
  • INSERT VALUES
  • SELECT van systeemweergaven en DMV's
  • EXPLAIN
  • DBCC

Gelijktijdigheidssites

Gelijktijdigheidssites zijn een handige manier om de resources bij te houden die beschikbaar zijn voor het uitvoeren van query's. Ze zijn vergelijkbaar met tickets die u koopt om zitplaatsen te reserveren bij een concert, omdat zitplaatsen beperkt zijn. Het totale aantal gelijktijdigheidssites per datawarehouse wordt bepaald door het serviceniveau. Voordat een query kan worden uitgevoerd, moet deze voldoende gelijktijdigheidssites kunnen reserveren. Wanneer een query is voltooid, worden de gelijktijdigheidssites vrijgegeven.

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

Alleen door resources beheerde query's verbruiken gelijktijdigheidssleuven. Systeemquery's en sommige triviale query's verbruiken geen sites. Het exacte aantal verbruikte gelijktijdigheidssites 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. Als u een lijst met de resourceklassen wilt weergeven, gebruikt u de volgende query:

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 staticrc10-databaserol, 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 verhogen, gebruikt u sp_addrolemember om de gebruiker toe te voegen aan een databaserol van een grote resourceklasse. Met de volgende code wordt een gebruiker toegevoegd aan de grotere 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 of andere resourceklassen is, gaan ze naar de standaard smallrc-resourceklasse met een geheugentoekenning van 3%.

EXEC sp_droprolemember 'largerc', 'loaduser';

Prioriteit van resourceklasse

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

  • Dynamische resourceklassen hebben voorrang op statische resourceklassen. Als een gebruiker bijvoorbeeld lid is van zowel mediumrc (dynamisch) als 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. Als een gebruiker lid is van zowel staticrc20 als statirc80, worden query's uitgevoerd met staticrc80-resourcetoewijzingen.

Aanbevelingen

Notitie

Overweeg om gebruik te maken van workloadbeheermogelijkheden (workloadisolatie, classificatie en urgentie) 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 belastingsbewerking. Geef die gebruiker een permanente resourceklasse in plaats van de resourceklasse regelmatig te wijzigen. Statische resourceklassen bieden meer controle over de workload, dus we raden u aan statische resourceklassen te gebruiken voordat u dynamische resourceklassen overweegt.

Resourceklassen voor het laden van gebruikers

CREATE TABLE gebruikt standaard geclusterde columnstore-indexen. Het comprimeren van gegevens in een columnstore-index is een geheugenintensieve bewerking en geheugendruk kan de indexkwaliteit verminderen. Geheugenbelasting kan leiden tot een hogere resourceklasse 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 belastingen 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 tabel die is geladen en de gegevensgrootte. Zie De kwaliteit van de rijgroep maximaliseren voor meer informatie over geheugenvereisten.

Zodra u de geheugenvereiste hebt vastgesteld, kiest u of u de belastinggebruiker wilt toewijzen aan een statische of dynamische resourceklasse.

  • Gebruik een statische resourceklasse wanneer de tabelgeheugenvereisten binnen een specifiek bereik vallen. Laden worden uitgevoerd met het juiste geheugen. Wanneer u het datawarehouse schaalt, hebben de belastingen geen geheugen meer nodig. Door een statische resourceklasse te gebruiken, blijven de geheugentoewijzingen constant. Met deze consistentie bespaart u geheugen en kunnen meer query's gelijktijdig worden uitgevoerd. We raden u aan om eerst de statische resourceklassen te gebruiken, omdat deze meer controle bieden.
  • Gebruik een dynamische resourceklasse wanneer de tabelgeheugenvereisten sterk variëren. Belasting vereist mogelijk meer geheugen dan het huidige DWU- of cDWU-niveau biedt. Als u het datawarehouse schaalt, wordt er meer geheugen toegevoegd aan belastingbewerkingen, waardoor de belasting sneller kan worden uitgevoerd.

Resourceklassen voor query's

Sommige query's zijn rekenintensief en sommige 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 een incidentele behoefte aan 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 verwachtingen van resources 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 wordt de hoeveelheid geheugen die aan de gebruiker is toegewezen, niet gewijzigd. Daarom kunnen er meer query's parallel op het systeem worden uitgevoerd.

De juiste geheugentoekenningen zijn afhankelijk van veel factoren, zoals de hoeveelheid gegevens die wordt opgevraagd, de aard van de tabelschema's en verschillende joins, selectie- en groepspredicaten. Over het algemeen kan het toewijzen van meer geheugen query's sneller voltooien, maar vermindert de algehele gelijktijdigheid. Als gelijktijdigheid geen probleem is, heeft overtoewijzing van geheugen geen nadelige gevolgen voor doorvoer.

Als u de prestaties wilt afstemmen, gebruikt u verschillende resourceklassen. De volgende sectie bevat een opgeslagen procedure waarmee u de beste resourceklasse kunt bepalen.

Voorbeeldcode voor het vinden van de beste resourceklasse

Gebruik de prc_workload_management_by_DWU opgeslagen procedure om:

  • Zie de gelijktijdigheid en geheugentoekenning per resourceklasse op een bepaalde SLO.
    • Geef NULL het schema en de tabelnaam op.
  • Zie de beste resourceklasse voor de geheugenintensieve CCI-bewerkingen (laden, tabel kopiëren, index herbouwen, enzovoort) voor niet-gepartitioneerde CCI-tabellen in een bepaalde resourceklasse.
    • De opgeslagen proc maakt gebruik van een tabelschema om de vereiste geheugentoelening te achterhalen.

Zie het gebruiksvoorbeeld voor voorbeelden.

Afhankelijkheden en beperkingen

  • Deze opgeslagen procedure is niet ontworpen om de geheugenvereiste voor een gepartitioneerde cci-tabel te berekenen.
  • Deze opgeslagen procedure houdt geen rekening met geheugenvereisten voor het SELECT-deel van CTAS/INSERT-SELECT en gaat ervan uit dat het een SELECT 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 niet correct werkt, werkt deze opgeslagen proc niet correct.
  • Deze opgeslagen procedure is afhankelijk van bestaande gelijktijdigheidslimietaanbiedingen en als deze wijziging niet goed werkt, werkt deze opgeslagen procedure niet goed.
  • Deze opgeslagen procedure is afhankelijk van bestaande resourceklasseaanbiedingen en als deze wijziging dan werkt deze opgeslagen procedure niet correct.

Notitie

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

  • Een DW-parameter bevat een ongeldige SLO-waarde
  • Of er is geen overeenkomende resourceklasse voor de CCI-bewerking in de tabel.

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

Verbruiksvoorbeeld

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

  • @DWU: geef een NULL parameter op voor het extraheren van de huidige DWU uit de DW-database of geef een ondersteunde DWU op in de vorm van DW100c
  • @SCHEMA_NAME: Geef een schemanaam van de tabel op
  • @TABLE_NAME: Geef een tabelnaam op van de interesse

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

Gerelateerde inhoud

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