Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


find % CPU from dm_os_performance_counters


find % CPU from dm_os_performance_counters

Author
Message
Gopi Krishnan-468302
Gopi Krishnan-468302
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 251
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
crazyEmu
crazyEmu
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 686
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
itsmemegamind
itsmemegamind
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 45
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 !!!!!
subsonic-abercrombie
subsonic-abercrombie
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 156
"You can also go for a third party tool for the same purpose"

Why? SQL Server has some reporting built into it. Cool
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search