Limiter les ressources d'un login avec le Resource Governor

Le cas est fréquent chez les éditeurs qui hébergent la base de leurs clients. On veut leur ouvrir un accès en lecture seule, pour quelques requêtes ou un tableau de bord, sans qu’ils y branchent un outil de reporting de production qui ferait tourner le serveur pour eux. S’ils ont besoin de plus, ils passent à une offre avec un serveur dédié.

SQL Server ne sait pas limiter un utilisateur en nombre de requêtes par jour. Ce qu’il sait faire, c’est reconnaître une session au moment de la connexion, par son login, le nom du poste ou le nom de l’application, et la placer dans un espace où ses ressources sont plafonnées. C’est le rôle du Resource Governor.

Tous les exemples de cette page s’exécutent sur la base de démonstration PachaDataFormation. Les mesures ont été faites sur trois conteneurs : SQL Server 2022 Developer (16.0.4265.3), SQL Server 2025 en édition Enterprise Developer et en édition Standard Developer (17.0.4065.4), avec 22 cœurs visibles. Le conteneur de l’édition Standard était limité à 8 processeurs par podman : ses temps absolus ne se comparent pas à ceux des deux autres.

Éditions et versions

Jusqu’à SQL Server 2022, le Resource Governor est réservé à l’édition Enterprise (et à l’édition Developer, qui en a les fonctionnalités). Depuis SQL Server 2025, il est aussi disponible en édition Standard, avec les mêmes fonctionnalités qu’en édition Enterprise selon Microsoft. L’édition Express n’y a pas droit.

La page des éditions pour Linux indique pourtant que la gouvernance des I/O reste réservée à l’édition Enterprise. Sur notre conteneur Linux SQL Server 2025 en édition Standard Developer, la limite d’IOPS s’applique exactement comme en édition Enterprise.

Le login en lecture seule

On commence par le login lui-même, avec le seul droit de lire. Remplacez le mot de passe de l’exemple.

USE [master];
GO
CREATE LOGIN [client_lecture] WITH PASSWORD = N'Chang3r-Ce-M0t-De-Passe!', CHECK_POLICY = ON;
GO
USE [PachaDataFormation];
GO
CREATE USER [client_lecture] FOR LOGIN [client_lecture];
ALTER ROLE [db_datareader] ADD MEMBER [client_lecture];
GO

Avec le seul rôle db_datareader, ce login ne peut pas modifier les tables, mais rien ne l’empêche de consommer. Il peut lancer une jointure qui occupe plusieurs cœurs, un tri qui demande des centaines de mégaoctets, ou remplir tempdb : sur SQL Server 2022, il a copié sans difficulté 300 000 lignes dans une table temporaire avec un SELECT ... INTO #copie. C’est ce que le Resource Governor va encadrer.

Le pool de ressources

Un pool de ressources est une part du serveur : CPU, mémoire de travail des requêtes, I/O. Toutes les sessions qu’on y place se partagent cette part.

USE [master];
GO
CREATE RESOURCE POOL [pool_clients]
WITH (
    CAP_CPU_PERCENT = 10,
    MAX_MEMORY_PERCENT = 10,
    MAX_IOPS_PER_VOLUME = 100
);
GO

CPU

MAX_CPU_PERCENT ne s’applique qu’en cas de contention avec d’autres pools : si le serveur est calme, le pool peut prendre tout le CPU. CAP_CPU_PERCENT est un plafond dur, appliqué même quand le serveur n’a rien d’autre à faire. Pour des clients qu’on veut brider, c’est CAP_CPU_PERCENT qu’il faut.

La documentation ne dit pas sur quoi porte le pourcentage. Nos mesures montrent qu’il porte sur le CPU de toute l’instance, pas sur un cœur. Sur nos 22 cœurs, 10 % représentent un peu plus de deux cœurs. Une requête limitée à un seul thread n’utilise qu’environ 4,5 % de la machine : un plafond de 10 % ne la ralentit pas. À 2 %, soit 0,44 cœur, la même requête passe de 5,0 à 10,5 secondes : un peu plus du double, proche des 2,3 que donne le calcul (4,5 % divisé par 2 %). Calculez donc le pourcentage à partir du nombre de cœurs : sur un serveur de 16 cœurs, un cœur représente 6,25 %.

Microsoft précise que la régulation du CPU est statistique : de courts pics au-dessus de la limite restent possibles, et des requêtes très courtes peuvent ne pas consommer assez longtemps pour être régulées.

Mémoire

MAX_MEMORY_PERCENT ne limite pas la mémoire au sens large. Il limite la mémoire de travail des requêtes, celle qu’elles réservent pour trier ou construire des tables de hachage. Le buffer pool, qui garde les pages de données en cache, reste partagé entre tous les pools : un client qui lit une grosse table peut toujours en évincer les pages des autres applications.

À 10 %, le pool disposait sur notre instance d’environ 1,2 Go de mémoire de travail. Une requête seule peut en prendre 25 % par défaut (réglage du groupe, plus bas), soit environ 300 Mo.

I/O

MAX_IOPS_PER_VOLUME limite le nombre d’opérations d’entrées-sorties par seconde et par volume disque. Ce n’est pas un débit : les lectures anticipées de SQL Server font souvent plusieurs centaines de kilo-octets. Dans notre test, un parcours de table à froid a lu 54 Mo en 120 à 140 opérations selon les essais, soit 400 à 450 Ko chacune.

La limite porte sur les I/O physiques des requêtes, lectures et écritures. Une table déjà en cache se lit sans aucune I/O, donc sans ralentissement. Les écritures du journal, du checkpoint et du lazy writer ne sont pas gouvernées non plus. En revanche, les écritures et relectures dans tempdb d’un tri qui déborde le sont, et c’est ce qui rend ce réglage efficace (voir les mesures plus bas).

Le groupe de charge de travail

Les sessions ne sont pas placées directement dans un pool, mais dans un groupe de charge de travail (workload group) rattaché à un pool. Le groupe porte les limites qui s’appliquent requête par requête.

USE [master];
GO
CREATE WORKLOAD GROUP [groupe_clients]
WITH (
    GROUP_MAX_REQUESTS = 2,
    MAX_DOP = 1,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 25,
    REQUEST_MAX_CPU_TIME_SEC = 60
)
USING [pool_clients];
GO

GROUP_MAX_REQUESTS fixe le nombre de requêtes qui s’exécutent en même temps dans le groupe. Au-delà, la requête attend son tour, sans erreur. Son effet se prévoit facilement : avec 2, un outil qui ouvre dix connexions n’en fait jamais travailler que deux.

MAX_DOP limite le parallélisme de chaque requête du groupe. Il l’emporte sur l’option d’instance max degree of parallelism et sur la configuration de base, et un indicateur OPTION (MAXDOP n) n’est respecté que s’il ne dépasse pas cette valeur. Avec MAX_DOP = 1 et GROUP_MAX_REQUESTS = 2, le client n’occupe jamais plus de deux cœurs, quel que soit le plafond CPU du pool.

REQUEST_MAX_MEMORY_GRANT_PERCENT est la part de la mémoire du pool qu’une seule requête peut réserver. 25 est la valeur par défaut, l’écrire rend simplement le réglage visible. Dans un groupe créé par l’utilisateur, une requête dont le besoin minimal de mémoire dépasse ce plafond voit d’abord son parallélisme réduit, puis échoue avec l’erreur 8657 : ne descendez pas ce réglage trop bas, ni la mémoire du pool.

Le nom de REQUEST_MAX_CPU_TIME_SEC laisse croire qu’il arrête la requête. Par défaut, une requête qui dépasse ce temps CPU n’est pas arrêtée : SQL Server déclenche l’événement étendu cpu_threshold_exceeded et incrémente le compteur total_cpu_limit_violation_count, puis la laisse finir. Pour qu’elle soit annulée, il faut activer le trace flag global 2422. La requête reçoit alors l’erreur 10961. Ce trace flag vaut pour tous les groupes de l’instance.

DBCC TRACEON (2422, -1);

DBCC TRACEON ne survit pas à un redémarrage. Pour le rendre permanent, ajoutez -T2422 aux paramètres de démarrage dans le Gestionnaire de configuration SQL Server, ou, sous Linux, sudo /opt/mssql/bin/mssql-conf traceflag 2422 on puis un redémarrage du service.

