SQL Server 2014 – Gestion des IO avec le resource governor

Enfin diront certains! Oui, on attendait tous la gestion des IO dans le gouverneur de ressources de SQL Server. Il était possible de spécifier le MaxDop, le nombre de requêtes simultanées, la quantité de mémoire, l’affinité CPU, le maximum de CPU utilisée. Enfin, SQL Server 2014 s’attaque au throttling d’IO. Cette nouveauté pourrait presque passer inaperçue tant Hekaton monopolise toutes les attentions.

Imaginez simplement que, à chaud, vous puissiez limiter la consommation IO de certaines de vos requêtes, en fonction de l’application, de l’utilisateur ou du nom d’hôte. Cette possibilité est enfin disponible ! Je n’ose même plus compter le nombre de fois, où présentant le resource governor, on m’a interpelé sur ce manque, alors que cette fonctionnalité constitue, pour moi, l’atout majeur de ce dispositif. Oui, j’apprécie le fait de limiter la CPU en cas de stress, oui j’ai du jouer avec la mémoire afin de forcer certaines requêtes a ne pas réserver 58 Gb de RAM avant de pouvoir s’exécuter. Mais j’attends surtout de cette QoS, car il faut bien l’appeler comme cela de pouvoir prioriser mes IO en fonction de l’applicatif, privilégier la prod au reporting (exit les mecs qui font de la BI avec des requêtes énormes – Romain Casteres François Jehl Jean-Pierre Riehl – non, je blague les mecs, j’adore vos requêtes qui écroulent els serveurs !).

Bref, vous l’aurez compris, découvrir dans la DMV dm_resource_governor_configuration une pléiade de nouveau champs a piqué ma curiosité.

dm_resource_governor_configuration – nouveaux champs :

• max_outstanding_io_per_volume
• min_iops_per_volume
• max_iops_per_volume
• read_io_queued_total
• read_io_issued_total
• read_io_completed_total
• read_io_throttled_total
• read_bytes_total
• read_io_stall_total_ms
• read_io_stall_queued_ms
• write_io_queued_total
• write_io_issued_total
• write_io_completed_total
• write_io_throttled_total
• write_bytes_total
• write_io_stall_total_ms
• write_io_stall_queued_ms
• io_issue_violations_total
• io_issue_delay_total_ms

Place à un petit script de test pour mesurer l’efficacité du dispositif.

CREATE RESOURCE POOL Pool_LowIOPS
WITH (
	min_cpu_percent=0, 
	max_cpu_percent=100, 
	min_memory_percent=0, 
	max_memory_percent=100, 
	cap_cpu_percent=100, 
	AFFINITY SCHEDULER = AUTO, 
	min_iops_per_volume=0, 
	max_iops_per_volume=0 );
GO


CREATE WORKLOAD GROUP Group_LowIOPS
USING Pool_LowIOPS
GO

CREATE WORKLOAD GROUP Group_LowIOPS
USING Pool_LowIOPS
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Les deux options min_iops_per_volume et max_iops_per_volume laissent entrevoir la possibilité de caper les nombre d’IO par volume tout comme la possibilité d’en réserver un minimum. La valeur indique que le throttling ne sera pas actif.

Une fois créé le pool de ressources et le groupe de charge, les compteurs de performances nous montrent que la limitation d’IO peut être appliqué autant sur la lecture que sur l’écriture :

Intéressant, mais j’aurais préféré une granularité plus fine permettant de différentier la lecture et l’écriture au niveau de la création du pool de ressources. peut être dans une prochaine version …

Afin de mener à bien le test, 2 utilisateurs vont être créés afin de distinguer 2 activités concurrentes niveau IO disque. Afin de forcer des lectures physiques, j’autorise l’utilisation du DROPCLEANBUFFERS à mes utilisateurs au travers de l’appartenance au groupe serveur sysadmin. Oui, je sais, c’est bien trop de droits, mais il s’agit d’un test.

