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: Friday, January 03, 2014 1:44 PM
Points: 4, Visits: 205
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 05, 2012 2:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:52 PM
Points: 40, Visits: 669
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 04, 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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse