How It Works: Resource Governor 'INTERNAL' Group

  • Article

From: Customer
Sent: Friday, February 20, 2009 2:04 PM

I have seen sometimes a user connection classified as Internal when I run the following query:

USE master;
SELECT sess.session_id, cast(sess.login_name as varchar(40)) login_name,
cast(Host_Name as varchar(30)) HostName, grps.name
FROM sys.dm_exec_sessions AS sess
JOIN sys.dm_resource_governor_workload_groups AS grps
    ON sess.group_id = grps.group_id
WHERE session_id > 50;

 

ANSWER(S)

There are several reasons you might see 'internal' as the group.  SQL Server uses the internal pool/group to complete 'critical' operations.   The production of a trace event is an example.  You would not want a query to fail because a memory limitation was encountered producing a trace event.  Especially is C2 auditing is enabled as this would result in service shutdown.

SQL Server can switch the request in and out of the internal group as required.   The following is a non-exhaustive list of these areas.

  • Trace Event Production
  • Login Processing
  • Classifier Execution
  • sp_reset_connection Execution
  • System tasks such as Resource Monitor
  • DAC Connection - always internal
  • Task Binding/Startup Processing

It is common to run the query and receive 'internal' as the group name.   On a busy server logins and tasks are being processed and as they are initially handled they will use the internal group.   Web servers often use connection pooling which can generate high rates of sp_reset_connection activity that use the internal group.

It is also important to note the query plan used for the example query.  The query involves table valued function(s) so it is possible that timing plays a factor in the join activity.   Each table is produced by the separate functions and then joined so on a busy server the data could be slightly different at the exact time of the join.

|--Hash Match(Inner Join, HASH:(SYSSESSIONS.[group_id])=(DM_RG_GROUPS.[group_id]))                                                                        
     |--Filter(WHERE:(SYSSESSIONS.[session_id]>(50)))                                                                                                     
     |    |--Compute Scalar(DEFINE:([Expr1000]=CONVERT(varchar(30),SYSSESSIONS.[login_name],0), [Expr1002]=CONVERT(varchar(30),SYSSESSIONS.[host_name],0)))
     |         |--Table-valued function                                                                                                                   
     |--Compute Scalar(DEFINE:([Expr1001]=CONVERT(varchar(30),DM_RG_GROUPS.[name],0)))                                                                    

The internal group state is used for short periods so you should not see the internal group assignment last long for requests with a primary assignment to a group other than internal.

Bob Dorr
SQL Server Principal Escalation Engineer