CREATE LOGIN UserNormal WITH PASSWORD = 'pwd', CHECK_POLICY = OFF
CREATE LOGIN User_LowIO WITH PASSWORD = 'pwd', CHECK_POLICY = OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [UserNormal]
ALTER SERVER ROLE [sysadmin] ADD MEMBER [User_LowIO]
GO

Reste à présent à créer la fonction de classification et a l’activer pour le gouverneur de ressources.

CREATE FUNCTION fn_Classification_RessourceGovernor ()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
	DECLARE @val varchar(32)
	SET @val = 'default';
	
	if  'User_LowIO' = SUSER_SNAME() 
		SET @val = 'Group_LowIOPS';
	
	return @val;
END
GO

ALTER RESOURCE GOVERNOR 
WITH (CLASSIFIER_FUNCTION = dbo.fn_Classification_RessourceGovernor)
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Ensuite, je vais créer 2 fichiers .sql qui vont contenir une requête me permettant de simuler une charge de travail. Pour ne pas me retrouver bloqué par l’autre processus, et donc dissimuler l’impact de la gestion des IO par le gouverneur de ressources, j’ai opté pour 2 tables distinctes, en forçant un cluster index scan pour maximiser les IOs. J’en profiter pour aller à l’essentiel pour ne faire que des IOs en ne respectant pas le verrouillage éventuellement posé par d’autres processus.

--RQ_IO_Intensive1.sql
set nocount on
declare @i int
set @i = 43659	

while @i = 43659
begin
	SELECT count(*)
	FROM    AdventureWorks.sales.salesOrderHeader WITH (INDEX=0,NOLOCK)
	WHERE   SalesOrderID = @i 
	OPTION(MAXDOP 1)
	
	DBCC DROPCLEANBUFFERS

	set @i = @i - 1;
end

Je lance l’exécution des deux scripts en simultané dans deux invites de commande :

sqlcmd -S localhost -U UserNormal -P pwd -i « C:\temp\RQ_IO_Intensive1.sql »
sqlcmd -S localhost -U User_LowIO -P pwd -i « C:\temp\RQ_IO_Intensive2.sql »

Ensuite je vais graduellement limiter le nombre d’IOSs de chaque pool de ressources.

ALTER RESOURCE POOL Pool_LowIOPS
WITH (max_iops_per_volume = 80);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
Go


ALTER RESOURCE POOL Pool_LowIOPS
WITH (max_iops_per_volume = 50);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
Go


ALTER RESOURCE POOL Pool_LowIOPS
WITH (max_iops_per_volume = 20);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
Go

La DMV sys.dm_resource_governor_resource_pools permet de se rendre compte du travail effectué par le resource governor durant ce test:

Et le graphe issu de Perfmon nous permet de voir le résultat du throttling d’IOs :

On distingue les différentes pallier correspondant aux limitations d’IOs activées sur le groupe de ressources. Il apparait clairement que la limitation d’IO d’un groupe de ressources permet à l’autre groupe de ressources de bénéficier de plus de ressources et augmente ainsi le nombre de Reads/sec.

Si l’on zoome les paliers apparaissent alors plus distinctement :

On voit aussi que la charge de travail (mes requêtes de test) ne permettent pas de lisser les IOs, ou du moins d’avoir une courbe plus lisse. Ainsi, la valeur maximale n’est pas toujours atteinte, sauf lorsque le throttling est plus agressif, avec une valeur très faible. Je vais travailler sur ce point de manière a vous proposer rapidement un autre scénario. Cela dit, quel que soit le scénario de test, cela ne remplacera pas un serveur en production qui doit encaisser de très grosses lectures disque (Datawarehouse, Fast Track).

Qui veut mettre mettre SQL 2014 en production dès maintenant ?

Nul doute que cette fonctionnalité fera bien des heureux parmi les DBA et les admins système qui se cassent la tête sur des problématiques d’IO concurrents … Elle n’est pas liée à une condition de stress comme max_cpu_percent, et peut même permettre de lisser des IO entre 2 instances hébergées sur un même serveur. je préfère éviter cette configuration mais si vous n’avez pas le choix, alors le resource governor peut vous aider à prioriser vos flux.

Happy throttling !