September 10, 2012 at 3:52 pm
Hi ,
Iam collecting a baseline report for one of the sql server instance, where i have only instance level access, dont have access to run the perfmon. Hence i collected details from the DMV - but iam getting values like 1023,2010, some times 0 , 1. How to get the correct percentage of CPU utilised by sql server from this DMV.
SQLServer:Resource Pools CPU usage %
SELECT *
FROM [master].[sys].[dm_os_performance_counters]
WHERE
([object_name] = 'SQLServer:Resource Pool Stats' AND [counter_name] = 'CPU usage %')
and instance_name ='default'
Thanks
GKRISH
November 5, 2012 at 2:33 pm
Sorry this is a old post few months old. Thought I would post the answer in case anyone else ends up here due to a google search.
This page has a good explanation of how to query this dmv : http://rtpsqlguy.wordpress.com/2009/08/11/sys-dm_os_performance_counters-explained/
I would try something like:
select perfCount.object_name, perfCount.counter_name,
CASE WHEN perfBase.cntr_value = 0
THEN 0
ELSE (CAST(perfCount.cntr_value AS FLOAT) / perfBase.cntr_value) * 100
END AS cntr_Value
from
(select * from sys.dm_os_performance_counters
where object_Name = 'SQLServer:Resource Pool Stats'
and counter_name = 'CPU usage %' ) perfCount
inner join
(select * from sys.dm_os_performance_counters
where object_Name = 'SQLServer:Resource Pool Stats'
and counter_name = 'CPU usage % base') perfBase
on perfCount.Object_name = perfBase.object_name
Blog: http://crazyemu.wordpress.com/
Twit: @crazySQL
November 22, 2012 at 4:45 am
You can also go for a third party tool for the same purpose which will generate a comprehensive report of CPU usage disk space etc which will be helpful to improve the productivity of SQL server !!!!!
July 30, 2014 at 7:25 am
"You can also go for a third party tool for the same purpose"
Why? SQL Server has some reporting built into it. 😎
And can you be certain that the tool you've selected accurately reports on the problem?
I like the other answer that gives me a script more than a tool because you get to dig into what's going on. As soon as the tool company raises prices to where you can't buy them or it goes out of business, you're out of business. Have to take the time to learn these internals. More I look around, the more I see folks dependent on tools and not understanding what goes on underneath. It's a big reason why I love this site. We dig in here.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply