SQL Data Warehouse での同時実行とワークロード管理Concurrency and workload management in SQL Data Warehouse

Microsoft Azure SQL Data Warehouse では、予測可能なパフォーマンスを大規模に実現するために、メモリと CPU の優先順位付けのようなリソース割り当てに加えて、同時実行レベルを制御できるようになっています。To deliver predictable performance at scale, Microsoft Azure SQL Data Warehouse helps you control concurrency levels and resource allocations like memory and CPU prioritization. この記事では、同時実行とワークロード管理の機能がどのように実装されたか、そしてこれらの機能をデータ ウェアハウスでどのように制御できるかについて説明しながら、両機能の概念を紹介します。This article introduces you to the concepts of concurrency and workload management, explaining how both features have been implemented and how you can control them in your data warehouse. SQL Data Warehouse のワークロード管理は、マルチユーザー環境をサポートすることを目的としています。SQL Data Warehouse workload management is intended to help you support multi-user environments. マルチテナント ワークロードは想定されていません。It is not intended for multi-tenant workloads.

同時実行の制限Concurrency limits

SQL Data Warehouse では、最大 1,024 個の同時接続が可能です。SQL Data Warehouse allows up to 1,024 concurrent connections. 1,024 のすべての接続でクエリを同時に送信することができます。All 1,024 connections can submit queries concurrently. ただし、スループットを最適化する目的で、各クエリに与えられるメモリ許可が最小限になるようにするために、一部のクエリがキューに配置される場合があります。However, to optimize throughput, SQL Data Warehouse may queue some queries to ensure that each query receives a minimal memory grant. クエリの実行時にキューに配置されます。Queuing occurs at query execution time. 同時実行の制限に達したときにクエリをキューに配置することによって、アクティブなクエリが絶対に必要なメモリ リソースに必ずアクセスできるようになるため、全体のスループットが向上します。By queuing queries when concurrency limits are reached, SQL Data Warehouse can increase total throughput by ensuring that active queries get access to critically needed memory resources.

同時実行の制限は、同時実行クエリ同時実行スロットの 2 つの概念によって管理されます。Concurrency limits are governed by two concepts: concurrent queries and concurrency slots. クエリは、クエリの同時実行の制限内および同時実行スロット割り当て内で実行する必要があります。For a query to execute, it must execute within both the query concurrency limit and the concurrency slot allocation.

  • 同時実行クエリとは、同時に実行されるクエリを指します。Concurrent queries are the queries executing at the same time. SQL Data Warehouse では、大きな DWU サイズで最大 32 個の同時実行クエリをサポートしています。SQL Data Warehouse supports up to 32 concurrent queries on the larger DWU sizes.
  • 同時実行スロットは、DWU に基づいて割り当てられます。Concurrency slots are allocated based on DWU. 100 DWU ごとに、4 つの同時実行スロットが提供されます。Each 100 DWU provides 4 concurrency slots. たとえば、DW100 では 4 つの同時実行スロットが割り当てられ、DW1000 では 40 個の同時実行スロットが割り当てられます。For example, a DW100 allocates 4 concurrency slots and DW1000 allocates 40. 各クエリは、クエリの リソース クラス に応じて 1 つまたは複数の同時実行スロットを使用します。Each query consumes one or more concurrency slots, dependent on the resource class of the query. smallrc リソース クラスで実行されているクエリは、1 つの同時実行スロットを使用します。Queries running in the smallrc resource class consume one concurrency slot. より高いリソース クラスで実行されているクエリは、複数の同時実行スロットを使用します。Queries running in a higher resource class consume more concurrency slots.

次の表に、各 DWU サイズでの同時実行クエリと同時実行スロットの両方の制限を示します。The following table describes the limits for both concurrent queries and concurrency slots at the various DWU sizes.

同時実行の制限Concurrency limits

DWUDWU 同時クエリの最大数Max concurrent queries 割り当てられる同時実行スロット数Concurrency slots allocated
DW100DW100 44 44
DW200DW200 88 88
DW300DW300 1212 1212
DW400DW400 1616 1616
DW500DW500 2020 2020
DW600DW600 2424 2424
DW1000DW1000 3232 4040
DW1200DW1200 3232 4848
DW1500DW1500 3232 6060
DW2000DW2000 3232 8080
DW3000DW3000 3232 120120
DW6000DW6000 3232 240240

これらのいずれかのしきい値に達すると、新しいクエリはキューに配置されます。キューに配置されたクエリは、先入れ先出し方式で実行されます。When one of these thresholds is met, new queries are queued and executed on a first-in, first-out basis. クエリが完了してクエリとスロットの数が制限値を下回ると、キューに配置されたクエリは解放されます。As a queries finishes and the number of queries and slots falls below the limits, queued queries are released.

注意

SELECT クエリは、同時実行の制限を受けることはありません。Select queries executing exclusively on dynamic management views (DMVs) or catalog views are not governed by any of the concurrency limits. ユーザーは、システムで実行されるクエリの数にとらわれずにシステムを監視できます。You can monitor the system regardless of the number of queries executing on it.

リソース クラスResource classes

リソース クラスにより、クエリに対するメモリ割り当てと CPU サイクルの制御が行われます。Resource classes help you control memory allocation and CPU cycles given to a query. 2 つの種類のリソース クラスをデータベース ロールの形式でユーザーに割り当てることができます。You can assign two types of resource classes to a user in the form of database roles. 2 つの種類のリソース クラスは次のとおりです。The two types of resource classes are as follows:

  1. 動的なリソースのクラス (smallrc、mediumrc、largerc、xlargerc) は、現在の DWU に応じてメモリの量を割り当てます。Dynamic Resource Classes (smallrc, mediumrc, largerc, xlargerc) allocate a variable amount of memory depending on the current DWU. つまり、DWU をスケールアップすると、クエリに対しより多くのメモリが自動的に取得されます。This means that when you scale up to a larger DWU, your queries automatically get more memory.
  2. 静的リソース クラス (staticrc10、staticrc20、staticrc30、staticrc40、staticrc50、staticrc60、staticrc70、staticrc80) では、現在の DWU に関係なくの同じ量のメモリが割り当てられます (DWU 自体に十分なメモリが割り当てられていることを前提として)。Static Resource Classes (staticrc10, staticrc20, staticrc30, staticrc40, staticrc50, staticrc60, staticrc70, staticrc80) allocate the same amount of memory regardless of the current DWU (provided that the DWU itself has enough memory). つまり、より大きな DWU では、各リソース クラスで同時により多くのクエリを実行できます。This means that on larger DWUs, you can run more queries in each resource class concurrently.

smallrc および staticrc10 のユーザーにはより少ないメモリが割り当てられ、その結果、より多くの同時実行が可能になります。Users in smallrc and staticrc10 are given a smaller amount of memory and can take advantage of higher concurrency. これに対し、xlargerc または staticrc80 に割り当てられたユーザーには多くのメモリが与えられるため、同時実行が許可されるクエリの数は少なくなります。In contrast, users assigned to xlargerc or staticrc80 are given large amounts of memory, and therefore fewer of their queries can run concurrently.

