SQL Resource Governor Usage & Usage By Database

[djlaney3]

Is it possible to add info about resource governors? Currently in an in house app, I query the perfmon counters to get the CPU, Memory, and IOPS current usage AND target usage as defined by the resource governors. I also have data for databases summarized by what is running. It is basically a summarized view of whoIsActive but grouped by database.

[NickCraver]

We don't use this internally, so I don't have the counters to test with - can you provide some more detail on what you're querying? I'm not at all against adding a section to the instance dashboard if this functionality is enabled on a particular instance.

[djlaney3]

Here are the scripts I use. The first one gives a great snapshot of what is happening by database: select databaseName = db_name(ses.database_id), runningProcesses = coalesce(sp.running, 0), waitingProcesses = coalesce(sp.wating, 0), totalSessions = count(distinct ses.session_id), totalSystemSessions = sum(case when ses.is_user_process = 0 then 1 else 0 end), totalUserSessions = sum(case when ses.is_user_process = 1 then 1 else 0 end), totalRequests = count(req.request_id), totalSystemRequests = sum(case when ses.is_user_process = 0 and req.request_id is not null then 1 else 0 end), totalUserRequests = sum(case when ses.is_user_process = 1 and req.request_id is not null then 1 else 0 end), totalConnections = count(distinct con.connection_id), totalApplications = count(distinct ses.program_name), totalConnectedComputers = count(distinct con.client_net_address), totalWorkLoadGroups = count(distinct wg.pool_id), totalResourcePools = count(distinct rp.name), totalBlockedRequests = sum(case when req.blocking_session_id > 0 then 1 else 0 end) from master.sys.dm_exec_sessions ses left outer join master.sys.dm_exec_requests req on (ses.session_id = req.session_id) left outer join master.sys.resource_governor_workload_groups as wg on (ses.group_id = wg.group_id) left outer join master.sys.resource_governor_resource_pools as rp on (wg.pool_id = rp.pool_id) left outer join master.sys.dm_exec_connections con on (con.session_id = ses.session_id) left outer join ( select databaseName = db_name(bsp.dbid), running = sum(case when bsp.waittime = 0 then 1 else 0 end), wating = sum(case when bsp.waittime = 0 then 0 else 1 end) from master.sys.sysprocesses bsp where bsp.status <> 'sleeping' and bsp.dbid > 0 group by bsp.dbid) sp on (db_name(ses.database_id) = sp.databaseName ) where ses.database_id > 0 group by db_name(ses.database_id), ses.database_id, coalesce(sp.running, 0), coalesce(sp.wating, 0) order by ses.database_id

This second one is for the resource governors. This will only work on Sql 2014 due to the IOPS governors, if you remove that part it would work on Sql 2012.

    declare @totalServerMemory bigint
    select @totalServerMemory = opc.cntr_value      from master.sys.dm_os_performance_counters opc      where opc.object_name = 'SQLServer:Memory Manager'          and opc.counter_name = 'Total Server Memory (KB)';              with perfMon(objectName, instanceName, counterName, value)          as (        select objectName = opc.object_name,            instanceName = opc.instance_name,           counterName = opc.counter_name,         value = opc.cntr_value      from master.sys.dm_os_performance_counters opc      where opc.object_name = 'SQLServer:Resource Pool Stats'         and opc.counter_name in ('CPU usage %','CPU usage % base','CPU usage target %','Used memory (KB)','Target memory (KB)','Disk Read IO/sec','Disk Write IO/sec')      )
    select instanceName,            cpuUsage = cast([Cpu Usage %] as float) / cast([CPU usage % base] as float),            cpuUsageTarget = cast([Cpu Usage Target %] as float) / 100,         usedMemory = cast([Used memory (KB)] as float) / @totalServerMemory,            targetMemory = cast([Target memory (KB)] as float) / @totalServerMemory,            diskIops = [Disk Read IO/sec] + [Disk Write IO/sec]     into #tb_stats      from (select tmp.instanceName,                  tmp.counterName,                    tmp.value               from perfMon tmp) pm        pivot (max(pm.value) for pm.counterName in ([Cpu Usage %],[CPU usage % base],[Cpu Usage Target %],[Used memory (KB)],[Target memory (KB)],[Disk Read IO/sec],[Disk Write IO/sec])) pv       order by instanceName

    select instanceName = s.instanceName,           cpuUsage = s.cpuUsage,          cpuUsageTarget = s.cpuUsageTarget,          usedMemory = s.usedMemory,          targetMemory = s.targetMemory,          diskIops = s.diskIops,          maxIops = tmp.maxIops       from #tb_stats s            inner join (select instanceName = rp.name,                          maxIops = rp.max_iops_per_volume                        from master.sys.resource_governor_resource_pools rp) tmp on (s.instanceName = tmp.instanceName)     order by s.instanceName
    drop table #tb_stats