Le temps CPU n’est vérifié que toutes les cinq secondes : une limite de 2 secondes a annulé notre requête après 7 à 8 secondes, et une requête de 4,6 secondes de CPU est passée sans être détectée.

tempdb, depuis SQL Server 2025

SQL Server 2025 ajoute une limite sur l’espace qu’un groupe occupe dans les fichiers de données de tempdb : tables temporaires, variables table, débordements de tris et de hachages. Le version store n’est pas compté, ni le journal de tempdb : pour contenir celui-ci, Microsoft conseille d’activer ADR (accelerated database recovery) dans tempdb.

USE [master];
GO
ALTER WORKLOAD GROUP [groupe_clients] WITH (GROUP_MAX_TEMPDB_DATA_MB = 512);
GO

Au dépassement, la requête est annulée avec l’erreur 1138, Could not allocate a new page for database 'tempdb' because that would exceed the limit set for workload group. Il existe aussi GROUP_MAX_TEMPDB_DATA_PERCENT, mais il ne s’applique pas sur une installation par défaut, dont les fichiers tempdb ont une taille maximale illimitée et une croissance automatique : la limite en mégaoctets est plus simple. SQL Server 2022 refuse cette option avec une erreur de syntaxe.

La fonction de classification

Reste à dire au Resource Governor quelles sessions vont dans groupe_clients. C’est le travail d’une fonction de classification, appelée à chaque nouvelle connexion, qui renvoie le nom du groupe. Elle doit être créée dans master, avec SCHEMABINDING.

USE [master];
GO
CREATE FUNCTION dbo.fn_classification_rg()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE WHEN SUSER_SNAME() = N'client_lecture'
                THEN N'groupe_clients'
                ELSE N'default'
           END;
END;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_classification_rg);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

ALTER RESOURCE GOVERNOR RECONFIGURE active le Resource Governor, désactivé à l’installation, et applique la configuration. Il faut le relancer après chaque modification d’un pool ou d’un groupe.

Le login est le critère le plus sûr, parce que SQL Server l’a authentifié. La fonction peut aussi classer sur le nom du poste client, HOST_NAME(), ou sur le nom de l’application, APP_NAME(), par exemple pour brider un outil précis quel que soit le compte utilisé :

USE [master];
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
ALTER FUNCTION dbo.fn_classification_rg()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE
               WHEN SUSER_SNAME() = N'client_lecture' THEN N'groupe_clients'
               WHEN HOST_NAME() = N'POSTE-BI-01' THEN N'groupe_clients'
               WHEN APP_NAME() LIKE N'Microsoft Office%' THEN N'groupe_clients'
               ELSE N'default'
           END;
END;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_classification_rg);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Ces deux valeurs sont déclarées par le client dans sa chaîne de connexion, et rien ne les vérifie. Elles servent à ranger des applications de bonne foi. Quelqu’un qui veut contourner la limite n’a qu’à annoncer un autre nom. L’inverse est vrai aussi : dans notre test, une session sa ouverte avec le nom de poste POSTE-BI-01 s’est retrouvée dans groupe_clients.

Le début de ce script détache la fonction, parce qu’une fonction de classification active ne peut être ni modifiée ni supprimée (erreur 10920). Entre les deux RECONFIGURE, les nouvelles connexions vont dans le groupe default.

Quelques règles pour cette fonction :

• elle s’exécute à chaque connexion, avant que la session soit utilisable : gardez-la simple, sans lecture de table si possible, sinon les connexions ralentissent ou expirent ;
• si elle renvoie NULL, le nom d’un groupe qui n’existe pas, ou si elle échoue, la session va dans default ;
• elle classe la session pour toute sa durée : une session ouverte avant l’activation reste dans son groupe. Microsoft précise que la fonction est évaluée pour chaque nouvelle session, même avec un pool de connexions.

Vérifier la classification

Connectez-vous avec client_lecture, puis, depuis une session d’administrateur :

SELECT s.session_id,
       s.login_name,
       s.host_name,
       s.program_name,
       g.name AS groupe,
       p.name AS pool
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_resource_governor_workload_groups AS g ON g.group_id = s.group_id
JOIN sys.dm_resource_governor_resource_pools AS p ON p.pool_id = g.pool_id
WHERE s.is_user_process = 1;

Lancée par client_lecture, cette requête échoue : les vues du Resource Governor demandent la permission VIEW SERVER PERFORMANCE STATE, dont il n’a pas besoin.

SSMS ou T-SQL

Dans SSMS, le Resource Governor se trouve dans l’Explorateur d’objets, sous Gestion (Management), puis Resource Governor. Tant qu’il est désactivé, son icône porte une croix rouge.

Un clic droit sur ce nœud propose la création d’un pool.

Sa fenêtre de propriétés permet de créer des pools et des groupes. Pour les groupes, elle expose le nombre de requêtes simultanées, le temps CPU, la part de mémoire et le degré de parallélisme. Pour les pools, elle ne propose que les pourcentages minimum et maximum de CPU et de mémoire : le plafond dur CAP_CPU_PERCENT, les IOPS et la limite tempdb n’y figurent pas.

La fonction de classification s’écrit aussi en T-SQL, la fenêtre ne fait que la choisir dans une liste. Sur une instance où aucune fonction n’a été créée, cette liste ne propose que None.

Autant tout faire dans une fenêtre de requête SSMS, connectée à l’instance avec un compte qui a la permission CONTROL SERVER. Le T-SQL a un autre avantage : la configuration vit dans master, et un script se rejoue sur un autre serveur ou après une reconstruction.

Ce que le client constate

Pour mesurer l’effet de chaque réglage, nous avons lancé la même requête avec sa et avec client_lecture. Elle cherche les homonymes qui habitent des villes différentes :

USE [PachaDataFormation];
GO
SELECT COUNT_BIG(*) AS homonymes
FROM Contact.ProspectUS AS a
JOIN Contact.ProspectUS AS b ON b.Nom = a.Nom AND b.Ville <> a.Ville;

Temps écoulés sur SQL Server 2025 en édition Enterprise Developer, avec la configuration du script ci-dessus sauf mention contraire. Les proportions sont les mêmes en édition Standard de SQL Server 2025, dont le conteneur était limité à 8 processeurs et où tous les temps sont environ quatre fois plus longs. Sur 2022, le plafond de 2 % pèse davantage : 12,8 secondes.

À 10 %, le client ne perd que le parallélisme. Quand un outil multiplie les requêtes, GROUP_MAX_REQUESTS se fait sentir : la troisième attend que l’une des deux premières se termine. Pendant cette attente, sys.dm_resource_governor_workload_groups montre active_request_count = 2 et queued_request_count = 1. Le temps passé dans la file n’apparaît pas dans SET STATISTICS TIME, qui ne compte que l’exécution : mesurez-le côté client.

Pour les I/O, un parcours de Contact.ProspectUS_N après vidage du cache (CHECKPOINT puis DBCC DROPCLEANBUFFERS, à ne pas lancer en production), soit 120 à 140 lectures selon les essais, sur SQL Server 2025 :

USE [PachaDataFormation];
GO
SELECT COUNT_BIG(*) AS nb
FROM Contact.ProspectUS_N
WHERE Email LIKE N'%@gmail%';

Les limites se combinent. Avec MAX_MEMORY_PERCENT = 1, les autres réglages inchangés, un tri de Contact.ProspectUS_N n’obtient plus que 34 Mo de mémoire et déborde dans tempdb :

USE [PachaDataFormation];
GO
SELECT COUNT_BIG(*) AS echantillon
FROM (SELECT Email,
             ROW_NUMBER() OVER (ORDER BY Email, Nom, Prenom, Adresse) AS rn
      FROM Contact.ProspectUS_N) AS t
WHERE rn % 1000 = 0;

Sans limite d’I/O, ce débordement coûte peu : 0,8 seconde. Avec MAX_IOPS_PER_VOLUME = 100, ses quelque 1 270 écritures et 1 270 relectures dans tempdb passent par la limite d’IOPS, et la requête met 26 secondes. La même requête prend 0,3 seconde pour sa. Mesures sur SQL Server 2025.

Suivre la consommation et ajuster

sys.dm_resource_governor_workload_groups et sys.dm_resource_governor_resource_pools tiennent des compteurs cumulés par groupe et par pool :

