May 5, 2016 at 8:58 am
We are testing out the use of Resource Governor on SQL 2014 to ensure that end-user Ad Hoc queries don't hog the server resources and that we retain resources for OLTP users.
I've written a classifier function which makes every attempt to lump sessions into the appropriate group, but we select the appropriate group based on SUSER_NAME(). Occasionally, we encounter a new service account that hasn't been identified in the classifier function. or we get questioned about what resource group a particular account is running in.
Does anyone know of a way to identify which resource group a given session is running in?
Thanks.
Larry
May 5, 2016 at 9:24 am
sys.dm_exec_sessions has a group_id column, which is the id of the workload group to which that session is assigned. You can join that with the appropriate resource governor DMVs.
This is the basic idea; additional columns can be brought in as necessary.
SELECT es.session_id,
workload_group=wg.name,
resource_pool=rp.name
FROM sys.dm_exec_sessions es
INNER JOIN
sys.dm_resource_governor_workload_groups wg ON es.group_id=wg.group_id
INNER JOIN
sys.dm_resource_governor_resource_pools rp ON wg.pool_id=rp.pool_id;
Cheers!
May 9, 2016 at 11:09 am
Jacob - Many thanks! That seemed to get me where I wanted to go...many thanks!
The final query looks like this:
-- shows which users are in which RG pools
SELECT es.session_id, es.login_name,
workload_group=wg.name,
resource_pool=rp.name
FROM sys.dm_exec_sessions es
INNER JOIN sys.dm_resource_governor_workload_groups wg
ON es.group_id=wg.group_id
INNER JOIN sys.dm_resource_governor_resource_pools rp
ON wg.pool_id=rp.pool_id
WHERE es.login_name <> 'sa'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply