Udostępnij za pośrednictwem

Zarządzanie obciążeniami przy użyciu klas zasobów w usłudze Azure Synapse Analytics

  • Artykuł

Wskazówki dotyczące używania klas zasobów do zarządzania pamięcią i współbieżnością zapytań puli SQL usługi Synapse w usłudze Azure Synapse.

Co to są klasy zasobów?

Wydajność zapytania jest określana przez klasę zasobów użytkownika. Klasy zasobów są wstępnie określone limity zasobów w puli SQL usługi Synapse, które zarządzają zasobami obliczeniowymi i współbieżnością na potrzeby wykonywania zapytań. Klasy zasobów mogą pomóc w konfigurowaniu zasobów dla zapytań, ustawiając limity liczby zapytań uruchamianych współbieżnie i zasobów obliczeniowych przypisanych do każdego zapytania. There's a trade-off between memory and concurrency.

  • Mniejsze klasy zasobów zmniejszają maksymalną ilość pamięci na zapytanie, ale zwiększają współbieżność.
  • Większe klasy zasobów zwiększają maksymalną ilość pamięci na zapytanie, ale zmniejszają współbieżność.

Istnieją dwa typy klas zasobów:

  • Klasy zasobów statycznych, które doskonale nadają się do zwiększenia współbieżności w stałym rozmiarze zestawu danych.
  • Dynamiczne klasy zasobów, które są odpowiednie dla zestawów danych, które rosną i wymagają zwiększonej wydajności, ponieważ poziom usługi jest skalowany w górę.

Resource classes use concurrency slots to measure resource consumption. Concurrency slots are explained later in this article.

  • Aby wyświetlić wykorzystanie zasobów dla klas zasobów, zobacz Limity pamięci i współbieżności.
  • Aby dostosować klasę zasobów, możesz uruchomić zapytanie w ramach innego użytkownika lub zmienić członkostwo w klasie zasobów bieżącego użytkownika.

Statyczne klasy zasobów

Statyczne klasy zasobów przydzielają taką samą ilość pamięci niezależnie od bieżącego poziomu wydajności mierzonego w jednostkach magazynu danych. Ponieważ zapytania uzyskują tę samą alokację pamięci niezależnie od poziomu wydajności, skalowanie w poziomie magazynu danych umożliwia uruchamianie większej liczby zapytań w ramach klasy zasobów. Statyczne klasy zasobów są idealne, jeśli wolumin danych jest znany i stały.

Statyczne klasy zasobów są implementowane przy użyciu wstępnie zdefiniowanych ról bazy danych:

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

Dynamiczne klasy zasobów

Dynamiczne klasy zasobów przydzielają zmienną ilość pamięci w zależności od bieżącego poziomu usługi. Chociaż statyczne klasy zasobów są korzystne dla wyższych woluminów współbieżności i danych statycznych, dynamiczne klasy zasobów są lepiej dostosowane do rosnącej lub zmiennej ilości danych. W przypadku skalowania w górę do większego poziomu usług zapytania automatycznie uzyskują większą ilość pamięci.

Z wyjątkiem smallrc dynamiczne klasy zasobów są implementowane przy użyciu wstępnie zdefiniowanych ról bazy danych:

  • mediumrc
  • largerc
  • xlargerc

Smallrc nie jest wyświetlana jako rola bazy danych, ale jest domyślną klasą zasobów.

Alokacja pamięci dla każdej klasy zasobów jest następująca.

Poziom usługi 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 to
DW30000c		 3% 10% 22% 70%

Domyślna klasa zasobów

Domyślnie każdy użytkownik jest członkiem dynamicznej klasy zasobów smallrc.

Klasa zasobów administratora usługi jest stała w rozmiarze smallrc i nie można jej zmienić. Administrator usługi jest użytkownikiem utworzonym podczas procesu aprowizacji. Administrator usługi w tym kontekście to login określony jako "Logowanie administratora serwera" podczas tworzenia nowej puli SQL usługi Synapse z nowym serwerem.

Uwaga

Użytkownicy lub grupy zdefiniowane jako administratorzy usługi Active Directory są również administratorami usług.

Resource class operations

Klasy zasobów mają na celu poprawę wydajności działań związanych z zarządzaniem danymi i manipulowaniem nimi. Złożone zapytania mogą również korzystać z działania w ramach dużej klasy zasobów. Na przykład wydajność zapytań dla dużych sprzężeń i sortowania może poprawić się, gdy klasa zasobów jest wystarczająco duża, aby umożliwić wykonywanie zapytania w pamięci.

Operacje zarządzane przez klasy zasobów

Te operacje podlegają klasom zasobów:

  • INSERT - SELECT, , UPDATEDELETE
  • SELECT (podczas wykonywania zapytań dotyczących tabel użytkowników)
  • ALTER INDEX - REBUILD lub REORGANIZE
  • ALTER TABLE REBUILD
  • CREATE INDEX
  • CREATE CLUSTERED COLUMNSTORE INDEX
  • CREATE TABLE AS SELECT (CTAS)
  • Ładowanie danych
  • Operacje przenoszenia danych przeprowadzane przez usługę Data Movement Service (DMS)

Uwaga

Instrukcje SELECT dotyczące dynamicznych widoków zarządzania (DMV) lub innych widoków systemowych nie podlegają żadnym z limitów współbieżności. System można monitorować niezależnie od liczby wykonywanych na nim zapytań.

Operacje nie podlegają klasom zasobów

Niektóre zapytania są zawsze uruchamiane w klasie zasobów smallrc, mimo że użytkownik jest członkiem większej klasy zasobów. Te wykluczone zapytania nie są liczone do limitu współbieżności. For example, if the concurrency limit is 16, many users can be selecting from system views without affecting the available concurrency slots.

The following statements are exempt from resource classes and always run in smallrc:

  • CREATE lub DROP TABLE
  • ALTER TABLE ... SWITCH, SPLIT lub MERGE PARTITION
  • ALTER INDEX DISABLE
  • DROP INDEX
  • CREATE, UPDATE lub DROP STATISTICS
  • TRUNCATE TABLE
  • ALTER AUTHORIZATION
  • CREATE LOGIN
  • CREATE, ALTER lub DROP USER
  • CREATE, ALTER lub DROP PROCEDURE
  • CREATE lub DROP VIEW
  • INSERT VALUES
  • SELECT from system views and DMVs
  • EXPLAIN
  • DBCC

Concurrency slots

Concurrency slots are a convenient way to track the resources available for query execution. Są one jak bilety, które kupujesz, aby zarezerwować miejsca na koncercie, ponieważ siedzenia są ograniczone. Łączna liczba gniazd współbieżności na magazyn danych zależy od poziomu usługi. Before a query can start executing, it must be able to reserve enough concurrency slots. When a query completes, it releases its concurrency slots.

  • Zapytanie uruchomione z 10 miejscami współbieżności może uzyskać dostęp do 5 razy więcej zasobów obliczeniowych niż zapytanie uruchomione z 2 miejscami współbieżności.
  • Jeśli każde zapytanie wymaga 10 miejsc współbieżności i istnieje 40 miejsc współbieżności, tylko 4 zapytania mogą być uruchamiane współbieżnie.

Only resource governed queries consume concurrency slots. Zapytania systemowe i niektóre proste zapytania nie używają żadnych slotów. The exact number of concurrency slots consumed is determined by the query's resource class.

Wyświetlanie klas zasobów

Klasy zasobów są implementowane jako wstępnie zdefiniowane role bazy danych. Istnieją dwa typy klas zasobów: dynamiczne i statyczne. Aby wyświetlić listę klas zasobów, użyj następującego zapytania:

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

Zmienianie klasy zasobów użytkownika

Klasy zasobów są implementowane przez przypisywanie użytkowników do ról bazy danych. Gdy użytkownik uruchamia zapytanie, zapytanie jest uruchamiane z klasą zasobów użytkownika. Jeśli na przykład użytkownik jest członkiem roli bazy danych staticrc10, zapytania są uruchamiane z małą ilością pamięci. Jeśli użytkownik bazy danych jest członkiem ról bazy danych xlargerc lub staticrc80, zapytania są uruchamiane z dużą ilością pamięci.

Aby zwiększyć klasę zasobów użytkownika, użyj sp_addrolemember, aby dodać użytkownika do roli bazy danych o dużej klasie zasobów. Poniższy kod dodaje użytkownika do roli bazy danych o nazwie largerc. Każde żądanie pobiera 22% pamięci systemowej.

EXEC sp_addrolemember 'largerc', 'loaduser';

Aby zmniejszyć klasę zasobów, użyj sp_droprolemember. Jeśli "loaduser" nie jest członkiem żadnej ani jakiejkolwiek innej klasy zasobów, trafia do domyślnej klasy zasobów smallrc z przydziałem pamięci wynoszącym 3%.

EXEC sp_droprolemember 'largerc', 'loaduser';

Pierwszeństwo klasy zasobów

Użytkownicy mogą być członkami wielu klas zasobów. Gdy użytkownik należy do więcej niż jednej klasy zasobów:

  • Dynamiczne klasy zasobów mają pierwszeństwo przed statycznych klas zasobów. Jeśli na przykład użytkownik jest członkiem zarówno mediumrc (dynamiczny), jak i staticrc80 (statyczny), zapytania są uruchamiane za pomocą mediumrc.
  • Większe klasy zasobów mają pierwszeństwo przed mniejszymi klasami zasobów. Jeśli na przykład użytkownik jest członkiem mediumrc i largerc, zapytania są uruchamiane z largerc. Podobnie, jeśli użytkownik jest członkiem zarówno staticrc20, jak i statirc80, zapytania są uruchamiane przy użyciu alokacji zasobów staticrc80.

Zalecenia

Uwaga

Rozważ wykorzystanie możliwości zarządzania obciążeniami (izolacja obciążenia, klasyfikacja i znaczenie), aby uzyskać większą kontrolę nad obciążeniem i przewidywalną wydajnością.

Zalecamy utworzenie użytkownika dedykowanego do uruchamiania określonego typu zapytania lub operacji ładowania. Nadaj użytkownikowi stałą klasę zasobów zamiast często zmieniać klasę zasobów. Statyczne klasy zasobów zapewniają większą ogólną kontrolę nad obciążeniem, dlatego zalecamy użycie statycznych klas zasobów przed rozważeniem dynamicznych klas zasobów.

Resource classes for load users

CREATE TABLE domyślnie używa klastrowanych indeksów magazynu kolumn. Kompresowanie danych do indeksu magazynu kolumnowego jest operacją obciążającą pamięć, a presja na pamięć może obniżyć jakość indeksu. Przeciążenie pamięci może prowadzić do potrzeby wyższej klasy zasobów podczas ładowania danych. Aby upewnić się, że obciążenia mają wystarczającą ilość pamięci, możesz utworzyć użytkownika wyznaczonego do uruchamiania obciążeń i przypisać tego użytkownika do wyższej klasy zasobów.

Pamięć potrzebna do wydajnego przetwarzania obciążeń zależy od charakteru załadowanej tabeli i rozmiaru danych. Aby uzyskać więcej informacji na temat wymagań dotyczących pamięci, zobacz Maksymalizacja jakości grupy wierszy.

Po określeniu wymagania dotyczącego pamięci wybierz, czy chcesz przypisać użytkownika ładowania do statycznej lub dynamicznej klasy zasobów.

  • Użyj statycznej klasy zasobów, gdy wymagania dotyczące pamięci tabeli mieszczą się w określonym zakresie. Loads run with appropriate memory. Podczas skalowania magazynu danych obciążenia nie wymagają większej ilości pamięci. Dzięki użyciu statycznej klasy zasobów alokacje pamięci pozostają stałe. Ta spójność oszczędza pamięć i umożliwia jednoczesne uruchamianie większej liczby zapytań. Zalecamy, aby nowe rozwiązania używały najpierw statycznych klas zasobów, ponieważ zapewniają większą kontrolę.
  • Użyj dynamicznej klasy zasobów, gdy wymagania dotyczące pamięci tabeli różnią się znacznie. Loads might require more memory than the current DWU or cDWU level provides. Skalowanie magazynu danych zwiększa ilość pamięci do operacji ładowania, co pozwala na szybsze wykonywanie obciążeń.

Klasy zasobów dla zapytań

Niektóre zapytania są obliczeniowo intensywne, a niektóre nie.

  • Wybierz dynamiczną klasę zasobów, gdy zapytania są złożone, ale nie wymagają wysokiej współbieżności. Na przykład generowanie raportów dziennych lub cotygodniowych jest okazjonalnym zapotrzebowaniem na zasoby. Jeśli raporty przetwarzają duże ilości danych, skalowanie magazynu danych zapewnia większą ilość pamięci do istniejącej klasy zasobów użytkownika.
  • Wybierz statyczną klasę zasobów, gdy oczekiwania dotyczące zasobów różnią się w ciągu dnia. Na przykład, statyczna klasa zasobów działa dobrze, gdy magazyn danych jest odpytywany przez wiele osób. Podczas skalowania magazynu danych ilość pamięci przydzielonej użytkownikowi nie zmienia się. W związku z tym więcej zapytań można wykonywać równolegle w systemie.

