Partager via

CREATE RESOURCE POOL (Transact-SQL)

  • Article

S’applique à :SQL ServerAzure SQL Managed Instance

Crée un resource governor pool de ressources. Un pool de ressources représente un sous-ensemble des ressources physiques (PROCESSEUR, mémoire et E/S) d’une instance du moteur de base de données. Resource Governor vous permet de réserver ou de limiter les ressources du serveur entre les pools de ressources, jusqu’à un maximum de 64 pools.

Resource Governor n’est pas disponible dans chaque édition de SQL Server. Pour obtenir la liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Éditions et fonctionnalités prises en charge de SQL Server 2022.

Note

Pour Azure SQL Managed Instance, vous devez être dans le contexte de la base de données master pour modifier la configuration du gouverneur de ressources.

Conventions de la syntaxe Transact-SQL

Syntaxe

CREATE RESOURCE POOL pool_name
[ WITH
    (
        [ MIN_CPU_PERCENT = value ]
        [ [ , ] MAX_CPU_PERCENT = value ]
        [ [ , ] CAP_CPU_PERCENT = value ]
        [ [ , ] AFFINITY {SCHEDULER =
                  AUTO
                | ( <scheduler_range_spec> )
                | NUMANODE = ( <NUMA_node_range_spec> )
                } ]
        [ [ , ] MIN_MEMORY_PERCENT = value ]
        [ [ , ] MAX_MEMORY_PERCENT = value ]
        [ [ , ] MIN_IOPS_PER_VOLUME = value ]
        [ [ , ] MAX_IOPS_PER_VOLUME = value ]
    )
]
[;]

<scheduler_range_spec> ::=
{ SCHED_ID | SCHED_ID TO SCHED_ID }[,...n]

<NUMA_node_range_spec> ::=
{ NUMA_node_ID | NUMA_node_ID TO NUMA_node_ID }[,...n]

Arguments

pool_name

Nom défini par l'utilisateur du pool de ressources. pool_name est alphanumérique, peut contenir jusqu’à 128 caractères, doit être unique dans une instance du moteur de base de données et doit respecter les règles relatives aux identificateurs de base de données .

MIN_CPU_PERCENT = valeur

Spécifie la bande passante de l'UC moyenne garantie pour toutes les demandes dans le pool de ressources en cas de contention de l'UC. value est un entier dont la valeur par défaut est 0. La plage autorisée pour value est comprise entre 0 et 100.

MAX_CPU_PERCENT = valeur

Spécifie la bande passante processeur moyenne maximale que toutes les requêtes du pool de ressources reçoivent lorsqu’il existe une contention du processeur. value est un entier dont la valeur par défaut est 100. La plage autorisée pour value est comprise entre 1 et 100.

CAP_CPU_PERCENT = valeur

S’applique à : SQL Server 2012 (11.x) et versions ultérieures.

Spécifie une limite matérielle sur la bande passante de l’UC que toutes les requêtes du pool de ressources reçoivent. Limite le niveau de la bande passante maximum de l'UC pour qu'il soit identique à la valeur spécifiée. value est un entier dont la valeur par défaut est 100. La plage autorisée pour value est comprise entre 1 et 100.

Note

En raison de la nature statistique de la gouvernance du processeur, vous remarquerez peut-être des pics courts occasionnels dépassant la valeur spécifiée dans CAP_CPU_PERCENT.

AFFINITY {SCHEDULER = AUTO | ( <scheduler_range_spec> ) | NUMANODE = (<NUMA_node_range_spec>)}

S’applique à : SQL Server 2012 (11.x) et versions ultérieures.

Attache le pool de ressources aux planificateurs spécifiques. La valeur par défaut est AUTO.

La spécification <scheduler_range_spec> pour AFFINITY SCHEDULER affinités entre le pool de ressources et les planificateurs du moteur de base de données identifiés par les ID donnés. Ces ID correspondent aux valeurs de la colonne scheduler_id dans sys.dm_os_schedulers.

La spécification <NUMA_node_range_spec> pour AFFINITY NUMANODE affinités entre le pool de ressources et les planificateurs du moteur de base de données qui mappent aux processeurs logiques correspondant au nœud NUMA donné ou à une plage de nœuds. Vous pouvez utiliser la requête Transact-SQL suivante pour découvrir le mappage entre la configuration NUMA physique et les ID du planificateur du moteur de base de données.

SELECT osn.memory_node_id AS numa_node_id,
       sc.cpu_id,
       sc.scheduler_id
FROM sys.dm_os_nodes AS osn
INNER JOIN sys.dm_os_schedulers AS sc
ON osn.node_id = sc.parent_node_id
   AND
   sc.scheduler_id < 1048576;

MIN_MEMORY_PERCENT = valeur

Spécifie la quantité minimale de mémoire de l’espace de travail de requête réservée au pool de ressources qui ne peut pas être partagée avec d’autres pools de ressources. value est un entier dont la valeur par défaut est 0. La plage autorisée pour valeur est comprise entre 0 et 100.

MAX_MEMORY_PERCENT = valeur

Spécifie la quantité maximale de mémoire de l’espace de travail de requête que les requêtes dans ce pool de ressources peuvent utiliser. value est un entier dont la valeur par défaut est 100. La plage autorisée pour value est comprise entre 1 et 100.

MIN_IOPS_PER_VOLUME = valeur

S’applique à : SQL Server 2014 (12.x) et versions ultérieures.

Spécifie les opérations d'E/S minimales par seconde (IOPS) par volume disque à réserver au pool de ressources. La plage autorisée pour value est comprise entre 0 et 2^31-1 (2 147 483 647). Spécifiez 0 pour indiquer aucun minimum pour le pool. La valeur par défaut est 0.

MAX_IOPS_PER_VOLUME = valeur

S’applique à : SQL Server 2014 (12.x) et versions ultérieures.

Spécifie les opérations d'E/S maximales par seconde (IOPS) par volume disque à autoriser pour le pool de ressources. La plage autorisée pour value est comprise entre 0 et 2^31-1 (2 147 483 647). Spécifiez 0 pour supprimer une limite d’E/S par seconde pour le pool. La valeur par défaut est 0.

Si la MAX_IOPS_PER_VOLUME d’un pool est définie sur 0, le pool n’est pas régi par les E/S et peut prendre tous les E/S par seconde dans le système même si d’autres pools ont MIN_IOPS_PER_VOLUME défini. Dans ce cas, nous vous recommandons de définir la valeur MAX_IOPS_PER_VOLUME pour ce pool sur un nombre élevé (par exemple, la valeur maximale 2^31-1) pour que ce pool soit régi par les E/S et pour respecter les réservations d’E/S par seconde qui peuvent exister pour d’autres pools.

Notes

La somme de MIN_CPU_PERCENT ou de MIN_MEMORY_PERCENT pour tous les pools de ressources ne peut pas dépasser 100 %.

MIN_IOPS_PER_VOLUME et MAX_IOPS_PER_VOLUME spécifiez les E/S minimales et maximales par seconde. Les E/S peuvent être des lectures ou des écritures, et peuvent être de n’importe quelle taille. Par conséquent, avec les mêmes limites d’E/S par seconde, le débit minimal et maximal d’E/S peut varier en fonction de la combinaison de tailles d’E/S dans la charge de travail.

Les valeurs de MAX_CPU_PERCENT et MAX_MEMORY_PERCENT doivent être respectivement supérieures ou égales aux valeurs de MIN_CPU_PERCENT et MIN_MEMORY_PERCENT.

CAP_CPU_PERCENT est différent de MAX_CPU_PERCENT, dans la mesure où les charges de travail associées au pool peuvent utiliser la capacité du processeur au-delà de la valeur MAX_CPU_PERCENT si elle est disponible, mais pas au-delà de la valeur CAP_CPU_PERCENT. Bien qu’il y ait des pics courts supérieurs à CAP_CPU_PERCENT, les charges de travail ne peuvent pas dépasser CAP_CPU_PERCENT pendant des périodes prolongées, même quand une capacité de processeur supplémentaire est disponible.

Le pourcentage total du processeur pour chaque composant affinité (planificateur ou nœud NUMA) ne peut pas dépasser 100 %.

Pour plus d’informations, consultez Resource Governor et pool de ressources Resource Governor.

Autorisations

Nécessite l’autorisation CONTROL SERVER.

Exemples

Pour obtenir d’autres exemples de configuration resource governor, consultez exemples de configuration Resource Governor et les meilleures pratiques.

Créer un pool de ressources

Cet exemple a créé un pool de ressources nommé bigPool. Ce pool utilise les paramètres de gouverneur de ressources par défaut.

CREATE RESOURCE POOL bigPool;
ALTER RESOURCE GOVERNOR RECONFIGURE;

Définir les réservations et les limites du processeur et de la mémoire

Cet exemple configure le pool de ressources adhocPool comme suit :

  • Réserve 10 % du processeur et 5 % de la mémoire de l’espace de travail de requête à l’aide de MIN_CPU_PERCENT et de MIN_MEMORY_PERCENT respectivement.
  • Définit une limite de mémoire d’espace de travail de requête de 15 % à l’aide de MAX_MEMORY_PERCENT.
  • Définit une limite de processeur souple de 20 pour cent une limite de 30 pour cent du processeur dur à l’aide de MAX_CPU_PERCENT et de CAP_CPU_PERCENT respectivement.
  • Affinitise le pool à deux plages de processeurs logiques (0 à 63 et 128 à 191) à l’aide de AFFINITY SCHEDULER.

S’applique à : SQL Server 2012 (11.x) et versions ultérieures.

CREATE RESOURCE POOL adhocPool
WITH (
     MIN_CPU_PERCENT = 10,
     MAX_CPU_PERCENT = 20,
     CAP_CPU_PERCENT = 30,
     MIN_MEMORY_PERCENT = 5,
     MAX_MEMORY_PERCENT = 15,
     AFFINITY SCHEDULER = (0 TO 63, 128 TO 191)
     );

Définir la réservation et la limite d’IOPS

Cet exemple réserve 200 IOPS par volume pour le pool à l’aide de MIN_IOPS_PER_VOLUMEet limite les IOPS par volume à 1 000 à l’aide de MAX_IOPS_PER_VOLUME. Ces valeurs régissent le nombre total d’opérations de lecture et d’écriture d’E/S physiques disponibles pour les requêtes utilisant le pool de ressources.

S’applique à : SQL Server 2014 (12.x) et versions ultérieures.

CREATE RESOURCE POOL PoolAdmin
WITH (
     MIN_IOPS_PER_VOLUME = 200,
     MAX_IOPS_PER_VOLUME = 1000
     );

Contenu connexe