Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

find % CPU from dm_os_performance_counters Expand / Collapse
Author
Message
Posted Monday, September 10, 2012 3:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 2:41 PM
Points: 4, Visits: 219
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
Post #1357039
Posted Monday, November 5, 2012 2:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 12, 2014 10:23 PM
Points: 40, Visits: 678
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

Post #1381267
Posted Thursday, November 22, 2012 4:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 4, 2013 12:43 AM
Points: 10, 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 !!!!!
Post #1387841
Posted 2 days ago @ 7:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:25 AM
Points: 13, Visits: 143
"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.
Post #1597716
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse