Számítási feladatok kezelése erőforrásosztályokkal az Azure Synapse Analyticsben
Útmutató az azure Synapse-beli Synapse SQL-készlet lekérdezéseinek memóriájának és egyidejűségének kezeléséhez erőforrásosztályok használatával.
Mik azok az erőforrásosztályok?
A lekérdezések teljesítménykapacitását a felhasználó erőforrásosztálya határozza meg. Az erőforrásosztályok előre meghatározott erőforráskorlátok a Synapse SQL-készletben, amelyek a számítási erőforrásokat és a lekérdezések végrehajtásának egyidejűségét szabályozzák. Az erőforrásosztályok segíthetnek az erőforrások lekérdezésekhez való konfigurálásában azáltal, hogy korlátozza az egyidejűleg futó lekérdezések számát és az egyes lekérdezésekhez rendelt számítási erőforrásokat. Van egy kompromisszum a memória és az egyidejűség között.
- A kisebb erőforrásosztályok csökkentik a lekérdezésenkénti maximális memóriát, de növelik az egyidejűséget.
- A nagyobb erőforrásosztályok növelik a lekérdezésenkénti maximális memóriát, de csökkentik az egyidejűséget.
Az erőforrásosztályok kétféleképpen használhatók:
- Statikus erőforrásosztályok, amelyek kiválóan alkalmasak a rögzített adathalmazméretek nagyobb egyidejűségére.
- Dinamikus erőforrásosztályok, amelyek megfelelőek a növekvő méretű adathalmazokhoz, és nagyobb teljesítményt igényelnek a szolgáltatási szint felskálázása során.
Az erőforrásosztályok egyidejűségi pontok használatával mérik az erőforrás-felhasználást. Az egyidejűségi pontokat a cikk későbbi részében ismertetjük.
- Az erőforrásosztályok erőforrás-kihasználtságának megtekintéséhez tekintse meg a memória és az egyidejűség korlátait.
- Az erőforrásosztály módosításához futtathatja a lekérdezést egy másik felhasználó alatt, vagy módosíthatja az aktuális felhasználó erőforrásosztály-tagságát .
Statikus erőforrásosztályok
A statikus erőforrásosztályok az aktuális teljesítményszinttől függetlenül ugyanazt a memóriamennyiséget foglalják le, amelyet az adattárházegységekben mérnek. Mivel a lekérdezések a teljesítményszinttől függetlenül ugyanazt a memóriafoglalást kapják, az adattárház horizontális felskálázásával több lekérdezés futtatható egy erőforrásosztályon belül. A statikus erőforrásosztályok akkor ideálisak, ha az adatkötet ismert és állandó.
A statikus erőforrásosztályok az alábbi előre definiált adatbázis-szerepkörökkel vannak implementálva:
- staticrc10
- staticrc20
- staticrc30
- staticrc40
- staticrc50
- staticrc60
- staticrc70
- staticrc80
Dinamikus erőforrásosztályok
A dinamikus erőforrásosztályok az aktuális szolgáltatási szinttől függően változó mennyiségű memóriát foglalnak le. Bár a statikus erőforrásosztályok előnyösek a magasabb egyidejűség és a statikus adatmennyiségek esetében, a dinamikus erőforrásosztályok jobban megfelelnek a növekvő vagy változó adatmennyiségnek. Ha nagyobb szolgáltatási szintre méretez fel, a lekérdezések automatikusan több memóriát kapnak.
A smallrc kivételével a dinamikus erőforrásosztályok az alábbi előre definiált adatbázis-szerepkörökkel vannak implementálva:
- mediumrc
- largerc
- xlargerc
A Smallrc nem adatbázisszerepkörként jelenik meg, hanem az alapértelmezett erőforrásosztály.
Az egyes erőforrásosztályok memóriafoglalása a következő.
Szolgáltatásszint | 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 lehetőséget | 5% | 10% | 22% | 70% |
DW1000c a DW30000c |
3% | 10% | 22% | 70% |
Alapértelmezett erőforrásosztály
Alapértelmezés szerint minden felhasználó tagja a smallrc dinamikus erőforrásosztálynak.
A szolgáltatásadminisztrátor erőforrásosztálya a smallrc helyen van javítva, és nem módosítható. A szolgáltatásadminisztrátor a kiépítési folyamat során létrehozott felhasználó. Ebben a kontextusban a szolgáltatásadminisztrátor a "Kiszolgálói rendszergazdai bejelentkezés" beállításhoz megadott bejelentkezés, amikor új Synapse SQL-készletet hoz létre egy új kiszolgálóval.
Feljegyzés
Az Active Directory-rendszergazdaként definiált felhasználók és csoportok szintén szolgáltatás-rendszergazdák.
Erőforrásosztály-műveletek
Az erőforrásosztályok célja az adatkezelési és -kezelési tevékenységek teljesítményének javítása. Az összetett lekérdezések nagy erőforrásosztályban is futtathatók. Például a nagy illesztések és rendezések lekérdezési teljesítménye javulhat, ha az erőforrásosztály elég nagy ahhoz, hogy lehetővé tegye a lekérdezés végrehajtását a memóriában.
Erőforrásosztályok által szabályozott műveletek
Ezeket a műveleteket erőforrásosztályok szabályozzák:
INSERT
-SELECT
, ,UPDATE
DELETE
SELECT
(felhasználói táblák lekérdezésekor)ALTER INDEX
-REBUILD
vagyREORGANIZE
ALTER TABLE REBUILD
CREATE INDEX
CREATE CLUSTERED COLUMNSTORE INDEX
CREATE TABLE AS SELECT
(CTAS)- Az adatok betöltése
- Az adatáthelyezési szolgáltatás (DMS) által végzett adatátviteli műveletek
Feljegyzés
A dinamikus felügyeleti nézetekre (DMV-kre) vagy más rendszernézetekre vonatkozó SELECT-utasításokra nem vonatkoznak az egyidejűségi korlátok. A rendszeren futtatott lekérdezések számától függetlenül figyelheti a rendszert.
Erőforrásosztályok által nem szabályozott műveletek
Egyes lekérdezések mindig a smallrc erőforrásosztályban futnak, annak ellenére, hogy a felhasználó egy nagyobb erőforrásosztály tagja. Ezek a kivétel nélküli lekérdezések nem számítanak bele az egyidejűségi korlátba. Ha például az egyidejűségi korlát 16, akkor sok felhasználó a rendelkezésre álló egyidejűségi pontok befolyásolása nélkül választhat a rendszernézetekből.
A következő utasítások mentesülnek az erőforrásosztályok alól, és mindig a smallrc-ben futnak:
CREATE
vagyDROP TABLE
ALTER TABLE ... SWITCH
,SPLIT
vagyMERGE PARTITION
ALTER INDEX DISABLE
DROP INDEX
CREATE
,UPDATE
vagyDROP STATISTICS
TRUNCATE TABLE
ALTER AUTHORIZATION
CREATE LOGIN
CREATE
,ALTER
vagyDROP USER
CREATE
,ALTER
vagyDROP PROCEDURE
CREATE
vagyDROP VIEW
INSERT VALUES
SELECT
rendszernézetekből és DMV-kbőlEXPLAIN
DBCC
Egyidejűségi pontok
Az egyidejűségi pontok kényelmesen követik a lekérdezés végrehajtásához rendelkezésre álló erőforrásokat. Ezek olyanok, mint a jegyek, amelyeket a koncertek foglalásához vásárol, mert az ülőhelyek korlátozottak. Az adattárházonkénti egyidejűségi pontok teljes számát a szolgáltatási szint határozza meg. Mielőtt egy lekérdezés elkezdené a végrehajtást, elegendő egyidejűségi helyet kell lefoglalnia. Amikor egy lekérdezés befejeződik, felszabadítja az egyidejűségi pontjait.
- A 10 egyidejűséggel rendelkező lekérdezések ötször több számítási erőforráshoz férhetnek hozzá, mint a 2 egyidejűséggel rendelkező lekérdezéshez.
- Ha minden lekérdezéshez 10 egyidejűségi pont szükséges, és 40 egyidejűségi pont van, akkor egyszerre csak 4 lekérdezés futtatható.
Csak az erőforrás által szabályozott lekérdezések használnak egyidejűségi pontokat. A rendszer-lekérdezések és néhány apró lekérdezés nem használ fel tárolóhelyet. A felhasznált egyidejűségi pontok pontos számát a lekérdezés erőforrásosztálya határozza meg.
Az erőforrásosztályok megtekintése
Az erőforrásosztályok előre definiált adatbázis-szerepkörökként vannak implementálva. Az erőforrásosztályok két típusa létezik: dinamikus és statikus. Az erőforrásosztályok listájának megtekintéséhez használja a következő lekérdezést:
SELECT name
FROM sys.database_principals
WHERE name LIKE '%rc%' AND type_desc = 'DATABASE_ROLE';
Felhasználó erőforrásosztályának módosítása
Az erőforrásosztályok úgy implementálódnak, hogy felhasználókat rendelnek adatbázis-szerepkörökhöz. Amikor egy felhasználó lekérdezést futtat, a lekérdezés a felhasználó erőforrásosztályával fut. Ha például egy felhasználó tagja a staticrc10 adatbázisszerepkörnek, a lekérdezések kis mennyiségű memóriával futnak. Ha egy adatbázis-felhasználó tagja az xlargerc vagy staticrc80 adatbázis-szerepköröknek, a lekérdezések nagy mennyiségű memóriával futnak.
A felhasználó erőforrásosztályának növeléséhez a sp_addrolemember segítségével vegye fel a felhasználót egy nagy erőforrásosztály adatbázisszerepkörére. Az alábbi kód hozzáad egy felhasználót a nagyobb méretű adatbázis szerepkörhöz. Minden kérés a rendszermemória 22%-át kapja meg.
EXEC sp_addrolemember 'largerc', 'loaduser';
Az erőforrásosztály csökkentéséhez használja a sp_droprolemember. Ha a "loaduser" nem tag vagy más erőforrásosztály, akkor az alapértelmezett smallrc erőforrásosztályba kerül, 3%-os memóriakiadással.
EXEC sp_droprolemember 'largerc', 'loaduser';
Erőforrásosztályok elsőbbsége
A felhasználók több erőforrásosztály tagjai lehetnek. Ha egy felhasználó egynél több erőforrásosztályhoz tartozik:
- A dinamikus erőforrásosztályok elsőbbséget élveznek a statikus erőforrásosztályokkal szemben. Ha például egy felhasználó a mediumrc (dinamikus) és a staticrc80 (statikus) tagja, a lekérdezések a mediumrc használatával futnak.
- A nagyobb erőforrásosztályok elsőbbséget élveznek a kisebb erőforrásosztályokkal szemben. Ha például egy felhasználó a mediumrc és a largerc tagja, a lekérdezések a largerc használatával futnak. Hasonlóképpen, ha egy felhasználó a staticrc20 és a statirc80 tagja is, a lekérdezések staticrc80 erőforrás-foglalásokkal futnak.
Ajánlások
Feljegyzés
Fontolja meg a számítási feladatok kezelési képességeinek (számítási feladatok elkülönítése, besorolása és fontossága) kihasználását a számítási feladatok és a kiszámítható teljesítmény hatékonyabb szabályozása érdekében.
Javasoljuk, hogy hozzon létre egy olyan felhasználót, aki egy adott típusú lekérdezési vagy betöltési művelet futtatására van dedikáltan. Adjon a felhasználónak állandó erőforrásosztályt ahelyett, hogy gyakran módosítaná az erőforrásosztályt. A statikus erőforrásosztályok nagyobb átfogó felügyeletet biztosít a számítási feladathoz, ezért javasoljuk, hogy a dinamikus erőforrásosztályok mérlegelése előtt használjon statikus erőforrásosztályokat.
Erőforrásosztályok betöltési felhasználók számára
CREATE TABLE
alapértelmezés szerint fürtözött oszlopcentrikus indexeket használ. Az adatok oszlopcentrikus indexbe való tömörítése memóriaigényes művelet, és a memóriaterhelés csökkentheti az index minőségét. A memóriaterhelés miatt az adatok betöltésekor magasabb erőforrásosztályra lehet szükség. Annak érdekében, hogy a terhelések elegendő memóriával rendelkezzenek, létrehozhat egy terhelések futtatására kijelölt felhasználót, és hozzárendelheti a felhasználót egy magasabb erőforrásosztályhoz.
A terhelés hatékony feldolgozásához szükséges memória a betöltött tábla jellegétől és az adatmérettől függ. A memóriakövetelményekkel kapcsolatos további információkért tekintse meg a sorcsoport minőségének maximalizálása című témakört.
A memóriakövetelmény meghatározása után válassza ki, hogy a terhelési felhasználót statikus vagy dinamikus erőforrásosztályhoz kívánja-e rendelni.
- Statikus erőforrásosztályt használjon, ha a táblamemória követelményei egy adott tartományba esnek. A terhelések megfelelő memóriával futnak. Az adattárház méretezésekor a terheléseknek nincs szükségük több memóriára. Statikus erőforrásosztály használatával a memóriafoglalások állandóak maradnak. Ez a konzisztencia megőrzi a memóriát, és több lekérdezés egyidejű futtatását teszi lehetővé. Javasoljuk, hogy az új megoldások először a statikus erőforrásosztályokat használják, mivel ezek nagyobb felügyeletet biztosítanak.
- Dinamikus erőforrásosztályt használjon, ha a táblamemória követelményei nagy mértékben eltérnek. A terhelések több memóriát igényelhetnek, mint a jelenlegi DWU vagy cDWU szint. Az adattárház skálázása több memóriát ad a betöltési műveletekhez, ami lehetővé teszi a terhelések gyorsabb végrehajtását.
Erőforrásosztályok lekérdezésekhez
Egyes lekérdezések nagy számítási igényűek, mások pedig nem.
- Akkor válasszon dinamikus erőforrásosztályt, ha a lekérdezések összetettek, de nem igényelnek magas egyidejűséget. Például napi vagy heti jelentések létrehozása időnként szükség van erőforrásokra. Ha a jelentések nagy mennyiségű adatot dolgoznak fel, az adattárház skálázása több memóriát biztosít a felhasználó meglévő erőforrásosztályának.
- Akkor válasszon statikus erőforrásosztályt, ha az erőforrásokkal kapcsolatos elvárások a nap folyamán változnak. Egy statikus erőforrásosztály például akkor működik jól, ha az adattárházat sokan kérdezik le. Az adattárház skálázása során a felhasználó számára lefoglalt memória mennyisége nem változik. Következésképpen több lekérdezés is végrehajtható párhuzamosan a rendszeren.
A megfelelő memóriahasználat számos tényezőtől függ, például a lekérdezett adatok mennyiségétől, a táblázat sémáinak jellegétől, valamint a különböző illesztésektől, kiválasztási és csoportosítási predikátumoktól. Általánosságban elmondható, hogy a több memória kiosztása lehetővé teszi a lekérdezések gyorsabb végrehajtását, de csökkenti az általános egyidejűséget. Ha az egyidejűség nem jelent problémát, a memória túlosztása nem rontja az átviteli sebességet.
A teljesítmény finomhangolásához használjon különböző erőforrásosztályokat. A következő szakasz egy tárolt eljárást biztosít, amely segít a legjobb erőforrásosztály megállapításában.
Példakód a legjobb erőforrásosztály megtalálásához
prc_workload_management_by_DWU
A tárolt eljárás használatával:
- Tekintse meg az egyidejűséget és a memóriakiadást erőforrásosztályonként egy adott SLO-ban.
- Adja meg
NULL
a sémát és a táblanevet is.
- Adja meg
- Tekintse meg a memóriaigényes CCI-műveletek (terhelés, másolási táblázat, újraépítési index stb.) legjobb erőforrásosztályát egy adott erőforrásosztály nem particionált CCI-tábláján.
- A tárolt proc táblasémával állapítja meg a szükséges memóriahasználatot.
Példákért lásd : Használati példa.
Függőségek > korlátozások
- Ez a tárolt eljárás nem a particionált cci-táblák memóriaigényének kiszámítására szolgál.
- Ez a tárolt eljárás nem veszi figyelembe a CTAS/INSERT-SELECT SELECT részének memóriakövetelményeit, és feltételezi, hogy AZ SELECT.
- Ez a tárolt eljárás egy ideiglenes táblát használ, amely abban a munkamenetben érhető el, ahol a tárolt eljárás létrejött.
- Ez a tárolt eljárás az aktuális ajánlatoktól (például hardverkonfigurációtól, DMS-konfigurációtól) függ, és ha ezek bármelyike megváltozik, a tárolt proc nem fog megfelelően működni.
- Ez a tárolt eljárás a meglévő egyidejűségi korlátajánlatoktól függ, és ha ezek megváltoznak, akkor ez a tárolt eljárás nem fog megfelelően működni.
- Ez a tárolt eljárás a meglévő erőforrásosztály-ajánlatoktól függ, és ha ezek módosulnak, akkor ez a tárolt eljárás nem fog megfelelően működni.
Feljegyzés
Ha a megadott paraméterekkel rendelkező tárolt eljárás végrehajtása után nem kap kimenetet, akkor két eset lehet.
- Bármelyik DW paraméter érvénytelen SLO-értéket tartalmaz
- Vagy a táblában nincs egyező erőforrásosztály a CCI-művelethez.
A DW100c esetében például a legmagasabb rendelkezésre álló memóriahozzáférés 1 GB, és ha a táblázatséma elég széles ahhoz, hogy átlépje az 1 GB-os követelményt.
Használati példa
Szintaxis:
EXEC dbo.prc_workload_management_by_DWU @DWU VARCHAR(7), @SCHEMA_NAME VARCHAR(128), @TABLE_NAME VARCHAR(128)
@DWU
: Adjon meg egy paramétertNULL
az aktuális DWU kinyeréséhez a DW DB-ből, vagy adjon meg bármilyen támogatott DWU-t "DW100c" formátumban@SCHEMA_NAME
: Adja meg a tábla sémanevét@TABLE_NAME
: Adja meg a kamattáblázat nevét
Példák a tárolt proc végrehajtására:
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;
Az alábbi utasítás az előző példákban használt utasítást hozza létre Table1
.
CREATE TABLE Table1 (a int, b varchar(50), c decimal (18,10), d char(10), e varbinary(15), f float, g datetime, h date);
Tárolt eljárás definíciója
-------------------------------------------------------------------------------
-- 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
Kapcsolódó tartalom
Az adatbázis-felhasználók és a biztonság kezelésével kapcsolatos további információkért tekintse meg az adatbázis biztonságossá tételét a Synapse SQL-ben. További információ arról, hogy a nagyobb erőforrásosztályok hogyan javíthatják a fürtözött oszlopcentrikus index minőségét: Memóriaoptimalizálás az oszlopcentrikus tömörítéshez.