使用 Azure Synapse Analytics 中的资源类管理工作负载Workload management with resource classes in Azure Synapse Analytics

有关在 Azure Synapse 中使用资源类管理 Synapse SQL 池查询的内存和并发性的指导。Guidance for using resource classes to manage memory and concurrency for Synapse SQL pool queries in Azure Synapse.

什么是资源类What are resource classes

查询的性能容量由用户的资源类决定。The performance capacity of a query is determined by the user's resource class. 资源类是 Synapse SQL 池中预先确定的资源限制,用于控制查询执行的计算资源和并发性。Resource classes are pre-determined resource limits in Synapse SQL pool that govern compute resources and concurrency for query execution. 资源类可以通过对并发运行的查询数和分配给每个查询的计算资源数设置限制,帮助你配置查询资源。Resource classes can help you configure resources for your queries by setting limits on the number of queries that run concurrently and on the compute-resources assigned to each query. 我们需要在内存和并发性之间进行权衡。There's a trade-off between memory and concurrency.

  • 较小的资源类可以减少每个查询的最大内存量,但同时会提高并发性。Smaller resource classes reduce the maximum memory per query, but increase concurrency.
  • 较大的资源类可以增加每个查询的最大内存量,但同时会降低并发性。Larger resource classes increase the maximum memory per query, but reduce concurrency.

有两种类型的资源类:There are two types of resource classes:

  • 静态资源类:非常适用于在数据集大小固定的情况下提高并发性。Static resources classes, which are well suited for increased concurrency on a data set size that is fixed.
  • 动态资源类:非常适用于大小和性能随着服务级别的扩展而增加和提升的数据集。Dynamic resource classes, which are well suited for data sets that are growing in size and need increased performance as the service level is scaled up.

资源类使用并发性槽位来测量资源消耗。Resource classes use concurrency slots to measure resource consumption. 本文稍后将介绍并发性槽位Concurrency slots are explained later in this article.

静态资源类Static resource classes

不管当前性能级别是什么,静态资源类都会分配相同的内存量(以数据仓库单位表示)。Static resource classes allocate the same amount of memory regardless of the current performance level, which is measured in data warehouse units. 由于不管性能级别是什么,查询都会获得相同的内存分配,因此,横向扩展数据仓库可以在资源类中运行更多的查询。Since queries get the same memory allocation regardless of the performance level, scaling out the data warehouse allows more queries to run within a resource class. 如果数据量已知且保持不变,则最理想的选择是静态资源类。Static resource classes are ideal if the data volume is known and constant.

静态资源类是使用以下预定义的数据库角色实现的:The static resource classes are implemented with these pre-defined database roles:

  • staticrc10staticrc10
  • staticrc20staticrc20
  • staticrc30staticrc30
  • staticrc40staticrc40
  • staticrc50staticrc50
  • staticrc60staticrc60
  • staticrc70staticrc70
  • staticrc80staticrc80

动态资源类Dynamic resource classes

动态资源类根据当前服务级别分配可变内存量。Dynamic Resource Classes allocate a variable amount of memory depending on the current service level. 静态资源类适用于较高的并发性和静态数据量,而动态资源类更适合数据量会增长或有所变化的情况。While static resource classes are beneficial for higher concurrency and static data volumes, dynamic resource classes are better suited for a growing or variable amount of data. 提升到更高的服务级别时,查询可自动获得更多的内存。When you scale up to a larger service level, your queries automatically get more memory.

动态资源类是使用以下预定义的数据库角色实现的:The dynamic resource classes are implemented with these pre-defined database roles:

  • smallrcsmallrc
  • mediumrcmediumrc
  • largerclargerc
  • xlargercxlargerc

每个资源类的内存分配如下所示。The memory allocation for each resource class is as follows.

服务级别Service Level smallrcsmallrc mediumrcmediumrc largerclargerc xlargercxlargerc
DW100cDW100c 25%25% 25%25% 25%25% 70%70%
DW200cDW200c 12.5%12.5% 12.5%12.5% 22%22% 70%70%
DW300cDW300c 8%8% 10%10% 22%22% 70%70%
DW400cDW400c 6.25%6.25% 10%10% 22%22% 70%70%
DW500cDW500c 5%5% 10%10% 22%22% 70%70%
DW1000c 到DW1000c to
DW30000cDW30000c
3%3% 10%10% 22%22% 70%70%

默认资源类Default resource class

默认情况下,每个用户都是动态资源类 (smallrc) 的成员。By default, each user is a member of the dynamic resource class smallrc.

服务管理员的资源类在 smallrc 中是固定的,不可更改。The resource class of the service administrator is fixed at smallrc and cannot be changed. 服务管理员是预配过程中创建的用户。The service administrator is the user created during the provisioning process. 使用新服务器新建 Synapse SQL 池时,此上下文中的服务管理员是为“服务器管理员登录名”指定的登录名。The service administrator in this context is the login specified for the "Server admin login" when creating a new Synapse SQL pool with a new server.

备注

定义为 Active Directory 管理员的用户或组也是服务管理员。Users or groups defined as Active Directory admin are also service administrators.

资源类操作Resource class operations

资源类旨在改进数据管理和操作活动设计的性能。Resource classes are designed to improve performance for data management and manipulation activities. 复杂的查询在大型资源类下运行也能受益。Complex queries can also benefit from running under a large resource class. 例如,如果资源类足够大,使查询能够在内存中执行,则针对大型联接和排序的查询的性能可以得到改善。For example, query performance for large joins and sorts can improve when the resource class is large enough to enable the query to execute in memory.

资源类控制的操作Operations governed by resource classes

以下操作由资源类控制:These operations are governed by resource classes:

  • INSERT-SELECT、UPDATE、DELETEINSERT-SELECT, UPDATE, DELETE
  • SELECT(查询用户表时)SELECT (when querying user tables)
  • ALTER INDEX - REBUILD 或 REORGANIZEALTER INDEX - REBUILD or REORGANIZE
  • ALTER TABLE REBUILDALTER TABLE REBUILD
  • CREATE INDEXCREATE INDEX
  • CREATE CLUSTERED COLUMNSTORE INDEXCREATE CLUSTERED COLUMNSTORE INDEX
  • CREATE TABLE AS SELECT (CTAS)CREATE TABLE AS SELECT (CTAS)
  • 数据加载Data loading
  • 数据移动服务 (DMS) 执行的数据移动操作Data movement operations conducted by the Data Movement Service (DMS)

备注

针对动态管理视图 (DMV) 或其他系统视图执行的 SELECT 语句不受任何并发限制的约束。SELECT statements on dynamic management views (DMVs) or other system views are not governed by any of the concurrency limits. 用户可以对系统进行监视,而不用考虑在系统中执行的查询的数目。You can monitor the system regardless of the number of queries executing on it.

资源类不会控制的操作Operations not governed by resource classes

某些查询始终在 smallrc 资源类中运行,即使用户是更大资源类的成员。Some queries always run in the smallrc resource class even though the user is a member of a larger resource class. 这些例外的查询不会计入并发性限制。These exempt queries do not count towards the concurrency limit. 例如,如果并发性限制为 16,则许多用户都可以从系统视图中进行选择,而不影响可用的并发性槽位。For example, if the concurrency limit is 16, many users can be selecting from system views without impacting the available concurrency slots.

以下语句属于资源类的例外情况,始终在 smallrc 中运行:The following statements are exempt from resource classes and always run in smallrc:

  • CREATE 或 DROP TABLECREATE or DROP TABLE
  • ALTER TABLE ...SWITCH、SPLIT 或 MERGE PARTITIONALTER TABLE ... SWITCH, SPLIT, or MERGE PARTITION
  • ALTER INDEX DISABLEALTER INDEX DISABLE
  • DROP INDEXDROP INDEX
  • CREATE、UPDATE 或 DROP STATISTICSCREATE, UPDATE, or DROP STATISTICS
  • TRUNCATE TABLETRUNCATE TABLE
  • ALTER AUTHORIZATIONALTER AUTHORIZATION
  • CREATE LOGINCREATE LOGIN
  • CREATE、ALTER 或 DROP USERCREATE, ALTER, or DROP USER
  • CREATE、ALTER 或 DROP PROCEDURECREATE, ALTER, or DROP PROCEDURE
  • CREATE 或 DROP VIEWCREATE or DROP VIEW
  • INSERT VALUESINSERT VALUES
  • SELECT(从系统视图和 DMV)SELECT from system views and DMVs
  • EXPLAINEXPLAIN
  • DBCCDBCC

并发槽位Concurrency slots

使用并发槽位可以方便地跟踪可用于执行查询的资源。Concurrency slots are a convenient way to track the resources available for query execution. 这些槽位就像是演唱会的门票,因为席位有限,必须预订。They are like tickets that you purchase to reserve seats at a concert because seating is limited. 每个数据仓库的并发性槽位总数由服务级别决定。The total number of concurrency slots per data warehouse is determined by the service level. 在查询可以开始执行之前,必须预留足够的并发槽位。Before a query can start executing, it must be able to reserve enough concurrency slots. 查询完成后,会释放其并发槽位。When a query completes, it releases its concurrency slots.

  • 使用 10 个并发槽位运行的查询可以访问的计算资源,是使用 2 个并发槽位运行的查询的 5 倍。A query running with 10 concurrency slots can access 5 times more compute resources than a query running with 2 concurrency slots.
  • 如果每个查询需要 10 个并发槽位并且有 40 个并发槽位,则只有 4 个查询可以并发运行。If each query requires 10 concurrency slots and there are 40 concurrency slots, then only 4 queries can run concurrently.

只有受资源控制的查询消耗并发槽位。Only resource governed queries consume concurrency slots. 系统查询和一些不重要的查询不消耗任何槽位。System queries and some trivial queries don't consume any slots. 消耗的确切并发槽位数由查询的资源类决定。The exact number of concurrency slots consumed is determined by the query's resource class.

查看资源类View the resource classes

资源类实现为预定义的数据库角色。Resource classes are implemented as pre-defined database roles. 有两种类型的资源类:动态和静态。There are two types of resource classes: dynamic and static. 若要查看资源类列表,请使用以下查询:To view a list of the resource classes, use the following query:

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

更改用户的资源类Change a user's resource class

资源类是通过将用户分配到数据库角色来实现的。Resource classes are implemented by assigning users to database roles. 当用户运行查询时,该查询将使用该用户的资源类来运行。When a user runs a query, the query runs with the user's resource class. 例如,如果某个用户是 staticrc10 数据库角色的成员,则其查询将使用较小的内存量来运行。For example, if a user is a member of the staticrc10 database role, their queries run with small amounts of memory. 如果某个数据库用户是 xlargerc 或 staticrc80 数据库角色的成员,则其查询将使用较大的内存量来运行。If a database user is a member of the xlargerc or staticrc80 database roles, their queries run with large amounts of memory.

若要提高用户的资源类,请使用 sp_addrolemember 将用户添加到大型资源类的数据库角色。To increase a user's resource class, use sp_addrolemember to add the user to a database role of a large resource class. 以下代码将用户添加到 largerc 数据库角色。The below code adds a user to the largerc database role. 每个请求获取 22% 的系统内存。Each request gets 22% of the system memory.

EXEC sp_addrolemember 'largerc', 'loaduser';

若要降低资源类,可使用 sp_droprolememberTo decrease the resource class, use sp_droprolemember. 如果“loaduser”不是成员或任何其他资源类,则会转到具有 3% 内存授予的默认 smallrc 资源类。If 'loaduser' is not a member or any other resource classes, they go into the default smallrc resource class with a 3% memory grant.

EXEC sp_droprolemember 'largerc', 'loaduser';

资源类优先顺序Resource class precedence

用户可以是多个资源类的成员。Users can be members of multiple resource classes. 如果用户属于多个资源类:When a user belongs to more than one resource class:

  • 动态资源类优先于静态资源类。Dynamic resource classes take precedence over static resource classes. 例如,如果某个用户是 mediumrc(动态)和 staticrc80(静态)的成员,则查询将使用 mediumrc 来运行。For example, if a user is a member of both mediumrc(dynamic) and staticrc80 (static), queries run with mediumrc.
  • 更大的资源类优先于更小的资源类。Larger resource classes take precedence over smaller resource classes. 例如,如果某个用户是 mediumrc 和 largerc 的成员,则查询将使用 largerc 来运行。For example, if a user is a member of mediumrc and largerc, queries run with largerc. 同样,如果某个用户是 staticrc20 和 statirc80 的成员,则查询将使用 staticrc80 资源分配来运行。Likewise, if a user is a member of both staticrc20 and statirc80, queries run with staticrc80 resource allocations.

建议Recommendations

备注

请考虑利用工作负荷管理功能(工作负荷隔离分类重要性),以更好地控制工作负荷和可预测的性能。Consider leveraging workload management capabilities (workload isolation, classification and importance) for more control over your workload and predictable performance.

我们建议创建一个专门用于运行特定类型的查询或负载操作的用户。We recommend creating a user that is dedicated to running a specific type of query or load operation. 为该用户提供永久性的资源类,而不是频繁更改资源类。Give that user a permanent resource class instead of changing the resource class on a frequent basis. 静态资源类对工作负荷提供的整体控制度更高,因此,我们建议先使用静态资源类,然后再考虑动态资源类。Static resource classes afford greater overall control on the workload, so we suggest using static resource classes before considering dynamic resource classes.

负载用户的资源类Resource classes for load users

CREATE TABLE 默认使用聚集列存储索引。CREATE TABLE uses clustered columnstore indexes by default. 将数据压缩成列存储索引是一种内存密集型操作,内存压力可能会降低索引质量。Compressing data into a columnstore index is a memory-intensive operation, and memory pressure can reduce the index quality. 加载数据时,内存压力可能导致需要更高的资源类。Memory pressure can lead to needing a higher resource class when loading data. 为确保负载具有足够的内存,可以创建一个专门用于运行负载的用户,并将该用户分配到较高的资源类。To ensure loads have enough memory, you can create a user that is designated for running loads and assign that user to a higher resource class.

有效处理负载所需的内存量取决于所加载表的性质以及数据大小。The memory needed to process loads efficiently depends on the nature of the table loaded and the data size. 有关内存要求的详细信息,请参阅最大程度地提高行组的质量For more information on memory requirements, see Maximizing rowgroup quality.

确定内存要求后,选择是要将负载用户分配到静态还是动态资源类。Once you have determined the memory requirement, choose whether to assign the load user to a static or dynamic resource class.

  • 当表的内存要求在特定的范围以内时,可使用静态资源类。Use a static resource class when table memory requirements fall within a specific range. 负载将使用适当的内存来运行。Loads run with appropriate memory. 扩展数据仓库时,负载不需要更多的内存。When you scale the data warehouse, the loads do not need more memory. 使用静态资源类时,内存分配会保持恒定。By using a static resource class, the memory allocations stay constant. 这种一致性可以节省内存,并允许更多的查询并发运行。This consistency conserves memory and allows more queries to run concurrently. 我们建议在新解决方案中先使用静态资源类,因为这些资源类提供更高的控制度。We recommend that new solutions use the static resource classes first as these provide greater control.
  • 当表的内存要求差别很大时,可使用动态资源类。Use a dynamic resource class when table memory requirements vary widely. 负载所需的内存量可能超过了当前 DWU 或者 cDWU 级别能够提供的内存量。Loads might require more memory than the current DWU or cDWU level provides. 扩展数据仓库可为负载操作添加更多的内存,从而使负载的执行速度加快。Scaling the data warehouse adds more memory to load operations, which allows loads to perform faster.

查询的资源类Resource classes for queries

有些查询是计算密集型的,有些则不是。Some queries are compute-intensive and some aren't.

  • 当查询较为复杂但不需要高并发性时,可以选择动态资源类。Choose a dynamic resource class when queries are complex, but don't need high concurrency. 例如,生成每日或每周报告只是偶尔需要资源。For example, generating daily or weekly reports is an occasional need for resources. 如果报告要处理大量的数据,则扩展数据仓库可将更多的内存提供给用户的现有资源类。If the reports are processing large amounts of data, scaling the data warehouse provides more memory to the user's existing resource class.
  • 当一天中的资源预期有变化时,可选择静态资源类。Choose a static resource class when resource expectations vary throughout the day. 例如,如果有许多人查询数据仓库,则静态资源类就很合适。For example, a static resource class works well when the data warehouse is queried by many people. 缩放数据仓库时,分配给用户的内存量不会变化。When scaling the data warehouse, the amount of memory allocated to the user doesn't change. 因此,可在系统中同时执行多个查询。Consequently, more queries can be executed in parallel on the system.

适当的内存授予取决于许多因素,例如,查询的数据量、表架构的性质,以及各种联接、选择和组合谓词。Proper memory grants depend on many factors, such as the amount of data queried, the nature of the table schemas, and various joins, select, and group predicates. 一般而言,分配更多的内存可让查询更快完成,但同时会降低整体并发性。In general, allocating more memory allows queries to complete faster, but reduces the overall concurrency. 如果并发性不是个问题,则过度分配内存不会给吞吐量带来坏处。If concurrency is not an issue, over-allocating memory does not harm throughput.

若要优化性能,可使用不同的资源类。To tune performance, use different resource classes. 下一部分提供了一个可以帮助推算最佳资源类的存储过程。The next section gives a stored procedure that helps you figure out the best resource class.

用于找出最佳资源类的示例代码Example code for finding the best resource class

可以使用以下指定的存储过程,根据给定的 SLO 推算每个资源类的并发性和内存授予,以及根据给定的资源类推算对非分区 CCI 表执行内存密集型 CCI 操作时可用的最佳资源类:You can use the following specified stored procedure to figure out concurrency and memory grant per resource class at a given SLO and the best resource class for memory intensive CCI operations on non-partitioned CCI table at a given resource class:

下面是此存储过程的用途:Here's the purpose of this stored procedure:

  1. 用于查看每个资源类的、根据给定 SLO 推算的并发性和内存授予。To see the concurrency and memory grant per resource class at a given SLO. 如此示例中所示,用户需要为架构和表名提供 NULL。User needs to provide NULL for both schema and tablename as shown in this example.
  2. 用于查看根据给定的资源类推算对非分区 CCI 表执行内存密集型 CCI 操作(加载、复制表、重建索引等)时可用的最佳资源类。To see the best resource class for the memory-intensive CCI operations (load, copy table, rebuild index, etc.) on non-partitioned CCI table at a given resource class. 该存储过程使用表架构来找出所需的内存授予。The stored proc uses table schema to find out the required memory grant.

依赖关系和限制Dependencies & Restrictions

  • 此存储过程并不旨在计算分区 CCI 表的内存要求。This stored procedure isn't designed to calculate the memory requirement for a partitioned cci table.
  • 此存储过程不会针对 CTAS/INSERT-SELECT 的 SELECT 部分考虑内存要求,而是假设它是一个 SELECT。This stored procedure doesn't take memory requirements into account for the SELECT part of CTAS/INSERT-SELECT and assumes it's a SELECT.
  • 此存储过程使用其创建时所在的会话中提供的临时表。This stored procedure uses a temp table, which is available in the session where this stored procedure was created.
  • 此存储过程依赖于当前的供应值(例如硬件配置、DMS 配置),如果其中的任何值发生更改,则此存储过程将无法正常工作。This stored procedure depends on the current offerings (for example, hardware configuration, DMS config), and if any of that changes then this stored proc won't work correctly.
  • 此存储过程依赖于现有的并发限制选项,如果这些选项发生更改,则此存储过程将无法正常工作。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.

备注

如果结合提供的参数执行存储过程后未获得输出,则可能存在两种情况。If you are not getting output after executing stored procedure with parameters provided, then there could be two cases.

  1. DW 参数包含无效的 SLO 值Either DW Parameter contains an invalid SLO value
  2. 或者,针对表执行的 CCI 操作没有匹配的资源类。Or, there is no matching resource class for the CCI operation on the table.

例如,在 DW100c 级别,可用的最高内存授予是 1 GB。如果表架构很宽,就会超过 1 GB 的要求。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.

用例Usage example

语法:Syntax:
EXEC dbo.prc_workload_management_by_DWU @DWU VARCHAR(7), @SCHEMA_NAME VARCHAR(128), @TABLE_NAME VARCHAR(128)

  1. @DWU: 提供 NULL 参数以从 DW DB 中提取当前 DWU,或者以“DW100c”的形式提供任何受支持的 DWU@DWU: Either provide a NULL parameter to extract the current DWU from the DW DB or provide any supported DWU in the form of 'DW100c'
  2. @SCHEMA_NAME: 提供表的架构名称@SCHEMA_NAME: Provide a schema name of the table
  3. @TABLE_NAME: 提供相关的表名@TABLE_NAME: Provide a table name of the interest

有关执行此存储过程的示例:Examples executing this stored 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;  

以下语句创建前面示例中所用的 Table1。The following statement creates Table1 that is used in the preceding examples. 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(7),
 @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

后续步骤Next steps

有关如何管理数据库用户和安全性的详细信息,请参阅在 Synapse SQL 中保护数据库For more information about managing database users and security, see Secure a database in Synapse SQL. 有关较大资源类如何改进聚集列存储索引质量的详细信息,请参阅列存储压缩的内存优化For more information about how larger resource classes can improve clustered columnstore index quality, see Memory optimizations for columnstore compression.