SQL Server Resource Governor : pools, groupes de charge et fonction de classification
Configurer Resource Governor avec trois pools de ressources, trois groupes de charge et une fonction de classification par login.
Resource Governor permet de limiter le CPU et la mémoire que des groupes de sessions peuvent consommer sur SQL Server. Cet exemple oriente les sessions vers trois groupes de charge (administration, application, reporting) via une fonction de classification, chaque groupe étant lié à son propre pool de ressources.
Principe
- Une fonction de classification (créée dans
master, avecSCHEMABINDING) renvoie le nom d'un groupe de charge pour chaque nouvelle session. - Chaque groupe de charge (workload group) appartient à un pool de ressources qui définit les limites de CPU et de mémoire.
- La fonction est enregistrée avec
ALTER RESOURCE GOVERNOR, puis la configuration est appliquée avecRECONFIGURE.
Pools de ressources
CREATE RESOURCE POOL [poolApp]
WITH (min_cpu_percent=10,
max_cpu_percent=20,
min_memory_percent=0,
max_memory_percent=100);
GO
CREATE RESOURCE POOL [poolAdmin]
WITH (min_cpu_percent=10,
max_cpu_percent=100,
min_memory_percent=0,
max_memory_percent=100);
GO
CREATE RESOURCE POOL [poolReport]
WITH (min_cpu_percent=10,
max_cpu_percent=70,
min_memory_percent=0,
max_memory_percent=100);
GO
Groupes de charge
CREATE WORKLOAD GROUP [GroupAdmin]
WITH (group_max_requests=0,
importance=Medium,
request_max_cpu_time_sec=0,
request_max_memory_grant_percent=25,
request_memory_grant_timeout_sec=0,
max_dop=0)
USING [poolAdmin];
GO
CREATE WORKLOAD GROUP [GroupApp]
WITH (group_max_requests=300,
importance=Medium,
request_max_cpu_time_sec=0,
request_max_memory_grant_percent=25,
request_memory_grant_timeout_sec=0,
max_dop=1)
USING [poolApp];
GO
CREATE WORKLOAD GROUP [GroupReport]
WITH (group_max_requests=0,
importance=Medium,
request_max_cpu_time_sec=0,
request_max_memory_grant_percent=25,
request_memory_grant_timeout_sec=0,
max_dop=0)
USING [poolReport];
GO
Fonction de classification
La fonction classe les sessions selon le nom de login ou le nom de l'application. Les motifs de login ci-dessous sont des exemples.
USE master;
GO
CREATE FUNCTION [dbo].[rgclassifier_V1]() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grp_name AS sysname;
IF (SUSER_NAME() LIKE '%adm%' OR APP_NAME() LIKE '%SQL Server Management%')
SET @grp_name = 'GroupAdmin';
IF (SUSER_NAME() LIKE '%AppService')
SET @grp_name = 'GroupApp';
IF (SUSER_NAME() LIKE '%Report%')
SET @grp_name = 'GroupReport';
RETURN @grp_name;
END;
GO
Activer la fonction de classification
USE master;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[rgclassifier_V1]);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
Vérifier la configuration
SELECT * FROM sys.dm_resource_governor_workload_groups;
SELECT * FROM sys.dm_resource_governor_configuration;
SELECT * FROM sys.dm_resource_governor_resource_pools;
Pour tester la fonction pour un login donné, exécuter SELECT [dbo].[rgclassifier_V1](); dans un bloc EXECUTE AS LOGIN = '...', puis REVERT.
Remarques
- Une session qui ne correspond à aucune condition reçoit
NULLet est placée dans le groupedefault. - Si plusieurs conditions correspondent, la dernière l'emporte ; ordonner les tests en conséquence.
- Les modifications ne sont effectives qu'après
ALTER RESOURCE GOVERNOR RECONFIGURE.