Gestion des charges de travail avec des classes de ressources dans Azure Synapse AnalyticsWorkload management with resource classes in Azure Synapse Analytics

Conseils d’utilisation des classes de ressources pour gérer la mémoire et la concurrence pour les requêtes de pool SQL Synapse dans Azure Synapse.Guidance for using resource classes to manage memory and concurrency for Synapse SQL pool queries in Azure Synapse.

Que sont les classes de ressources ?What are resource classes

La capacité de performances d’une requête est déterminée par la classe de ressources de cette dernière.The performance capacity of a query is determined by the user's resource class. Les classes de ressources sont des limites de ressources prédéterminées dans un pool SQL Synapse qui régissent les ressources de calcul et la concurrence lors de l’exécution des requêtes.Resource classes are pre-determined resource limits in Synapse SQL pool that govern compute resources and concurrency for query execution. Les classes de ressources peuvent vous aider à configurer des ressources pour vos requêtes en définissant des limites applicables au nombre de requêtes qui s’exécutent simultanément et aux ressources de calcul qui leur sont respectivement attribuées.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. Il faut faire un compromis entre la mémoire et la concurrence.There's a trade-off between memory and concurrency.

  • Des classes de ressources plus petites réduisent la mémoire maximale par requête, mais augmentent la simultanéité.Smaller resource classes reduce the maximum memory per query, but increase concurrency.
  • Des classes de ressources plus grandes augmentent la mémoire maximale par requête, mais réduisent la concurrence.Larger resource classes increase the maximum memory per query, but reduce concurrency.

Il existe deux types de classes de ressources :There are two types of resource classes:

  • Les classes de ressources statiques, qui sont bien adaptées pour la concurrence accrue sur un jeu de données de taille fixe.Static resources classes, which are well suited for increased concurrency on a data set size that is fixed.
  • Les classes de ressources dynamiques, qui sont bien adaptées pour les jeux de données dont la taille augmente et dont les performances doivent s’améliorer à mesure que le niveau de service monte en puissance.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.

Les classes de ressources utilisent des emplacements de concurrence pour mesurer la consommation des ressources.Resource classes use concurrency slots to measure resource consumption. Les emplacements de concurrence sont expliqués plus loin dans cet article.Concurrency slots are explained later in this article.

Classes de ressources statiquesStatic resource classes

Les classes de ressources statiques allouent la même quantité de mémoire, mesurée en unités d’entrepôt de données, quel que soit le niveau de performance actuel.Static resource classes allocate the same amount of memory regardless of the current performance level, which is measured in data warehouse units. Étant donné que la mémoire allouée aux requêtes est la même quel que soit le niveau de performance, une augmentation de l’échelle de l’entrepôt de données permet l’exécution d’un plus grand nombre de requêtes au sein d’une classe de ressources.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. Les classes de ressources statiques sont idéales si le volume de données est connu et constant.Static resource classes are ideal if the data volume is known and constant.

Les classes de ressources statiques sont implémentées avec ces rôles de base de données prédéfinis :The static resource classes are implemented with these pre-defined database roles:

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

Classes de ressources dynamiquesDynamic resource classes

Les classes de ressources dynamiques allouent une quantité de mémoire variable en fonction du niveau de service actuel.Dynamic Resource Classes allocate a variable amount of memory depending on the current service level. Alors que les classes de ressources statiques conviennent dans les situations de concurrence accrue et pour les volumes de données statiques, les classes de ressources dynamiques sont mieux adaptées pour une quantité de données croissante ou variable.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. Lors de l’augmentation de l’échelle du niveau de service, vos requêtes obtiennent automatiquement davantage de mémoire.When you scale up to a larger service level, your queries automatically get more memory.

Les classes de ressources dynamiques sont implémentées avec les rôles de base de données prédéfinis suivants :The dynamic resource classes are implemented with these pre-defined database roles:

  • smallrcsmallrc
  • mediumrcmediumrc
  • largerclargerc
  • xlargercxlargerc

L’allocation de mémoire pour chaque classe de ressources est la suivante.The memory allocation for each resource class is as follows.

Niveau de serviceService 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%

Classe de ressources par défautDefault resource class

Par défaut, chaque utilisateur appartient à la classe de ressources dynamiques smallrc.By default, each user is a member of the dynamic resource class smallrc.

La classe de ressources de l’administrateur de service est fixée à smallrc et ne peut pas être changée.The resource class of the service administrator is fixed at smallrc and cannot be changed. L’administrateur de service est l’utilisateur créé pendant le processus d’approvisionnement.The service administrator is the user created during the provisioning process. Dans ce contexte, l’administrateur de services fédérés est la connexion spécifiée pour la « connexion d’administrateur du serveur » au moment de la création d’un pool SQL Synapse avec un nouveau serveur.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.

Notes

Les utilisateurs ou groupes définis en tant qu’administrateur Active Directory sont également administrateurs de service.Users or groups defined as Active Directory admin are also service administrators.

Opérations de ressources de classeResource class operations

Les classes de ressources sont conçues pour améliorer les performances de gestion et de manipulation de données.Resource classes are designed to improve performance for data management and manipulation activities. Les requêtes complexes peuvent également bénéficier d’une exécution sous une classe de ressource de grande taille.Complex queries can also benefit from running under a large resource class. Par exemple, les performances des requêtes pour un grand nombre de jointures et de tris peuvent être améliorées lorsque la classe de ressources est suffisamment grande pour permettre l’exécution de la requête dans la mémoire.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.

Opérations régies par des classes de ressourcesOperations governed by resource classes

Les opérations suivantes sont régies par des classes de ressources :These operations are governed by resource classes:

  • INSERT-SELECT, UPDATE, DELETEINSERT-SELECT, UPDATE, DELETE
  • SELECT (lors de l’interrogation des tables d’utilisateur)SELECT (when querying user tables)
  • ALTER INDEX - REBUILD ou 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)
  • Chargement de donnéesData loading
  • Opérations de déplacement de données effectuées par le Service le déplacement des données (DMS)Data movement operations conducted by the Data Movement Service (DMS)

Notes

Les instructions SELECT sur des vues de gestion dynamique (DMV) ou d’autres vues système ne sont régies par aucune limite de concurrence.SELECT statements on dynamic management views (DMVs) or other system views are not governed by any of the concurrency limits. Les utilisateurs peuvent surveiller le système en toutes circonstances, quel que soit le nombre de requêtes en cours d’exécution dessus.You can monitor the system regardless of the number of queries executing on it.

Opérations non régies par des classes de ressourcesOperations not governed by resource classes

Certaines requêtes s’exécutent toujours dans la classe de ressources smallrc, même si l’utilisateur est membre d’une classe de ressources plus grande.Some queries always run in the smallrc resource class even though the user is a member of a larger resource class. Ces requêtes exemptes ne sont pas prises en compte pour la limite de concurrence.These exempt queries do not count towards the concurrency limit. Par exemple, si la limite de concurrence est définie sur 16, de nombreux utilisateurs peuvent sélectionner des vues système sans que cela ait d’incidence sur les emplacements de concurrence disponibles.For example, if the concurrency limit is 16, many users can be selecting from system views without impacting the available concurrency slots.

Les instructions suivantes sont exemptes de classes de ressources et s’exécutent toujours dans smallrc :The following statements are exempt from resource classes and always run in smallrc:

  • CREATE ou DROP TABLECREATE or DROP TABLE
  • l’instruction ALTER TABLE ... SWITCH, SPLIT ou MERGE PARTITIONALTER TABLE ... SWITCH, SPLIT, or MERGE PARTITION
  • ALTER INDEX DISABLEALTER INDEX DISABLE
  • DROP INDEXDROP INDEX
  • CREATE, UPDATE ou DROP STATISTICSCREATE, UPDATE, or DROP STATISTICS
  • TRUNCATE TABLETRUNCATE TABLE
  • ALTER AUTHORIZATIONALTER AUTHORIZATION
  • CREATE LOGINCREATE LOGIN
  • CREATE, ALTER ou DROP USERCREATE, ALTER, or DROP USER
  • CREATE, ALTER ou DROP PROCEDURECREATE, ALTER, or DROP PROCEDURE
  • CREATE ou DROP VIEWCREATE or DROP VIEW
  • INSERT VALUESINSERT VALUES
  • SELECT à partir des affichages système et des DMVSELECT from system views and DMVs
  • EXPLAINEXPLAIN
  • DBCCDBCC

Emplacements de concurrenceConcurrency slots

Les emplacements de concurrence sont pratiques pour suivre les ressources disponibles pour l’exécution des requêtes.Concurrency slots are a convenient way to track the resources available for query execution. Il sont comparables à des tickets que vous achetez afin de réserver des sièges pour assister à un concert dont le nombre de places est limité.They are like tickets that you purchase to reserve seats at a concert because seating is limited. Le nombre total d’emplacements de concurrence par entrepôt de données est déterminé par le niveau de service.The total number of concurrency slots per data warehouse is determined by the service level. Avant que son exécution puisse démarrer, une requête doit pouvoir réserver suffisamment d’emplacements de concurrence.Before a query can start executing, it must be able to reserve enough concurrency slots. Une fois terminée, la requête libère ses emplacements de concurrence.When a query completes, it releases its concurrency slots.

  • Une requête s’exécutant avec 10 emplacements de concurrence peut accéder à 5 fois plus de ressources de calcul qu’une requête s’exécutant avec 2 emplacements de concurrence.A query running with 10 concurrency slots can access 5 times more compute resources than a query running with 2 concurrency slots.
  • Si chaque requête nécessite 10 emplacements de concurrence alors que 40 sont disponibles, seules 4 requêtes peuvent s’exécuter simultanément.If each query requires 10 concurrency slots and there are 40 concurrency slots, then only 4 queries can run concurrently.

Seules ds requêtes régies par des ressources consomment des emplacements de concurrence.Only resource governed queries consume concurrency slots. Les requêtes système et certaines requêtes banales ne consomment aucun emplacement.System queries and some trivial queries don't consume any slots. Le nombre exact d’emplacements de concurrence consommés est déterminé par la classe de ressources de la requête.The exact number of concurrency slots consumed is determined by the query's resource class.

Afficher les classes de ressourcesView the resource classes

Les classes de ressources sont implémentées sous forme de rôles de base de données prédéfinis.Resource classes are implemented as pre-defined database roles. Il existe deux types de classes de ressources : statiques et dynamiques.There are two types of resource classes: dynamic and static. Pour afficher une liste des classes de ressources, utilisez la requête suivante :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';

Modifier la classe de ressources d’un utilisateurChange a user's resource class

Les classes de ressources sont implémentées en assignant des utilisateurs à des rôles de base de données.Resource classes are implemented by assigning users to database roles. Quand un utilisateur exécute une requête, celle-ci est exécutée avec la classe de ressources de l’utilisateur.When a user runs a query, the query runs with the user's resource class. Par exemple, si un utilisateur est membre du rôle de base de données staticrc10, ses requêtes s’exécutent avec de petites quantités de mémoire.For example, if a user is a member of the staticrc10 database role, their queries run with small amounts of memory. Si un utilisateur de base de données est membre du rôle de base de données xlargerc ou staticrc80, ses requêtes s’exécutent avec de grandes quantités de mémoire.If a database user is a member of the xlargerc or staticrc80 database roles, their queries run with large amounts of memory.

Pour augmenter la classe de ressources d’un utilisateur, utilisez sp_addrolemember afin d’ajouter l’utilisateur à un rôle de base de données d’une grande classe de ressources.To increase a user's resource class, use sp_addrolemember to add the user to a database role of a large resource class. Le code ci-dessous ajoute un utilisateur au rôle de base de données largerc.The below code adds a user to the largerc database role. Chaque requête obtient 22 % de la mémoire système.Each request gets 22% of the system memory.

EXEC sp_addrolemember 'largerc', 'loaduser';

Pour réduire la classe de ressources, utilisez sp_droprolemember.To decrease the resource class, use sp_droprolemember. Si « loaduser » n’est membre d’aucune autre classe de ressources, il est placé dans la classe de ressources smallrc par défaut avec une allocation de mémoire de 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';

Précédence de classe de ressourcesResource class precedence

Les utilisateurs peuvent être membres de plusieurs classes de ressources.Users can be members of multiple resource classes. Quand un utilisateur appartient à plus d’une classe de ressources :When a user belongs to more than one resource class:

  • les classes de ressources dynamiques ont la précédence sur les classes de ressources statiques.Dynamic resource classes take precedence over static resource classes. Par exemple, si un utilisateur est membre des classes de ressources mediumrc (dynamique) et staticrc80 (statique), les requêtes s’exécutent avec la classe de ressources mediumrc.For example, if a user is a member of both mediumrc(dynamic) and staticrc80 (static), queries run with mediumrc.
  • Les classes de ressources plus grandes ont la précédence sur les classes de ressources plus petites.Larger resource classes take precedence over smaller resource classes. Par exemple, si un utilisateur est membre des classes de ressources mediumrc et largerc, les requêtes s’exécutent avec la classe de ressources largerc.For example, if a user is a member of mediumrc and largerc, queries run with largerc. De même, si un utilisateur est membre des classes de ressources staticrc20 et statirc80, les requêtes s’exécutent avec les allocations de ressources de staticrc80.Likewise, if a user is a member of both staticrc20 and statirc80, queries run with staticrc80 resource allocations.

RecommandationsRecommendations

Notes

Tirez parti des fonctionnalités de gestion des charges de travail (isolation de charge de travail, classification et importance) pour obtenir un meilleur contrôle de votre charge de travail et des performances prévisibles.Consider leveraging workload management capabilities (workload isolation, classification and importance) for more control over your workload and predictable performance.

Nous recommandons de créer un utilisateur dédié à l’exécution d’un type spécifique d’opération de requête ou de chargement.We recommend creating a user that is dedicated to running a specific type of query or load operation. Accordez à cet utilisateur une classe de ressources permanente au lieu de modifier fréquemment la classe de ressources.Give that user a permanent resource class instead of changing the resource class on a frequent basis. Les classes de ressources statiques offrent un plus grand contrôle global de la charge de travail. Nous vous suggérons donc de les utiliser avant d’envisager l’utilisation de classes de ressources dynamiques.Static resource classes afford greater overall control on the workload, so we suggest using static resource classes before considering dynamic resource classes.

Classes de ressources pour les utilisateurs de chargementResource classes for load users

L’instruction CREATE TABLE utilise des index cluster columnstore par défaut.CREATE TABLE uses clustered columnstore indexes by default. La compression de données dans un index columnstore est une opération nécessitant beaucoup de mémoire, et la sollicitation de la mémoire peut réduire la qualité de l’index.Compressing data into a columnstore index is a memory-intensive operation, and memory pressure can reduce the index quality. La sollicitation de la mémoire peut conduire à un besoin d’une classe de ressources supérieure lors du chargement de données.Memory pressure can lead to needing a higher resource class when loading data. Pour garantir que les chargements disposent de suffisamment de mémoire, vous pouvez créer un utilisateur désigné pour exécuter les chargements, et assigner cet utilisateur à une classe de ressources supérieure.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.

La quantité de mémoire nécessaire au traitement des chargements dépend de la nature de la table chargée et de la taille des données.The memory needed to process loads efficiently depends on the nature of the table loaded and the data size. Pour plus d’informations sur la mémoire requise, voir Optimiser la qualité du rowgroup pour columnstore.For more information on memory requirements, see Maximizing rowgroup quality.

Après avoir déterminé la mémoire requise, choisissez d’affecter l’utilisateur de chargement à une classe de ressources statique ou dynamique.Once you have determined the memory requirement, choose whether to assign the load user to a static or dynamic resource class.

  • Utilisez une classe de ressources statique lorsque la mémoire requise pour la table s’inscrit dans une plage spécifique.Use a static resource class when table memory requirements fall within a specific range. Les chargements s’exécutent avec une mémoire appropriée.Loads run with appropriate memory. Lorsque vous modifiez l’échelle de l’entrepôt de données, les charges n’ont pas besoin de davantage de mémoire.When you scale the data warehouse, the loads do not need more memory. En utilisant une classe de ressources statique, les allocations de mémoire restent constantes.By using a static resource class, the memory allocations stay constant. Cette cohérence conserve la mémoire et permet l’exécution concurrente de davantage de requêtes.This consistency conserves memory and allows more queries to run concurrently. Nous recommandons que les nouvelles solutions utilisent prioritairement les classes de ressources statiques, car celles-ci offrent davantage de contrôle.We recommend that new solutions use the static resource classes first as these provide greater control.
  • Utilisez une classe de ressources dynamique lorsque la mémoire requise pour la table varie considérablement.Use a dynamic resource class when table memory requirements vary widely. Des chargements peuvent nécessiter plus de mémoire que ce qu’offre le niveau actuel de DWU ou de cDWU.Loads might require more memory than the current DWU or cDWU level provides. La mise à l’échelle de l’entrepôt de données ajoute de la mémoire aux opérations de chargement, ce qui permet d’accélérer les chargements.Scaling the data warehouse adds more memory to load operations, which allows loads to perform faster.

Classes de ressources pour les requêtesResource classes for queries

Certaines requêtes nécessitent beaucoup de ressources système, et d’autres non.Some queries are compute-intensive and some aren't.

  • Choisissez une classe de ressources dynamique quand les requêtes sont complexes mais n’impliquent pas de concurrence élevée.Choose a dynamic resource class when queries are complex, but don't need high concurrency. Par exemple, la génération de rapports quotidiens ou hebdomadaires entraîne un besoin occasionnel de ressources.For example, generating daily or weekly reports is an occasional need for resources. Si les rapports résultent du traitement de grandes quantités de données, la mise à l’échelle de l’entrepôt de données fournit davantage de mémoire à la classe de ressources existante de l’utilisateur.If the reports are processing large amounts of data, scaling the data warehouse provides more memory to the user's existing resource class.
  • Choisissez une classe de ressources statique lorsque les attentes de ressources varient pendant la journée.Choose a static resource class when resource expectations vary throughout the day. Par exemple, une classe de ressources statique fonctionne bien lorsque l’entrepôt de données est interrogé par de nombreuses personnes.For example, a static resource class works well when the data warehouse is queried by many people. Lors de la mise à l’échelle de l’entrepôt de données, la quantité de mémoire allouée à l’utilisateur ne change pas.When scaling the data warehouse, the amount of memory allocated to the user doesn't change. Par conséquent, davantage de requêtes peuvent être exécutées en parallèle sur le système.Consequently, more queries can be executed in parallel on the system.

L’allocation de mémoire appropriée dépend de nombreux facteurs tels que la quantité de données interrogées, la nature des schémas de table et les divers prédicats joins, select et group.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. En règle général, si l’allocation de davantage de mémoire permet d’accélérer l’exécution des requêtes, cela limite la concurrence globale.In general, allocating more memory allows queries to complete faster, but reduces the overall concurrency. Si la concurrence n’est pas un problème, une allocation excessive de mémoire n’affecte pas le débit.If concurrency is not an issue, over-allocating memory does not harm throughput.

Pour ajuster les performances, utilisez des classes de ressources différentes.To tune performance, use different resource classes. La section suivante fournit une procédure stockée permettant de déterminer la classe de ressources optimale.The next section gives a stored procedure that helps you figure out the best resource class.

Exemple de code pour rechercher la classe de ressources optimaleExample code for finding the best resource class

Vous pouvez utiliser la procédure stockée spécifiée suivante pour déterminer la concurrence et l’allocation de mémoire par classe de ressources pour un SLO donné et la classe de ressources optimale pour les opérations ICC sur une table ICC non partitionnée pour une classe de ressources donnée :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:

Cette procédure stockée vise à :Here's the purpose of this stored procedure:

  1. Déterminer la concurrence et l’allocation de mémoire par classe de ressources pour un SLO donné.To see the concurrency and memory grant per resource class at a given SLO. L’utilisateur doit fournir la valeur NULL pour le schéma et le nom de table, comme indiqué dans cet exemple.User needs to provide NULL for both schema and tablename as shown in this example.
  2. Déterminer la classe de ressources optimale pour les opérations ICC utilisant beaucoup de mémoire (chargement, copie de table, régénération d’index, etc.) sur une table ICC non partitionnée à une classe de ressources donnée.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. La procédure stockée utilise un schéma de table pour déterminer l’allocation de mémoire requise.The stored proc uses table schema to find out the required memory grant.

Dépendances et restrictionsDependencies & Restrictions

  • Cette procédure stockée n’est pas conçue pour calculer la mémoire requise pour une table ICC partitionnée.This stored procedure isn't designed to calculate the memory requirement for a partitioned cci table.
  • Cette procédure stockée ne prend pas en compte la mémoire requise pour la partie SELECT d’une instruction CTAS/INSERT-SELECT et part du principe qu’il s’agit d’une instruction 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.
  • Cette procédure stockée utilise une table temporaire disponible dans la session où la procédure stockée a été créée.This stored procedure uses a temp table, which is available in the session where this stored procedure was created.
  • Cette procédure stockée dépend des offres du moment (par exemple, configuration matérielle, configuration DMS). Si l’une d’elles change, cette procédure stockée ne fonctionnera plus correctement.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.
  • Cette procédure stockée dépend des offres de limite de concurrence existantes. Si elles changent, cette procédure stockée ne fonctionnera plus correctement.This stored procedure depends on existing concurrency limit offerings and if these change then this stored procedure won't work correctly.
  • Cette procédure stockée dépend des offres de classes de ressources existantes. Si celles-ci changent, cette procédure stockée ne fonctionnera plus correctement.This stored procedure depends on existing resource class offerings and if these change then this stored procedure won't work correctly.

Notes

Si vous n’obtenez pas de sortie après exécution de la procédure stockée avec les paramètres fournis, il se peut que vous soyez confronté à l’un des deux cas suivants :If you are not getting output after executing stored procedure with parameters provided, then there could be two cases.

  1. Un paramètre DW contient une valeur SLO non valide.Either DW Parameter contains an invalid SLO value
  2. Il n’existe aucune classe de ressources correspondant à l’opération ICC sur la table.Or, there is no matching resource class for the CCI operation on the table.

Par exemple, pour DW100c, l’allocation de mémoire la plus élevée disponible est de 1 Go si le schéma de table est suffisamment volumineux pour dépasser l’exigence de 1 Go.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.

Exemple d’utilisationUsage example

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

  1. @DWU: fournissez un paramètre NULL pour extraire la DWU active de la base de données DW ou fournissez une DWU prise en charge au format « DW100c »@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: fournissez le nom de schéma de la table@SCHEMA_NAME: Provide a schema name of the table
  3. @TABLE_NAME: fournissez le nom de la table qui vous intéresse@TABLE_NAME: Provide a table name of the interest

Exemples d’exécution de cette procédure stockée :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;  

L’instruction suivante crée la Table1 utilisée dans les exemples précédents.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);

Définition de la procédure stockéeStored 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

Étapes suivantesNext steps

Pour plus d’informations sur la gestion de la sécurité et des utilisateurs de base de données, consultez Sécuriser une base de données dans SQL Synapse.For more information about managing database users and security, see Secure a database in Synapse SQL. Pour plus d’informations sur la façon dont des classes de ressources plus élevées peuvent améliorer la qualité des index cluster columnstore, voir Optimiser la qualité du rowgroup pour columnstore.For more information about how larger resource classes can improve clustered columnstore index quality, see Memory optimizations for columnstore compression.