Tracing or Logging Resource Governor classification function behavior in Sql Server 2008

Question

I'm trying to use the Resource Governor in SQL Server 2008 but I find it hard to debug the classification function and figure out what the input variables will have, i.e. does SUSER_NAME() contain the domain name? What does the APP_NAME() string look like?

It's also hard to verify that it's working correctly. What group did the function return? The only way I can see this is to fire up the performance monitor and watch unblinkingly for little blips in the right CPU counter.

Is there some way I can either run it in Debug mode, where I can set a breakpoint and step through and look at variable values, or can I at least do the old-school method of writing trace statements to a file so I can see what's going on?

Thanks...

Answer 1

It's been a while since you asked, but maybe you haven't gotten it all figured out yet.

I'm not aware of any way to log the classifier function, though you could probably use the SQL Server Profiler. You can verify that it is working correctly by looking at something other than the performance monitor.

You can use a simple query to see what SUSER_NAME() and APP_NAME() look like, at least for your connection:

SELECT 
    SUSER_NAME(), APP_NAME();

(Yes SUSER_NAME() will contain the domain name for accounts using Windows Authentication.)

You can query the DMVs (Dynamic Management Vies) to show you the current user connections along with the resource governor pool and work group that is assigned to it:

SELECT
    Sess.session_id,
    Sess.program_name,
    Sess.host_name,
    Sess.login_name,
    Sess.nt_domain,
    Sess.nt_user_name,
    Sess.original_login_name,
    RG_WG.pool_id,
    RG_P.name as Pool_Name,
    Sess.group_id,
    RG_WG.name as WorkGroup_Name
FROM sys.dm_exec_sessions Sess
    INNER JOIN sys.dm_resource_governor_workload_groups RG_WG
        ON Sess.group_id = RG_WG.group_id
    INNER JOIN sys.dm_resource_governor_resource_pools RG_P
        ON RG_WG.pool_id = RG_P.pool_id
WHERE
    Sess.is_user_process = 1;