Date: Wed, 23 Mar 2016 16:47:15 -0700 From: notifications@github.com To: Opserver@noreply.github.com CC: djlaney3@hotmail.com Subject: Re: [Opserver] SQL Resource Governor Usage & Usage By Database (#157)

We don't use this internally, so I don't have the counters to test with - can you provide some more detail on what you're querying? I'm not at all against adding a section to the instance dashboard if this functionality is enabled on a particular instance.

— You are receiving this because you authored the thread. Reply to this email directly or view it on GitHub

[djlaney3]

Here is a screen shot of my output. I can easily see which DBs are being used as well as what governors are doing the work.

[NickCraver]

Formatting here for readability soon as I can visualize this on a dashboard well:

SELECT databaseName = db_name(ses.database_id),
    runningProcesses = coalesce(sp.running, 0),
    waitingProcesses = coalesce(sp.wating, 0),
    totalSessions = count(DISTINCT ses.session_id),
    totalSystemSessions = sum(CASE WHEN ses.is_user_process = 0 THEN 1 ELSE 0 END),
    totalUserSessions = sum(CASE WHEN ses.is_user_process = 1 THEN 1 ELSE 0 END),
    totalRequests = count(req.request_id),
    totalSystemRequests = sum(CASE WHEN ses.is_user_process = 0
                AND req.request_id IS NOT NULL THEN 1 ELSE 0 END),
    totalUserRequests = sum(CASE WHEN ses.is_user_process = 1
                AND req.request_id IS NOT NULL THEN 1 ELSE 0 END),
    totalConnections = count(DISTINCT con.connection_id),
    totalApplications = count(DISTINCT ses.program_name),
    totalConnectedComputers = count(DISTINCT con.client_net_address),
    totalWorkLoadGroups = count(DISTINCT wg.pool_id),
    totalResourcePools = count(DISTINCT rp.NAME),
    totalBlockedRequests = sum(CASE WHEN req.blocking_session_id > 0 THEN 1 ELSE 0 END)
FROM master.sys.dm_exec_sessions ses
LEFT JOIN master.sys.dm_exec_requests req ON (ses.session_id = req.session_id)
LEFT JOIN master.sys.resource_governor_workload_groups AS wg ON (ses.group_id = wg.group_id)
LEFT JOIN master.sys.resource_governor_resource_pools AS rp ON (wg.pool_id = rp.pool_id)
LEFT JOIN master.sys.dm_exec_connections con ON (con.session_id = ses.session_id)
LEFT JOIN (
    SELECT databaseName = db_name(bsp.dbid),
        running = sum(CASE WHEN bsp.waittime = 0 THEN 1 ELSE 0 END),
        wating = sum(CASE WHEN bsp.waittime = 0 THEN 0 ELSE 1 END)
    FROM master.sys.sysprocesses bsp
    WHERE bsp.STATUS <> 'sleeping'
        AND bsp.dbid > 0
    GROUP BY bsp.dbid
    ) sp ON (db_name(ses.database_id) = sp.databaseName)
WHERE ses.database_id > 0
GROUP BY db_name(ses.database_id),
    ses.database_id,
    coalesce(sp.running, 0),
    coalesce(sp.wating, 0)
ORDER BY ses.database_id

This second one is for the resource governors. This will only work on Sql 2014 due to the IOPS governors, if you remove that part it would work on Sql 2012.

DECLARE @totalServerMemory BIGINT

SELECT @totalServerMemory = opc.cntr_value
FROM master.sys.dm_os_performance_counters opc
WHERE opc.object_name = 'SQLServer:Memory Manager'
    AND opc.counter_name = 'Total Server Memory (KB)';

WITH perfMon (
    objectName,
    instanceName,
    counterName,
    value)
AS (
    SELECT objectName = opc.object_name,
        instanceName = opc.instance_name,
        counterName = opc.counter_name,
        value = opc.cntr_value
    FROM master.sys.dm_os_performance_counters opc
    WHERE opc.object_name = 'SQLServer:Resource Pool Stats'
        AND opc.counter_name IN (
            'CPU usage %',
            'CPU usage % base',
            'CPU usage target %',
            'Used memory (KB)',
            'Target memory (KB)',
            'Disk Read IO/sec',
            'Disk Write IO/sec'))
SELECT instanceName,
    cpuUsage = cast([Cpu Usage %] AS FLOAT) / cast([CPU usage % base] AS FLOAT),
    cpuUsageTarget = cast([Cpu Usage Target %] AS FLOAT) / 100,
    usedMemory = cast([Used memory (KB)] AS FLOAT) / @totalServerMemory,
    targetMemory = cast([Target memory (KB)] AS FLOAT) / @totalServerMemory,
    diskIops = [Disk Read IO/sec] + [Disk Write IO/sec]
INTO #tb_stats
FROM (
    SELECT tmp.instanceName,
        tmp.counterName,
        tmp.value
    FROM perfMon tmp) pm
pivot(max(pm.value) FOR pm.counterName IN (
            [Cpu Usage %],
            [CPU usage % base],
            [Cpu Usage Target %],
            [Used memory (KB)],
            [Target memory (KB)],
            [Disk Read IO/sec],
            [Disk Write IO/sec])) pv
ORDER BY instanceName

SELECT instanceName = s.instanceName,
    cpuUsage = s.cpuUsage,
    cpuUsageTarget = s.cpuUsageTarget,
    usedMemory = s.usedMemory,
    targetMemory = s.targetMemory,
    diskIops = s.diskIops,
    maxIops = tmp.maxIops
FROM #tb_stats s
INNER JOIN (
    SELECT instanceName = rp.NAME,
        maxIops = rp.max_iops_per_volume
    FROM master.sys.resource_governor_resource_pools rp
    ) tmp ON (s.instanceName = tmp.instanceName)
ORDER BY s.instanceName

DROP TABLE #tb_stats

[djlaney3]

An additional note: On the first query by database, if there are any blockers, I highlight the row in red to make it visually easy to see that there is blocking going on.

[djlaney3]

Any chance this can get some priority? I could implement it myself, but want it to match your coding schemes.

[djlaney3]

@NickCraver I'd be happy to start on this. I'd like to see it on the instance tab, but not sure what your thoughts are. Can you comment on it? It should be a pretty easy add that I can do being queries.