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.

リソース クラスは 2 種類あります。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

静的リソース クラスは、現在のパフォーマンス レベルに関係なく、同じメモリ量を割り当てます。これは Data Warehouse ユニットで測定されます。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
  • Data Movement Service (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
  • システム ビューおよび DMV からの SELECTSELECT 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. リソース クラスには、動的と静的の 2 種類があります。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_droprolemember を使用します。To decrease the resource class, use sp_droprolemember. 'loaduser' が他のどのリソース クラスのメンバーでもない場合、既定の smallrc リソース クラスに、3% のメモリを割り当てられて追加されます。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. ユーザーが 1 つ以上のリソース クラスに属す場合: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.

RecommendationsRecommendations

注意

ワークロードと予測可能なパフォーマンスをより詳細に制御するために、ワークロード管理機能 (ワークロードの分離分類および重要度) を活用することを検討してください。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.

注意

指定されたパラメーターを使用してストアド プロシージャを実行しても出力が得られない場合は、次の 2 つの理由が考えられます。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:DW DB から現在の DWU を抽出するため NULL パラメーターを指定するか、'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(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

次のステップ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.