SELECT g.name,
       g.statistics_start_time,
       g.total_request_count,
       g.total_queued_request_count,
       g.total_cpu_usage_ms,
       g.total_cpu_limit_violation_count,
       g.total_reduced_memgrant_count,
       g.max_request_grant_memory_kb,
       p.read_io_completed_total,
       p.read_io_throttled_total,
       p.write_io_throttled_total
FROM sys.dm_resource_governor_workload_groups AS g
JOIN sys.dm_resource_governor_resource_pools AS p ON p.pool_id = g.pool_id
WHERE g.name = N'groupe_clients';

Ils partent du dernier démarrage de l’instance ou de la dernière remise à zéro, dont statistics_start_time donne la date :

ALTER RESOURCE GOVERNOR RESET STATISTICS;

Cette commande remet à zéro les compteurs de tous les groupes et de tous les pools de l’instance.

total_queued_request_count et les compteurs *_throttled_total disent si le client bute sur les limites. total_cpu_usage_ms donne sa consommation réelle. total_request_count compte les requêtes terminées, c’est-à-dire les lots envoyés au serveur, et non des requêtes métier : une connexion sqlcmd qui exécute un simple SELECT 1; l’augmente de 3, parce que sqlcmd envoie deux lots SET QUOTED_IDENTIFIER et SET TEXTSIZE avant la requête. Sur SQL Server 2025, peak_tempdb_data_space_kb et total_tempdb_data_limit_violation_count couvrent tempdb.

Relevés de temps en temps, ces chiffres suffisent pour ajuster les limites, ou pour montrer au client ce qu’il consomme.

Modifier la configuration en production

Une modification du plafond CPU d’un pool, validée par ALTER RESOURCE GOVERNOR RECONFIGURE, s’applique aussi aux requêtes en cours. Nous avons lancé la requête des homonymes avec CAP_CPU_PERCENT = 2, puis relevé le plafond à 100 au bout de trois secondes : elle s’est terminée en 6,3 secondes au lieu de 10,5, ce qui correspond à un changement immédiat. Le degré de parallélisme, lui, est fixé au démarrage de chaque requête. Passer MAX_DOP à 1 s’applique aussi aux plans déjà en cache, qui s’exécutent alors en série. Dans l’autre sens, un plan compilé en série le reste : pour que les requêtes du groupe profitent d’une valeur plus haute, videz le cache du pool avec DBCC FREEPROCCACHE ('pool_clients'), comme l’indique la documentation.

USE [master];
GO
ALTER RESOURCE POOL [pool_clients] WITH (CAP_CPU_PERCENT = 5);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Deux précautions :

• rattacher un groupe à un autre pool échoue tant que le groupe a des sessions ouvertes ;
• si une fonction de classification défectueuse empêche les connexions, la connexion administrateur dédiée (DAC, ADMIN: devant le nom du serveur) n’est pas classée et permet de la détacher. Par défaut, elle n’accepte que les connexions depuis le serveur lui-même ; à distance, il faut avoir activé l’option remote admin connections au préalable.

La configuration est stockée dans master. Dans un groupe de disponibilité, elle ne se réplique pas : rejouez le script sur chaque réplica.

Revenir en arrière

Pour tout supprimer, fermez d’abord les sessions de client_lecture : un groupe qui a des sessions ouvertes ne se supprime pas, et un login connecté non plus.

USE [master];
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
DROP FUNCTION dbo.fn_classification_rg;
DROP WORKLOAD GROUP [groupe_clients];
DROP RESOURCE POOL [pool_clients];
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
USE [PachaDataFormation];
GO
DROP USER [client_lecture];
GO
USE [master];
GO
DROP LOGIN [client_lecture];
GO

Si vous avez activé le trace flag 2422, désactivez-le avec DBCC TRACEOFF (2422, -1);, et retirez -T2422 des paramètres de démarrage, ou, sous Linux, lancez sudo /opt/mssql/bin/mssql-conf traceflag 2422 off.

Le Resource Governor reste activé, sans autre effet que de placer toutes les sessions dans default. ALTER RESOURCE GOVERNOR DISABLE; le ramène à son état d’installation.