find % CPU from dm_os_performance

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » find % CPU from dm_os_performance_counters

find % CPU from dm_os_performance_counters

Rate Topic

Display Mode

Topic Options

Author

Message

Gopi Krishnan-468302

Posted Monday, September 10, 2012 3:52 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, February 23, 2013 2:31 AM
Points: 4, Visits: 184

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

crazyEmu

Posted Monday, November 05, 2012 2:33 PM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:30 PM
Points: 39, Visits: 592

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

itsmemegamind

Posted Thursday, November 22, 2012 4:45 AM

Grasshopper

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 »

Permissions