Odpowiednie przydziały pamięci zależą od wielu czynników, takich jak ilość przetwarzanych danych, charakterystyki schematów tabel, oraz różne sprzężenia, operacje SELECT i predykaty grupujące. Ogólnie rzecz biorąc, przydzielanie większej ilości pamięci umożliwia szybsze wykonywanie zapytań, ale zmniejsza ogólną współbieżność. Jeśli współbieżność nie jest problemem, nadmierna alokacja pamięci nie zaszkodzi przepływności.

Aby dostosować wydajność, użyj różnych klas zasobów. The next section gives a stored procedure that helps you figure out the best resource class.

Przykładowy kod do znajdowania najlepszej klasy zasobów

Use the prc_workload_management_by_DWU stored procedure to:

  • See the concurrency and memory grant per resource class at a given SLO.
    • Podaj NULL wartość dla schematu i nazwy tabeli.
  • Zobacz najlepszą klasę zasobów dla operacji CCI intensywnie korzystających z pamięci (ładowanie, kopiowanie tabeli, ponowne kompilowanie indeksu itp.) w niepartycyjnej tabeli CCI w danej klasie zasobów.
    • Przechowywana procedura używa schematu tabeli do określenia wymaganego przydziału pamięci.

Przykłady można znaleźć w temacie Przykład użycia.

Zależności i ograniczenia

  • Ta procedura składowana nie jest przeznaczona do obliczania zapotrzebowania na pamięć dla partycjonowanej tabeli cci.
  • This stored procedure doesn't take memory requirements into account for the SELECT part of CTAS/INSERT-SELECT and assumes it's a SELECT.
  • Ta procedura składowana używa tabeli tymczasowej, która jest dostępna w sesji, w której utworzono tę procedurę składowaną.
  • Ta procedura składowana zależy od bieżących ofert (na przykład konfiguracji sprzętu, konfiguracji usługi DMS), a jeśli którakolwiek z tych się zmieni, to procedura składowana nie będzie działać poprawnie.
  • This stored procedure depends on existing concurrency limit offerings and if these change then this stored procedure won't work correctly.
  • This stored procedure depends on existing resource class offerings and if these change then this stored procedure won't work correctly.

Uwaga

Jeśli nie otrzymujesz danych wyjściowych po wykonaniu procedury składowanej z podanymi parametrami, mogą wystąpić dwa przypadki.

  • Dowolny parametr DW zawiera nieprawidłową wartość SLO
  • Lub nie ma pasującej klasy zasobów dla operacji CCI w tabeli.

For example, at DW100c, the highest memory grant available is 1 GB, and if table schema is wide enough to cross the requirement of 1 GB.

Przykład użycia

Składnia:
EXEC dbo.prc_workload_management_by_DWU @DWU VARCHAR(7), @SCHEMA_NAME VARCHAR(128), @TABLE_NAME VARCHAR(128)

  • @DWU: podaj NULL parametr umożliwiający wyodrębnienie bieżącej jednostki DWU z bazy danych DW lub podanie dowolnej obsługiwanej jednostki DWU w postaci "DW100c"
  • @SCHEMA_NAME: Podaj nazwę schematu tabeli
  • @TABLE_NAME: Podaj nazwę interesującej tabeli

Przykłady wykonywania tej przechowywanej procedury:

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;

Poniższa instrukcja tworzy Table1 , który jest używany w poprzednich przykładach. CREATE TABLE Table1 (a int, b varchar(50), c decimal (18,10), d char(10), e varbinary(15), f float, g datetime, h date);

Stored procedure definition

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

Powiązana zawartość

Aby uzyskać więcej informacji na temat zarządzania użytkownikami i zabezpieczeniami bazy danych, zobacz Zabezpieczanie bazy danych w usłudze Synapse SQL. Aby uzyskać więcej informacji o tym, jak większe klasy zasobów mogą poprawić jakość sklasteryzowanego indeksu magazynu kolumnowego, zobacz Optymalizacje pamięci dla kompresji magazynu kolumn.