Getting the CPU Usage per Resource Pool

Question

Good day,
I am trying to query the CPU Usage per Resource Pool using the query below but I'm getting a CPU usage of more than 100% . Could you kindly verify if I'm missing something?

with ResPoolCpu as
(
select instance_name as pool_name,
counter_name,
cntr_value
from sys.dm_os_performance_counters
where object_name like '%Resource Pool Stats%'
and counter_name like 'CPU usage % %' escape ''
),

ResPoolCpuTotal as
(
select rcp1.pool_name,
convert
(
decimal(5,2),
(rcp1.cntr_value * 1.0 / rcp2.cntr_value) * 100
) as cpu_usage
from ResPoolCpu rcp1
inner join ResPoolCpu rcp2 on rcp1.pool_name = rcp2.pool_name
where rcp1.counter_name not like '%base%' and rcp2.counter_name like '%base%'
)
select * from ResPoolCpuTotal

Answer 1

Hi @MichelleSantos-5814,

Sorry that I’m not sure if the formula you used is correct.
You can try to add SQLSERVER:Resource Pool Stats: CPU Usage % and select <All instances> in the Performance Monitor.

And then you can see CPU Usage % per Resource Pool.

Please refer to this blog which might help.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @AmeliaGu-MSFT
Thank you for accommodating my inquiry. Is it possible to query this information from SSMS?

Answer 3

I think the problem is here:

where rcp1.counter_name not like '%base%' or rcp2.counter_name like '%base%'

OR should be AND.

This becomes obvious if you run the query like this:

with ResPoolCpu as
(
select instance_name as pool_name,
counter_name,
cntr_value
from sys.dm_os_performance_counters
where object_name like '%Resource Pool Stats%'
and counter_name like 'CPU usage *% %' escape '*'
)

select *,
convert
(
decimal(5,2),
(rcp1.cntr_value * 1.0 / nullif(rcp2.cntr_value, 0))* 100
) as cpu_usage
from ResPoolCpu rcp1
inner join ResPoolCpu rcp2 on rcp1.pool_name = rcp2.pool_name
where rcp1.counter_name not like '%base%' and rcp2.counter_name like '%base%'

You get too many combinations. Note also the addition of nullif for the divisor to avoid divide-by-zero errors.

Answer 4

Hi @Erland Sommarskog
You are correct, it should be AND instead of OR. Still I'll getting CPU > 100% though.

Answer 5

Hi MichelleSantos-5814,
Sorry for my delay.
The results you posted look strange.
This my result:

And the value of CPU Usage per Resource Pool is the same as the value in the Performance Monitor.
Could you please check the counter SQLSERVER:Resource Pool Stats: CPU Usage % in the Performance Monitor?

Best Regards,
Amelia