Know What Sessions Were Assigned to Each Workload Groups

Applies to: Microsoft SQL Server 2008 RC 0

The following query shows what workload group and resource pool in Resource Governor was assigned to each session on a Microsoft SQL Server instance by a classifier function.

You can use the following query to find out the name of the workload group and the resource pool, in Resource Governor, that the classifier function assigned each session of a Microsoft SQL Server Instance.
 

SELECT    session_id as 'Session ID',
              [host_name] as 'Host Name',
              [program_name] as 'Program Name',
              nt_user_name as 'User Name',
              SDRGWG.[Name] as 'Group Assigned',
              DRGRP.[name] as 'Pool Assigned'
FROM sys.dm_exec_sessions SDES
        INNER JOIN sys.dm_resource_governor_workload_groups SDRGWG
                ON SDES.group_id = SDRGWG.group_id
        INNER JOIN sys.dm_resource_governor_resource_pools DRGRP
                ON SDRGWG.pool_id = DRGRP.pool_id

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.