Is there any way on DPA where we can see CPU usage by per user? in windows for SQL server

Is there any way on DPA where we can see CPU usage by per user? in windows for SQL server 

Parents
  • No, that is not possible as far as I know. 

    You can add your own custom queries to DPA monitoring. With queries you can show CPU usage per database but per user, haven't seen that.

  • Also advise me where i can setup this SQL query? provided by you

    in DPA alert or Reporting advise further 

    The quick poll that is the default one is as follows run one time per second by default. 

    SELECT s.loginame, db_name(s.dbid) name, s.hostname, 

           s.program_name, s.sql_handle, 

           s.stmt_start, s.stmt_end, s.spid, 

           CONVERT(smallint, s.waittype) waittype, s.lastwaittype,

           s.ecid, s.waittime, CONVERT(varchar(64), s.context_info) context_info,

           RTRIM(r.wait_resource) waitresource, s.blocked , 

           r.plan_handle, r.statement_start_offset, r.statement_end_offset, r.start_time, 

           r.query_plan_hash 

    FROM master..sysprocesses AS s WITH(NOLOCK) 

    LEFT OUTER JOIN sys.dm_exec_requests r WITH(NOLOCK) ON r.session_id = s.spid

    WHERE (s.dbid<>0 

           AND s.cmd<>'AWAITING COMMAND' 

           AND s.cmd NOT LIKE '%BACKUP%' 

           AND s.cmd NOT LIKE '%RESTORE%' 

           AND s.cmd NOT LIKE 'FG MONITOR%' 

           AND s.hostprocess > '' 

           AND s.spid>50 AND s.spid<>@@SPID) 

           AND lastwaittype NOT IN ('BROKER_EVENTHANDLER', 'CLR_MANUAL_EVENT', 'SLEEP_BPOOL_FLUSH', 'SLEEP_TASK', 'WAITFOR')

    The CPU query items you can find also in an XML file in DPA they run one time per minute. 

    <metricsQuery className="com.confio.idc.database.metrics.domain.queries.SingleValueMetricsQuery">
    <sql>
    <![CDATA[
    SELECT top 1
    100 - systemidle
    FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int') AS systemidle,
    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sqlprocessutilization, timestamp
    FROM (SELECT timestamp,
    CONVERT(XML, record) AS record
    FROM sys.dm_os_ring_buffers
    WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
    AND record LIKE '%%') AS x) AS y
    ORDER BY record_id DESC
    ]]></sql>
    <frequency>60</frequency>
    <queryTimeout>20</queryTimeout>
    <name>CPU Utilization</name>
    <supportedDatabaseList>
    <supportedDatabase>
    <databaseType>SQL Server</databaseType>
    <minimumVersion>9.00.3175</minimumVersion>
    <excludedParameters>
    <excludedParameter name="DATABASE_DEPLOYMENT" value="Amazon,Google" />
    </excludedParameters>
    </supportedDatabase>
    <supportedDatabase>
    <databaseType>Azure SQL Managed Instance</databaseType>
    <minimumVersion>9.00.3175</minimumVersion>
    </supportedDatabase>
    </supportedDatabaseList>
    </metricsQuery>

    <metricsQuery className="com.confio.idc.database.metrics.domain.queries.SingleValueMetricsQuery">
    <sql>
    <![CDATA[
    with processor_Info_cte as (
    select (cpu_count / hyperthread_ratio) as number_of_physical_cpus
    from sys.dm_os_sys_info
    ),
    utilization_cte as (
    select top 1 record_id
    ,sql_process_utilization
    ,system_idle
    ,100 - system_idle - sql_process_utilization as other_process_utilization
    from (
    select record.value('(./Record/@id)[1]', 'int') as record_id,
    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as system_idle,
    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as sql_process_utilization
    from (
    select convert(XML, record) as record
    from sys.dm_os_ring_buffers
    where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
    and record like '%<SystemHealth>%'
    ) as x
    ) as y order by record_id desc
    )

    select (case when other_process_utilization < 0 then
    sql_process_utilization/a.number_of_physical_cpus else
    sql_process_utilization end) as sql_process_utilization
    from utilization_cte cross apply (
    select (number_of_physical_cpus*1.0) as number_of_physical_cpus from processor_Info_cte) as a
    ]]>
    </sql>
    <frequency>60</frequency>
    <queryTimeout>20</queryTimeout>
    <name>Instance CPU Utilization</name>
    <supportedDatabaseList>
    <supportedDatabase>
    <databaseType>SQL Server</databaseType>
    <minimumVersion>10.0.0</minimumVersion>
    </supportedDatabase>
    <supportedDatabase>
    <databaseType>Azure SQL Managed Instance</databaseType>
    <minimumVersion>10.0.0</minimumVersion>
    </supportedDatabase>
    </supportedDatabaseList>
    </metricsQuery>

  • you don't setup those queries that was a query that is in the product as I was attempting to explain how the data is collected and why you can't match CPU to a active user. 

Reply Children
No Data