既定では、各ユーザーは小規模リソース クラス smallrc のメンバーです。By default, each user is a member of the small resource class, smallrc. リソース クラスのサイズを大きくするにはプロシージャ sp_addrolemember を使用し、リソース クラスのサイズを小さくするには sp_droprolemember を使用します。The procedure sp_addrolemember is used to increase the resource class, and sp_droprolemember is used to decrease the resource class. たとえば、次のコマンドは、ロード ユーザーのリソース クラスをサイズの大きな largerc に割り当てます。For example, this command would increase the resource class of loaduser to largerc:

EXEC sp_addrolemember 'largerc', 'loaduser'

リソース クラスの割り当てを受け入れないクエリQueries that do not honor resource classes

クエリの中には、多くのメモリを割り当ててもメリットがない種類のクエリもあります。There are a few types of queries that do not benefit from a larger memory allocation. リソース クラスの割り当ては無視されますが、その代わり、これらのクエリは常に小規模リソース クラスで実行されます。The system ignores their resource class allocation and always run these queries in the small resource class instead. これらのクエリが常に小規模リソース クラスで実行されると、同時実行スロットの空きが足りなくてもクエリを実行できます。このため、必要以上にスロットを消費することはなくなります。If these queries always run in the small resource class, they can run when concurrency slots are under pressure and they won't consume more slots than needed. 詳細については、「リソース クラスの例外」をご覧ください。See Resource class exceptions for more information.

リソース クラスの割り当てに関する詳細情報Details on resource class assignment

リソース クラスに関する詳細をもう少し以下に示します。A few more details on resource class:

  • ロールの変更 " アクセス許可が必要です。Alter role permission is required to change the resource class of a user.
  • ユーザーを 1 つ以上上のリソース クラスに追加できますが、動的リソース クラスは、静的リソース クラスよりも優先されます。Although you can add a user to one or more of the higher resource classes, dynamic resource classes take precedence over static resource classes. つまり、ユーザーが mediumrc (動的) と staticrc80 (静的) の両方に割り当てられている場合、実際に割り当てられるリソース クラスは、mediumrc になります。That is, if a user is assigned to both mediumrc(dynamic) and staticrc80(static), mediumrc is the resource class that is honored.
    • 特定の種類のリソース クラスがユーザーに複数 (2 つ以上の動的リソース クラスまたは 1 つ以上の静的リソース クラス) 割り当てられた場合、最上位のリソース クラスが割り当てられます。When a user is assigned to more than one resource class in a specific resource class type (more than one dynamic resource class or more than one static resource class), the highest resource class is honored. つまり、mediumrc and largerc の両方がユーザーに割り当てられた場合、より上位のリソース クラス (largerc) が使用されます。That is, if a user is assigned to both mediumrc and largerc, the higher resource class (largerc) is honored. ユーザーが staticrc20statirc80 の両方に割り当てられている場合、staticrc80 が使用されます。And if a user is assigned to both staticrc20 and statirc80, staticrc80 is honored.
  • システム管理ユーザーのリソース クラスは変更できません。The resource class of the system administrative user cannot be changed.

詳細な例は、この記事の最後にある「 ユーザー リソース クラスの変更例」を参照してください。For a detailed example, see Changing user resource class example.

メモリの割り当てMemory allocation

ユーザーのリソース クラスを引き上げることには長所と短所があります。There are pros and cons to increasing a user's resource class. ユーザーのリソース クラスを引き上げると、そのユーザーのクエリで利用可能なメモリが増え、クエリの実行速度が上がります。Increasing a resource class for a user, gives their queries access to more memory, which may mean queries execute faster. その一方で、リソース クラスを引き上げると、同時実行可能なクエリの数が減ります。However, higher resource classes also reduce the number of concurrent queries that can run. これは、大量のメモリを 1 つのクエリに割り当てるか、同様にメモリの割り当てが必要な他のクエリの同時実行を許可するかのトレードオフです。This is the trade-off between allocating large amounts of memory to a single query or allowing other queries, which also need memory allocations, to run concurrently. あるユーザーにクエリのメモリが大量に割り当てられている場合、他のユーザーがクエリを実行するために同じメモリにアクセスすることはできなくなります。If one user is given high allocations of memory for a query, other users will not have access to that same memory to run a query.

次の表に、DWU とリソース クラスごとに各ディストリビューションに割り当てられるメモリを示します。The following table maps the memory allocated to each distribution by DWU and resource class.

動的なリソース クラスの配布あたりのメモリ割り当て (MB)Memory allocations per distribution for dynamic resource classes (MB)

DWUDWU smallrcsmallrc mediumrcmediumrc largerclargerc xlargercxlargerc
DW100DW100 100100 100100 200200 400400
DW200DW200 100100 200200 400400 800800
DW300DW300 100100 200200 400400 800800
DW400DW400 100100 400400 800800 1,6001,600
DW500DW500 100100 400400 800800 1,6001,600
DW600DW600 100100 400400 800800 1,6001,600
DW1000DW1000 100100 800800 1,6001,600 3,2003,200
DW1200DW1200 100100 800800 1,6001,600 3,2003,200
DW1500DW1500 100100 800800 1,6001,600 3,2003,200
DW2000DW2000 100100 1,6001,600 3,2003,200 6,4006,400
DW3000DW3000 100100 1,6001,600 3,2003,200 6,4006,400
DW6000DW6000 100100 3,2003,200 6,4006,400 12,80012,800

次の表に、DWU と静的リソース クラスごとに各ディストリビューションに割り当てられるメモリを示します。The following table maps the memory allocated to each distribution by DWU and static resource class. リソースのクラスが高くなるほど、グローバル DWU 制限に従ってメモリ量が減少することに注意してください。Note that the higher resource classes have their memory reduced to honor the global DWU limits.

静的なリソース クラスの配布あたりのメモリ割り当て (MB)Memory allocations per distribution for static resource classes (MB)

DWUDWU staticrc10staticrc10 staticrc20staticrc20 staticrc30staticrc30 staticrc40staticrc40 staticrc50staticrc50 staticrc60staticrc60 staticrc70staticrc70 staticrc80staticrc80
DW100DW100 100100 200200 400400 400400 400400 400400 400400 400400
DW200DW200 100100 200200 400400 800800 800800 800800 800800 800800
DW300DW300 100100 200200 400400 800800 800800 800800 800800 800800
DW400DW400 100100 200200 400400 800800 1,6001,600 1,6001,600 1,6001,600 1,6001,600
DW500DW500 100100 200200 400400 800800 1,6001,600 1,6001,600 1,6001,600 1,6001,600
DW600DW600 100100 200200 400400 800800 1,6001,600 1,6001,600 1,6001,600 1,6001,600
DW1000DW1000 100100 200200 400400 800800 1,6001,600 3,2003,200 3,2003,200 3,2003,200
DW1200DW1200 100100 200200 400400 800800 1,6001,600 3,2003,200 3,2003,200 3,2003,200
DW1500DW1500 100100 200200 400400 800800 1,6001,600 3,2003,200 3,2003,200 3,2003,200
DW2000DW2000 100100 200200 400400 800800 1,6001,600 3,2003,200 6,4006,400 6,4006,400
DW3000DW3000 100100 200200 400400 800800 1,6001,600 3,2003,200 6,4006,400 6,4006,400
DW6000DW6000 100100 200200 400400 800800 1,6001,600 3,2003,200 6,4006,400 12,80012,800

この表から、xlargerc リソース クラスの DW2000 で実行されているクエリは、60 個の分散データベースそれぞれにある 6,400 MB のメモリにアクセスすることがわかります。From the preceding table, you can see that a query running on a DW2000 in the xlargerc resource class would have access to 6,400 MB of memory within each of the 60 distributed databases. SQL Data Warehouse には、60 個のディストリビューションがあります。In SQL Data Warehouse, there are 60 distributions. そのため、特定のリソース クラスでのクエリの合計メモリ割り当て量を計算するには、上記の値に 60 を掛ける必要があります。Therefore, to calculate the total memory allocation for a query in a given resource class, the above values should be multiplied by 60.

システム全体のメモリ割り当て (GB)Memory allocations system-wide (GB)

DWUDWU smallrcsmallrc mediumrcmediumrc largerclargerc xlargercxlargerc
DW100DW100 66 66 1212 2323
DW200DW200 66 1212 2323 4747
DW300DW300 66 1212 2323 4747
DW400DW400 66 2323 4747 9494
DW500DW500 66 2323 4747 9494
DW600DW600 66 2323 4747 9494
DW1000DW1000 66 4747 9494 188188
DW1200DW1200 66 4747 9494 188188
DW1500DW1500 66 4747 9494 188188
DW2000DW2000 66 9494 188188 375375
DW3000DW3000 66 9494 188188 375375
DW6000DW6000 66 188188 375375 750750

このシステム全体のメモリ割り当ての表から、xlargerc リソース クラスの DW2000 で実行されているクエリには、SQL Data Warehouse 全体で合計 375 GB のメモリが割り当てられることがわかります (6,400 MB * 60 ディストリビューション / 1,024 (GB 単位))。From this table of system-wide memory allocations, you can see that a query running on a DW2000 in the xlargerc resource class is allocated a total of 375 GB of memory (6,400 MB * 60 distributions / 1,024 to convert to GB) over the entirety of your SQL Data Warehouse.

静的リソース クラスに同じ計算が適用されます。The same calculation applies to static resource classes.

使用される同時実行スロット数Concurrency slot consumption

より上位のリソース クラスで実行されるクエリには、より多くのメモリが与えられます。SQL Data Warehouse grants more memory to queries running in higher resource classes. メモリは、固定のリソースです。Memory is a fixed resource. そのため、1 つのクエリに割り当てられるメモリが多くなるほど、同時に実行できるクエリの数が少なくなります。Therefore, the more memory allocated per query, the fewer concurrent queries can execute. 次の表で、これまでに説明したすべての概念を 1 つにまとめ、各 DWU で利用可能な同時実行スロットの数と各リソース クラスで使用されるスロットの数を示します。The following table reiterates all of the previous concepts in a single view that shows the number of concurrency slots available by DWU and the slots consumed by each resource class.

動的リソース クラスの同時実行スロットの割り当てと使用量Allocation and consumption of concurrency slots for dynamic resource classes

DWUDWU 同時クエリの最大数Maximum concurrent queries 割り当てられる同時実行スロット数Concurrency slots allocated smallrc で使用されるスロット数Slots used by smallrc mediumrc で使用されるスロット数Slots used by mediumrc largerc で使用されるスロット数Slots used by largerc xlargerc で使用されるスロット数Slots used by xlargerc
DW100DW100 44 44 11 11 22 44
DW200DW200 88 88 11 22 44 88
DW300DW300 1212 1212 11 22 44 88
DW400DW400 1616 1616 11 44 88 1616
DW500DW500 2020 2020 11 44 88 1616
DW600DW600 2424 2424 11 44 88 1616
DW1000DW1000 3232 4040 11 88 1616 3232
DW1200DW1200 3232 4848 11 88 1616 3232
DW1500DW1500 3232 6060 11 88 1616 3232
DW2000DW2000 3232 8080 11 1616 3232 6464
DW3000DW3000 3232 120120 11 1616 3232 6464
DW6000DW6000 3232 240240 11 3232 6464 128128

静的リソース クラスの同時実行スロットの割り当てと使用量Allocation and consumption of concurrency slots for static resource classes

DWUDWU 同時クエリの最大数Maximum concurrent queries 割り当てられる同時実行スロット数Concurrency slots allocated staticrc10staticrc10 staticrc20staticrc20 staticrc30staticrc30 staticrc40staticrc40 staticrc50staticrc50 staticrc60staticrc60 staticrc70staticrc70 staticrc80staticrc80
DW100DW100 44 44 11 22 44 44 44 44 44 44
DW200DW200 88 88 11 22 44 88 88 88 88 88
DW300DW300 1212 1212 11 22 44 88 88 88 88 88
DW400DW400 1616 1616 11 22 44 88 1616 1616 1616 1616
DW500DW500 2020 2020 11 22 44 88 1616 1616 1616 1616
DW600DW600 2424 2424 11 22 44 88 1616 1616 1616 1616
DW1000DW1000 3232 4040 11 22 44 88 1616 3232 3232 3232
DW1200DW1200 3232 4848 11 22 44 88 1616 3232 3232 3232
DW1500DW1500 3232 6060 11 22 44 88 1616 3232 3232 3232
DW2000DW2000 3232 8080 11 22 44 88 1616 3232 6464 6464
DW3000DW3000 3232 120120 11 22 44 88 1616 3232 6464 6464
DW6000DW6000 3232 240240 11 22 44 88 1616 3232 6464 128128

この表から、DW1000 として実行されている SQL Data Warehouse では、最大 32 個の同時実行クエリと合計 40 個の同時実行スロットが提供されることが分かります。From these tables, you can see that SQL Data Warehouse running as DW1000 allocates a maximum of 32 concurrent queries and a total of 40 concurrency slots. すべてのユーザーが smallrc で実行している場合、クエリごとに 1 つの同時実行スロットが使用されるため、32 個のクエリを同時実行できます。If all users are running in smallrc, 32 concurrent queries would be allowed because each query would consume 1 concurrency slot. DW1000 のすべてのユーザーが mediumrc で実行している場合、各クエリには総メモリの 47 GB の割り当てについてディストリビューションあたり 800 MB が割り当てられ、同時実行は 5 ユーザー (40 個の同時実行スロット/mediumrc ユーザーあたり 8 スロット) に制限されます。If all users on a DW1000 were running in mediumrc, each query would be allocated 800 MB per distribution for a total memory allocation of 47 GB per query, and concurrency would be limited to 5 users (40 concurrency slots / 8 slots per mediumrc user).

適切なリソース クラスを選択します。Selecting proper resource class

お勧めの方法として、ユーザーのリソース クラスを変更するのではなく、ユーザーを永続的にリソース クラスに割り当てます。A good practice is to permanently assign users to a resource class rather than changing their resource classes. たとえば、クラスター化列ストア テーブルへの読み込みでは、より多くのメモリを割り当てることでより高品質のインデックスを作成できます。For example, loads to clustered columnstore tables create higher-quality indexes when allocated more memory. 読み込み時により多くのメモリにアクセスできるようにするには、データの読み込み専用のユーザーを作成し、このユーザーを永続的に上位のリソース クラスに割り当てます。To ensure that loads have access to higher memory, create a user specifically for loading data and permanently assign this user to a higher resource class. ここでベスト プラクティスをいくつか紹介します。There are a couple of best practices to follow here. 前述のように、SQL DW では、静的リソース クラスと動的リソース クラスの 2 つの種類のリソース クラスをサポートしています。As mentioned above, SQL DW supports two kinds of resource class types: static resource classes and dynamic resource classes.

読み込みのベスト プラクティスLoading best practices

  1. 通常のデータ量読み込みが予想される場合は、静的リソース クラスをお勧めします。If the expectations are loads with regular amount of data, a static resource class is a good choice. 後で、計算能力を向上するために拡張すれば、ロード ユーザーは多くのメモリを消費しないため、データ ウェアハウスは多数の同時実行クエリを既定で実行できます。Later, when scaling up to get more computational power, the data warehouse will be able to run more concurrent queries out-of-the-box, as the load user does not consume more memory.
  2. 大きな負荷が予想される状況では、動的リソース クラスをお勧めします。If the expectations are bigger loads in some occasions, a dynamic resource class is a good choice. 後で、計算能力を向上するため拡張すれば、ロード ユーザーはその場でメモリを増やせるため、読み込みを高速で実行できます。Later, when scaling up to get more computational power, the load user will get more memory out-of-the-box, hence allowing the load to perform faster.

読み込みを効率的に処理を必要とされるメモリは、読み込まれるテーブルおよび処理されるデータ量の性質によって異なります。The memory needed to process loads efficiently depends on the nature of the table loaded and the amount of data processed. たとえば、CCI テーブルにデータを読み込むには、CCI 行グループを最適化するためのメモリが必要です。For instance, loading data into CCI tables requires some memory to let CCI rowgroups reach optimality. 詳細については、列ストア インデックスのデータ読み込みのガイダンスを参照してください。For more details, see the Columnstore indexes - data loading guidance.

ベスト プラクティスとしては、200 MB のメモリを読み込みに使用することをお勧めします。As a best practice, we advise you to use at least 200MB of memory for loads.

クエリのベスト プラクティスQuerying best practices

クエリには、その複雑さによっては異なる要件があります。Queries have different requirements depending on their complexity. クエリ ニーズに応じて、クエリあたりのメモリを増やすことも、同時実行機能の向上を図ることも、全体的なスループットの増加に有効な方法です。Increasing memory per query or increasing the concurrency are both valid ways to augment overall throughput depending on the query needs.

  1. 負荷が標準的な場合は、クエリが複雑であり (たとえば、毎日または毎週レポートを生成する)、同時実行が必要ない場合は、動的リソース クラスをお勧めします。If the expectations are regular, complex queries (for instance, to generate daily and weekly reports) and do not need to take advantage of concurrency, a dynamic resource class is a good choice. 大量のデータを処理する必要がある場合は、クエリを実行するユーザーに割り当てるメモリが自動的に増量されるよう、データ ウェアハウスを拡張することをお勧めします。If the system has more data to process, scaling up the data warehouse will therefore automatically provide more memory to the user running the query.
  2. 負荷が変動する、または同時実行パターンが 1 日のうちに変動することが予想される場合 (たとえばデータベースが、広範にアクセスできる Web UI を通じて照会される場合)、静的リソース クラスをお勧めします。If the expectations are variable or diurnal concurrency patterns (for instance if the database is queried through a web UI broadly accessible), a static resource class is a good choice. 後でデータ ウェアハウスが拡張されると、静的リソース クラスに関連付けられているユーザーは自動的に複数の同時実行クエリを実行することできます。Later, when scaling up to data warehouse, the user associated with the static resource class will automatically be able to run more concurrent queries.

照会されるデータの量、テーブル スキーマの性質、さまざまな結合、選択、およびグループ述語など多くの要因が関係するため、クエリのニーズに応じて適切なメモリ割り当てを選択するのは容易ではありません。Selecting proper memory grant depending on the need of your query is non-trivial, as it depends on many factors, such as the amount of data queried, the nature of the table schemas, and various join, selection, and group predicates. 一般的な観点からすると、メモリ割り当て量を増やすと、クエリの実行時間が短縮されますが、全体的な同時実行能力は減少します。From a general standpoint, allocating more memory will allow queries to complete faster, but would reduce the overall concurrency. 同時実行能力が問題でない場合は、メモリは過剰ぎみに割り当てて問題ありません。If concurrency is not an issue, over-allocating memory does not harm. スループットを微調整するには、さまざまな種類のリソース クラスを試してみる必要があります。To fine-tune throughput, trying various flavors of resource classes may be required.

次のストアド プロシージャを使用して、特定の SLO の同時実行性とリソース クラスあたりのメモリ割り当てを割り出せば、特定のリソース クラスの非分割 CCI テーブルでのメモリ消費量の多い CCI 操作に最適なリソース クラスを特定することができます。You can use the following stored procedure to figure out concurrency and memory grant per resource class at a given SLO and the closest best resource class for memory intensive CCI operations on non-partitioned CCI table at a given resource class:

説明:Description:

このストアド プロシージャの目的を次に示します。Here's the purpose of this stored procedure:

  1. 特定の SLO の同時実行性とリソース クラスあたりのメモリ割り当てを割り出す。To help user figure out concurrency and memory grant per resource class at a given SLO. 次の例で示すように、ユーザーは、このスキーマとテーブル名の両方について NULL を指定する必要があります。User needs to provide NULL for both schema and tablename for this as shown in the example below.
  2. 特定のリソース クラスの非分割 CCI テーブルでのメモリ消費量の多い CCI 操作 (読み込み、テーブルのコピー、インデックスの再構築など) に最適なリソース クラスを特定する。To help user figure out the closest best resource class for the memory intensed 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 for this.

依存関係と制限事項:Dependencies & Restrictions:

  • このストアド プロシージャは、分割型 CCI テーブルのメモリの要件を計算するものではありません。This stored proc is not designed to calculate memory requirement for partitioned-cci table.
  • このストアド プロシージャでは、CTAS/INSERT-SELECT の SELECT 部分のメモリ要件は計算に入れられておらず、それが単純な SELECT であると想定ています。This stored proc doesn't take memory requirement into account for the SELECT part of CTAS/INSERT-SELECT and assumes it to be a simple SELECT.
  • このストアド プロシージャでは、このストアド プロシージャが作成されたセッションで使用できるよう、一時テーブルを使用しています。This stored proc uses a temp table so this can be used in the session where this stored proc was created.
  • このストアド プロシージャは、現在の環境 (ハードウェア構成、DMS 構成など) に依存しており、そのいずれかが変更されると正しく動作しません。This stored proc depends on the current offerings (e.g. hardware configuration, DMS config) and if any of that changes then this stored proc would not work correctly.
  • このストアド プロシージャは、現在存在する同時実行性制限に依存しており、そのいずれかが変更されると正しく動作しません。This stored proc depends on existing offered concurrency limit and if that changes then this stored proc would not work correctly.
  • このストアド プロシージャは、現在提供されているリソース クラスに依存しており、そのいずれかが変更されると正しく動作しません。This stored proc depends on existing resource class offerings and if that changes then this stored proc wuold not 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 値が含まれている1. Either DW Parameter contains invalid SLO value
2.または、テーブル名が提供されている場合、CCI 操作に一致するリソース クラスがない。2. OR there are no matching resource class for CCI operation if table name was provided.
たとえば、DW100 で可能なメモリの最大割り当て量は 400 MB ですが、テーブル スキーマのサイズが 400 MB の要件に適合する大きさである必要があります。For example, at DW100, highest memory grant available is 400MB and if table schema is wide enough to cross the requirement of 400MB.

使用例: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 パラメーターを指定するか、'DW100' の形式でサポートされている 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 'DW100'
  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 'DW2000', 'dbo', 'Table1';  
EXEC dbo.prc_workload_management_by_DWU NULL, 'dbo', 'Table1';  
EXEC dbo.prc_workload_management_by_DWU 'DW6000', NULL, NULL;  
EXEC dbo.prc_workload_management_by_DWU NULL, NULL, NULL;  

上記の例で使用される Table1 は、以下のように作成できますTable1 used in the above examples could be created as below:
CREATE TABLE Table1 (a int, b varchar(50), c decimal (18,10), d char(10), e varbinary(15), f float, g datetime, h date);

ストアド プロシージャの定義:Here's the 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.
SET @DWU = (
  SELECT 'DW'+CAST(COUNT(*)*100 AS VARCHAR(10))
  FROM sys.dm_pdw_nodes
  WHERE type = 'COMPUTE')
END

DECLARE @DWU_NUM INT
SET @DWU_NUM = CAST (SUBSTRING(@DWU, 3, LEN(@DWU)-2) AS INT)

-- Raise error if either schema name or table name is supplied but not both them supplied
--IF ((@SCHEMA_NAME IS NOT NULL AND @TABLE_NAME IS NULL) OR (@TABLE_NAME IS NULL AND @SCHEMA_NAME IS NOT NULL))
--     RAISEERROR('User need to supply either both Schema Name and Table Name or none of them')

-- Dropping temp table if exists.
IF OBJECT_ID('tempdb..#ref') IS NOT NULL
BEGIN
  DROP TABLE #ref;
END

-- Creating ref. temptable (CTAS) to hold mapping info.
-- CREATE TABLE #ref
CREATE TABLE #ref
WITH (DISTRIBUTION = ROUND_ROBIN)
AS 
WITH
-- Creating concurrency slots mapping for various DWUs.
alloc
AS
(
  SELECT 'DW100' 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 'DW200', 8, 8, 1, 2, 4, 8, 1, 2, 4, 8, 8, 8, 8, 8
  UNION ALL
    SELECT 'DW300', 12, 12, 1, 2, 4, 8, 1, 2, 4, 8, 8, 8, 8, 8
  UNION ALL
    SELECT 'DW400', 16, 16, 1, 4, 8, 16, 1, 2, 4, 8, 16, 16, 16, 16
  UNION ALL
     SELECT 'DW500', 20, 20, 1, 4, 8, 16, 1, 2, 4, 8, 16, 16, 16, 16
  UNION ALL
    SELECT 'DW600', 24, 24, 1, 4, 8, 16, 1, 2, 4, 8, 16, 16, 16, 16
  UNION ALL
    SELECT 'DW1000', 32, 40, 1, 8, 16, 32, 1, 2, 4, 8, 16, 32, 32, 32
  UNION ALL
    SELECT 'DW1200', 32, 48, 1, 8, 16, 32, 1, 2, 4, 8, 16, 32, 32, 32
  UNION ALL
    SELECT 'DW1500', 32, 60, 1, 8, 16, 32, 1, 2, 4, 8, 16, 32, 32, 32
  UNION ALL
    SELECT 'DW2000', 32, 80, 1, 16, 32, 64, 1, 2, 4, 8, 16, 32, 64, 64
  UNION ALL
   SELECT 'DW3000', 32, 120, 1, 16, 32, 64, 1, 2, 4, 8, 16, 32, 64, 64
  UNION ALL
    SELECT 'DW6000', 32, 240, 1, 32, 64, 128, 1, 2, 4, 8, 16, 32, 64, 128
)
-- Creating workload mapping to their corresponding slot consumption and default memory grant.
,map
AS
(
  SELECT 'SloDWGroupC00' AS wg_name,1 AS slots_used,100 AS tgt_mem_grant_MB
  UNION ALL
    SELECT 'SloDWGroupC01',2,200
  UNION ALL
    SELECT 'SloDWGroupC02',4,400
  UNION ALL
    SELECT 'SloDWGroupC03',8,800
  UNION ALL
    SELECT 'SloDWGroupC04',16,1600
  UNION ALL
    SELECT 'SloDWGroupC05',32,3200
  UNION ALL
    SELECT 'SloDWGroupC06',64,6400
  UNION ALL
    SELECT 'SloDWGroupC07',128,12800
)
-- Creating ref based on current / asked DWU.
, ref
AS
(
  SELECT  a1.*
  ,       m1.wg_name          AS wg_name_smallrc
  ,       m1.tgt_mem_grant_MB AS tgt_mem_grant_MB_smallrc
  ,       m2.wg_name          AS wg_name_mediumrc
  ,       m2.tgt_mem_grant_MB AS tgt_mem_grant_MB_mediumrc
  ,       m3.wg_name          AS wg_name_largerc
  ,       m3.tgt_mem_grant_MB AS tgt_mem_grant_MB_largerc
  ,       m4.wg_name          AS wg_name_xlargerc
  ,       m4.tgt_mem_grant_MB AS tgt_mem_grant_MB_xlargerc
  ,       m5.wg_name          AS wg_name_staticrc10
  ,       m5.tgt_mem_grant_MB AS tgt_mem_grant_MB_staticrc10
  ,       m6.wg_name          AS wg_name_staticrc20
  ,       m6.tgt_mem_grant_MB AS tgt_mem_grant_MB_staticrc20
  ,       m7.wg_name          AS wg_name_staticrc30
  ,       m7.tgt_mem_grant_MB AS tgt_mem_grant_MB_staticrc30
  ,       m8.wg_name          AS wg_name_staticrc40
  ,       m8.tgt_mem_grant_MB AS tgt_mem_grant_MB_staticrc40
  ,       m9.wg_name          AS wg_name_staticrc50
  ,       m9.tgt_mem_grant_MB AS tgt_mem_grant_MB_staticrc50
  ,       m10.wg_name          AS wg_name_staticrc60
  ,       m10.tgt_mem_grant_MB AS tgt_mem_grant_MB_staticrc60
  ,       m11.wg_name          AS wg_name_staticrc70
  ,       m11.tgt_mem_grant_MB AS tgt_mem_grant_MB_staticrc70
  ,       m12.wg_name          AS wg_name_staticrc80
  ,       m12.tgt_mem_grant_MB AS tgt_mem_grant_MB_staticrc80
  FROM alloc a1
  JOIN map   m1  ON a1.slots_used_smallrc     = m1.slots_used
  JOIN map   m2  ON a1.slots_used_mediumrc    = m2.slots_used
  JOIN map   m3  ON a1.slots_used_largerc     = m3.slots_used
  JOIN map   m4  ON a1.slots_used_xlargerc    = m4.slots_used
  JOIN map   m5  ON a1.slots_used_staticrc10    = m5.slots_used
  JOIN map   m6  ON a1.slots_used_staticrc20    = m6.slots_used
  JOIN map   m7  ON a1.slots_used_staticrc30    = m7.slots_used
  JOIN map   m8  ON a1.slots_used_staticrc40    = m8.slots_used
  JOIN map   m9  ON a1.slots_used_staticrc50    = m9.slots_used
  JOIN map   m10  ON a1.slots_used_staticrc60    = m10.slots_used
  JOIN map   m11  ON a1.slots_used_staticrc70    = m11.slots_used
  JOIN map   m12  ON a1.slots_used_staticrc80    = m12.slots_used
-- WHERE   a1.DWU = @DWU
  WHERE   a1.DWU = UPPER(@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 (@DWU_NUM AS FLOAT)/6000)) > 0 THEN FLOOR(8 * (CAST (@DWU_NUM AS FLOAT)/6000)) 
                     ELSE 1 
              END AS multipliplication_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)*multipliplication_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)*multipliplication_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)*multipliplication_factor/1048576    AS DECIMAL(18,2)) - r1.tgt_mem_grant_MB)
GO

クエリの重要度Query importance

SQL Data Warehouse では、ワークロード グループを使用してリソース クラスを実装します。SQL Data Warehouse implements resource classes by using workload groups. 各 DWU サイズでのリソース クラスの動作を制御するワークロード グループは合計で 8 つあります。There are a total of eight workload groups that control the behavior of the resource classes across the various DWU sizes. いずれの DWU でも、使用するのは 8 つのワークロード グループのうち 4 つだけです。For any DWU, SQL Data Warehouse uses only four of the eight workload groups. 各ワークロード グループは smallrc、mediumrc、largerc、または xlargerc のうちの 1 つに割り当てられるため、これは当然のことです。This makes sense because each workload group is assigned to one of four resource classes: smallrc, mediumrc, largerc, or xlargerc. ワークロード グループを理解するうえで、これらのワークロード グループの一部は上位の 重要度に設定されることに注意する必要があります。The importance of understanding the workload groups is that some of these workload groups are set to higher importance. 重要度は、CPU のスケジュール設定に使用されます。Importance is used for CPU scheduling. 重要度が "高" のクエリには、重要度が "中" のクエリと比べて 3 倍の CPU サイクルが与えられます。Queries run with high importance will get three times more CPU cycles than those with medium importance. そのため、同時実行スロットのマッピングにより、CPU の優先度も決まります。Therefore, concurrency slot mappings also determine CPU priority. 16 個以上のスロットを使用するクエリは、"高" 重要度として実行されます。When a query consumes 16 or more slots, it runs as high importance.

次の表に、各ワークロード グループの重要度のマッピングを示します。The following table shows the importance mappings for each workload group.

同時実行スロットおよび重要度に対するワークロード グループのマッピングWorkload group mappings to concurrency slots and importance

ワークロード グループWorkload groups 同時実行スロットのマッピングConcurrency slot mapping MB / ディストリビューションMB / Distribution 重要度のマッピングImportance mapping
SloDWGroupC00SloDWGroupC00 11 100100 Medium
SloDWGroupC01SloDWGroupC01 22 200200 Medium
SloDWGroupC02SloDWGroupC02 44 400400 Medium
SloDWGroupC03SloDWGroupC03 88 800800 Medium
SloDWGroupC04SloDWGroupC04 1616 1,6001,600 High
SloDWGroupC05SloDWGroupC05 3232 3,2003,200 High
SloDWGroupC06SloDWGroupC06 6464 6,4006,400 High
SloDWGroupC07SloDWGroupC07 128128 12,80012,800 High

同時実行スロットの割り当てと使用数 の表から、DW500 では smallrc、mediumrc、largerc、および xlargerc のそれぞれで 1 個、4 個、8 個、または 16 個の同時実行スロットが使用されることが分かります。From the Allocation and consumption of concurrency slots chart, you can see that a DW500 uses 1, 4, 8 or 16 concurrency slots for smallrc, mediumrc, largerc, and xlargerc, respectively. 上の表でこれらの値を調べることで、各リソース クラスの重要度を確認できます。You can look those values up in the preceding chart to find the importance for each resource class.

DW500 での重要度に対するリソース クラスのマッピングDW500 mapping of resource classes to importance

リソース クラスResource class ワークロード グループWorkload group 使用される同時実行スロット数Concurrency slots used MB / ディストリビューションMB / Distribution 重要度Importance
smallrcsmallrc SloDWGroupC00SloDWGroupC00 11 100100 Medium
mediumrcmediumrc SloDWGroupC02SloDWGroupC02 44 400400 Medium
largerclargerc SloDWGroupC03SloDWGroupC03 88 800800 Medium
xlargercxlargerc SloDWGroupC04SloDWGroupC04 1616 1,6001,600 High
staticrc10staticrc10 SloDWGroupC00SloDWGroupC00 11 100100 Medium
staticrc20staticrc20 SloDWGroupC01SloDWGroupC01 22 200200 Medium
staticrc30staticrc30 SloDWGroupC02SloDWGroupC02 44 400400 Medium
staticrc40staticrc40 SloDWGroupC03SloDWGroupC03 88 800800 Medium
staticrc50staticrc50 SloDWGroupC03SloDWGroupC03 1616 1,6001,600 High
staticrc60staticrc60 SloDWGroupC03SloDWGroupC03 1616 1,6001,600 High
staticrc70staticrc70 SloDWGroupC03SloDWGroupC03 1616 1,6001,600 High
staticrc80staticrc80 SloDWGroupC03SloDWGroupC03 1616 1,6001,600 High

次の DMV クエリを使用すると、リソース ガバナーの観点からメモリ リソースの割り当ての違いを詳細に確認できます。また、トラブルシューティングを行うときにワークロード グループのアクティブな使用状況と過去の使用状況を分析することもできます。You can use the following DMV query to look at the differences in memory resource allocation in detail from the perspective of the resource governor, or to analyze active and historic usage of the workload groups when troubleshooting.

WITH rg
AS
(   SELECT  
     pn.name                        AS node_name
    ,pn.[type]                        AS node_type
    ,pn.pdw_node_id                    AS node_id
    ,rp.name                        AS pool_name
    ,rp.max_memory_kb*1.0/1024                AS pool_max_mem_MB
    ,wg.name                        AS group_name
    ,wg.importance                    AS group_importance
    ,wg.request_max_memory_grant_percent        AS group_request_max_memory_grant_pcnt
    ,wg.max_dop                        AS group_max_dop
    ,wg.effective_max_dop                AS group_effective_max_dop
    ,wg.total_request_count                AS group_total_request_count
    ,wg.total_queued_request_count            AS group_total_queued_request_count
    ,wg.active_request_count                AS group_active_request_count
    ,wg.queued_request_count                AS group_queued_request_count
    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
    JOIN    sys.dm_pdw_nodes pn
            ON    wg.pdw_node_id    = pn.pdw_node_id
    WHERE   wg.name like 'SloDWGroup%'
        AND     rp.name = 'SloDWPool'
)
SELECT    pool_name
,        pool_max_mem_MB
,        group_name
,        group_importance
,        (pool_max_mem_MB/100)*group_request_max_memory_grant_pcnt AS max_memory_grant_MB
,        node_name
,        node_type
,       group_total_request_count
,       group_total_queued_request_count
,       group_active_request_count
,       group_queued_request_count
FROM    rg
ORDER BY
    node_name
,    group_request_max_memory_grant_pcnt
,    group_importance
;

同時実行の制限が考慮されるクエリQueries that honor concurrency limits

ほとんどのクエリは、リソース クラスによって管理されます。Most queries are governed by resource classes. これらのクエリは、同時実行クエリと同時実行スロットの両方のしきい値以下である必要があります。These queries must fit inside both the concurrent query and concurrency slot thresholds. ユーザーは、同時実行スロット モデルからクエリを除外することはできません。A user cannot choose to exclude a query from the concurrency slot model.

繰り返しになりますが、次のステートメントではリソース クラスが考慮されます。To reiterate, the following statements honor resource classes:

  • INSERT-SELECTINSERT-SELECT
  • UPDATEUPDATE
  • 削除DELETE
  • SELECT (ユーザー テーブルのクエリを実行する場合)SELECT (when querying user tables)
  • ALTER INDEX REBUILDALTER INDEX REBUILD
  • ALTER INDEX REORGANIZEALTER INDEX 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)

同時実行の制限に対するクエリの例外Query exceptions to concurrency limits

クエリには、ユーザーの割り当てられているリソース クラスが考慮されないものがあります。Some queries do not honor the resource class to which the user is assigned. 同時実行の制限に対するこうした例外は特定のコマンドに必要なメモリ リソースが少ない場合に発生します。その理由は多くの場合、コマンドがメタデータ操作となるためです。These exceptions to the concurrency limits are made when the memory resources needed for a particular command are low, often because the command is a metadata operation. これらの例外により、大量のメモリ割り当てを必要としないクエリに対して、多くのメモリが割り当てられる状態を回避できます。The goal of these exceptions is to avoid larger memory allocations for queries that will never need them. このような場合、ユーザーに割り当てられた実際のリソース クラスに関係なく、既定の小規模リソース クラス (smallrc) が常に使用されます。In these cases, the default small resource class (smallrc) is always used regardless of the actual resource class assigned to the user. たとえば、CREATE LOGIN は常に smallrc で実行されます。For example, CREATE LOGIN will always run in smallrc. この操作の実行に必要なリソースは非常に少ないので、同時実行スロット モデルにクエリを含めても意味はありません。The resources required to fulfill this operation are very low, so it does not make sense to include the query in the concurrency slot model. また、これらのクエリには 32 ユーザーの同時実行の制限は適用されず、1,024 セッションというセッションの上限までクエリを無制限に実行できます。These queries are also not limited by the 32 user concurrency limit, an unlimited number of these queries can run up to the session limit of 1,024 sessions.

次のステートメントでは、リソース クラスは考慮されません。The following statements do not honor resource classes:

  • 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

ユーザー リソース クラスの変更例 Change a user resource class example

  1. ログインを作成する: SQL Data Warehouse データベースをホストしている SQL Server のマスター データベースへの接続を開き、次のコマンドを実行します。Create login: Open a connection to your master database on the SQL server hosting your SQL Data Warehouse database and execute the following commands.

    CREATE LOGIN newperson WITH PASSWORD = 'mypassword';
    CREATE USER newperson for LOGIN newperson;
    

    注意

    Azure SQL Data Warehouse ユーザーの master データベースにユーザーを作成することをお勧めします。It is a good idea to create a user in the master database for Azure SQL Data Warehouse users. マスターでユーザーを作成すると、ユーザーはデータベース名を指定せずに SSMS のようなツールを使用してログインできます。Creating a user in master allows a user to login using tools like SSMS without specifying a database name. また、オブジェクト エクスプ ローラーを使用して、SQL Server のすべてのデータベースを表示することもできます。It also allows them to use the object explorer to view all databases on a SQL server. ユーザーの作成および管理の詳細については、「SQL Data Warehouse でのデータベース保護」を参照してください。For more details about creating and managing users, see Secure a database in SQL Data Warehouse.

  2. SQL Data Warehouse ユーザーを作成する: SQL Data Warehouse データベースへの接続を開き、次のコマンドを実行します。Create SQL Data Warehouse user: Open a connection to the SQL Data Warehouse database and execute the following command.

    CREATE USER newperson FOR LOGIN newperson;
    
  3. アクセス許可を与える: 次の例では、SQL Data Warehouse データベースに対する CONTROL を付与します。Grant permissions: The following example grants CONTROL on the SQL Data Warehouse database. データベース レベルので CONTROL は、SQL Server での db_owner に相当します。CONTROL at the database level is the equivalent of db_owner in SQL Server.

    GRANT CONTROL ON DATABASE::MySQLDW to newperson;
    
  4. リソース クラスのサイズを大きくする: ワークロードの高い管理ロールにユーザーを追加するには、次のクエリを使用します。Increase resource class: Use the following query to add a user to a higher workload management role.

    EXEC sp_addrolemember 'largerc', 'newperson'
    
  5. リソース クラスのサイズを小さくする: ワークロード管理ロールからユーザーを削除するには、次のクエリを使用します。Decrease resource class: Use the following query to remove a user from a workload management role.

    EXEC sp_droprolemember 'largerc', 'newperson';
    

    注意

    smallrc からユーザーを削除することはできません。It is not possible to remove a user from smallrc.

キューに配置されたクエリの検出とその他の DMVQueued query detection and other DMVs

sys.dm_pdw_exec_requests DMV を使用すると、同時実行キューで待機中のクエリを特定できます。You can use the sys.dm_pdw_exec_requests DMV to identify queries that are waiting in a concurrency queue. 同時実行スロットを待機しているクエリは、 中断状態となります。Queries waiting for a concurrency slot will have a status of suspended.

SELECT      r.[request_id]                 AS Request_ID
        ,r.[status]                 AS Request_Status
        ,r.[submit_time]             AS Request_SubmitTime
        ,r.[start_time]                 AS Request_StartTime
        ,DATEDIFF(ms,[submit_time],[start_time]) AS Request_InitiateDuration_ms
        ,r.resource_class                         AS Request_resource_class
FROM    sys.dm_pdw_exec_requests r;

ワークロード管理ロールを確認するには、 sys.database_principalsを使用します。Workload management roles can be viewed with sys.database_principals.

SELECT  ro.[name]           AS [db_role_name]
FROM    sys.database_principals ro
WHERE   ro.[type_desc]      = 'DATABASE_ROLE'
AND     ro.[is_fixed_role]  = 0;

次のクエリは、各ユーザーが割り当てられているロールを示します。The following query shows which role each user is assigned to.

SELECT     r.name AS role_principal_name
        ,m.name AS member_principal_name
FROM    sys.database_role_members rm
JOIN    sys.database_principals AS r            ON rm.role_principal_id        = r.principal_id
JOIN    sys.database_principals AS m            ON rm.member_principal_id    = m.principal_id
WHERE    r.name IN ('mediumrc','largerc', 'xlargerc');

SQL Data Warehouse には、次の待機の種類があります。SQL Data Warehouse has the following wait types:

  • LocalQueriesConcurrencyResourceType: 同時実行スロットのフレームワークの外に配置されたクエリ。LocalQueriesConcurrencyResourceType: Queries that sit outside of the concurrency slot framework. DMV クエリと、 SELECT @@VERSION のようなシステム関数は、ローカル クエリの例です。DMV queries and system functions such as SELECT @@VERSION are examples of local queries.
  • UserConcurrencyResourceType: 同時実行スロットのフレームワーク内に配置されたクエリ。UserConcurrencyResourceType: Queries that sit inside the concurrency slot framework. エンドユーザー テーブルに対するクエリは、このリソースの種類を使用した例です。Queries against end-user tables represent examples that would use this resource type.
  • DmsConcurrencyResourceType: データ移動操作に起因する待機。DmsConcurrencyResourceType: Waits resulting from data movement operations.
  • BackupConcurrencyResourceType: この待機は、データベースがバックアップ中であることを示します。BackupConcurrencyResourceType: This wait indicates that a database is being backed up. この種類のリソースの最大値は 1 です。The maximum value for this resource type is 1. 同時に複数のバックアップが要求された場合は、他の要求はキューに配置されます。If multiple backups have been requested at the same time, the others will queue.

sys.dm_pdw_waits DMV を使用すると、要求がどのリソースを待っているのかを調べることができます。The sys.dm_pdw_waits DMV can be used to see which resources a request is waiting for.

SELECT  w.[wait_id]
,       w.[session_id]
,       w.[type]            AS Wait_type
,       w.[object_type]
,       w.[object_name]
,       w.[request_id]
,       w.[request_time]
,       w.[acquire_time]
,       w.[state]
,       w.[priority]
,    SESSION_ID()            AS Current_session
,    s.[status]            AS Session_status
,    s.[login_name]
,    s.[query_count]
,    s.[client_id]
,    s.[sql_spid]
,    r.[command]            AS Request_command
,    r.[label]
,    r.[status]            AS Request_status
,    r.[submit_time]
,    r.[start_time]
,    r.[end_compile_time]
,    r.[end_time]
,    DATEDIFF(ms,r.[submit_time],r.[start_time])        AS Request_queue_time_ms
,    DATEDIFF(ms,r.[start_time],r.[end_compile_time])    AS Request_compile_time_ms
,    DATEDIFF(ms,r.[end_compile_time],r.[end_time])        AS Request_execution_time_ms
,    r.[total_elapsed_time]
FROM    sys.dm_pdw_waits w
JOIN    sys.dm_pdw_exec_sessions s  ON w.[session_id] = s.[session_id]
JOIN    sys.dm_pdw_exec_requests r  ON w.[request_id] = r.[request_id]
WHERE    w.[session_id] <> SESSION_ID();

sys.dm_pdw_resource_waits DMV では、特定のクエリによって使用されるリソースの待機のみが表示されます。The sys.dm_pdw_resource_waits DMV shows only the resource waits consumed by a given query. リソースの待機時間では、リソースが提供されるまでの時間のみが考慮されます。これに対し、シグナルの待機時間は、基になる SQL Server によりクエリが CPU にスケジュール設定されるまでの時間です。Resource wait time only measures the time waiting for resources to be provided, as opposed to signal wait time, which is the time it takes for the underlying SQL servers to schedule the query onto the CPU.

SELECT  [session_id]
,       [type]
,       [object_type]
,       [object_name]
,       [request_id]
,       [request_time]
,       [acquire_time]
,       DATEDIFF(ms,[request_time],[acquire_time])  AS acquire_duration_ms
,       [concurrency_slots_used]                    AS concurrency_slots_reserved
,       [resource_class]
,       [wait_id]                                   AS queue_position
FROM    sys.dm_pdw_resource_waits
WHERE    [session_id] <> SESSION_ID();

sys.dm_pdw_wait_stats DMV を使用すると、待機のこれまでの傾向を分析できます。The sys.dm_pdw_wait_stats DMV can be used for historic trend analysis of waits.

SELECT    w.[pdw_node_id]
,        w.[wait_name]
,        w.[max_wait_time]
,        w.[request_count]
,        w.[signal_time]
,        w.[completed_count]
,        w.[wait_time]
FROM    sys.dm_pdw_wait_stats w;

次のステップNext steps

データベース ユーザーの管理とセキュリティの詳細については、「SQL Data Warehouse でのデータベース保護」を参照してください。For more information about managing database users and security, see Secure a database in SQL Data Warehouse. 大規模なリソース クラスを使用して、クラスター化された列ストア インデックスの品質を向上させる方法については、「 セグメントの品質を向上させるためのインデックスの再構築」を参照してください。For more information about how larger resource classes can improve clustered columnstore index quality, see [Rebuilding indexes to improve segment quality].