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
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.
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.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 190,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.