syntaxhelp.com Your online cheat sheet!

Enable Resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Disable Resource Governor

ALTER RESOURCE GOVERNOR DISABLE;
GO

Reload the configuration of resource governor after any change

-- Reload configuration after any parameter change about workloads, resource pools ...
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Initialise statistics of resource governor's use

ALTER RESOURCE GOVERNOR RESET STATISTICS;
GO

Visualisation of resource governor's state

SELECT * 
FROM sys.resource_governor_configuration;
GO

Visualisation of resource governor's configuration (workloads and resource pools)

SELECT 
	p.name AS pool_name,
	p.min_cpu_percent AS pool_min_cpu_percent,
	p.max_cpu_percent AS pool_max_cpu_percent,
	g.name AS group_name,
	g.importance AS group_importance
FROM sys.resource_governor_resource_pools AS p
INNER JOIN sys.resource_governor_workload_groups AS g
ON p.pool_id = g.pool_id;
GO

Knowing the distribution of requests in the workload groups

SELECT 
	p.name AS pool_name,
	g.name AS group_name,
	r.session_id,
	t.text AS sql_txt
FROM sys.resource_governor_resource_pools AS p
INNER JOIN sys.resource_governor_workload_groups AS g
ON p.pool_id = g.pool_id
INNER JOIN sys.dm_exec_requests AS r
ON r.group_id = g.group_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE p.name <> 'internal'
ORDER BY p.name, g.name;
GO

Knowing the distribution of the user and requests for each resource pool and workload group

SELECT 
	w.group_id,
	w.name AS groupe_name,
	active_request_count,
	COALESCE(s.nb_users, 0) AS nb_users
FROM sys.dm_resource_governor_workload_groups AS w
LEFT JOIN 
(
	SELECT	
		group_id,
		COUNT(*) AS nb_users
	FROM sys.dm_exec_sessions
	GROUP BY group_id
) AS s
ON w.group_id = s.group_id;
GO

What's the use of resource pools

WITH total_resources
AS
(
	SELECT 
		SUM(total_cpu_usage_ms) AS total_cpu_usage_ms,
		SUM(cache_memory_kb) AS total_cache_memory_kb,
		SUM(compile_memory_kb) AS total_compile_memory_kb,
		SUM(used_memory_kb) AS total_used_memory_kb
	FROM sys.dm_resource_governor_resource_pools
)
SELECT
	p.name AS pool_name, 
	p.total_cpu_usage_ms * 100.0 / t.total_cpu_usage_ms AS total_cpu_usage,
	p.cache_memory_kb * 100.0 / t.total_cache_memory_kb AS cache_memory,
	p.compile_memory_kb * 100.0 / t.total_compile_memory_kb AS compile_memory,
	p.used_memory_kb * 100.0 / t.total_used_memory_kb AS used_memory
FROM sys.dm_resource_governor_resource_pools AS p
CROSS JOIN total_resources AS t;
GO