Manajemen beban kerja dengan kelas sumber daya di Azure Synapse Analytics

  • Artikel

Panduan untuk menggunakan kelas sumber daya untuk mengelola memori dan konkurensi untuk kueri kumpulan SQL Synapse di Azure Synapse.

Apa itu kelas sumber daya

Kapasitas performa kueri ditentukan oleh kelas sumber daya pengguna. Kelas sumber daya adalah batas sumber daya yang telah ditentukan sebelumnya di kumpulan SQL Synapse yang mengatur sumber daya komputasi dan konkurensi untuk eksekusi kueri. Kelas sumber daya dapat membantu Anda mengonfigurasi sumber daya untuk kueri Anda dengan menetapkan batas jumlah kueri yang berjalan secara bersamaan dan pada sumber daya komputasi yang ditetapkan untuk setiap kueri. Ada pertukaran antara memori dan konkurensi.

  • Kelas sumber daya yang lebih kecil mengurangi memori maksimum per kueri, tetapi meningkatkan konkurensi.
  • Kelas sumber daya yang lebih besar meningkatkan memori maksimum per kueri, tetapi mengurangi konkurensi.

Ada dua jenis kelas sumber daya:

  • Kelas sumber daya statis, yang sangat cocok untuk peningkatan konkurensi pada ukuran himpunan data yang tetap.
  • Kelas sumber daya dinamis, yang sangat cocok untuk kumpulan data yang semakin besar ukurannya dan membutuhkan peningkatan performa saat skala tingkat layanan dinaikkan.

Kelas sumber daya menggunakan slot konkurensi untuk mengukur konsumsi sumber daya. Slot konkurensi dijelaskan nanti dalam artikel ini.

Kelas sumber daya statis

Kelas sumber daya statis mengalokasikan jumlah memori yang sama terlepas dari tingkat performa saat ini, yang diukur dalam unit gudang data. Karena kueri mendapatkan alokasi memori yang sama terlepas dari tingkat performanya, menskalakan gudang data memungkinkan lebih banyak kueri dijalankan dalam kelas sumber daya. Kelas sumber daya statis ideal jika volume data diketahui dan konstan.

Kelas sumber daya statis diimplementasikan dengan peran database yang telah ditentukan sebelumnya ini:

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

Kelas sumber daya dinamis

Kelas Sumber Daya Dinamis mengalokasikan jumlah memori yang bervariasi tergantung pada tingkat layanan saat ini. Sementara kelas sumber daya statis bermanfaat untuk konkurensi yang lebih tinggi dan volume data statis, kelas sumber daya dinamis lebih cocok untuk jumlah data yang bertambah atau bervariasi. Saat Anda meningkatkan skala ke tingkat layanan yang lebih besar, kueri Anda secara otomatis mendapatkan lebih banyak memori.

Kelas sumber daya dinamis diimplementasikan dengan peran database yang telah ditentukan sebelumnya ini:

  • smallrc
  • mediumrc
  • largerc
  • xlargerc

Alokasi memori untuk setiap kelas sumber daya adalah sebagai berikut.

Tingkat Layanan 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 ke
DW30000c		 %3 10% 22% 70%

Kelas sumber daya default

Secara default, setiap pengguna adalah anggota kelas sumber daya dinamis smallrc.

Kelas sumber daya administrator layanan diperbaiki di smallrc dan tidak dapat diubah. Administrator layanan adalah pengguna yang dibuat selama proses provisi. Administrator layanan dalam konteks ini adalah login yang ditentukan untuk "Login admin server" saat membuat kumpulan SQL Synapse baru dengan server baru.

Catatan

Pengguna atau grup yang ditetapkan sebagai admin Direktori Aktif juga merupakan administrator layanan.

Operasi kelas sumber daya

Kelas sumber daya dirancang untuk meningkatkan performa manajemen data dan aktivitas manipulasi. Kueri kompleks juga dapat mengambil manfaat dari menjalankan pada kelas sumber daya yang besar. Misalnya, performa kueri untuk gabungan dan pengurutan besar dapat ditingkatkan bila kelas sumber daya cukup besar untuk memungkinkan kueri dijalankan di memori.

Operasi diatur oleh kelas sumber daya

Operasi ini diatur oleh kelas sumber daya:

  • INSERT-SELECT, UPDATE, DELETE
  • SELECT (saat mengkueri tabel pengguna)
  • ALTER INDEX - REBUILD atau REORGANIZE
  • ALTER TABLE REBUILD
  • CREATE INDEX
  • CREATE CLUSTERED COLUMNSTORE INDEX
  • BUAT TABEL SEBAGAI PILIH (CTAS)
  • Pemuatan Data
  • Operasi pergerakan data yang dilakukan oleh Data Movement Service (DMS)

Catatan

Pernyataan SELECT pada tampilan manajemen dinamis (DMV) atau tampilan sistem lainnya tidak diatur oleh batas konkurensi mana pun. Anda dapat memantau sistem terlepas dari jumlah kueri yang dijalankan di dalamnya.

Operasi yang tidak diatur oleh kelas sumber daya

Beberapa kueri selalu berjalan di kelas sumber daya smallrc meskipun pengguna adalah anggota dari kelas sumber daya yang lebih besar. Kueri yang dikecualikan ini tidak diperhitungkan dalam batas konkurensi. Misalnya, jika batas konkurensi adalah 16, banyak pengguna dapat memilih dari tampilan sistem tanpa memengaruhi slot konkurensi yang tersedia.

Pernyataan berikut dikecualikan dari kelas sumber daya dan selalu dijalankan di smallrc:

  • CREATE atau DROP TABLE
  • ALTER TABLE ... SWITCH, SPLIT, atau MERGE PARTITION
  • ALTER INDEX DISABLE
  • DROP INDEX
  • CREATE, UPDATE, atau DROP STATISTICS
  • POTONG TABEL
  • ALTER AUTHORIZATION
  • CREATE LOGIN
  • CREATE, ALTER, atau DROP USER
  • CREATE, ALTER, atau DROP PROCEDURE
  • CREATE atau DROP VIEW
  • INSERT VALUES
  • SELECT dari tampilan sistem dan DMV
  • EXPLAIN
  • DBCC

Slot konkurensi

Slot konkurensi adalah cara mudah untuk melacak sumber daya yang tersedia untuk eksekusi kueri. Mereka seperti tiket yang Anda beli untuk memesan kursi di konser karena tempat duduk terbatas. Jumlah total slot konkurensi per gudang data ditentukan oleh tingkat layanan. Sebelum kueri dapat mulai dieksekusi, kueri harus dapat memesan slot konkurensi yang cukup. Ketika kueri selesai, kueri merilis slot konkurensinya.

  • Kueri yang berjalan dengan 10 slot konkurensi dapat mengakses sumber daya komputasi 5 kali lebih banyak daripada kueri yang berjalan dengan 2 slot konkurensi.
  • Jika setiap kueri membutuhkan 10 slot konkurensi dan ada 40 slot konkurensi, maka hanya 4 kueri yang dapat dijalankan secara bersamaan.

Hanya kueri yang diatur sumber daya yang menggunakan slot konkurensi. Kueri sistem dan beberapa kueri sepele tidak menggunakan slot apa pun. Jumlah persis slot konkurensi yang digunakan ditentukan oleh kelas sumber daya kueri.

Melihat kelas sumber daya

Kelas sumber daya diimplementasikan sebagai peran database yang telah ditentukan sebelumnya. Ada dua jenis kelas sumber daya: dinamis dan statis. Untuk melihat daftar kelas sumber daya, gunakan kueri berikut:

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

Mengubah kelas sumber daya pengguna

Kelas sumber daya diimplementasikan dengan menugaskan pengguna ke peran database. Saat pengguna menjalankan kueri, kueri berjalan dengan kelas sumber daya pengguna. Misalnya, jika pengguna adalah anggota dari peran database staticrc10, kueri mereka dijalankan dengan sejumlah kecil memori. Jika pengguna database adalah anggota dari peran database xlargerc atau staticrc80, kueri mereka dijalankan dengan memori dalam jumlah besar.

Untuk meningkatkan kelas sumber daya pengguna, gunakan sp_addrolemember untuk menambahkan pengguna ke peran database dari kelas sumber daya yang besar. Kode di bawah ini menambahkan pengguna ke peran database yang lebih besar. Setiap permintaan mendapat 22% memori sistem.

EXEC sp_addrolemember 'largerc', 'loaduser';

Untuk mengurangi kelas sumber daya, gunakan sp_droprolemember. Jika 'loaduser' bukan anggota atau kelas sumber daya lainnya, mereka masuk ke kelas sumber daya smallrc default dengan pemberian memori 3%.

EXEC sp_droprolemember 'largerc', 'loaduser';

Prioritas kelas sumber daya

Pengguna dapat menjadi anggota dari beberapa kelas sumber daya. Saat pengguna termasuk dalam lebih dari satu kelas sumber daya:

  • Kelas sumber daya dinamis lebih diutamakan daripada kelas sumber daya statis. Misalnya, jika pengguna adalah anggota mediumrc(dinamis) dan staticrc80 (statis), kueri dijalankan dengan mediumrc.
  • Kelas sumber daya yang lebih besar lebih diutamakan daripada kelas sumber daya yang lebih kecil. Misalnya, jika pengguna adalah anggota mediumrc dan lebih largerc, kueri dijalankan dengan lebih largerc. Demikian juga, jika pengguna adalah anggota staticrc20 dan statirc80, kueri dijalankan dengan alokasi sumber daya staticrc80.

Rekomendasi

Catatan

Pertimbangkan untuk memanfaatkan kemampuan pengelolaan beban kerja (isolasi beban kerja, klasifikasi, dan kepentingan) untuk kontrol lebih besar atas beban kerja dan performa yang dapat diprediksi.

Sebaiknya buat pengguna khusus untuk menjalankan jenis kueri atau operasi beban tertentu. Berikan pengguna itu kelas sumber daya permanen alih-alih sering mengubah kelas sumber daya. Kelas sumber daya statis memberikan kontrol keseluruhan yang lebih besar pada beban kerja, jadi sebaiknya gunakan kelas sumber daya statis sebelum mempertimbangkan kelas sumber daya dinamis.

Kelas sumber daya untuk memuat pengguna

CREATE TABLE menggunakan indeks penyimpan kolom terkluster secara default. Mengompresi data ke dalam indeks penyimpan kolom adalah operasi intensif memori, dan tekanan memori dapat mengurangi kualitas indeks. Tekanan memori dapat menyebabkan membutuhkan kelas sumber daya yang lebih tinggi saat memuat data. Untuk memastikan beban memiliki memori yang cukup, Anda dapat membuat pengguna yang ditujukan untuk menjalankan beban dan menetapkan pengguna tersebut ke kelas sumber daya yang lebih tinggi.

Memori yang dibutuhkan untuk memproses beban secara efisien tergantung pada sifat tabel yang dimuat dan ukuran data. Untuk informasi selengkapnya tentang persyaratan memori, lihat Memaksimalkan kualitas grup baris.

Setelah Anda menentukan kebutuhan memori, pilih apakah akan menetapkan pengguna beban ke kelas sumber daya statis atau dinamis.

  • Gunakan kelas sumber daya statis saat persyaratan memori tabel berada dalam rentang tertentu. Beban berjalan dengan memori yang sesuai. Saat Anda menskalakan gudang data, beban tidak memerlukan lebih banyak memori. Dengan menggunakan kelas sumber daya statis, alokasi memori tetap konstan. Konsistensi ini menghemat memori dan memungkinkan lebih banyak kueri dijalankan secara bersamaan. Kami menyarankan agar solusi baru menggunakan kelas sumber daya statis terlebih dahulu karena ini memberikan kontrol yang lebih besar.
  • Gunakan kelas sumber daya dinamis saat persyaratan memori tabel sangat bervariasi. Beban mungkin memerlukan lebih banyak memori dibandingkan yang disediakan level DWU atau cDWU saat ini. Penskalaan gudang data menambahkan lebih banyak memori untuk memuat operasi, yang memungkinkan pemuatan dilakukan lebih cepat.

Kelas sumber daya untuk kueri

Beberapa kueri membutuhkan komputasi intensif dan yang lain tidak.

  • Pilih kelas sumber daya dinamis saat kueri rumit, tetapi tidak memerlukan konkurensi tinggi. Misalnya, membuat laporan harian atau mingguan adalah kebutuhan sumber daya sesekali. Jika laporan memproses data dalam jumlah besar, penskalaan gudang data menyediakan lebih banyak memori untuk kelas sumber daya pengguna yang ada.
  • Pilih kelas sumber daya statis ketika ekspektasi sumber daya bervariasi sepanjang hari. Misalnya, kelas sumber daya statis berfungsi dengan baik ketika gudang data di-query oleh banyak orang. Misalnya, kelas sumber daya statis bekerja dengan baik ketika gudang data dikueri oleh banyak orang. Akibatnya, lebih banyak kueri dapat dieksekusi secara paralel pada sistem.

Pemberian memori yang tepat bergantung pada banyak faktor, seperti jumlah data yang dikueri, sifat skema tabel, dan berbagai predikat gabungan, pilih, dan grup. Secara umum, mengalokasikan lebih banyak memori memungkinkan kueri diselesaikan lebih cepat, tetapi mengurangi keseluruhan konkurensi. Jika konkurensi tidak menjadi masalah, pengalokasian memori yang berlebihan tidak membahayakan throughput.

Untuk menyetel performa, gunakan kelas sumber daya yang berbeda. Bagian berikutnya memberikan prosedur tersimpan yang membantu Anda mengetahui kelas sumber daya terbaik.

Contoh kode untuk menemukan kelas sumber daya terbaik

Anda dapat menggunakan prosedur tersimpan yang ditentukan berikut ini untuk mengetahui konkurensi dan pemberian memori per kelas sumber daya pada SLO tertentu dan kelas sumber daya terbaik untuk operasi CCI intensif memori pada tabel CCI yang tidak dipartisi pada kelas sumber daya tertentu:

Inilah tujuan dari prosedur tersimpan ini:

  1. Untuk melihat pemberian konkurensi dan memori per kelas sumber daya pada SLO tertentu. Pengguna perlu memberikan NULL untuk skema dan nama tabel seperti yang ditunjukkan dalam contoh ini.
  2. Untuk melihat kelas sumber daya terbaik untuk operasi CCI intensif memori (memuat, menyalin tabel, membangun kembali indeks, dll.) pada tabel CCI yang tidak dipartisi pada kelas sumber daya tertentu. Prosesor yang disimpan menggunakan skema tabel untuk mengetahui pemberian memori yang diperlukan.

Dependensi & Pembatasan

  • Prosedur tersimpan ini tidak dirancang untuk menghitung kebutuhan memori untuk tabel cci yang dipartisi.
  • Prosedur tersimpan ini tidak memperhitungkan persyaratan memori untuk bagian SELECT dari CTAS/INSERT-SELECT dan menganggapnya sebagai SELECT.
  • Prosedur tersimpan ini menggunakan tabel temp, yang tersedia di sesi tempat prosedur tersimpan ini dibuat.
  • Prosedur tersimpan ini bergantung pada penawaran saat ini (misalnya, konfigurasi perangkat keras, konfigurasi DMS), dan jika ada yang berubah maka prosesor tersimpan ini tidak akan berfungsi dengan benar.
  • Prosedur tersimpan ini bergantung pada penawaran batas konkurensi yang ada dan jika ini berubah maka prosedur tersimpan ini tidak akan berfungsi dengan benar.
  • Prosedur tersimpan ini bergantung pada penawaran kelas sumber daya yang ada dan jika ini berubah maka prosedur tersimpan ini tidak akan berfungsi dengan benar.

Catatan

Jika Anda tidak mendapatkan output setelah menjalankan prosedur tersimpan dengan parameter yang disediakan, maka mungkin ada dua kasus.

  1. Salah satu Parameter DW berisi nilai SLO yang tidak valid
  2. Atau, tidak ada kelas sumber daya yang cocok untuk operasi CCI di atas meja.

Misalnya, di DW100c, hibah memori tertinggi yang tersedia adalah 1 GB, dan jika skema tabel cukup lebar untuk memenuhi persyaratan 1 GB.

Contoh penggunaan

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

  1. @DWU: Memberikan parameter NUL untuk mengekstrak DWU saat ini dari DW DB atau memberikan DWU yang didukung dalam bentuk 'DW100c'
  2. @SCHEMA_NAME: Memberikan nama skema tabel
  3. @TABLE_NAME: Memberikan nama tabel yang diminati

Contoh menjalankan prosesor tersimpan ini:

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;

Pernyataan berikut membuat Tabel1 yang digunakan dalam contoh sebelumnya. CREATE TABLE Table1 (a int, b varchar(50), c decimal (18,10), d char(10), e varbinary(15), f float, g datetime, h date);

Definisi prosedur tersimpan

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

Langkah berikutnya

Untuk informasi selengkapnya tentang mengelola pengguna dan keamanan database, lihat Mengamankan database di Synapse SQL. Untuk informasi selengkapnya tentang bagaimana kelas sumber daya yang lebih besar dapat meningkatkan kualitas indeks penyimpanan kolom tergugus, lihat Pengoptimalan memori untuk kompresi penyimpan kolom.